My range of data in I:J starts at row 5. In the range, all the cells in column J have…
- 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
- 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 SubThis code includes an additional
Elsestatement that triggers when a non-empty cell in column I is encountered. It checks if there are any values stored in thecopyRangevariable and, if there are, it copies those values to column F in the desired range. It also resets thecopyRangevariable toNothingto 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
copyRangevariable and, if so, copies them to column F.I hope this helps! Let me know if you have any further questions.
- 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.
- 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 SubIn this modified code, I've introduced a new
copyAreavariable 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
copyAreais empty. If it is, it initializescopyAreawith the current cell in column J. IfcopyAreaalready contains values, it unions the current cell with the existingcopyArea.When a non-empty cell in column I is encountered, the code checks if
copyAreais not empty. IfcopyAreahas values, it unions it with the existingcopyRangevariable, and then resetscopyAreatoNothingto start storing a new group of empty values in column J.After the loop, the code checks if
copyAreastill contains values. If it does, it unions it with the existingcopyRange.Finally, if
copyRangehas 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!
- User
The modified code is not storing all the groups of empty values found in column J and copy them to column F
- 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 SubIn this modified code, I've introduced a new
tempRangevariable to store each group of empty cells in column J. When a non-empty cell in column I is encountered, the code checks iftempRangeis not empty. IftempRangecontains values, it unions it with the existingcopyRangevariable and then resetstempRangetoNothingto start storing a new group of empty values in column J.After the loop, the code checks if
tempRangestill contains values. If it does, it unions it with the existingcopyRange.Finally, if
copyRangehas 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!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.