Solved: Add Formula to Cell Using VBA

[bconner]

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]

RngStart.Offset(0, 1).Formula = "=Trim(A1)"
[/code]

You need to change this line to

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

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

Best regards,

Lincoln

[bconner]

Lynnnow, thank you it worked perfectly

[lynnnow]

Remember to mark your thread "Solved"

[Bob Phillips]

No loop


[vba]

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