Solved: Error in code need help.

[Shazam]

Hi everyone,


I'm getting a error in the code. its saying:

Run-time error '1004'

Select method of range class.

This is the line its highlighting.

[VBA]Range("B2").Select 'select the cell where you want to place your image [/VBA]

Here is the entire code.

[VBA]
Sub Convert_Group_Objects_into_JPEGS()

Dim sht As Worksheet
Dim shp As Shape



Application.ScreenUpdating = False
For Each sht In ActiveWorkbook.Sheets
For Each shp In sht.Shapes
sht.Activate
If shp.Type = msoGroup Then
shp.Cut

Range("B2").Select 'select the cell where you want to place your image
sht.PasteSpecial Format:="Picture (JPEG)", Link:=False, _
DisplayAsIcon:=False
Exit For
End If
Next
Next

End Sub
[/VBA]

Do you know what I'm doing wrong?

The code above convert all group objects in the workbook into Jpegs.

[malik641]

Hey Shazam,

I'm not getting the error...but either way I would use:

[VBA]
sht.Range("B2")
[/VBA]
Rather than not explicitly calling the sheet you're on. I don't think that's your problem (because I didn't get the error using your exact code).

How about a dummy workbook?

[Shazam]

Hi malik641,


Thank you for replying. Here is the sample workbook that I'm trying to run the code from.

But the code you gave me it work.

[VBA]
sht.Range("B2").Select
[/VBA]

[mdmackillop]

You should reorder these lines as follows, so you don't activate the sheet for every shape.
[VBA]
For Each sht In ActiveWorkbook.Sheets
sht.Activate
For Each shp In sht.Shapes

[/VBA]

[malik641]

Shazam,

Glad to see it's working. I'm still unsure why the code was getting the error. I thought it had to do with the ActiveSheet possibly not being the sht variable during the for-next loop, but after some testing, the ActiveSheet name and sht.Name was the same during the loop.

...But, I still think that Excel didn't know which sheet range you were talking about. Which is (maybe) why it needed the explicit call to the variable sht 's range.


Anyway, if it's resolved don't forget to mark it solved

[Shazam]

You should reorder these lines as follows, so you don't activate the sheet for every shape.
[vba]
For Each sht In ActiveWorkbook.Sheets
sht.Activate
For Each shp In sht.Shapes

[/vba]

Hi mdmackillop,

I did not know that. That will be very useful.


malik641,


Thank you for the help.

[mdmackillop]

Hi Shazam,
It's a general rule for any loop. Avoid including any code in the loop that can be carried out once either before or after the loop.

[Ken Puls]

I'm still unsure why the code was getting the error.

In order to select a cell on a sheet, that sheet must be active.

I would suggest, although I haven't tested, that the Range in Shazam's original code, (without qualification) was pointing back to the original sheet, and not the newly activated one. I always always always explicitly refer to my ranges to avoid this kind of issue.

[malik641]

I would suggest, although I haven't tested, that the Range in Shazam's original code, (without qualification) was pointing back to the original sheet, and not the newly activated one. I always always always explicitly refer to my ranges to avoid this kind of issue.

That's what I thought....but when I tested it I checked the ActiveSheet.Name and the sht.Name in the FOR loop and it was the same....but still, like you, I'm going to assume the default was pointing to the original sheet rather than the newly activated one.

It's better to get used to explicitly calling out everything anyway. It's better coding practice, and not to mention when VBA goes .Net (if it does... haven't looked it up yet) we're going to have to explicitly call out what we used to use for defaults.

[Ken Puls]

Hi Joseph,

I think that you would have needed to test the Range("B2").Parent to see the issue in this case.

[malik641]

Hi Joseph,

I think that you would have needed to test the Range("B2").Parent to see the issue in this case.

Hey Ken,

Will do