ALLOCATE A FORMULA INTO A VARIABLE IN VBA

gab

New Member
Joined
Jul 24, 2007
Messages
24
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sure:

the initial code (and it works, but i don want the result in a cell) was:

Cells(3,13). Activate
ActiveCell.FormulaR1C1 = "=CELL(""row"",INDEX(C[-2],MATCH(""LATE INTEREST & BK FEES :"",C[-2],0)))"

a = Cells(3, 13)
But as you see...the result is allocating in a cell, not in a variable. I would like something like this:


a = CELL("row",INDEX(C[-2],MATCH(""LATE INTEREST & BK FEES :"",C[-2],0)))

so the variable "a" will be equal to the formula. And that's the issue!!! thanks for any help!
 
Upvote 0
a = cells(3,13).value

i would just put that after the formula one

if you need both done this will then put the value in a
 
Upvote 0
unfortanately yes anthony. i'm new in mr. excel, and instead of clicking on post reply, i did on new topic. sorry
 
Upvote 0
thanks quiet riot,....but i dont want the result in the cell and in variable a. I need just in a.
 
Upvote 0
maybe something like this..



a = Application.CELL("row", Index("K:K", Match("LATE INTEREST & BK FEES :", "K:K", 0)))
 
Upvote 0
Let me switch to this topic, as it got more answers than the other one (see http://www.mrexcel.com/board2/viewtopic.php?t=284356)

Do you intend to acquire in a variable the number of the row that contains the string LATE INTEREST & BK FEES :??

In this case, your formula is unnecessarily complicated, as MATCH itsel return this value.

So, using Application.WorksheetFunction, you may use:

VARIABLE1=Application.WorksheetFunction.match("LATE INTEREST & BK FEES :",C[-2],0)

Please test and inform. Bye.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top