Nested Loop Excel VBA

Question

I need your help in nexted VBA loop. I have some data in two columns and blank rows between rows. This macro loop through a column and find out if it contain certain character. If it' blank then I want it to move to next row. If it contain "Den", then select a specific worksheet ("D-Temp") else select ("M-Temp"). After selecting right Worksheet, it need to fill up text boxs with data from 2nd column as per Row no. The code I have created so far is

Sub Template()

    Dim j As Long
    Dim c As Range, t As Range
    Dim ws As String

    j = 5
    With Sheets("Sample ")
        For Each c In .Range("I3", .Cells(.Rows.Count, "I").End(xlUp))
            If c.Value = "" Then
                Next ' `Not getting how to jump to next one`
            ElseIf c.Value = "DEN" Then
                ws = "D-Temp"
            Else
                ws = "M-Temp"
            End If

        For Each t In .Range("P3", .Cells(.Rows.Count, "P").End(xlUp))
            If t.Value <> "" Then
                j = j + 1
                Sheets("M-Temp").Copy after:=Sheets(Sheets.Count)
                ActiveSheet.Shapes("Textbox 1").TextFrame.Characters.Text = t.Value
                ActiveSheet.Shapes("textbox 2").TextFrame.Characters.Text = t.Offset(, -1).Value
            End If
        Next
    Next
    End With

Any help ?? Below is the sample Data I have :

Type    Name 1  Name2
DEN     Suyi    Nick
                     'Blank row'
PX      Mac     Cruise

I want macro to Identify Type & select template worksheet (D or M) as per that and fill textboxes on that template with Name 1 & Name2 respectively.

I believe you just need to say "Next j" instead of "Next". You need to specificy which variable it should affect. — Emily Alden
– Emily Alden, Commented May 21, 2018 at 16:35
Beg your pardon, but what's the point in filling same textbox with different values in a loop? — JohnyL
– JohnyL, Commented May 22, 2018 at 7:14
@JohnyL There are two text boxes on each template. I want macro to filled those text boxes with name1 & name 2 as per Type. — prashant
– prashant, Commented May 22, 2018 at 11:42
@prashant Oops, my bad... Now I see. When you copy, a new sheet is active sheet. — JohnyL
– JohnyL, Commented May 22, 2018 at 11:44

DisplayName · Accepted Answer · 2018-05-22 06:24:28Z

1

may be you're after this:

Option Explicit

Sub Template()
    Dim c As Range

    With Sheets("Sample")
        For Each c In .Range("I3", .Cells(.Rows.Count, "I").End(xlUp)).SpecialCells(xlCellTypeConstants) ' loop through referenced sheet column C not empty cells form row 3 down to last not empty one
            Worksheets(IIf(c.Value = "DEN", "D-Temp", "M-Temp")).Copy after:=Sheets(Sheets.Count) ' create copy of proper template: it'll be the currently "active" sheet
            With ActiveSheet ' reference currently "active" sheet
                .Shapes("Textbox 1").TextFrame.Characters.Text = c.Offset(, 7).Value ' fill referenced sheet "TextBox 1" shape text with current cell (i.e. 'c') offset 7 columns (i.e. column "P") value
                .Shapes("Textbox 2").TextFrame.Characters.Text = c.Offset(, 6).Value ' fill referenced sheet "TextBox 2" shape text with current cell (i.e. 'c') offset 6 columns (i.e. column "O") value
            End With
        Next
    End With
End Sub

answered May 22, 2018 at 6:24

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DisplayName Over a year ago

@prashant, any feedback?

prashant Over a year ago

Sorry to delay in response. Your code is working perfectly fine the way I want to. just one small thing. You have used .Specialcells(xlCellTypeConstants) to omit blank cells. However it's still considering those cells. Any idea how to correct it ??

prashant Over a year ago

I got this now with a simple tweak in you code. Thanks a lot for your help !!

Tim Williams · Accepted Answer · 2018-05-21 16:41:08Z

0

If I'm not mis-understanding your current nesting...

With Sheets("Sample ")
    For Each c In .Range("I3", .Cells(.Rows.Count, "I").End(xlUp))

        If c.Value <> "" Then
            If c.Value = "DEN" Then
                ws = "D-Temp"
            Else
                ws = "M-Temp"
            End If
            For Each t In .Range("P3", .Cells(.Rows.Count, "P").End(xlUp))
                If t.Value <> "" Then
                    j = j + 1
                    Sheets("M-Temp").Copy after:=Sheets(Sheets.Count)
                    ActiveSheet.Shapes("Textbox 1").TextFrame.Characters.Text = t.Value
                    ActiveSheet.Shapes("textbox 2").TextFrame.Characters.Text = t.Offset(, -1).Value
                End If
            Next
        End if 'not blank
    Next
End With

answered May 21, 2018 at 16:41

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

1 Comment

prashant Over a year ago

Thanks for your input. Your code is skipping blank rows and creating template. But I want this to fill textbox as per that specific row value. I have mention sample data in question. Let me know If I am clear enough.

Bill Hileman · Accepted Answer · 2018-05-21 16:41:31Z

0

If I understand your question, correctly, you need to change your if/then logic slightly:

Sub Template()

    Dim j As Long
    Dim c As Range, t As Range
    Dim ws As String

    j = 5
    With Sheets("Sample ")
        For Each c In .Range("I3", .Cells(.Rows.Count, "I").End(xlUp))
            If c.Value <> "" Then
                If c.Value = "DEN" Then
                    ws = "D-Temp"
                    Exit For
                Else
                    ws = "M-Temp"
                    Exit For
                End If
            End If
        Next
        For Each t In .Range("P3", .Cells(.Rows.Count, "P").End(xlUp))
            If t.Value <> "" Then
                j = j + 1
                Sheets("M-Temp").Copy after:=Sheets(Sheets.Count)
                ActiveSheet.Shapes("Textbox 1").TextFrame.Characters.Text = t.Value
                ActiveSheet.Shapes("textbox 2").TextFrame.Characters.Text = t.Offset(, -1).Value
            End If
        Next
    End With
End Sub

You might want to add code to make sure that ws is set to something (not all columns were blank).

answered May 21, 2018 at 16:41

Bill Hileman

2,8462 gold badges20 silver badges25 bronze badges

1 Comment

prashant Over a year ago

the above code is creating "D" template only inspite of value in "I" column. I have edited my question with sample data. Let me know if it's clear enough.

Collectives™ on Stack Overflow

Nested Loop Excel VBA

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related