Assign Formula Result to variable

Sorry for such a beginner question, but all examples I have seen use strings.
My Goal is to assign the value returned by a formula to a variable, and then set a cell equal to the value of the variable.

The formula I am using returns a value of 10.

[VBA]

Sub GetPrice()

Dim StockNum As Integer

'These two lines result in "0" being placed in Cell E6
StockNum = FormulaR1C1 = "=COUNTA(Main!R3C2:R3C11)"
Range("Main!E6").Value = StockNum ' "Why does make the Cell "0" and not "10" ?

'This line results in "10" being places in Cell E7
Range("Main!E7").FormulaR1C1 = "=COUNTA(Main!R3C2:R3C11)"

End Sub

[/VBA]

Thank you for you help.

Welcome to the forum!

It should have complained when you compiled it with FormulaR1C1 as a variable if you had Option Explicit as the first line of code which I recommend. When you set [vba]FormulaR1C1 = "=COUNTA(Main!R3C2:R3C11)" [/vba] you are asking for a Boolean evaluation which returns False if not preset where it resolves to false or 0. In fact, any variable=someothervariable="whatever" always returns False. You can add the Compile button to the VBE toolbar. You can set a VBE option to add Option Explicit.

Use Evaluate() if you want to evaluate a formula without putting it into a cell's formula.

e.g.
[vba]Range("Main!E6").Value = Evaluate("=COUNTA(Main!R3C2:R3C11)" )[/vba]

Hi Kenneth,

Thank you for your help and welcome. I understand now why I was getting a Boolean return, but I am still confused on how to assign the value returned by the formula to a variable.

Currently this gives me a missmatch error:

[VBA]
Sub GetPrice()

Dim StockNum As Integer

StockNum = Evaluate("=COUNTA(Main!R3C2:R3C11)") 'Error 13

Range("Main!E6").Value = StockNum

End Sub

[/VBA]

Thanks Again

Quote:

---

Originally Posted by somail

Sorry for such a beginner question, but all examples I have seen use strings.
My Goal is to assign the value returned by a formula to a variable, and then set a cell equal to the value of the variable.

The formula I am using returns a value of 10.

[vba]

Sub GetPrice()

Dim StockNum As Integer

'These two lines result in "0" being placed in Cell E6
StockNum = FormulaR1C1 = "=COUNTA(Main!R3C2:R3C11)"
Range("Main!E6").Value = StockNum ' "Why does make the Cell "0" and not "10" ?

'This line results in "10" being places in Cell E7
Range("Main!E7").FormulaR1C1 = "=COUNTA(Main!R3C2:R3C11)"

End Sub

[/vba]

Thank you for you help.

---

Greetings somail,

There are a couple of issues. Firstly, for this code to run without reporting an error immedietely means that you are not using Option Explicit at the top of your modules. Otherwise, FormulaR1C1 would have been reported as a Compile error: Variable not defined.

To fix this, whilst in VBE (the code window) go to Tools|Options from the menubar. From the Editor tab, tick the Require Variable Declaration checkbox ,then the <OK> button. Any modules you already have created will need Option Explicit manually typed in at the very top of the modules, but new module created will automatically have this inserted. Neat huh?

Okay, now to the code: Without declaration, FormulaR1C1 is an implicitly created variable that Excel has to guess at. Seeing as it has not been assigned a value, it is being evaluated by Excel as Empty when it gets evaluated in

StockNum = FormulaR1C1 = "=COUNTA(Main!R3C2:R3C11)"

Basically, your code is saying: what is the result of... does an empty variable equal a string. Since the result is False, StockNum becomes zero as an integer.

Try:

[VBA]
Range("Main!E7").FormulaR1C1 = "=COUNTA(Main!R3C2:R3C11)"
Range("Main!E7").Value = Range("Main!E7").Value
[/VBA]

Hope that helps,

Mark

I am not a fan of R1C1 notation normally.
[VBA]Debug.Print Evaluate("=COUNTA(Main!B3:K3)")[/VBA]
Of course it will error if the worksheet named Main does not exist.

Got it!

Thanks for the Help you two

Ok, I wanted to ask another question but did not want to load up the front page with multiple questions from myself.

What I want to do, is Step through 10 cells (B3:K3) and store the String Value of each Cell into an array. Immediatley after storing a value, I would like to output the String 3 cells below the originals in Row B. (basically as a check, and just so I understand the process)

Here is my code. I am getting an error 450.

[VBA]

Sub Macro1()

Dim StockName(10) As String

For i = 1 To 10

StockName(i) = Worksheets("Main").Range.Cells(3, i + 2).Value

Worksheets("Main").Cells(6, i + 2).Value = StockName(i)

Next

End Sub

[\VBA]

Thanks again for all of your help.

The end code tag for vba is /vba.

