Set range according to active sheet name VBA

[lengyel109]

Hello there!

I ran into some issues using VBA with my limited knowledge.
I am trying to combine a code to delete pictures in a range, which works as it should:

Dim pic As Object
For Each pic In ActiveSheet.Pictures
    If Not Application.Intersect(pic.TopLeftCell, Range("A8:T35")) Is Nothing Then
        pic.Delete
    End If
Next pic
End Sub

With an If statement to set the range, if a specific sheet is active:

Dim pic As Object
Dim lc As String
If ActiveSheet.Name = "ESC 1-4" Then lc = (G35)
For Each pic In ActiveSheet.Pictures
    If Not Application.Intersect(pic.TopLeftCell, Range("A8:lc")) Is Nothing Then
        pic.Delete
    End If    
Next pic
End Sub

The yellow part is where I get a run time error '1004' Method 'Range of object'_Global' failed.
So I guess I messed the string somehow right?
This would ofc repeat with different sheet names, with each sheet having a different end range.
Any ideas?

Thanks in Advance, Mark

 

[Engberg]

Ye, (G35) should be "G35" and "A8:lc" should be "A8:" & lc.

Try replacing

'If ActiveSheet.Name = "ESC 1-4" Then lc = (G35)
'For Each pic In ActiveSheet.Pictures
' If Not Application.Intersect(pic.TopLeftCell, Range("A8:lc")) Is Nothing Then

With

If ActiveSheet.Name = "ESC 1-4" Then
    lc = "G35"
End If
For Each pic In ActiveSheet.Pictures
If Not Application.Intersect(pic.TopLeftCell, Range("A8:" & lc)) Is Nothing Then

To add more ranges depending on sheet name, expand the first If with Else Ifs:

If ActiveSheet.Name = "ESC 1-4" Then 
    lc = "G35"
Else If ActiveSheet.Name = "ESC 5-8" Then
    lc = "H35"
Else If ActiveSheet.Name = "And so on" Then
    lc = "T35"
End If

For Each pic In ActiveSheet.Pictures
If Not Application.Intersect(pic.TopLeftCell, Range("A8:" & lc)) Is Nothing Then

 

[lengyel109]

Ye, (G35) should be "G35" and "A8:lc" should be "A8:" & lc.

Try replacing

'If ActiveSheet.Name = "ESC 1-4" Then lc = (G35)
'For Each pic In ActiveSheet.Pictures
' If Not Application.Intersect(pic.TopLeftCell, Range("A8:lc")) Is Nothing Then

With

If ActiveSheet.Name = "ESC 1-4" Then
    lc = "G35"
End If
For Each pic In ActiveSheet.Pictures
If Not Application.Intersect(pic.TopLeftCell, Range("A8:" & lc)) Is Nothing Then

To add more ranges depending on sheet name, expand the first If with Else Ifs:

If ActiveSheet.Name = "ESC 1-4" Then
    lc = "G35"
Else If ActiveSheet.Name = "ESC 5-8" Then
    lc = "H35"
Else If ActiveSheet.Name = "And so on" Then
    lc = "T35"
End If

For Each pic In ActiveSheet.Pictures
If Not Application.Intersect(pic.TopLeftCell, Range("A8:" & lc)) Is Nothing Then

Oh yeah, that worked beautifully! Thanks for the help!