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

Can you help with this problem? The code below finds and copys the creteria where column…

  1. User

    Can you help with this problem? The code below finds and copys the creteria where column G is empty and pastes the required rows from A to G into a new excel workbook. However between each row of the required criteria that it finds, it is also pasting values of only column D and G where the value of column G is not empty. I do not need these rows nor the values of column G if it contains a value:

    Sub CopyEmptyPOWorkbook()

    Dim i As Long, lastRow As Long Dim myRange As Range, myCell As Range Dim text As String

    lastRow = Cells(Rows.count, "A").End(xlUp).Row Set myRange = Range("A1:A" & lastRow)

    Dim newWb As Workbook Set newWb = Workbooks.Add

    Dim newWs As Worksheet Set newWs = newWb.Sheets.Add(After:=newWb.Sheets(newWb.Sheets.count)) newWs.Name = "Results"

    newWs.Cells(1, 1).Value = "COMPANY" newWs.Cells(1, 2).Value = "WORK" newWs.Cells(1, 3).Value = "QUOTE REF" newWs.Cells(1, 4).Value = "VALUE" newWs.Cells(1, 5).Value = "P REQUEST" newWs.Cells(1, 6).Value = "PR DATE" newWs.Cells(1, 7).Value = "P ORDER"

    Dim newRow As Long newRow = 1 For Each myCell In myRange If myCell.Value <> "" Then newRow = newRow + 1

    For i = 1 To 7 If Cells(myCell.Row, i + 6).Value = "" Then ''''''newWs.Cells(newRow, i).Value = Trim(Cells(myCell.Row, i).Value) newWs.Cells(newRow, i).Value = Cells(myCell.Row, i).Value

    End If Next i End If Next myCell

    newWb.Activate

    MsgBox "Please examine the new workbook and make any necessary changes before saving it. To save the file, click File > Save As, or use the keyboard shortcut Ctrl+S.", vbInformation, "New Workbook Created"

    End Sub

  2. Assistant

    To fix the issue of unwanted rows and values being pasted, you can simply add a check for column G before copying the entire row. Replace the code inside the inner For loop with the following:

    If Cells(myCell.Row, 7).Value = "" Then 'check column G newWs.Cells(newRow, i).Value = Cells(myCell.Row, i).Value End If

    This will only copy and paste the entire row if the value in column G is empty. Otherwise, it will skip that row and move on to the next one.

  3. User

    The code now works but I have a lot of empty rows between the pasted rows. How do I get rid of the empty rows

  4. Assistant

    To get rid of the empty rows, you can modify the code to only increment the row number in the new workbook if a valid row is found. Replace this section of the code:

    newRow = newRow + 1
    
    For i = 1 To 7
        If Cells(myCell.Row, i + 6).Value = "" Then
            If Cells(myCell.Row, 7).Value = "" Then 'check column G
                newWs.Cells(newRow, i).Value = Cells(myCell.Row, i).Value
            End If
        End If
    Next i
    

    With this updated version:

    For i = 1 To 7
        If Cells(myCell.Row, i + 6).Value = "" Then
            If Cells(myCell.Row, 7).Value = "" Then 'check column G
                newWs.Cells(newRow, i).Value = Cells(myCell.Row, i).Value
                isRowValid = True
            End If
        End If
    Next i
    
    If isRowValid Then
        newRow = newRow + 1
        isRowValid = False
    End If
    

    This code still checks if the row is valid, but it sets a boolean flag (isRowValid) to True if a valid row is found. The row number in the new workbook is only incremented if the flag is True. This way, empty rows are not created in the new workbook.

  5. User

    Can you please add your suggestion to the netire code bellow:

  6. Assistant

    Sure, you can use the modified code below:

    Sub CopyEmptyPOWorkbook()
    
    Dim i As Long, lastRow As Long
    Dim myRange As Range, myCell As Range
    Dim text As String
    
    lastRow = Cells(Rows.count, "A").End(xlUp).Row
    Set myRange = Range("A1:A" & lastRow)
    
    Dim newWb As Workbook
    Set newWb = Workbooks.Add
    
    Dim newWs As Worksheet
    Set newWs = newWb.Sheets.Add(After:=newWb.Sheets(newWb.Sheets.count))
    newWs.Name = "Results"
    
    newWs.Cells(1, 1).Value = "COMPANY"
    newWs.Cells(1, 2).Value = "WORK"
    newWs.Cells(1, 3).Value = "QUOTE REF"
    newWs.Cells(1, 4).Value = "VALUE"
    newWs.Cells(1, 5).Value = "P REQUEST"
    newWs.Cells(1, 6).Value = "PR DATE"
    newWs.Cells(1, 7).Value = "P ORDER"
    
    Dim newRow As Long
    Dim isRowValid As Boolean
    newRow = 1
    For Each myCell In myRange
        isRowValid = False 'initialize flag
        If myCell.Value <> "" Then
            For i = 1 To 7
                If Cells(myCell.Row, i + 6).Value = "" Then
                    If Cells(myCell.Row, 7).Value = "" Then 'check column G
                        newWs.Cells(newRow, i).Value = Cells(myCell.Row, i).Value
                        isRowValid = True 'set flag to True
                    End If
                End If
            Next i
            If isRowValid Then 'increment row number if valid row found
                newRow = newRow + 1
                isRowValid = False
            End If
        End If
    Next myCell
    
    newWb.Activate
    
    MsgBox "Please examine the new workbook and make any necessary changes before saving it. To save the file, click File > Save As, or use the keyboard shortcut Ctrl+S.", vbInformation, "New Workbook Created"
    
    End Sub
    

    This code initializes the isRowValid flag to False, and sets it to True if a valid row is found. The row number in the new workbook is only incremented if the isRowValid flag is True.

  7. User

    It got rid of the blank rows, But it also got rid of my Header Row 1

  8. Assistant

    I apologize for the error in the previous code. To fix this, we can add a separate check to handle the header row. Here is the updated code with the header handling:

    Sub CopyEmptyPOWorkbook()
    
    Dim i As Long, lastRow As Long
    Dim myRange As Range, myCell As Range
    Dim text As String
    
    lastRow = Cells(Rows.count, "A").End(xlUp).Row
    Set myRange = Range("A1:A" & lastRow)
    
    Dim newWb As Workbook
    Set newWb = Workbooks.Add
    
    Dim newWs As Worksheet
    Set newWs = newWb.Sheets.Add(After:=newWb.Sheets(newWb.Sheets.count))
    newWs.Name = "Results"
    
    Dim headerRow As Range
    Set headerRow = Range("A1:G1") 'initialize header row range
    
    headerRow.Copy newWs.Range("A1") 'copy header row to new worksheet
    
    Dim newRow As Long
    Dim isRowValid As Boolean
    newRow = 2 'start on row 2 (below header row)
    For Each myCell In myRange
        isRowValid = False 'initialize flag
        If myCell.Value <> "" Then
            For i = 1 To 7
                If Cells(myCell.Row, i + 6).Value = "" Then
                    If Cells(myCell.Row, 7).Value = "" Then 'check column G
                        newWs.Cells(newRow, i).Value = Cells(myCell.Row, i).Value
                        isRowValid = True 'set flag to True
                    End If
                End If
            Next i
            If isRowValid Then 'increment row number if valid row found
                newRow = newRow + 1
                isRowValid = False
            End If
        End If
    Next myCell
    
    newWb.Activate
    
    MsgBox "Please examine the new workbook and make any necessary changes before saving it. To save the file, click File > Save As, or use the keyboard shortcut Ctrl+S.", vbInformation, "New Workbook Created"
    
    End Sub
    

    This code initializes the headerRow range to contain the header row, and then copies it to the new worksheet before the loop starts. The newRow variable starts on row 2, and the loop only increments the row number if a valid row is found.

  9. User

    Instead of copying the Header row, cant it create the head row as in the formula and when deleting empty rows, not delete row 1: Sub CopyEmptyPOWorkbook()

    Dim i As Long, lastRow As Long Dim myRange As Range, myCell As Range Dim text As String

    lastRow = Cells(Rows.count, "A").End(xlUp).Row Set myRange = Range("A1:A" & lastRow)

    Dim newWb As Workbook Set newWb = Workbooks.Add

    Dim newWs As Worksheet Set newWs = newWb.Sheets.Add(After:=newWb.Sheets(newWb.Sheets.count)) newWs.Name = "Results"

    newWs.Cells(1, 1).Value = "COMPANY" newWs.Cells(1, 2).Value = "WORK" newWs.Cells(1, 3).Value = "QUOTE REF" newWs.Cells(1, 4).Value = "VALUE" newWs.Cells(1, 5).Value = "P REQUEST" newWs.Cells(1, 6).Value = "PR DATE" newWs.Cells(1, 7).Value = "P ORDER"

    Dim newRow As Long Dim isRowValid As Boolean newRow = 1 For Each myCell In myRange isRowValid = False 'initialize flag If myCell.Value <> "" Then For i = 1 To 7 If Cells(myCell.Row, i + 6).Value = "" Then If Cells(myCell.Row, 7).Value = "" Then 'check column G newWs.Cells(newRow, i).Value = Cells(myCell.Row, i).Value isRowValid = True 'set flag to True End If End If Next i If isRowValid Then 'increment row number if valid row found newRow = newRow + 1 isRowValid = False End If End If Next myCell

    newWb.Activate

    MsgBox "Please examine the new workbook and make any necessary changes before saving it. To save the file, click File > Save As, or use the keyboard shortcut Ctrl+S.", vbInformation, "New Workbook Created"

    End Sub

  10. Assistant

    Yes, you can create the header row with a formula instead of copying it from the original worksheet. Here is an updated version of the code:

    Sub CopyEmptyPOWorkbook()
    
    Dim i As Long, lastRow As Long
    Dim myRange As Range, myCell As Range
    Dim text As String
    
    lastRow = Cells(Rows.count, "A").End(xlUp).Row
    Set myRange = Range("A1:A" & lastRow)
    
    Dim newWb As Workbook
    Set newWb = Workbooks.Add
    
    Dim newWs As Worksheet
    Set newWs = newWb.Sheets.Add(After:=newWb.Sheets(newWb.Sheets.count))
    newWs.Name = "Results"
    
    newWs.Range("A1:G1").Formula = "=IF({1,0}, {""COMPANY"",""WORK"",""QUOTE REF"",""VALUE"",""P REQUEST"",""PR DATE"",""P ORDER""})"
    
    Dim newRow As Long
    Dim isRowValid As Boolean
    newRow = 2 'start on row 2 (below header row)
    For Each myCell In myRange
        isRowValid = False 'initialize flag
        If myCell.Value <> "" Then
            For i = 1 To 7
                If Cells(myCell.Row, i + 6).Value = "" Then
                    If Cells(myCell.Row, 7).Value = "" Then 'check column G
                        newWs.Cells(newRow, i).Value = Cells(myCell.Row, i).Value
                        isRowValid = True 'set flag to True
                    End If
                End If
            Next i
            If isRowValid Then 'increment row number if valid row found
                newRow = newRow + 1
                isRowValid = False
            End If
        End If
    Next myCell
    
    newWb.Activate
    
    MsgBox "Please examine the new workbook and make any necessary changes before saving it. To save the file, click File > Save As, or use the keyboard shortcut Ctrl+S.", vbInformation, "New Workbook Created"
    
    End Sub
    

    This code uses a formula (=IF({1,0}, {""COMPANY"",""WORK"",""QUOTE REF"",""VALUE"",""P REQUEST"",""PR DATE"",""P ORDER""})) to create the header row. This formula creates an array containing the text for each column header, and then returns that array as the row value. The rest of the code is the same as before, except that the newRow variable now starts on row 2.

  11. User

    It creates a header but all the values in the header are COMPANY

  12. Assistant

    I apologize for the error. The issue is that the formula that creates the header row is missing the curly braces that indicate an array formula. Here is the correct formula:

    newWs.Range("A1:G1").FormulaArray = "=IF({1,0}, {""COMPANY"",""WORK"",""QUOTE REF"",""VALUE"",""P REQUEST"",""PR DATE"",""P ORDER""})"
    

    This code should create the correct header row with the appropriate field names.

  13. User

    The first value in the header row COMPANY is correct the second value is showing FALSE the other values are showing #N/A

  14. Assistant

    I apologize for the error. The issue is that the formula is returning an array that is a mixture of text and logical values, which is causing the issues with the header row. Here is an updated version of the formula that should work:

    newWs.Range("A1:G1").FormulaArray = "=TRANSPOSE({""COMPANY"",""WORK"",""QUOTE REF"",""VALUE"",""P REQUEST"",""PR DATE"",""P ORDER""})"
    

    This formula creates an array of text values, and then uses the TRANSPOSE function to flip the array so the values are in a row instead of a column. This should produce the correct header row with all the correct values.

  15. User

    back to the same COMPANY value for all the other header values

  16. Assistant

    I apologize for the error. It turns out that the issue is with the curly braces in the TRANSPOSE function - they need to surround the entire formula, not just the array:

    newWs.Range("A1:G1").FormulaArray = "{" & "=TRANSPOSE({""COMPANY"",""WORK"",""QUOTE REF"",""VALUE"",""P REQUEST"",""PR DATE"",""P ORDER""})" & "}"
    

    This code should correctly produce the header row with all the correct values.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.