Excel Loop WITH Function

[brent.fraser]

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]

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]

Hi Paul!

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

Thanks!

B.