Two methods: When using arrays to range, you sometimes need a WorksheetFunction.Transpose(a) or WorksheetFunction.Transpose(WorksheetFunction.Transpose(a)) depending on the matrix orientation.
[vba]
Sub test1()
Worksheets("Main").Range("B3:K3").Copy Worksheets("Main").Range("B6:K6")
Application.CutCopyMode = False
End Sub

Sub test2()
Dim a() As Variant
a() = Worksheets("Main").Range("B3:K3").Value
Worksheets("Main").Range("B6:K6").Value = a()
End Sub[/vba]

A friendly Howdy to both of you, and welcome to vbaexpress to you somail :-) I am afraid I missed that you had just joined.

Just because part of this (the 'why ain't this string working in Evaluate?' part) was frustrating me for a moment, and because I cannot think of when I last used R1C1 notation (and thus, had not run into this issue), I did a small amount of testing. So... just in case it adds anything worthwhile:

Setup:

On worksheet named "MAIN", I entered values of {4,6,8,,,16,18,20,22} in B3:K3, where I left E3:G3 empty as indicated.

I then stepped thru this a couple of times:

[vba]
Sub test()
Dim lCountA As Long
Dim lSum As Long
On Error GoTo SimpleFix
lCountA = Evaluate("COUNTA(Main!B3:K3)")
lCountA = Evaluate("COUNTA(Main!R3C2:R3C11)")
lSum = Evaluate("SUM(Main!B3:K3)")
lSum = Evaluate("SUM(Main!R3C2:R3C11)")

lCountA = 0: lSum = 0

lCountA = IIf(Application.ReferenceStyle = xlA1, _
Evaluate("COUNTA(Main!B3:K3)"), _
Evaluate("COUNTA(Main!R3C2:R3C11)") _
)

lSum = IIf(Application.ReferenceStyle = xlA1, _
Evaluate("SUM(Main!B3:K3)"), _
Evaluate("SUM(Main!R3C2:R3C11)") _
)

Range("Main!E6").Value = lCountA
Range("Main!E7").Value = lSum

Exit Sub
SimpleFix:
lCountA = 0
lSum = 0
MsgBox Err.Number & vbCrLf & Err.Description
Err.Clear
Resume Next
End Sub
[/vba]

With A1 notation in effect:

The first lCountA = ... executes fine as one would expect, and likewise the second errors.
The first lSum = ... executes fine as one would expect, and again, the second errors.
The IIf Function gives proper returns of 7 and 94.

With R1C1 notation in effect:

The first lCountA = ... returns 1! The second lCountA correctly returns 7.
The first lSum = ... errors and the second correctly returns 94.
The IIf Function gives proper returns.

Well, not sure if that is worthwhile info, but it would seem to me that in a case where we think it advantageous to use Evaluate on a formula, we cannot depend upon an error occurring, and thus, could not depend upon error handling to fix anything. Since we cannot control what notation the user chooses, an IIf or If...Then would seem prudent.

FWIW, in the current scenario, I would certainly still just tack the formula into the cell and overwrite it with Range.Value = Range.Value.

Mark

Jeepers! I must be typing slower than my normal snail's pace.

Well, just to add to what Ken has already showed you, I would note as to your array. For example, if looping values into an array, either of the below will work.
[vba]
Sub Macro0()
Dim StockName(1 To 10) As String
Dim I As Long

For I = 1 To 10
'For error 450, ditch the .Range! You are still not using Option Explicit, or 'I' would
'fail to compile
'StockName(1, I) = Worksheets("Main").Range.Cells(3, I + 2).Value
StockName(I) = Worksheets("Main").Cells(3, I + 2).Value
Worksheets("Main").Cells(6, I + 2).Value = StockName(I)
Next
End Sub

Sub Macro1()
Dim StockName(1 To 1, 1 To 10) As String
Dim I As Long

For I = 1 To 10
StockName(1, I) = Worksheets("Main").Cells(3, I + 2).Value
Worksheets("Main").Cells(6, I + 2).Value = StockName(1, I)
Next
End Sub
[/vba]

Please note that even in the 1-dimension array, I set the base, rather than depending on Excel to do it. In yours, unless you have Option Base 1 included, you have an empty element, as your array is 0 to 10.

To keep my pea brain from being confused, I try and always use a 2-dimension array when working with a Range.

Mark

Awesome Thanks you too.

Kenneth, from your code I realized that I do not need to have range in there.

GTO, as someone just starting off (I am used to C++), that info is very helpful. It gets me doing "best practices" from the start.

EDIT: GTO, Thanks for the second post on Arrays, it cleared a lot up. I swear I put "Require Variable Decloration" :-)

Happy to be of what help I can and sorry if I sounded overly insistent on the Option Explicit part, but I promise it'll save headaches. It's sorta like a SpellCheck for VBIDE...:)

That's the bad thing about Mark, he has ideas that inspire. :)

I just hate to think about conversions since I am so lazy. Here is one lazy idea.

