CODEWITHSUNDEEP
excelvba
Khirxn
Khirxn

Reputation: 27

FillDown Method

I have a code that involves a Drag Down Formula (FillDown). After the VLOOKUP is done, the FillDown method works well if there are 2 or more rows with values in it. But, I can't seem to understand why if there is only one row with values in it, the FillDown method does not work properly. It brings down the value of the first row (Header) instead and replaces the value. You may look at the picture I attached to see what I meant. After VLOOKUP, the rows should show the values as can be seen in the second picture I attached.

enter image description here

enter image description here

     Workbooks("Data.xlsx").Activate
    
    'COPY PASTE POSITIVE BARCODES
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("A2:A" & lastrow).Copy
        
    Workbooks("SIMKA CT VALUE FORMULA.xlsx").Activate
    
    Sheets("Sheet3").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    
    'APPLICATION OF LOOKUP FORMULA
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(LOOKUP(2,1/('[Data.xlsx]Sheet2'!C1=RC1)/('[Data.xlsx]Sheet2'!C15=R1C),('[Data.xlsx]Sheet2'!C16)),"""")"
        
    Selection.AutoFill Destination:=Range("B2:F2"), Type:=xlFillDefault
    Range("B2:F2").Select
    Range("B2:F" & lastrow).FillDown

Upvotes: 0

Views: 352

Answers (1)

artnib
artnib

Reputation: 508

...
Selection.AutoFill Destination:=Range("B2:F2"), Type:=xlFillDefault
Range("B2:F2").Select
'FillDown isn't required for single row as it already has formulas
If lastrow > 2 Then Range("B2:F" & lastrow).FillDown

Upvotes: 1

Related Questions