Display Value from Sheet1-A1:A10 to Sheet2-B1

[sam]

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.

 

[sam]

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.

 

[Satish]

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.








- 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

 

[Rick Rothstein]

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

 

[Satish]

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


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

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

 

[Chan]

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)