asked on

Excel Macro - loop

Hello,
Can you please help with a macro,
I have about +or- 2000 rows in Column "B"
I have about +or- 15 rows in Column "E"

I need Column "I" to be,
Insert into Departments VALUES ('COLUMN B','COLUMN E','COLUMN B')

Please see attached Sample,
thanks
Sample.xlsx

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

If you want the colors then...

Sub InsertInto()

Dim lngLastRowB As Long
Dim lngLastRowE As Long
Dim lngRowB As Long
Dim lngRowE As Long
Dim lngNR As Long
Dim lngFirstRow As Long

lngLastRowB = Range("B1048576").End(xlUp).Row
lngLastRowE = Range("E1048576").End(xlUp).Row
lngNR = 1

For lngRowE = 2 To lngLastRowE
    lngFirstRow = lngNR + 1
    For lngRowB = 2 To lngLastRowB
        lngNR = lngNR + 1
        Cells(lngNR, "I") = "Insert into Departments VALUES ('" & Cells(lngRowB, "B").Text & "','" _
                          & Cells(lngRowE, "E").Text & "','" _
                          & Cells(lngRowB, "B").Text & "')"
    Next
    Range("I" & lngFirstRow & ":I" & lngNR).Interior.Color = Range("E" & lngRowE).Interior.Color
Next
End Sub

Open in new window

W.E.B

ASKER

Awesome,
Thank you.

Martin Liss

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2016