brent.fraser
05-25-2016, 09:11 AM
Hello,
I am writing a macro that will parse a report of over 900,000 rows with about 20 classes (in column D).
Depending on the value of the D column, I am wanting to analyze different columns for values. If there are values in the certain columns, the item is complete. If the columns are missing information, it is incomplete.
Currently I am doing a for loop and within it, I am also using a "With" function (that isn't working). Here's what I have so far:
elecN = 0
elecC = 0
For i = 2 To lastrow
With Worksheets("Data").Range("D" & i).Value = "ELEC - Electrical"
If Worksheets("Data").Range("C" & i).Value = "" _
Or Worksheets("Data").Range("E" & i).Value = "" _
Or Worksheets("Data").Range("R" & i).Value = "0" _
Or Worksheets("Data").Range("W" & i).Value = "0" Then
Worksheets("Data").Range("AH" & i).Value = "Electrical Incomplete"
Worksheets("Data").Range("AH" & i).Interior.ColorIndex = 3
Worksheets("Data").Range("AH" & i).Font.ColorIndex = 2
elecN = elecN + 1
'MsgBox "Incomplete " & elecN
Else
Worksheets("Data").Range("AH" & i).Value = "Electrical Complete"
'Worksheets("Data").Range("AF" & i).Interior.ColorIndex = 51
'Worksheets("Data").Range("AF" & i).Font.ColorIndex = 2
elecC = elecC + 1
End If
End With
Next i
I am testing this on a smaller version of the report (only 18 rows with two different values in column D - where I am using the "with" command), and the results I see are including all 18 results (not just the ones where D is "ELEC - Electrical."). When this is working, I will expand the "with" to include the other categories in column D.
Any idea where I am going wrong?
I am writing a macro that will parse a report of over 900,000 rows with about 20 classes (in column D).
Depending on the value of the D column, I am wanting to analyze different columns for values. If there are values in the certain columns, the item is complete. If the columns are missing information, it is incomplete.
Currently I am doing a for loop and within it, I am also using a "With" function (that isn't working). Here's what I have so far:
elecN = 0
elecC = 0
For i = 2 To lastrow
With Worksheets("Data").Range("D" & i).Value = "ELEC - Electrical"
If Worksheets("Data").Range("C" & i).Value = "" _
Or Worksheets("Data").Range("E" & i).Value = "" _
Or Worksheets("Data").Range("R" & i).Value = "0" _
Or Worksheets("Data").Range("W" & i).Value = "0" Then
Worksheets("Data").Range("AH" & i).Value = "Electrical Incomplete"
Worksheets("Data").Range("AH" & i).Interior.ColorIndex = 3
Worksheets("Data").Range("AH" & i).Font.ColorIndex = 2
elecN = elecN + 1
'MsgBox "Incomplete " & elecN
Else
Worksheets("Data").Range("AH" & i).Value = "Electrical Complete"
'Worksheets("Data").Range("AF" & i).Interior.ColorIndex = 51
'Worksheets("Data").Range("AF" & i).Font.ColorIndex = 2
elecC = elecC + 1
End If
End With
Next i
I am testing this on a smaller version of the report (only 18 rows with two different values in column D - where I am using the "with" command), and the results I see are including all 18 results (not just the ones where D is "ELEC - Electrical."). When this is working, I will expand the "with" to include the other categories in column D.
Any idea where I am going wrong?