Using VB to paste a function into a cell

moheganburner

Board Regular
Joined
Dec 2, 2005
Messages
158
Is it possible to use VB to paste a function into a cell? I tried to do it literally, i.e.:

Code:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,910
Messages
5,574,987
Members
412,632
Latest member
robertmwaring2
Top