PDA

View Full Version : Solved: Add Formula to Cell Using VBA



bconner
05-11-2010, 06:20 AM
Hello All,

I am trying to add a formula in column B that will Trim() data in Column A.
Below is the code I am using but I keep getting an object defined error




Sub Format_DBMS_Dictionary_Description()

Dim RngStart As Range
Dim RngEnd As Range

Set RngEnd = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)

Range("A1").Activate

For Each RngStart In RngEnd
RngStart.Offset(0, 1).Formula = "=Trim(A1)"
Next RngStart


End Sub

lynnnow
05-11-2010, 06:40 AM
RngStart.Offset(0, 1).Formula = "=Trim(A1)"
[/code]

You need to change this line to

RngStart.Offset(0, 1).Formula = "=Trim(" & RngStart.Address(False, False, xlA1) & ")"

But I didn't get any error with the code you provided.

Best regards,

Lincoln

bconner
05-11-2010, 06:48 AM
Lynnnow, thank you it worked perfectly

lynnnow
05-11-2010, 07:03 AM
Remember to mark your thread "Solved"

Bob Phillips
05-11-2010, 07:41 AM
No loop




Sub Format_DBMS_Dictionary_Description()
Range("B1").Resize(Cells(Rows.Count, 1).End(xlUp).Row).Formula = "=Trim(A1)"
End Sub