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

  • Thread starter Thread starter sam
  • Start date Start date
S

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.
 
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.
 
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 -
Click to expand...

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
Click to expand...

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.
Click to expand...

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
 
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
Click to expand...

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)
 
Back
Top