excel vba to drag down VLOOKUP formulas

[BORUCH]

hi

i have VLOOKUP formulas all over my worksheet starting from row 12 different spots i would like a vba that would find them all and drag it down same behavior like the fil handle till the first empty

thanks

 

[MARK858]

Are your Vlookups only being dragged down to the one cell as you show?

 

[BORUCH]

Are your Vlookups only being dragged down to the one cell as you show?

no it being dragged down till the blank line

 

[MARK858]

Try the code below on a copy of your data (please note it relies on no other formulas than the Vlookup's being in the range)

Sub BORUCH()
    Dim myCell As Range, x As Range, y As Long
    
    For Each myCell In Range("H13:K28").SpecialCells(xlCellTypeFormulas, 23)
        Set x = Cells(myCell.Row, 1)
        
        If x.Offset(1) <> "" Then
            y = Range(x, x.End(xlDown)).Rows.Count
            myCell.Resize(y).Formula = myCell.Formula
        End If
    
    Next

End Sub

 

[BORUCH]

Try the code below on a copy of your data (please note it relies on no other formulas than the Vlookup's being in the range)

Sub BORUCH()
    Dim myCell As Range, x As Range, y As Long
   
    For Each myCell In Range("H13:K28").SpecialCells(xlCellTypeFormulas, 23)
        Set x = Cells(myCell.Row, 1)
       
        If x.Offset(1) <> "" Then
            y = Range(x, x.End(xlDown)).Rows.Count
            myCell.Resize(y).Formula = myCell.Formula
        End If
   
    Next

End Sub

Thanks that worked