[vba]Sub R1C1()
Dim f As String, rc As Long

f = "=SUM(Main!R3C2:R3C11)" 'Equal sign is required for Application.ReferenceStyle

' "=SUM(Main!B3:K3)" = "=SUM(Main!R3C2:R3C11)"
' See this for another converter: http://www.vbaexpress.com/kb/getarticle.php?kb_id=254

If Application.ReferenceStyle = xlR1C1 Then
Worksheets("Main").Range("R3C2:R3C11").Clear
Worksheets("Main").Range("R3C2:R3C11").Value = 1
Else
Worksheets("Main").Range("B3:K3").Clear
Worksheets("Main").Range("B3:K3").Value = 1
End If

rc = IIf(Application.ReferenceStyle = xlA1, _
Evaluate(Application.ConvertFormula(f, xlR1C1, xlA1)), _
Evaluate(f))

MsgBox rc, vbInformation, "=SUM(Main!R3C2:R3C11)"
End Sub[/vba]

Hi Ken,

Well I am certainly glad I decided to post that bit, not only for the OP, but for a new 'trick' I didn't know! Thank you, as I don't believe I've run across .ConvertFormula, or if I ever spotted it in Help, I never used it and would never have recalled the Method. That is a neat thing to know!

I would mention a slight hiccup, as I bet you are also just so used to using A1 notation (as am I), that you may have not tested both ways. R1C1 notation supplied as a string to .Range evidently cannot be used. I searched 2000 help (I'm at home) and could not find anything at first, but searched online and found this under 2003 vba Help at:

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Under Range Property it states, "If you use a text argument for the range address, you must specify the address in A1-style notation (you cannot use R1C1-style notation)."

Of course then I went back and searched Range Property in 2000's Help, which includes:

"Cell1 Syntax 1: Required Variant. The name of the range. This must be an A1-style reference in the language of the macro. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they’re ignored. You can use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the macro."

Being as when I first ever read that topic, I probably had no clue as to what an intersection would be and wouldn't have tried using a space, I tested...

[vba]
Sub testrange()
'// INTERSECTION //
MsgBox Worksheets("MAIN").Range("B3:K3 I3:O3").Address(0, 0) 'Returns 'I3:K3'
MsgBox Range("MAIN!B3:K3 MAIN!I3:O3").Address(0, 0) 'SAA

'// UNION //
MsgBox Worksheets("MAIN").Range("B3:K3,I3:O3").Address(0, 0) 'Returns 'B3:K3,I3:O3' which seems weird
MsgBox Range("MAIN!B3:K3,MAIN!I3:O3").Address(0, 0) ' SAA

Dim r As Range
Set r = Range("MAIN!B3:K3,MAIN!I3:O3")

MsgBox r.Address(0, 0) 'Still returns 'B3:K3,I3:O3'
Set r = Worksheets("MAIN").Range("B3:K3,I3:O3")
MsgBox r.Address(0, 0) 'SAA

r.Value = 2 'works fine

'// Application.Union //

Set r = Application.Union(Range("MAIN!B3:K3"), Range("MAIN!I3:O3"))

MsgBox r.Address(0, 0) 'Returns 'B3:O3' and my brain hurts

r.Value = 3 'of course works

End Sub
[/vba]

Not sure how much of that I'll recall (much less use), but I found the union part rather strange.

Anyways, I am running way late, but found this interesting. Thanks again for the .ConvertFormula, I think a handy thing to know, whether using Evaluate or needing to supply an R1C1 string to .ExecuteExcel4Macro.:thumb

Mark

Good catch! To use Range properly with R1C1 set:
[VBA] Worksheets("Main").Range(Application.ConvertFormula("=R3C2:R3C11", xlR1C1, xlA1)).Select[/VBA]

For ExecuteExcel4Macro, I just use the Address though ConvertFormula could be used.
[VBA]
Sub t()
MsgBox GetValue("x:\test", "test.xlsx", "Sheet1", "A1")
End Sub

'=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
Private Function GetValue(path, file, sheet, ref)
' path = "d:\files"
' file = "budget.xls"
' sheet = "Sheet1"
' ref = "A1:R30"

Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "file not found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function[/VBA]

The use of areas for ranges that are typically non-consecutive is handy.
[VBA]Sub t()
Dim r As Range, c As Range, s As String
Set r = Application.Union(Range("MAIN!B3:C3"), Range("MAIN!E3:F3"))
For Each c In r
s = s & c.Address & vbLf
Next c
MsgBox "Application.Union(Range(""MAIN!B3:C3""), Range(""MAIN!E3:F3"")" & vbLf & vbLf & _
"By Each Cell: " & vbLf & s & vbLf & _
"r.Address: " & r.Address & vbLf & vbLf & _
"r.Address(0, 0): " & r.Address(0, 0)
End Sub[/VBA]