PDA

View Full Version : [SOLVED] Excel Loop WITH Function



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?

Paul_Hossler
05-25-2016, 10:40 AM
Not really the way to use With/End With. You seemed to be using it like a Filter (also an option), but With really is more object oriented

I'm guessing you were looking for something like this, which follows your approach.

Note the Worksheets("Data") in the With, and the dotted objects (i.e. .Range("D & I)




Sub test()
elecN = 0
elecC = 0
With Worksheets("Data")
For i = 2 To lastrow
If .Range("D" & i).Value = "ELEC - Electrical" Then
If .Range("C" & i).Value = "" _
Or .Range("E" & i).Value = "" _
Or .Range("R" & i).Value = "0" _
Or .Range("W" & i).Value = "0" Then
.Range("AH" & i).Value = "Electrical Incomplete"
.Range("AH" & i).Interior.ColorIndex = 3
.Range("AH" & i).Font.ColorIndex = 2
elecN = elecN + 1
'MsgBox "Incomplete " & elecN
Else
.Range("AH" & i).Value = "Electrical Complete"
'.Range("AF" & i).Interior.ColorIndex = 51
'.Range("AF" & i).Font.ColorIndex = 2
elecC = elecC + 1
End If
End If
Next i
End With
End Sub

brent.fraser
05-25-2016, 11:50 AM
Hi Paul!

Perfect! Works like a charm!!!!!!!

Thanks!

B.