Insert Formula with VBA

[Dave196]

Hi,

In the attached sample workbook, I am trying to insert the formula contained in "C2" down column "C" for each row of data. Is this possible using a Loop? Have I entered the code incorrectly?

All help is greatly appreciated.

[norie]

Dave

There's no attachment.

[Dave196]

Sorry,


here it is.

[Dave196]

Norrie:

I am having difficulty with the attachment. Will try again.

[Dave196]

Hi,

I believe the file was too large. Here it is.

[Ger Plante]

Hi Dave... maybe, I am missing something here, but Why dont you just drag the formula down (i.e. "fill down") from C2 to C31.... the formula appears to been have been correctly referenced using $, so the references should remain correct all the way down the rows... ? Or do you specifically want to automate this in VBA? (i.e. you might have 1000's of rows....)

Ger

[Ger Plante]

If its VBA you want, you could try this...

This will fill down the formula from C2... to however many rows there are (or to be more specific, to however many rows has data in Column A).

Public Sub fill_Down() 
     
    Dim my_range As Range 
     
    With Worksheets("locman") 
         
        Set my_range = .Range("C2:C" & .Range("A65536").End(xlUp).Row) 
        my_range.Formula = _ 
        "=IF(ISERROR(VLOOKUP(A2,pickcan!A$2:B$90,2,0))," & """OK to Count""" & ", VLOOKUP(A2,pickcan!A$2:B$90,2,0))" 
    End With 
     
End Sub 

HTH
Ger

[Dave196]

Ger:

Yes, automating it with VBA is what I was looking for.

Thanks a million for your help; works like a charm!