CODEWITHSUNDEEP
excelvba
Gexas
Gexas

Reputation: 678

How to get row number then looping through selected range

I am creating macro, which would create Word document from template and move info from each selected row in Excel. So far, documents are created and information is moved, but from wrong cells:

For example, when I select cells from C5 to C8 macro creates 3 documents and populate them with info from C1 to C3. I need these docs to be populated with info from selected rows.

I understand I need to fix reference, but I cant think a way to refer to selected range row in which loop currently is. Please give me some advice. My code so far:

Sub Move_info_UPDT()

Dim objWord
Dim objDoc
Dim i As Long

For i = 1 To Selection.Rows.Count

    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Add(Template:="C:\Users\grusa\Desktop\test2.dotx", NewTemplate:=False, DocumentType:=0)

    With objDoc
        'I need it to  be cell from the same row loop currently is
        ' now this line move info to first doc from C1, to second from C2 and etc.
        .ContentControls.Item(1).Range.Text = Worksheets("Lapas").Cells(i, "C").Value
    End With

    objWord.Visible = True

    Next

objWord.Visible = True

End Sub

Additional question: since whole process can take a while, are there a way to show some kind of loading bar to show that macro is working?

Upvotes: 0

Views: 317

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You need to change the loop to absolute counting not relatively to the selected range as you did.

For a status information you can use Application.StatusBar to show the working step of the macro in Excel's status bar.

Option Explicit

Public Sub Move_info_UPDT()
    Dim objWord As Object
    Dim objDoc As Object

    Dim ProcessRange As Range
    Set ProcessRange = Selection

    Dim i As Long
    For i = ProcessRange.Row To ProcessRange.Rows.Count + ProcessRange.Row - 1
        Set objWord = CreateObject("Word.Application")
        Set objDoc = objWord.Documents.Add(Template:="C:\Users\grusa\Desktop\test2.dotx", NewTemplate:=False, DocumentType:=0)

        With objDoc
            'I need it to  be cell from the same row loop currently is
            ' now this line move info to first doc from C1, to second from C2 and etc.
            .ContentControls.Item(1).Range.Text = ProcessRange.Parent.Cells(i, "C").Value
        End With

        objWord.Visible = True
        Application.StatusBar = "Progress: " & i - ProcessRange.Row + 1 & " of " & ProcessRange.Rows.Count
        DoEvents 'keep Excel responsive
    Next i

    Application.StatusBar = ""
End Sub

Upvotes: 1

Inarion
Inarion

Reputation: 608

You can use these for the start and end of your loop:

Dim FirstRow as Long
Dim LastRow as Long
FirstRow = Selection.Rows(1).Row
LastRow = FirstRow + Selection.Rows.Count

The loop then goes like:

For i = FirstRow To LastRow
    ' ...
Next i

Upvotes: 0

Related Questions