ALLOCATE A FORMULA INTO A VARIABLE IN VBA

[gab]

HI!,,,I got an issue....till now, I allocate result of formula FORMULA R1C1 into the active cells,,,AND NOW I wan to allocate it into a variable x or y (I dont want that the result appears in the spreadsheet, just into a variable to work the code!)

ANY HELP IS WELCOME!

 

[gab]

hi quiet riot...thanks for your answer, but unfortunately the formula you gave me is not working....i even modified to: a = Application.Cells("row", Index("K:K", Match(""LATE INTEREST & BK FEES :"", "K:K", 0))) ...(with 1 more " between the string,,,but is not working)...thanks again and please, i'll appreciatte more help

 

[gab]

thanks antony, but the formula you gave no return the row number. dp i have to enter another command refering row in the line you gave me??

thanks!!!!!!

 

[Anthony47]

thanks antony, but the formula you gave no return the row number

What does it return? Either read the variable or add the following instruction after VARIABLE1=Application.etc etc

Msgbox(VARIABLE1)

This will display the value of the variable.

Bye.

 

[gab]

hi anthony,

the formula "variable1" returns an error. I recognize the first formula is complicated, but a formula just with "match" is not returning the row number. Do i have to insert an additional formula (i.e row) in the variable1 etc etc???

thanks

 

[gab]

THANKS SO MUCH FPR THE FORMULA ANTHONY!....it wasnt work at the beginning because in vba the workshhetfuntion need to especified the cells as Range ( ).....and in this way:

a = Application.WorksheetFunction.Match("LATE INTEREST & BK FEES :", Range("K:K"), 0)

IT WORKS!...THANKS!!!!!!!!

 

[Anthony47]

I dont understand.
Try this new version:

Variable1 = Application.WorksheetFunction.Match("LATE INTEREST & BK FEES :", Range("k:k"), 0)

BUT PLEASE replace K:K with the address of the column to be looked in.

Also note that if the string is not found, an error will be genarated that have to be managed. Are you sure that the string "LATE INTEREST & BK FEES :" exists axactely in your database? (no added space, for example).

Bye.