Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· openaiHugging Face Suzhen/CodeChat

I want the output of this code bellow to start from row 20 of column B in the sheet…

  1. 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

  2. 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.

  3. 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

  4. 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 Sub
    

    This 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.

  5. 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.

  6. 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 Sub
    

    This 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.

  7. 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.

  8. 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 Sub
    

    This 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.