Issues passing data to Word from Excel using a loop and bookmarks 2

[JasonEnsor]

Well it's a week of questions for me

in my excel document i have user data in rows, each row contains a letter depending on the users situation each week for that month. I was hoping to loop though each week and check what letter was in the cell then pass the data to a relevant word bookmark named "Wk1","Wk2" etc.... however i can not get my loop to pass data. outside of my loop i can. i am assuming it's because i am passing a variable value in....but i really don't know. The example below hardly seemsd worth the effort but the case statements are normally long i have shortened them for demo purposes.

  Wk1 = Range("D" & SwimmerT.SwimmerRow)
                Wk2 = Range("E" & SwimmerT.SwimmerRow)
                Wk3 = Range("F" & SwimmerT.SwimmerRow)
                Wk4 = Range("G" & SwimmerT.SwimmerRow)
                Wk5 = Range("H" & SwimmerT.SwimmerRow)

                Dim Counter
                Dim wkNo
                
                Set wordDoc = wordApp.Documents.Add(Template:=InvoiceTemplatePath)
                    
                For Counter = 1 To util.MaxWeeks
                    wkNo = "Wk" & CStr(Counter)
                    With wkNo
                        Select Case wkNo
                            Case ""
                                  wordDoc.Bookmarks(wkNo).Range.Text = SwimmerT.SwimmerName
                            Case "P"
                                wordDoc.Bookmarks(wkNo).Range.Text = SwimmerT.SwimmerName
                            Case "A"
                                wordDoc.Bookmarks(wkNo).Range.Text = SwimmerT.SwimmerName
                            Case "PH"
                                wordDoc.Bookmarks(wkNo).Range.Text = SwimmerT.SwimmerName
                            Case "CF"
                                wordDoc.Bookmarks(wkNo).Range.Text = SwimmerT.SwimmerName
                            Case "C"
                                wordDoc.Bookmarks(wkNo).Range.Text = SwimmerT.SwimmerName
                        End Select
                    End With
                Next Counter
                
                wordDoc.Bookmarks("Wk2").Range.Text = curMonth

Any help is appreciated, as i don't want to have to go back to the long winded way again.

Regards

Jason

 

[mintjulep]

wkNo is prefixed by "Wk", then a number. "Wk1", "Wk2" etc.

You have no cases that look like that.

 

[PHV]

What about this ?

Dim Wk(1 To 5) As String
Wk(1) = Range("D" & SwimmerT.SwimmerRow)
Wk(2) = Range("E" & SwimmerT.SwimmerRow)
Wk(3) = Range("F" & SwimmerT.SwimmerRow)
Wk(4) = Range("G" & SwimmerT.SwimmerRow)
Wk(5) = Range("H" & SwimmerT.SwimmerRow)
...
For Counter = 1 To util.MaxWeeks
    wkNo = Wk(Counter)
    Select Case wkNo
...
    End Select
Next Counter
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JasonEnsor]

Thanks PHV for the help in shortening the routine, and thanks mintjulep for making me re-look at my code, I stepped through it and realised the values were not being assigned as I wanted them too. It's working like a charm now

Regards

Jason