locked
VLOOKUP range keeps changing RRS feed

  • Question

  • I have an excel macro worksheet where I use a vlookup function. I originally set up the formula as this:

    =IF(ISNA(VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),WeeklyHistory!A$1:$ZZ$9000,8,"false")),0,VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),WeeklyHRHistory!A$1:$ZZ$9000,8,"false"))

    but when I run the macro it changes it:

    =IF(ISNA(VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),WeeklyHistory!A$1:$ZZ$8955,8,"false")),0,VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),WeeklyHRHistory!A$1:$ZZ$8955,8,"false")).

    If I run it a third time it reduces to $ZZ$8820. 

    The number of lines it is going down is the exact # of lines I clear out and reload each time in the WeeklyHistory tab. So I know what it's doing, but WHY??? How do I keep it from changing each time?

    Thanks.

    Monday, May 18, 2020 4:56 PM

All replies

  • If you delete rows from the range A$1:$ZZ$9000, Excel will automatically adjust the range in the formula. If you don't want that, you could change the formula to

    =IF(ISNA(VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),INDIRECT("WeeklyHistory!A$1:$ZZ$9000"),8,False)),0,VLOOKUP(K1 & "_" & TEXT(A23,"yyyymmdd"),INDIRECT("WeeklyHRHistory!A$1:$ZZ$9000"),8,False))

    PS Is it correct that the first VLOOKUP has WeeklyHistory and the second one WeeklyHRHistory?


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Monday, May 18, 2020 7:26 PM