Using VB to paste a function into a cell

[moheganburner]

Is it possible to use VB to paste a function into a cell? I tried to do it literally, i.e.:

Sub Paste()

Sheets("Sheet3").Range("A:A65536").Function = "=TRIM(IF(COUNTIF(B1:C1,"*Joe*"),"Pay","")&" "&IF(SUM(COUNTIF(D1:E1,{"*Scott*","*Frank*","*Pete*"})),"Fire",""))&" "&IF(COUNTIF(F1:G1,"*Tim*"),"Raise","")"

End Sub

but I think all the quotations are a problem.

Thanks,
MB

 

[Derek]

Hi there

The best way to get a formula into code is to use the macro recorder.
First type in the formula on your worksheet and ensure it is returning the answer correctly. Start the macro recorder, select the cell with the formula, delete the = sign then reinsert it and press Enter. Stop the recorder and check out the code in the VB editor (Alt+F11).

If you have a scrollable formula (ie the formula in A1 can be scrolled right to column Z and down to row 500), record the formula in A1 as above then modify the code in the VB Editor to change the range ... eg Range("A1:Z500")

regards
Derek

 

[moheganburner]

I think I got it. Thanks, that "Macro Recorder" is a huge help.

MB