VBA Formula Problem

  • Thread starter Thread starter Greg Rivet
  • Start date Start date
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
 
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.
 
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)
 
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...
 
Back
Top