Cyberdude
09-26-2006, 02:09 PM
The sheet has a cell with the defined name Shrs2.
The Shrs2 cell contains a formula that generates a value of ?9200?, which is displayed as the number ?9200?.
My objective is to create a formula on the worksheet using VBA. I have been unable to create the formula with the value of a VBA variable. The value of the VBA variable (Temp) is the defined named Shrs2.
I attempt to run the following sub:
Sub BummerFormula()
Dim Temp As String
Temp = "Shrs2"
Range("AK44").Formula = "=" & Temp ?<-(This displays ?9200? in cell ?AK44?)
?(The next statement gets an error ?Application-defined or object-defined error?)
Range("AK45").Formula = "= ""and "" " & Temp
End Sub The only difference between the two statements is that I?ve added the constant ?and? to be concatenated with value of variable Temp.
If I enter the following formula directly on the worksheet:
=?and " & Shrs2
then the value displayed is ?and 9200?.
What am I missing?? :confused:
The Shrs2 cell contains a formula that generates a value of ?9200?, which is displayed as the number ?9200?.
My objective is to create a formula on the worksheet using VBA. I have been unable to create the formula with the value of a VBA variable. The value of the VBA variable (Temp) is the defined named Shrs2.
I attempt to run the following sub:
Sub BummerFormula()
Dim Temp As String
Temp = "Shrs2"
Range("AK44").Formula = "=" & Temp ?<-(This displays ?9200? in cell ?AK44?)
?(The next statement gets an error ?Application-defined or object-defined error?)
Range("AK45").Formula = "= ""and "" " & Temp
End Sub The only difference between the two statements is that I?ve added the constant ?and? to be concatenated with value of variable Temp.
If I enter the following formula directly on the worksheet:
=?and " & Shrs2
then the value displayed is ?and 9200?.
What am I missing?? :confused: