How to use multiple "With"

[kelly mort]

Hello all,
I need help with how to use multiple with in a procedure. I want to use them to reference Worksheets. So say I have four sheets .

For i = 1 To 20
    With Sheet1
      .OLEObjects("Image" & i).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "B") & ".jpg")
      .OLEObjects("Image" & i + 10).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "F") & ".jpg")
    End With
Next i

So from the above code, I want them run on sheets 2 3 and 4. But I don't want separate procedures for them.
Regards
Kelly

 

[Eric W]

Depending on how your sheets are named, maybe:

For s = 2 to 4
    For i = 1 To 20
        With Sheets(s)
          .OLEObjects("Image" & i).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "B") & ".jpg")
          .OLEObjects("Image" & i + 10).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "F") & ".jpg")
        End With
    Next i
Next s

or

For Each s in Array("Sheet2", "Sheet3", "Sheet4")
    For i = 1 To 20
        With Sheets(s)
          .OLEObjects("Image" & i).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "B") & ".jpg")
          .OLEObjects("Image" & i + 10).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "F") & ".jpg")
        End With
    Next i
Next s

 

[alansidman]

Assuming that there are only four sheets

[FONT=Verdana]Dim ws as worksheet[/FONT]
[FONT=Verdana]For each ws in worksheets[/FONT]
[FONT=Verdana]With ws[/FONT]
For i = 1 To 20
      .OLEObjects("Image" & i).Object.Picture = LoadPicture(fPath & "" & .Cells(10 * i - 8, "B") & ".jpg")
      .OLEObjects("Image" & i + 10).Object.Picture = LoadPicture(fPath & "" & .Cells(10 * i - 8, "F") & ".jpg")
Next i
End With
Next ws

 

[kelly mort]

Depending on how your sheets are named, maybe:

For s = 2 to 4
    For i = 1 To 20
        With Sheets(s)
          .OLEObjects("Image" & i).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "B") & ".jpg")
          .OLEObjects("Image" & i + 10).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "F") & ".jpg")
        End With
    Next i
Next s

or

For Each s in Array("Sheet2", "Sheet3", "Sheet4")
    For i = 1 To 20
        With Sheets(s)
          .OLEObjects("Image" & i).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "B") & ".jpg")
          .OLEObjects("Image" & i + 10).Object.Picture = LoadPicture(fPath & "\" & .Cells(10 * i - 8, "F") & ".jpg")
        End With
    Next i
Next s

The for s = 2 To 4 , how do I Dim the s ?
Will the code use the sheet name or the code name?

I want it to use the codename . I ran it and nothing happened. I know I am doing something wrongly. But can't figure it yet.

 

[kelly mort]

Assuming that there are only four sheets

[FONT=Verdana]Dim ws as worksheet[/FONT]
[FONT=Verdana]For each ws in worksheets[/FONT]
[FONT=Verdana]With ws[/FONT]
For i = 1 To 20
      .OLEObjects("Image" & i).Object.Picture = LoadPicture(fPath & "" & .Cells(10 * i - 8, "B") & ".jpg")
      .OLEObjects("Image" & i + 10).Object.Picture = LoadPicture(fPath & "" & .Cells(10 * i - 8, "F") & ".jpg")
Next i
End With
Next ws

There are more than 4 sheets but I want to run it on only 4. They follow in order say 10, 11, 12, 13 .

 

[kelly mort]

The for s = 2 To 4 , how do I Dim the s ?
Will the code use the sheet name or the code name?

I want it to use the codename . I ran it and nothing happened. I know I am doing something wrongly. But can't figure it yet.

Okay I turned off my error handler and I had the error: run-time error "9"
Subscript out of range

 

[Eric W]

For the first example:

Dim s as Long

For the second example:

Dim s as Variant

The first example uses the number of the sheet. The number is usually not a good way to do things, since as you rename or move sheets, the number doesn't really relate to the actual sheet name, and can get confusing. I'd recommend the second version, just put the sheet names you want in the Array.


But even that can cause problems, if you rename your sheets. Then you'd get a run-time error "9", when VBA can't find the sheet name. To get around that, you can mark the sheets you want to process by putting (for example) an "X" in cell Z1 on the sheets you want it to run on. Then the code would look something like:

Sub test1()
Dim ws As Worksheet, i As Long
    
    For Each ws In Worksheets
        If ws.Range("Z1") = "X" Then
            With ws
                For i = 1 To 20
                    .OLEObjects("Image" & i).Object.Picture = LoadPicture(fPath & "" & .Cells(10 * i - 8, "B") & ".jpg")
                    .OLEObjects("Image" & i + 10).Object.Picture = LoadPicture(fPath & "" & .Cells(10 * i - 8, "F") & ".jpg")
                Next i
            End With
        End If
    Next ws
    
End Sub

(with thanks to Alan for using some of his code)

I'm not aware of a way to use codenames in a loop, and even if you could, you'd probably run into some of the same issues I mentioned. Hope this helps.

 

[kelly mort]

Oh okay. This last code is very cool. Thanks for it. Now I am cool To move on.
Kelly