Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

autofill the formula by vba in specific column

0

hello

I try  design a simple  macro  to  auto fill  the  formula   in COL B from  B4  to  lastrow contains  value    but   it  gives  me  error "Application-defined or Object-defined error"

in line  of  the  formula 

but  the  formula   works  normally  if  I  write  inside  the  cell 

so  any  suggestion  to  correct  this  error 

Sub n()
With ThisWorkbook.Sheets("Sheet1")
lr = Range("a" & Rows.Count).End(xlUp).Row
With Range("b4:b" & lr)
.Formula = "=TRIM(MID(SUBSTITUTE(a4,"",REPT("",100)),100,100))"
.Value = .Value
End With
End With

End Sub
Answer
Discuss

Answers

0
Selected Answer

Leopard

That formula may work in a cell but you cannot just paste it into a VBA line (since the inverted commas won't work as expected). Change it to this (with two "", in bold):

Sub n()
With ThisWorkbook.Sheets("Sheet1")
lr = Range("a" & Rows.Count).End(xlUp).Row
With Range("b4:b" & lr)
.Formula = "=TRIM(MID(SUBSTITUTE(a4,"" "",REPT("" "",100)),100,100))"
.Value = .Value
End With
End With

End Sub

Note that I also added the space I think you need to get the formula to work (so it's like " " in a cell).

Also you could replace each " in a cell formula with Chr(34) but that leads to a more verbose line like this:

.Formula = "=TRIM(MID(SUBSTITUTE(a4," & Chr(34) & " " & Chr(34) & ",REPT(" & Chr(34) & " " & Chr(34) & ",100)),100,100))"
Discuss

Discussion

John
thanks  for  your  help   it's  better 
leopard (rep: 88) Apr 27, '21 at 5:08 pm
You're welcome Leopard, thanks for selecting my answer
John_Ru (rep: 6102) Apr 27, '21 at 5:10 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login