paste Formula using VBA

  • Thread starter Thread starter Tang
  • Start date Start date
T

Tang

Dear all,

how to paste the following formula into column B using VBA, where A1 will
change according to row

=IF(SUBSTITUTE(A1,")","")=A1,A1,LEFT(A1,FIND(")",A1)))
 
rw=Activecell.Row
activecell.formular1c1="=IF(SUBSTITUTE(A" & rw & ","")"","""")=A" & rw &
",A" & rw & ",LEFT(A" & rw & ",FIND("")"",A" & rw & ")))"
 
Another way:


with activesheet
.range("b1:b" & .cells(.rows.count,"A").end(xlup).row).formula _
= "=IF(SUBSTITUTE(A1,"")"","""")=A1,A1,LEFT(A1,FIND("")"",A1)))"
end with

I guessed that you wanted to fill column B from row 1 to the last used row in
column A.

If you write your formula using the first cell in the range, it'll work just
like selecting the range on the worksheet, writing the formula for the
activecell and hitting ctrl-enter to fill the range.
 
thanks

Dave Peterson said:
Another way:


with activesheet
.range("b1:b" & .cells(.rows.count,"A").end(xlup).row).formula _
= "=IF(SUBSTITUTE(A1,"")"","""")=A1,A1,LEFT(A1,FIND("")"",A1)))"
end with

I guessed that you wanted to fill column B from row 1 to the last used row in
column A.

If you write your formula using the first cell in the range, it'll work just
like selecting the range on the worksheet, writing the formula for the
activecell and hitting ctrl-enter to fill the range.
Click to expand...
 
Back
Top