Fill down based on cell value

[izet99]

Hi,

Need some help modifying formula below.

It's vlookup formula which is placed into Cell H2 and the code copies the formula down to the bottom of the range. What guides the bottom of the range is the reference to the 7^th column in the second line of VBA code.

Sub FillTable()   'Excel VBA to filldown a range

    Range("H2").Value = "=VLOOKUP(G2,$A$2:$B$17,2,0)"
    Range("H2:H" & Cells(Rows.Count, 7).End(xlUp).Row).FillDown

End Sub

I would like to modify code to look into specific Cell H4, sheet "Setup".... H4 cell have formula to count number of items from another sheet… so if value of H4 is 53, I would like code above fill down my formula 53 rows.

Any help or suggestion would be greatly appreciated
Regards,
Izet

 

[Weaver]

If I've understood you correctly, try

    myvar = Sheets("setup").Range("H4").Value
    Range("H2").Resize(myvar).Formula = "=VLOOKUP(G2,$A$2:$B$17,2,0)"

 

[izet99]

Hi Weaver,

This is perfect, thank you very much, it works with my vlookup.... however my Array formula get rusty... it just copy first line down...

Sub FillTable()   'Excel VBA to filldown a range
       
        myvar = Sheets("Setup").Range("G5").Value
    Range("B25").Resize(myvar).FormulaArray = "=IF(ROWS(R25C[1]:RC[1])<=VLOOKUP(R5C6,Setup!R3C4:R18C7,4,FALSE),INDEX(rngWorkScope,SMALL(IF(rngProductLine=R9C4,IF(rngProjectType=R10C4,IF(rngModule=R5C6,IF(rngLevel=R6C6,ROW(rngWorkScope)-ROW(DepotScope!R2C5)+1)))),ROWS(R25C[1]:RC[1]))),"""")"

End Sub

Original Formula:
{=IF(ROWS(C$25:C25)<=VLOOKUP($F$5,Setup!$D$3:$G$18,4,FALSE),
INDEX(rngWorkScope,
SMALL(
IF(rngProductLine=$D$9,
IF(rngProjectType=$D$10,
IF(rngModule=$F$5,
IF(rngLevel=$F$6,
ROW(rngWorkScope)-ROW(DepotScope!$E$2)+1)))),
ROWS(C$25:C25))),"")}

Am I missing something regarding array formula?

 

[izet99]

Code below seems to work, it fills down array formula.

Sub FillTable()   'Excel VBA to filldown a range

   
        myvar = Sheets("Setup").Range("G5").Value
    
        Range("B25").FormulaArray = "=IF(ROWS(R25C[1]:RC[1])<=VLOOKUP(R5C6,Setup!R3C4:R18C7,4,FALSE),INDEX(rngWorkScope,SMALL(IF(rngProductLine=R9C4,IF(rngProjectType=R10C4,IF(rngModule=R5C6,IF(rngLevel=R6C6,ROW(rngWorkScope)-ROW(DepotScope!R2C5)+1)))),ROWS(R25C[1]:RC[1]))),"""")"
        
        Range("B25").AutoFill Range("B25:B25" & myvar)
        

End Sub

Thanks a lot for your help.