Macro to add a formula to a cell and fill down the column

[cfarrell93]

I'm trying to create a macro that will insert a formula into Column D and fill all the way down the column. The formula is a series of IF statements referencing the corresponding cell in Column C.

The formula I would like to insert is the following:

=IF(C2="G1","Generic Activity",IF(C2="D2","Delivered",IF(C2="E1","Exception",IF(C2="O1","Picked Up",IF(C2="G1","Generic Activity",IF(C2="D1","Delivered",IF(C2="M2","Manifested",IF(C2="M1","Manifested",""))))))))

I have tried to do this a few ways using information from other threads, but I continue to get error messages and bugs because of the formula. I think it's because of text within the IF statements ("G1", "Generic Activity, "E1", "Exception", etc.)

Any advice is much appreciated! Thanks in advance.

 

[oldbrewer]

hello

goodbye

hell

goodnight

hi

goodnight

hello

goodbye

sup

goodnight

I1 is

=IF(H1="hello","goodbye","goodnight")

it drags down fine

so if you had =if(M1="hello","goodbye","goodnight") in cell N1

macro would be

cells(1,14). Code here to copy cell

cells(I1).select

code to paste into I1

code to paste into I2

etc

<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[cfarrell93]

I have been trying this code but can't figure out why it isn't working:

Range("D2:D").Formula = "=IF(C2=""G1"",""Generic Activity"",IF(C2=""D2"",""Delivered"",IF(C2=""E1"",""Exception"",IF(C2=""O1"",""Picked Up"",IF(C2=""G1"",""Generic Activity"",IF(C2=""D1"",""Delivered"",IF(C2=""M2"",""Manifested"",IF(C2=""M1"",""Manifested"",""))))))))"

OldBrewer thanks for your response, I think your solution might be a bit beyond my knowledge of vba coding though.

 

[oldbrewer]

all mine does is to copy the master cell and paste it into a number of other cells

in a new workbook record a macro copy G1 and paste into A1 then A2 then A3 then stop macro, and look at the code

 

[Scott Huish]

Your formula seems to have some duplicate conditions, but you can greatly simplify that by putting the whole thing in a VLOOKUP:

=IFERROR(VLOOKUP(C2,{"G1","Generic Activity";"D2","Delivered";"E1","Exception";"O1","Picked Up";"M2","Manifested";"M1","Manifested"},2,0),"")

and then here you go:

Sub test()
Range("D2:D" & Range("C" & Rows.Count).End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(C2,{""G1"",""Generic Activity"";""D2"",""Delivered"";""E1"",""Exception"";""O1"",""Picked Up"";""M2"",""Manifested"";""M1"",""Manifested""},2,0),"""")"
End Sub