With and End With issue

[Pat]

The following portion of code has got the wrong amount of 'End If', 'End
With' etc, is it possible someone can see the fault with this?

Thanking you
Pat

For Each Target In rng1
sProd = Target.Parent.Cells(Target.Row, 26)

res = Application.Match(sProd, _
Worksheets("PList").Range("L30:L950"), 0)
If Not IsError(res) Then
With Worksheets("PList")

If IsNumeric(Target.Value) Then
Set rng2 = .Cells(res + 29, icol)
rng2.Value = rng2.Value - Target.Value
End If
End With
Else
MsgBox "Product Not found: " & sProd

End If

End With
Else
MsgBox Target.Address & " Caused an Error"
Resume Next
On Error Resume Next
End If
Next

 

[Bryan Dickerson]

Print it out and connect the With-End Withs and the If-Else-End Ifs. You'll
see it.

The following portion of code has got the wrong amount of 'End If', 'End
With' etc, is it possible someone can see the fault with this?

Thanking you
Pat

For Each Target In rng1
sProd = Target.Parent.Cells(Target.Row, 26)

res = Application.Match(sProd, _
Worksheets("PList").Range("L30:L950"), 0)
If Not IsError(res) Then
With Worksheets("PList")

If IsNumeric(Target.Value) Then
Set rng2 = .Cells(res + 29, icol)
rng2.Value = rng2.Value - Target.Value
End If
End With
Else
MsgBox "Product Not found: " & sProd

End If =====> Out of place

End With =====> Out of place
Else

=====> Out of place; Should this be "ElseIf"

 

[Pat]

Thank you for helping me out.

=====> Out of place; Should this be "ElseIf"

When ElseIf is entered it is not being accepted. Why should this be?

 

[Tushar Mehta]

Just match your If...End If, If...Else...End If, and With...End With.
You will find the redundant End With and Else...clauses

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Bob Phillips]

Pat,

You seem to have lost the flow on the If IsNumeric test, rather than Else
it, you end it. From the code, it looks as though it should read like this
to me

For Each Target In rng1
sProd = Target.Parent.Cells(Target.Row, 26)
res = Application.Match(sProd, _
Worksheets("PList").Range("L30:L950"), 0)
If Not IsError(res) Then
With Worksheets("PList")
If IsNumeric(Target.Value) Then
Set rng2 = .Cells(res + 29, icol)
rng2.Value = rng2.Value - Target.Value
Else
MsgBox "Product Not found: " & sProd
End If
End With
Else
MsgBox Target.Address & " Caused an Error"
Resume Next
On Error Resume Next
End If
Next

--

HTH

RP
(remove nothere from the email address if mailing direct)