0

I'm writing some code in Excel to scan a row for a particular value, then get the cell address of that value.

lngCol = 0

search_start = "D1"

Do While current_week <> week_one
    lgnCol = lngCol + 1
    current_week = Range(search_start).Offset(0, lngCol)
Loop

wk_one_col = current_week.Address
Debug.Print wk_one_col

The code is mostly working and I'm not getting any errors, but the lngCol variable isn't incrementing as it should be.

In the loop, lngCol is always 1 after the first loop and lngCol+1 is adding 1 to the original value of the variable, which is zero.

What I'm not understanding is, each loop should be incrementing it. So first loop, it adds 1 to zero, setting lngCol to 1. Second loop, it adds another 1 to 1, making it 2.

I have another function this idea is based off and it works as expected. Each loop the lngCol variable increments by 1.

I know I know I need the variable itself to be out of scope from the loop, otherwise it'll keep resetting itself to zero every time. Where am I going wrong here?

TomCrow
  • 47
  • 8
  • 2
    Put `Option Explicit` on top, and the truth will be revealed. – GSerg Nov 15 '21 at 11:13
  • Do you see the loop not exiting after the first iteration and the variable shill does not incrementing? What `week_one` should be? Was it initialized? Running the code step by step, using `F8` do you see the code looping and the variable still not get the increment? I have some doubts... – FaneDuru Nov 15 '21 at 11:24
  • 2
    Your doubts should be resolved by contemplating over the `lgnCol = lngCol` line a bit @FaneDuru. – GSerg Nov 15 '21 at 11:40
  • I agree with @FaneDuru that your code is not looping. The problem lies in the values of current_week and week_one. Looks like you are treating current_week as a number in one point and a range at a another. It's better _always_ to use Option Explicit and always be clear when referring to properties of ranges. – JohnRC Nov 15 '21 at 11:46
  • @JohnRC The code may or may not be looping; that would have no effect on the failure to change the contents of the `lngCol` variable. – GSerg Nov 15 '21 at 11:53
  • @GSerg I cannot get you, I'm afraid... Do you want suggesting that being not declared `As Long`, being a `Variant` will not be incremented on the line `lgnCol = lngCol + 1`?. If so, VBA is clever enough to 'guess` that is about a numeric variable. If not, like I said, I cannot get you... :) – FaneDuru Nov 15 '21 at 12:10
  • @FaneDuru No, I want to say that `lgn` is not `lng`. – GSerg Nov 15 '21 at 12:14
  • @GSerg Ups... :) – FaneDuru Nov 15 '21 at 12:17
  • 1
    And this, friends, is why I can't have nice things. Thank you @GSerg for being gentle pointing this out :) – TomCrow Nov 15 '21 at 16:04
  • @GSerg good spot. In my own defence, using `Option Explicit` would of course have spotted this. I never understood why this option is not phrased the other way round, so variable declaration would be on by default and have to be turned off by `Option MakeMyCodeHarderToDebug` – JohnRC Dec 09 '21 at 05:37

1 Answers1

0

Thank you to everyone who commented. The issue here was I had spelled the variable name wrong in the incrementing line:

lgnCol = lngCol + 1

Should be:

lngCol = lngCol + 1

TomCrow
  • 47
  • 8