VBA Formula Problem

G

Greg Rivet

I am using the code below and get an error with the formula statement. I
need help. TIA

Sub Salary()
Range("Salary").ClearContents
Selection.QueryTable.Refresh BackgroundQuery:=False
For Each c In Range("LName")
c.Formula = Upper(Left("D2", Find(",", "D2", 1) - 1))
Next
End Sub

Greg
 
J

J.E. McGimpsey

the formula needs to be a string. D2 should not be in quotes either:

c.Formula = "=Upper(Left(D2, Find("","", D2, 1) - 1))"

Note that this will set the entire range to read from D2. If you
want it instead to read from column D of the same line, you could
use something like

c.Formula = "=Upper(Left(D" & c.Row & ", Find("","", D" & _
c.Row & ", 1) - 1))"

or

c.FormulaR1C1 = "=Upper(Left(RC4, Find("","", RC4, 1) - 1))"


You can shorten that up a bit by replacing the loop with a single
line that sets the formula for the entire range:

Range("LName").Formula = "=Upper(Left(D2, Find("","", D2, 1) - 1))"

which will apply *relative* addressing to the entire range.
 
B

Bob Phillips

Greg,

I think this is what you want

Sub Salary()
Range("Salary").ClearContents
Selection.QueryTable.Refresh BackgroundQuery:=False
For Each c In Range("LName")
c.Formula = "=Upper(Left(D2, Find("","", D2, 1) - 1))"
Next
End Sub

You need to setup the formula as a string.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
G

Greg Rivet

J.E. and Bob thank you for your insight.

Greg
Bob Phillips said:
Greg,

I think this is what you want

Sub Salary()
Range("Salary").ClearContents
Selection.QueryTable.Refresh BackgroundQuery:=False
For Each c In Range("LName")
c.Formula = "=Upper(Left(D2, Find("","", D2, 1) - 1))"
Next
End Sub

You need to setup the formula as a string.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Top