Macro to Search for specific text and when found return the adjacent cell content into cell A1

[eddster]

Hi All,

I have a workbook with lots of worksheets with data that is generally the same but not always located in the same cell reference in each worksheet.

I would like to return the content into cell of "A1" of the cell that is adjacent (i.e. 1 cell to the right) of the cell that contains the word "Room Number"

so in essence a macro that searches every worksheet and if it finds the words "room number" in that worksheet it includes the room number in cell A1 of said worksheet

so for example

if the macro find the words "Room Number" in cell N3 it returns the value from cell O3 (lets say for example that is "BL001" ) and inputs that value "BL001" into cell A1

Any help much appreciated

Thank you

 

[DanteAmor]

Try this

Sub room_number()
  Dim sh As Worksheet
  Dim f As Range
 
  For Each sh In Sheets
    Set f = sh.Cells.Find("room number", , xlValues, xlPart, , , False)
    If Not f Is Nothing Then
      sh.Range("A1").Value = f.Offset(, 1).Value
    End If
  Next
End Sub

Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[eddster]

Excellent. Seems to work perfectly. many thanks

 

[eddster]

DanteAmor

would it be possible to extend this macro so that it were to do the same looking in this case for the word "items" (i can do this bit )

but when it finds the word "items" it then returns say the first 30 rows directly below the word items and if possible also the adjacent 2 columns to the right

e.g. table below (lets pretend it has 30 rows of info below "Items")

and it could transpose them to cells S1:U30 on that sheet for example

so i would end up with the same table but at least I know exactly where the info is located on every sheet

Items	colour	quantity
bucket	green	2
spade	red	4
ball	yellow	10
etc	colour	quant
etc	colour	quant

e right

 

[mohadin]

Hi
Possibly

Sub room_number()
    Dim sh As Worksheet
    Dim f As Range
    For Each sh In Sheets
        Set f = sh.Cells.Find("Item", , , 1)
        If Not f Is Nothing Then sh.Range("S1").Resize(30, 3).Value = f.Resize(30, 3).Value
    Next
End Sub

 

[Peter_SSs]

Excellent. Seems to work perfectly. many thanks

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

 

[eddster]

Thanks all

 

[DanteAmor]

DanteAmor
would it be possible to extend this macro so that it were to do the same looking in this case for the word "items" (i can do this bit )
but when it finds the word "items" it then returns say the first 30 rows directly below the word items and if possible also the adjacent 2 columns to the right

e.g. table below (lets pretend it has 30 rows of info below "Items")
and it could transpose them to cells S1:U30 on that sheet for example

Here my macro with the change you require.

If you run the macro repeatedly it will not update the data in cells S1:U30, so the search will need to have the following parameters:

Sub room_number()
  Dim sh As Worksheet
  Dim f As Range
 
  For Each sh In Sheets
    Set f = sh.Cells.Find("items", sh.Range("A1"), xlValues, xlPart, xlByColumns, , False)
    If Not f Is Nothing Then
      sh.Range("S1:U30").Value = f.Resize(30, 3).Value
    End If
  Next
End Sub

Try and tell me.