With and End With issue

  • Thread starter Thread starter Pat
  • Start date Start date
P

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
 
Print it out and connect the With-End Withs and the If-Else-End Ifs. You'll
see it.

Pat said:
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
Click to expand...
=====> Out of place; Should this be "ElseIf"
 
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
 
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)
 
Back
Top