Macro Excel that copy formula (vlookup or if) until a blank cell is found in column X


I need your help with a macro that will copy and paste a formula until a blank cell is reach in the column beside the formula.

In my tab "Inventory", in cell F2, my formula is validation with a IF Condition, that minimum requirement for the RAM.  That formula need to be copied down to the last row.

Cell F2:  =IF(E2<102400,"< 1gig", "> 1gig")

I also have 3 more formulas that need to be copied down to the last row as well

Cell K2: =VLOOKUP(J2,Equivalence!B:C,2,FALSE)
Cell L2: =VLOOKUP(K2,Equivalence!C:D,2,FALSE)

and a Nested IF formula in N2:
=IF(ISNUMBER(FIND("2000 Pro",M2)),"Windows 2000", IF(ISNUMBER(FIND("Windows(R)",M2)),"Windows XP x64",IF(ISNUMBER(FIND("XP Pro",M2)),"Windows XP",IF(ISNUMBER(FIND("7 Pro",M2)),"Windows 7",IF(ISNUMBER(FIND("7 Ult",M2)),"Windows 7 Ultimate",IF(ISNUMBER(FIND("7 Ent",M2)),"Windows 7 Enterprise","undertemined"))))))

Can you help?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rodney EndrigaData AnalystCommented:
Try this code and see if it produces the results you requested:

Sub EE_FormulaInsert()
Dim rng As Range, cell As Range

Set rng = Range("X1:X" & ActiveSheet.UsedRange.Rows.Count)

For Each cell In rng
    If IsEmpty(cell.Value) = True Then
        Exit For
        cell.Offset(0, -18).FormulaR1C1 = "=IF(RC[-1]<102400,""< 1gig"", ""> 1gig"")"
        cell.Offset(0, -13).FormulaR1C1 = "=VLOOKUP(RC[-1],Equivalence!C[-9]:C[-8],2,FALSE)"
        cell.Offset(0, -12).FormulaR1C1 = "=VLOOKUP(RC[-1],Equivalence!C[-9]:C[-8],2,FALSE)"
        cell.Offset(0, -10).FormulaR1C1 = _
            "=IF(ISNUMBER(FIND(""2000 Pro"",RC[-1])),""Windows 2000"", IF(ISNUMBER(FIND(""Windows(R)"",RC[-1])),""Windows XP x64"",IF(ISNUMBER(FIND(""XP Pro"",RC[-1])),""Windows XP"",IF(ISNUMBER(FIND(""7 Pro"",RC[-1])),""Windows 7"",IF(ISNUMBER(FIND(""7 Ult"",RC[-1])),""Windows 7 Ultimate"",IF(ISNUMBER(FIND(""7 Ent"",RC[-1])),""Windows 7 Enterprise"",""undertemined""))))))"
    End If
Next cell

End Sub

Data needs to be in (ColumnX).
Saurabh Singh TeotiaCommented:
Rather then using loop i will apply formulas in one go like this...

Sub applyformulas()
Dim lrow As Long
lrow = Cells(Cells.Rows.Count, "x").End(xlUp).Row
Range("F2:F" & lrow).Formula = "=IF(E2<102400,""< 1gig"", ""> 1gig"")"
Range("k2:k" & lrow).Formula = "=VLOOKUP(J2,Equivalence!B:C,2,FALSE)"
Range("L2:L" & lrow).Formula = "=VLOOKUP(K2,Equivalence!C:D,2,FALSE)"
Range("N2:N" & lrow).Formula = "=IF(ISNUMBER(FIND(""2000 Pro"",M2)),""Windows 2000"", IF(ISNUMBER(FIND(""Windows(R)"",M2)),""Windows XP x64"",IF(ISNUMBER(FIND(""XP Pro"",M2)),""Windows XP"",IF(ISNUMBER(FIND(""7 Pro"",M2)),""Windows 7"",IF(ISNUMBER(FIND(""7 Ult"",M2)),""Windows 7 Ultimate"",IF(ISNUMBER(FIND(""7 Ent"",M2)),""Windows 7 Enterprise"",""undertemined""))))))"

End Sub

Open in new window

Rob HensonFinance AnalystCommented:
If the formulas are alongside a column of data which is I assume expanding, you don't need a macro to do it.

Simply double click on the bottom right hand corner of the last cell populated with the formula. This will then populate down until it reaches the bottom of the data alongside it. If you have multiple neighbouring columns, select the cells with formulas and double click the bottom right of the rightmost of the selection.

Rob H

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

mldaigle1Author Commented:
Hello Experts,

I did try both propositions

@Saurabh : i put your code in a macro and unfortunately, nothing happen.  Maybe i am missing something

@Rob: This is a nice trick that i didn't know and try to record it in a macro, unfortunately, i will work for an exact amount of rows which i don't know in advance ... this may vary between 21k to 23k
Rob HensonFinance AnalystCommented:
Alternatively, convert your list to a Table. The formulas will then Auto-Populate as the table of data grows.

Another alternative, can you re-arrange your data so that the four columns with formulas are next to each other? You could then just select the last four and use the double click to populate all four columns in one go.

Rob H
mldaigle1Author Commented:
Hi Robs,

I'm not sure that the conversion will help since i am already importing a table (CSV file) and i am inserting the columns and formulas in a second step.

Even if i am putting all the formulas next to each other, recording my actions will only populate up to an exact amount of row unless there is a way to code the auto-fill

kinda stuck i thing...
Rob HensonFinance AnalystCommented:
I am suggesting that you don't need to do it with code at all, select the last four cells and double click. How much extra time will that take versus clicking a button to do it for you?
mldaigle1Author Commented:
You're maybe right.... does not take much longer to auto-populate the formulas with the doubleclicking.

I'm buying it!


Thanks Rob!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.