Is is possible to display a value from a range in sheet1 in a cell in Sheet2?

For eg:
In Sheet1, I can get a value in any cell between A1:A10 randomly, the value
is a mix of Numbers and alphabers, such as A1i, C3e, D9f etc
And I want to display this value in Sheet2 Cell B1

How can this be done?

Thanks in Advance.

So basically, Scan Sheet1-A1:A10 and locate a cell that has any value
displayed(Only one cell will display a value), then display that value in
Sheet2-B1

How can this be done?

Thanks in Advance.


"sam" wrote:

> Is is possible to display a value from a range in sheet1 in a cell in Sheet2?
>
> For eg:
> In Sheet1, I can get a value in any cell between A1:A10 randomly, the value
> is a mix of Numbers and alphabers, such as A1i, C3e, D9f etc
> And I want to display this value in Sheet2 Cell B1
>
> How can this be done?
>
> Thanks in Advance.

On Aug 28, 1:30*am, sam <s...@discussions.microsoft.com> wrote:
> So basically, Scan Sheet1-A1:A10 and locate a cell that has any value
> displayed(Only one cell will display a value), then display that value in
> Sheet2-B1
>
> How can this be done?
>
> Thanks in Advance.
>
>
>
> "sam" wrote:
> > Is is possible to display a value from a range in sheet1 in a cell in Sheet2?
>
> > For eg:
> > In Sheet1, I can get a value in any cell between A1:A10 randomly, the value
> > is a mix of Numbers and alphabers, such as A1i, C3e, D9f etc
> > And I want to display this value in Sheet2 Cell B1
>
> > How can this be done?
>
> > Thanks in Advance.- Hide quoted text -
>
> - Show quoted text -

Hi,

This can be done using this code -

Option Explicit
Sub findtext()

Dim k As Integer
With Worksheets(1)
For k = 1 To 10 'for the first 10 columns
If .Cells(1, k).Value <> "" Then
Worksheets(2).Cells(2, 1).Value = .Cells(1, k).Value
MsgBox "Value copied"
Exit For
Else
'Go to next column
End If
Next k
End With

If k = 11 Then
MsgBox "Didnt find any value"
End If

End Sub

You can extend this to any range you want. If you want the code to
check in the next row (i.e., A2 to A10), then you need to add another
For loop.

HTH,
Regards,
Satish

> Sub findtext()
>
> Dim k As Integer
> With Worksheets(1)
> For k = 1 To 10 'for the first 10 columns
> If .Cells(1, k).Value <> "" Then
> Worksheets(2).Cells(2, 1).Value = .Cells(1, k).Value
> MsgBox "Value copied"
> Exit For
> Else
> 'Go to next column
> End If
> Next k
> End With
>
> If k = 11 Then
> MsgBox "Didnt find any value"
> End If
>
> End Sub

If that is actually what the OP wants, then you can do it without looping...

Sub findtext()
Worksheets(1).Range("A2").Resize(, 10).Copy Worksheets(2).Range("A1")
End Sub

> You can extend this to any range you want. If you want the code
> to check in the next row (i.e., A2 to A10), then you need to add
> another For loop.

If the user wants to do more than one row, that too can be done without any
looping...

Sub findtext()
Worksheets(1).Range("A2").Resize(9, 10).Copy Worksheets(2).Range("A1")
End Sub

--
Rick (MVP - Excel)

On Aug 28, 9:58*am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> > Sub findtext()
>
> > Dim k As Integer
> > * * With Worksheets(1)
> > * * * * For k = 1 To 10 *'for the first 10 columns
> > * * * * * * If .Cells(1, k).Value <> "" Then
> > * * * * * * * *Worksheets(2).Cells(2, 1).Value = .Cells(1, k).Value
> > * * * * * * * * MsgBox "Value copied"
> > * * * * * * * * Exit For
> > * * * * * * Else
> > * * * * * * * * 'Go to next column
> > * * * * * * End If
> > * * * * Next k
> > * * End With
>
> > * * If k = 11 Then
> > * * * * MsgBox "Didnt find any value"
> > * * End If
>
> > End Sub
>
> If that is actually what the OP wants, then you can do it without looping....
>
> Sub findtext()
> * Worksheets(1).Range("A2").Resize(, 10).Copy Worksheets(2).Range("A1")
> End Sub
>
> > You can extend this to any range you want. If you want the code
> > to check in the next row (i.e., A2 to A10), then you need to add
> > another For loop.
>
> If the user wants to do more than one row, that too can be done without any
> looping...
>
> Sub findtext()
> * Worksheets(1).Range("A2").Resize(9, 10).Copy Worksheets(2).Range("A1")
> End Sub
>
> --
> Rick (MVP - Excel)- Hide quoted text -
>
> - Show quoted text -

Absolutely Rick!

I've a habit of using loops (And I know its not that good). I hope to
learn from posts like yours.

regards,
Satish

Why not just use the following in Sheet2-B1?

=CONCATENATE(Sheet1!A1,Sheet1!A2,Sheet1!A3,Sheet1!A4,Sheet1!A5,Sheet1!A6,Sheet1!A7,Sheet1!A8,Sheet1!A9,Sheet1!A10)