I want the output of this code bellow to start from row 20 of column B in the sheet…
- User
I want the output of this code bellow to start from row 20 of column B in the sheet Start Page, is this possible?
Sub SearchSheets()
Dim ws As Worksheet Dim i As Long Dim resultRow As Long
resultRow = 1 'initialize result row
For Each ws In ActiveWorkbook.Worksheets 'loop through all sheets in workbook If Len(ws.Name) = 3 And ws.Name <> "GRN" Then 'check if sheet name has only three letters and is not named "GRN" For i = 5 To ws.Cells(ws.Rows.Count, "I").End(xlUp).Row 'loop through column I from row 5 downwards If ws.Cells(i, "I").Value = "" And ws.Cells(i, "J").Value <> "" Then 'check if cell in Row I is empty and adjoining cell in Row J is not empty Worksheets("Start Page").Cells(resultRow, 2).Value = ws.Cells(i, "J").Value & ws.Range("B1").Value 'concatenate values and add to result row, starting from column B in sheet "Start Page" resultRow = resultRow + 1 'increment result row ElseIf ws.Cells(i, "I").Value = "" And ws.Cells(i, "J").Value = "" Then 'check if both cells in Row I and Row J are empty Exit For 'move to next sheet if both cells are empty End If Next i End If Next ws
End Sub
- Assistant
Yes, it is possible. You can simply change the line that concatenates the values and adds them to the result row to start from row 20 of column B in the "Start Page" sheet. Here's the modified line:
Worksheets("Start Page").Cells(resultRow + 19, 2).Value = ws.Cells(i, "J").Value & ws.Range("B1").Value
This will start populating the values from row 20 of column B in the "Start Page" sheet.
- User
The vba code capture all the data even if there are more than one matches in a sheet. However when the vba code does find more than one row that matches its search it does puts the concetated value s in different rows
- Assistant
Yes, that is because the code is designed to increment the result row by 1 for each match found. To capture multiple matches in one sheet and concatenate them into a single cell on the "Start Page" sheet, you can modify the code to concatenate the values using a delimiter.
Here's the modified code:
Sub SearchSheets() Dim ws As Worksheet Dim i As Long Dim resultRow As Long Dim concatenatedValue As String resultRow = 20 'initialize result row concatenatedValue = "" 'initialize concatenated value For Each ws In ActiveWorkbook.Worksheets 'loop through all sheets in workbook If Len(ws.Name) = 3 And ws.Name <> "GRN" Then 'check if sheet name has only three letters and is not named "GRN" For i = 5 To ws.Cells(ws.Rows.Count, "I").End(xlUp).Row 'loop through column I from row 5 downwards If ws.Cells(i, "I").Value = "" And ws.Cells(i, "J").Value <> "" Then 'check if cell in Row I is empty and adjoining cell in Row J is not empty concatenatedValue = concatenatedValue & ws.Cells(i, "J").Value & ", " 'concatenate values with delimiter ElseIf ws.Cells(i, "I").Value = "" And ws.Cells(i, "J").Value = "" Then 'check if both cells in Row I and Row J are empty If concatenatedValue <> "" Then 'check if there were any matches found in this sheet Worksheets("Start Page").Cells(resultRow, 2).Value = Left(concatenatedValue, Len(concatenatedValue) - 2) 'remove the last delimiter and add to result row, starting from row 20 of column B in sheet "Start Page" resultRow = resultRow + 1 'increment result row concatenatedValue = "" 'reset concatenated value for next sheet End If Exit For 'move to next sheet if both cells are empty End If Next i End If Next ws End SubThis code will concatenate all matching values in a single cell on the "Start Page" sheet with a comma and space delimiter (", "). If there are multiple matches in a sheet, it will concatenate them in the same cell. If there are no matches in a sheet, it will skip to the next sheet.
- User
what I am trying to explain is that when it finds more than one match on a sheet, it breaks the concatenated values of each row into different rows. For example if there are 3 rows of matches in sheet1, instead of just listing the result in 3 separate rows, it list the three results found in six separate rows. 3 rows with the value of B1 from the result sheet and three other rows of the values of J.
- Assistant
I see what you mean. To avoid splitting the concatenated values of multiple rows into separate rows in the output sheet, you can modify the code to concatenate the values of all rows into a single string and write the string to the output sheet in one go. Here's the modified code:
Sub SearchSheets() Dim ws As Worksheet Dim i As Long Dim resultRow As Long Dim concatenatedValue As String resultRow = 20 'initialize result row For Each ws In ActiveWorkbook.Worksheets 'loop through all sheets in workbook If Len(ws.Name) = 3 And ws.Name <> "GRN" Then 'check if sheet name has only three letters and is not named "GRN" 'initialize concatenated value for this sheet concatenatedValue = "" For i = 5 To ws.Cells(ws.Rows.Count, "I").End(xlUp).Row 'loop through column I from row 5 downwards If ws.Cells(i, "I").Value = "" And ws.Cells(i, "J").Value <> "" Then 'check if cell in Row I is empty and adjoining cell in Row J is not empty concatenatedValue = concatenatedValue & ws.Cells(i, "J").Value & " " 'concatenate values with delimiter ElseIf ws.Cells(i, "I").Value = "" And ws.Cells(i, "J").Value = "" Then 'check if both cells in Row I and Row J are empty If concatenatedValue <> "" Then 'check if there were any matches found in this sheet Worksheets("Start Page").Cells(resultRow, 2).Value = concatenatedValue & ws.Range("B1").Value 'add concatenated value to result row, starting from row 20 of column B in sheet "Start Page" resultRow = resultRow + 1 'increment result row concatenatedValue = "" 'reset concatenated value for next sheet End If Exit For 'move to next sheet if both cells are empty End If Next i End If Next ws End SubThis code will concatenate all matching values in a single string for each sheet, and write the string to the output sheet in a single row. If there are multiple matches in a sheet, they will be concatenated with a space delimiter (" ") and written to a single row on the output sheet.
- User
A good idea, but the string will be too long for me, I would rather just have 3 rows for 3 matches found on the same sheet.
- Assistant
I understand. In that case, you can modify the code to write each match to a separate row on the output sheet. Here's the modified code:
Sub SearchSheets() Dim ws As Worksheet Dim i As Long Dim resultRow As Long resultRow = 20 'initialize result row For Each ws In ActiveWorkbook.Worksheets 'loop through all sheets in workbook If Len(ws.Name) = 3 And ws.Name <> "GRN" Then 'check if sheet name has only three letters and is not named "GRN" For i = 5 To ws.Cells(ws.Rows.Count, "I").End(xlUp).Row 'loop through column I from row 5 downwards If ws.Cells(i, "I").Value = "" And ws.Cells(i, "J").Value <> "" Then 'check if cell in Row I is empty and adjoining cell in Row J is not empty Worksheets("Start Page").Cells(resultRow, 2).Value = ws.Cells(i, "J").Value & " " & ws.Range("B1").Value 'concatenate values and add to result row, starting from row 20 of column B in sheet "Start Page" resultRow = resultRow + 1 'increment result row ElseIf ws.Cells(i, "I").Value = "" And ws.Cells(i, "J").Value = "" Then 'check if both cells in Row I and Row J are empty Exit For 'move to next sheet if both cells are empty End If Next i End If Next ws End SubThis code will write each matching value to a separate row on the output sheet. If there are multiple matches in a sheet, they will be written to separate rows on the output sheet.