Excell Formula dinamicly changed

[np3il]

Hello,

Is there a way to change a number in the formula dynamically in a Excel Macro?

Ex.

=100*VLOOKUP($P2,QUERY!$A$2:$Q$3191,17,FALSE)

Change the 3191 with a variable or the contents of another cell.

Thanks
Np3il

 

[Zathras]

Look up the INDIRECT function in the help file.

 

[SkipVought]

Check out

How can I rename a table as it changes size faq68-1331

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[np3il]

Hello.

I have tried the INDIRECT function but this does not correctly the question I posted. It seems to me that the INDIURECT function is the same as =Cell.

**********************************
Ex.
A10 = 777

B21 =INDIRECT("A10") or =A10

B21 = 777 after any of the above is entered.
**********************************

Please note: I am using multiple sheets. The number 3191 is a row number that I need to change dynamically, meaning it is always changing with every run.

Ex. the sheet QUERY will generate x amounts or rows (different every day) the formula(s) are on a separate sheet OUTPUT. The value to change 3191 (currently hardcoded) does change every day.

I was thinking of a variable value or Range("A1").Value, but this direction has not been successful.

=100*VLOOKUP($P2,QUERY!$A$2:$Q$3191,17,FALSE)

I hope this explains it better.

Np3il

 

[AnotherHiggins]

Np3il,

Did you read Skip's FAQ that he linked to? It has your answer.

Something to keep in mind while you're reading it: You'll want to use the OFFSET function, then use the COUNT or COUNTA function within it to count how many rows there are.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[np3il]

John,

Yes I read it but did not find it usefull for my case. I went a different way by creating the string with the variable and then asigning the string to the corrent cell.

Ex.

sFormula = "=100*VLOOKUP(RC16,QUERY!R2C1:R" + Trim(Str(iRowCount + 1)) + "C17,17,FALSE)"
ActiveCell.FormulaR1C1 = sFormula

This solved the problem.

Thank for all your help
Np3il

 

[xlbo]

Skip has the right approach but syntactically, the answer is:

"=VLOOKUP($P2,QUERY!$A$2:$Q$" & variable & ",17,FALSE)"

but what you have cannot be in a macro as a formula must be entered as a STRING which you do not have....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question