PDA

View Full Version : Creating a Formula on a Worksheet with VBA



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:

Bob Phillips
09-26-2006, 03:56 PM
Sub BummerFormula()
Dim Temp As String
Temp = "Shares2"
Range("AK44").Formula = "=" & Temp
Range("AK45").Formula = "= ""and ""&" & Temp
End Sub

Simon Lloyd
09-26-2006, 03:57 PM
Hi ive not tested it but it looks like you left a couple of "'s out when i added them the and capitalised (AND) like this:Range("A5").Formula = "= """"" And """"" " & TempRegards,
Simon

Simon Lloyd
09-26-2006, 03:59 PM
"El Xid" strikes again!.................just shows i dont know what i'm talking about but i'm trying..............my mum says!

Regards,
Simon