Hi Everyone. It's been a while since I was on here last, but ran into an interesting problem with a nested for loop that I cannot solve. Hoping someone here will know the answer. I placed breakpoints in my code at the For x=1 to 10 line and after to see where run-time is ending. For some reason VBA is not executing the loop at all. Does anyone know what I'm missing? Thanks in advance.

sub help()
Dim x As Integer
Dim y As Integer
Dim lastcell As Long
Dim ws As Worksheet
Dim wscurr  As Worksheet
Dim gcounter As Long
Dim hcounter As Long
Dim num10000(1 To 10) As Double

Set ws = Sheets("ECLMadj")
Set wscurr = Sheets("10000-yr")
wscurr.Select
Range("A265536").End(xlUp).Select
lastcell = ActiveCell.Row
Range("A1").Select

num10000(1) = 2210
num10000(2) = 2220
num10000(3) = 2230
num10000(4) = 2240
num10000(5) = 2250
num10000(6) = 2310
num10000(7) = 2320
num10000(8) = 2330
num10000(9) = 2340
num10000(10) = 2350

gcounter = 1
x = 1
y = 1

For x = 1 To l0
hcounter = 1
  For y = 2 To lastcell
  wscurr.Select
      If Range("A" & y).Value = num10000(x) And Range("B" & y).Value >= 12 And Range("C" & y).Value <= 39 Then
         wscurr.Range("A" & y & ":F" & y).Copy Destination:=ws.Range(Cells(hcounter, gcounter))
         hcounter = hcounter + 1
      End If
  Next y
  gcounter = gcounter + 8
Next x
End Sub

Recommended Answers

All 6 Replies

Lines 30 and 31 are not needed because the variables get set at the beginning of the For-Loop. Is the function being called at all? Help() might have a name conflict. Put a breakpoint after the last Dim statement and try calling this Sub. See what happens.

As nutster pointed out, "help" is a reserved word. Change it to something help like "MySubHelp" etc....

Also, as pointed out, where do you make a call to the function. if not called, it will not execute. :)

Changed the name of the sub and eliminated lines 30 and 31. I had those these to see if I was missing something. VBA still skipping the loop. Breakpoints work up to the for loop. But any breakpoint in the loop has no effect and the program proceeds to End Sub with no errors. Weird...

The sub still has to be called to complete its functions, something like

Call MySubHelp

Also check if you have any "On error Resume Next" code. If so, delete it and see if any errors occur.

Remove the "wscurr.Select" in your loop statement, you already set it in the first part...

At the beginning of the module, place Option Explicit on its own line. This will catch any undeclared variables, like l0 (L-zero) that you are using as the end value of the For-Next loop. A token beginning with a letter followed by letters and/or digits is interpreted to be a varialbe name, unless it is in a list of keywords, in which case the compiler complains like crazy. By default, an undeclared variable, that is one not put in a Dim statement before it is first used, will have a value of 0. So the interpreter is treating this as:
For x = 1 to 0
This causes it to skip the entire loop.
Change l0 (L-zero) to 10 (one-zero) as the target value of the For loop.

commented: Good catch! I saw that too. +8

Thank you so much! This was a great catch. Must have been the late night and I dind't know about option explicit. Good tip!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.