paste Formula using VBA

[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)))

 

[Bob Umlas, Excel MVP]

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

 

[Dave Peterson]

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.

 

[Tang]

thanks

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.