Nested If problem

[tourless]

Hi Folks.

I'm having a problem with the following task... With range H4 to H last row, if cell begins with "Net", then look at cell in same row in Column D and if D cell value is positive number, place "1009992" into cell in same row in Column C. If H cell does not start with "Net", move on to the next row. I have captured my last row and can search the range for cells beginning with "Net" but then I'm lost with offsets and testing the value in D. Here's what I've got so far and it's not working at all.

With Sheets("My Sheet")
        For Each cell In .Range("H4:H" & lRow)
            If UCase(Left(cell.Value, 3)) = "Net" Then
                If ws.Cells(i, 4).Value > 0 Then
                ws.Cells(i, 3).Value = "1009992"
            End If
        Next
    End With

 

[Joe4]

This line can NEVER be true:

If UCase(Left(cell.Value, 3)) = "Net" Then

because if the first 3 characters are "Net", UCASE makes it "NET".

So maybe try:

If UCase(Left(cell.Value, 3)) = "NET" Then

Also, you have not defined "i" anywhere.
You would need to have:

i = cell.Row

before using it, or change "i" to "cell.Row" in your formulas.

 

[Joe4]

So your final code might look like:

    With Sheets("My Sheet")
        For Each cell In .Range("H4:H" & lRow)
            If (UCase(Left(cell.Value, 3)) = "NET") And (ws.Cells(cell.Row, 4).Value > 0) Then
                ws.Cells(cell.Row, 3).Value = "1009992"
            End If
        Next
    End With

provided you have calculated lRow somewhere earlier in your code.

 

[tourless]

So your final code might look like:

    With Sheets("My Sheet")
        For Each cell In .Range("H4:H" & lRow)
            If (UCase(Left(cell.Value, 3)) = "NET") And (ws.Cells(cell.Row, 4).Value > 0) Then
                ws.Cells(cell.Row, 3).Value = "1009992"
            End If
        Next
    End With

provided you have calculated lRow somewhere earlier in your code.

I did calculate lRow prior to this code block, but when I try to run it your way I get an error... Object variable or With block variable not set. I have 'cell' defined as a Range, not sure if that's the problem.

 

[Joe4]

I did calculate lRow prior to this code block, but when I try to run it your way I get an error... Object variable or With block variable not set. I have 'cell' defined as a Range, not sure if that's the problem.

Note that I removed one of your IF...THEN clauses. It sounds like you may have left and "IF" or "END IF" in there that should be removed.

If you cannot figure it out, please post the ENTIRE block of code that you currently have.

 

[tourless]

Note that I removed one of your IF...THEN clauses. It sounds like you may have left and "IF" or "END IF" in there that should be removed.

If you cannot figure it out, please post the ENTIRE block of code that you currently have.

It's pretty basic so far... but I'm obviously missing something.

Sub TestFix()

Dim lRow As Long
Dim cell As Range
Dim ws As Worksheet
Dim fnd As Variant, rplc As Variant

    With ActiveSheet
        lRow = .Cells(.Rows.Count, 8).End(xlUp).Row
    End With
    
    With ActiveSheet
        For Each cell In .Range("H4:H" & lRow)
            If (UCase(Left(cell.Value, 3)) = "NET") And (ws.Cells(cell.Row, 4).Value > 0) Then
                ws.Cells(cell.Row, 3).Value = "1009992"
            End If
        Next
    End With
    
    Application.ScreenUpdating = False
    fnd = ""
    rplc = ""
    Range("H4:H" & lRow).Replace what:="Net", Replacement:="", _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
    SearchFormat:=False, ReplaceFormat:=False
    Application.ScreenUpdating = True

End Sub

 

[Joe4]

OK, it seems to work better if we leave it as two IF/THEN clauses instead of putting them together, i.e.

    With ActiveSheet
        For Each cell In .Range("H4:H" & lRow)
            If UCase(Left(cell.Value, 3)) = "NET" Then
                If ws.Cells(cell.Row, 4).Value > 0 Then
                    ws.Cells(cell.Row, 3).Value = "1009992"
                End If
            End If
        Next
    End With

 

[tourless]

OK, it seems to work better if we leave it as two IF/THEN clauses instead of putting them together, i.e.

    With ActiveSheet
        For Each cell In .Range("H4:H" & lRow)
            If UCase(Left(cell.Value, 3)) = "NET" Then
                If ws.Cells(cell.Row, 4).Value > 0 Then
                    ws.Cells(cell.Row, 3).Value = "1009992"
                End If
            End If
        Next
    End With

ok, I stepped through it and see it fails when it hits the first instance of 'Net' I think because of the UCase.

 

[tourless]

Yup... this fixed it. Thank you very much for all your help.

With ActiveSheet
        For Each cell In .Range("H4:H" & lRow)
            If (Left(cell.Value, 3)) = "Net" And ws.Cells(cell.Row, 4).Value > 0 Then
                ws.Cells(cell.Row, 3).Value = "1009992"
            End If
        Next
    End With

 

[Joe4]

Hmmm... That is odd. "UCase" should not cause that kind of error.
If it works without it, that is fine, but just note that it must then equal "Net" exactly, and not something like "NET" or "net".

What type of values do you have in column H?