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

My range of data in I:J starts at row 5. In the range, all the cells in column J have…

  1. User

    My range of data in I:J starts at row 5. In the range, all the cells in column J have values. In the range, some of the cells in column I are empty. As you move down the range cell by cell, not all the empty cells in column I are grouped together. Occassionaly, a group of empty cells in column I is interupted by a cell or group of cells with values. I need a VBA code that will loop through all the cells in column J that have values, copy to memory all the values of column J where the value of column I on the same row is empty, then after completeing the entire loop of column J that has values, copy the matches (where I = "") stored in memory to column F starting at F9. The code below finds the first group of cells in column I where the values are empty but the loop stops and does not does not complete when it again encounters a cell in column I that has a value. Can you ammend the code below to provide the functionality described above.

    Sub IncompleteJobs()

    Dim wscf As Worksheet Dim wsjr As Worksheet Dim lastRow As Long Dim copyRange As Range Dim i As Long

    ActiveSheet.Range("F9:F38").ClearContents Application.Wait (Now + TimeValue("0:00:01")) ActiveSheet.Range("G3").Formula = ActiveSheet.Range("G3").Formula Application.Wait (Now + TimeValue("0:00:01")) ActiveSheet.Range("H3").Formula = ActiveSheet.Range("H3").Formula If ActiveSheet.Range("G3") = "" Or 0 Then

    Exit Sub End If

    Set wscf = Sheets(Range("G3").Value) Set wsjr = Sheets("Start Page") lastRow = wscf.Cells(Rows.count, "J").End(xlUp).Row

    For i = 5 To lastRow If wscf.Cells(i, "I") = "" Then If copyRange Is Nothing Then Set copyRange = wscf.Range("J" & i) Else Set copyRange = Union(copyRange, wscf.Range("J" & i)) End If End If Next i

    If Not copyRange Is Nothing Then wsjr.Range("F9").Resize(copyRange.Rows.count, 1).Value = copyRange.Value End If

    Application.Wait (Now + TimeValue("0:00:02"))

    Call LastTwentyJobs

    End Sub

  2. Assistant

    Here's the amended code that should achieve the functionality described:

    Sub IncompleteJobs()
    
        Dim wscf As Worksheet
        Dim wsjr As Worksheet
        Dim lastRow As Long
        Dim copyRange As Range
        Dim i As Long
    
        ActiveSheet.Range("F9:F38").ClearContents
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Range("G3").Formula = ActiveSheet.Range("G3").Formula
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Range("H3").Formula = ActiveSheet.Range("H3").Formula
        If ActiveSheet.Range("G3") = "" Or 0 Then
            Exit Sub
        End If
    
        Set wscf = Sheets(Range("G3").Value)
        Set wsjr = Sheets("Start Page")
        lastRow = wscf.Cells(Rows.Count, "J").End(xlUp).Row
        
        For i = 5 To lastRow
            If wscf.Cells(i, "I") = "" Then
                If copyRange Is Nothing Then
                    Set copyRange = wscf.Range("J" & i)
                Else
                    Set copyRange = Union(copyRange, wscf.Range("J" & i))
                End If
            Else
                If Not copyRange Is Nothing Then
                    wsjr.Range("F9").Resize(copyRange.Rows.Count, 1).Value = copyRange.Value
                    Set copyRange = Nothing
                End If
            End If
        Next i
    
        ' Copy any remaining values from the last group
        If Not copyRange Is Nothing Then
            wsjr.Range("F9").Resize(copyRange.Rows.Count, 1).Value = copyRange.Value
        End If
    
        Application.Wait (Now + TimeValue("0:00:02"))
        Call LastTwentyJobs
    
    End Sub
    

    This code includes an additional Else statement that triggers when a non-empty cell in column I is encountered. It checks if there are any values stored in the copyRange variable and, if there are, it copies those values to column F in the desired range. It also resets the copyRange variable to Nothing to start storing a new group of empty values in column J.

    Finally, after the loop completes, it checks if there are any remaining values stored in the copyRange variable and, if so, copies them to column F.

    I hope this helps! Let me know if you have any further questions.

  3. User

    The code you provided which includes an additional Else statement that triggers when a non-empty cell in column I is encountered appears to have worked partially. It checked if there were any values stored in the copyRange variable and, it copied those values to column F in the desired range. It appears to have resets the copyRange variable to Nothing to start storing a new group of empty values in column J. After the loop completed, it appears to have checked if there were any remaining values stored in the copyRange variable and copied them to column F. Your ammendment is overwrting the first conditions that match. When a new match is found the previous matches stored to memory are written over and only the last group of matches found remains in memory. Can the code store to memory all the conditions that are found.

  4. Assistant

    Apologies for the oversight. To store all the conditions found in memory, you can modify the code as follows:

    Sub IncompleteJobs()
    
        Dim wscf As Worksheet
        Dim wsjr As Worksheet
        Dim lastRow As Long
        Dim copyRange As Range
        Dim copyArea As Range
        Dim i As Long
    
        ActiveSheet.Range("F9:F38").ClearContents
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Range("G3").Formula = ActiveSheet.Range("G3").Formula
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Range("H3").Formula = ActiveSheet.Range("H3").Formula
        If ActiveSheet.Range("G3") = "" Or 0 Then
            Exit Sub
        End If
    
        Set wscf = Sheets(Range("G3").Value)
        Set wsjr = Sheets("Start Page")
        lastRow = wscf.Cells(Rows.Count, "J").End(xlUp).Row
        
        For i = 5 To lastRow
            If wscf.Cells(i, "I") = "" Then
                If copyArea Is Nothing Then
                    Set copyArea = wscf.Range("J" & i)
                Else
                    Set copyArea = Union(copyArea, wscf.Range("J" & i))
                End If
            ElseIf Not copyArea Is Nothing Then
                If copyRange Is Nothing Then
                    Set copyRange = copyArea
                Else
                    Set copyRange = Union(copyRange, copyArea)
                End If
                Set copyArea = Nothing
            End If
        Next i
        
        ' Copy any remaining values from the last group
        If Not copyArea Is Nothing Then
            If copyRange Is Nothing Then
                Set copyRange = copyArea
            Else
                Set copyRange = Union(copyRange, copyArea)
            End If
        End If
        
        If Not copyRange Is Nothing Then
            wsjr.Range("F9").Resize(copyRange.Rows.Count, 1).Value = copyRange.Value
        End If
    
        Application.Wait (Now + TimeValue("0:00:02"))
        Call LastTwentyJobs
    
    End Sub
    

    In this modified code, I've introduced a new copyArea variable to temporarily store a group of empty cells in column J.

    Within the loop, if a cell in column I is empty, the code checks if copyArea is empty. If it is, it initializes copyArea with the current cell in column J. If copyArea already contains values, it unions the current cell with the existing copyArea.

    When a non-empty cell in column I is encountered, the code checks if copyArea is not empty. If copyArea has values, it unions it with the existing copyRange variable, and then resets copyArea to Nothing to start storing a new group of empty values in column J.

    After the loop, the code checks if copyArea still contains values. If it does, it unions it with the existing copyRange.

    Finally, if copyRange has values, it copies them to column F in the desired range.

    This modified code should store all the groups of empty values found in column J and copy them to column F without overwriting the previous matches.

    Let me know if you have any further questions or need any more assistance!

  5. User

    The modified code is not storing all the groups of empty values found in column J and copy them to column F

  6. Assistant

    Apologies for the misunderstanding. If you want to store all the groups of empty values found in column J and copy them to column F, you can modify the code as follows:

    Sub IncompleteJobs()
    
        Dim wscf As Worksheet
        Dim wsjr As Worksheet
        Dim lastRow As Long
        Dim copyRange As Range
        Dim tempRange As Range
        Dim i As Long
    
        ActiveSheet.Range("F9:F38").ClearContents
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Range("G3").Formula = ActiveSheet.Range("G3").Formula
        Application.Wait (Now + TimeValue("0:00:01"))
        ActiveSheet.Range("H3").Formula = ActiveSheet.Range("H3").Formula
        If ActiveSheet.Range("G3") = "" Or 0 Then
            Exit Sub
        End If
    
        Set wscf = Sheets(Range("G3").Value)
        Set wsjr = Sheets("Start Page")
        lastRow = wscf.Cells(Rows.Count, "J").End(xlUp).Row
    
        For i = 5 To lastRow
            If wscf.Cells(i, "I") = "" Then
                If copyRange Is Nothing Then
                    Set copyRange = wscf.Range("J" & i)
                Else
                    If tempRange Is Nothing Then
                        Set tempRange = wscf.Range("J" & i)
                    Else
                        Set tempRange = Union(tempRange, wscf.Range("J" & i))
                    End If
                End If
            Else
                If Not tempRange Is Nothing Then
                    If copyRange Is Nothing Then
                        Set copyRange = tempRange
                    Else
                        Set copyRange = Union(copyRange, tempRange)
                    End If
                    Set tempRange = Nothing
                End If
            End If
        Next i
        
        If Not tempRange Is Nothing Then
            If copyRange Is Nothing Then
                Set copyRange = tempRange
            Else
                Set copyRange = Union(copyRange, tempRange)
            End If
        End If
    
        If Not copyRange Is Nothing Then
            wsjr.Range("F9").Resize(copyRange.Rows.Count, 1).Value = copyRange.Value
        End If
    
        Application.Wait (Now + TimeValue("0:00:02"))
        Call LastTwentyJobs
    
    End Sub
    

    In this modified code, I've introduced a new tempRange variable to store each group of empty cells in column J. When a non-empty cell in column I is encountered, the code checks if tempRange is not empty. If tempRange contains values, it unions it with the existing copyRange variable and then resets tempRange to Nothing to start storing a new group of empty values in column J.

    After the loop, the code checks if tempRange still contains values. If it does, it unions it with the existing copyRange.

    Finally, if copyRange has values, it copies them to column F in the desired range.

    I apologize for any confusion caused. This modified code should store all the groups of empty values found in column J and copy them to column F correctly.

    Let me know if you have any further questions or need any more assistance!

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