full range variable....

[sspandit]

how to make the full range variable...

Please Login or Register  to view this content.

where "num2" is a variable in a "FOR loop which keeps on incrementing.

i am not able to keep the range part dynamic relating to my incrementing variable "num2"

how should i do it??? please help me with this one?

[Richard Buttrey]

Hi,

If I've understood the request correctly try

Please Login or Register  to view this content.

HTH

[contaminated]

TRY

Please Login or Register  to view this content.

[sspandit]

Sorry guys. both the codes dont work...
error is as follows..


Compile error
Expected: list seperator or )

[rwgrietveld]

I think what you mean is this:

Don't use the FOR, NEXT loop but apply the formula to the range directly:

I do not understant why the LOOKUP TABLE is shifting down (unusual, should it ?), but here's the code

Please Login or Register  to view this content.

[sspandit]

I am trying on a simpler code line now... but it fails miserably in the "range area itself...

Please Login or Register  to view this content.

same Compile Error:
Expected: List Seperator or )

[sspandit]

thanks for the code YWGRIETVELD .
i have many conditions i.e. IF command before this.... so i have to keep it dynamic. i have posted the above "MAX" worksheetfunction. i needed the dynamic range for the same purpose.

fortunately i think i may have found the answer...

Range("F" & NUM2) = WorksheetFunction.Max(Range("ap" & NUM2 & ":" & "aw" & NUM2))


seems to work. i dont know why. but it seems to work. after this i will perform the MATCH command and use the index number in a VLOOKUP function for "col number option"

[rwgrietveld]

Huu,

One function at a time please. We work hard on your problem. Don't shift to a complete new approach till the first one is done (solved) !!

How can

Please Login or Register  to view this content.

be a solution to your first problem?

[Richard Buttrey]

Hi,

Are you sure? It seems to work for me.
Have you copied and pasted the code I gave or typed it manually. If so check the '&' and quotes around ":G" and the closing brackets.

Rgds

[sspandit]

To, Richard Buttrey,
your code works. i had made a small typing mistake. it works like a charm !! thanks a lot.

to, ywgrietveld,
sorry that i shifted to another code immediately. it assumed it would be difficult for me to understand the VlookUp function options so i shifted to an easier "MAX" function. neverthless thanks a lot for the code. it works.

Thanks a lot.