Range("J2").Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
False
Selection.FormulaArray = _
"=MIN(IF([Serial Number]=[@[Serial Number]],IF(NOT(ISBLANK([Service Date])),[Service Date],FALSE)))"
Range("J2:J142").FormulaArray = _
"=MIN(IF(Table1[Serial Number]=Table1[@[Serial Number]],IF(NOT(ISBLANK(Table1[Service Date])),Table1[Service Date],FALSE)))"
ActiveWindow.SmallScroll Down:=-27
Range("J1").Select
ActiveCell.FormulaR1C1 = "Min Day"
This is the macro recorded when the action is preformed. I want to modify this so that it works for table of all sizes. I tried using the code below but it says that "x1Down" is not defined. Any advice?
Dim example As Range
Set example = Range(Range("I2"), Range("I2").End(x1Down))
example.Offset(0, 1).Select
Selection.FormulaArray = _
"=MIN(IF([Serial Number]=[@[Serial Number]],IF(NOT(ISBLANK([Service Date])),[Service Date],FALSE)))"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Min Day"
Bookmarks