I would like a VBA code that checks each cell in column I from I5 to I34 in sheet 'Start…
- User
I would like a VBA code that checks each cell in column I from I5 to I34 in sheet 'Start Page'. Each cell that has text in it, is the name of another sheet in the same workbook. For each cell in column I from I5 to I34 in sheet 'Start Page' that is not empty it will check the corresponding sheet name and in the corresponding sheet, search column B for the exact word "Service". On the same row where the word "Service" is found, it will take the value in column H of the same row where the match was found, and then put that value in sheet 'Start Page' column J of the same row with column I that matches the sheet name from where the valve was found.
- Assistant
Here's the VBA code that should accomplish what you're asking:
Sub FindServiceValue()
Dim startSheet As Worksheet Dim searchSheet As Worksheet Dim searchRange As Range Dim searchValue As String Dim foundCell As Range
Set startSheet = ThisWorkbook.Sheets("Start Page")
For i = 5 To 34 If Not IsEmpty(startSheet.Range("I" & i).Value) Then Set searchSheet = ThisWorkbook.Sheets(startSheet.Range("I" & i).Value) Set searchRange = searchSheet.Range("B:B") searchValue = "Service"
Set foundCell = searchRange.Find(searchValue, LookIn:=xlValues, LookAt:=xlWhole) If Not foundCell Is Nothing Then startSheet.Range("J" & i).Value = foundCell.Offset(0, 5).Value End If End IfNext i
End Sub
You can run this code by pressing Alt + F8 to open the Macro dialog box, selecting the "FindServiceValue" macro, and clicking "Run".
- User
Thanks, the code ran accurately and without errors.
- Assistant
You're welcome! I'm glad the code worked for you. Let me know if you need any further assistance.
- User
In some of the sheets searched, there are more than one occurence of the word Service. The vba code you gave me only works with one occurence of the word. Can you modify the code to put each occurrence of the word Service found, into the next row of column J in the 'Start Page'
- Assistant
Sure! Here is the modified code which will now find and output all the occurrences of the word "Service" in the search range:
Sub FindServiceValue()
Dim startSheet As Worksheet Dim searchSheet As Worksheet Dim searchRange As Range Dim searchValue As String Dim foundCells As Range Dim foundCell As Range Dim outputRow As Long
Set startSheet = ThisWorkbook.Sheets("Start Page") outputRow = 5 'starting row for output in column J
For i = 5 To 34 If Not IsEmpty(startSheet.Range("I" & i).Value) Then Set searchSheet = ThisWorkbook.Sheets(startSheet.Range("I" & i).Value) Set searchRange = searchSheet.Range("B:B") searchValue = "Service"
Set foundCells = searchRange.Find(searchValue, LookIn:=xlValues, LookAt:=xlWhole) If Not foundCells Is Nothing Then For Each foundCell In foundCells startSheet.Range("J" & outputRow).Value = foundCell.Offset(0, 5).Value outputRow = outputRow + 1 Next foundCell End If End IfNext i
End Sub
This code keeps track of the row number in the 'Start Page' where the output should be written. Each time a match is found, it writes the value from column H of the same row as the match to the output row in column J of the 'Start Page', and then increments the output row number for the next output value.