Runtime Error '91': Object variable... When Running in Range Selection

[Stonesteel15]

Greetings!

I am currently encountering a problem in my automation. The scenario is when I am selecting the cell individually and running the code, there is no problem encountered, but when I am selecting the cells in range and running the code, the runtime error occurs, the last code is replacing the last row with certain text, it works when running the code in individual cell but error when running the code in selected range of cells. herein attached the worksheet and screenshots of the scenario.

error.jpg
SRA.jpg
SRB.jpg

SR History Viewer 1.0.xlsm

hope someone could find what causes the error.

[Mallycat]

How about providing some information about what cells you expect to be able to select and have the code run? When I select sheet SR cell A2 and run the code, I get an error anyway.

[Stonesteel15]

hmmm, the code is to populate the cell with the values extracted from a table of website of our company, there is no fixed number of rows being extracted, but the maximum number of rows from a page is 15 rows, sometimes I can only extract 5 rows, sometimes 8. The purpose of the code is to extract the history of escalation of SR. I'm just wondering why I am getting an error, if I am using, sheet activate,

1. Select SRs
2. Query in Web
3. Activate SR History sheet
4. Put the extracted data (looping starts here)
5. After looping, Activate the SR Sheet
6. Select next SR
7. Query in web
8. Activate SR History sheet
9. Put the extracted data (looping again, but this time, when it reaches the last row in the table in the web, error now occurs).

Is what they called "stack overflow" has something to do with this?

[Mallycat]

In your first post you said "when I am selecting the cell individually and running the code" but when I am selecting the cells in range and running the code" . My question to you is "which cells are you selecting when you run the code".

I can't get the code to work at all, so it is hard for me to take a look at it. If you are getting a 'stack overflow', then the place I would start to look at is all of those "set s =" statements. eg could you replace this...

Please Login or Register  to view this content.

with this...

Please Login or Register  to view this content.

[Stonesteel15]

I'm sorry I forgot to mention it, hmmm, the cells I am selecting is in Column A of sheet "SR", the values in these cells are the reference of the extracted data.

Ok, I'll try to replace the codes and test if it will work.

[Stonesteel15]

Hi Mallycat, unfortunately, it still doesn't work. I have the feeling that the switching/activating of sheets has something to do with this, and/or also the loop of codes.

[Stonesteel15]

You may refer to this video I uploaded for more clarification regarding the problem.

http://youtu.be/sjZaF-jEIOM

[Mallycat]

Unfortunately I can't test your code because the web query doesn't connect. Anyhow, this code looks suspect to me.

Please Login or Register  to view this content.

Why are you doing this? You need to leave your pre-macro selection alone. What you are doing here is changing your selection of multiple cells to one cell (CSPCells.Select). Change your code so that instead of it working off the active cell, it works of the cell set to the range CSPCells Do not make CSPCells your active cell.

[Stonesteel15]

hmm, how should I set that code then? I set that condition because sometimes there's a blank cell inserted between those SRs, so when it detects a blank cell, it will only put a space on it, otherwise it will proceed to the execution of codes.

[Mallycat]

Here's the problem as I see it.

You have this code

Please Login or Register  to view this content.

Then the rest of your macro operates on the variable CSPCells which is a range (a cell).
So what does this next code do?

Please Login or Register  to view this content.

if the value in CSPCells is null, then set the activecell to null? Why are you doing that - I don't understand. There is no relationship between the cell referenced by CSPCells and the active cell. Then the second part "else" is saying, if the value in CSPCells is not null, then select it. Once again, why would you want to select it? You don't need to select it to operate on it.

I don't really know, and you will need to test it, but my suspicion is that because you have the line CSPCells.Select, the "selection" has been changed. So if you go back to the first bit of code I posted above "For Each CSPCells In Selection.Cells", your "selection.cells" has now changed.

I suggest you just comment the code out and see if that fixes the problem. If it doesn't there is no need to change it.

If you want to skip over blank cells in your range, just wrap an if statement around all your code. ie

Please Login or Register  to view this content.

[Stonesteel15]

Very well explained Mallycat, since I do not know much of VBA, (I just do self-study). Your explanation made me realize that I don't need that part of the code, "CSPCells.Select" since I am already pointing in a selection of range which will automatically move into the next selected cell after the execution of codes. . So I tried using this code:

Please Login or Register  to view this content.

It worked indeed. but only by selecting again one cell at a time. But when I tried selecting a range of 3 cells, the same problem occurs when it reaches the second selected cell.

[Stonesteel15]

If I just wanted to paste the data to the other sheet without switching or activating the other sheet, what code should I use? I just wanted to proceed the execution of codes but staying only in the active sheet "SR" and not switching in the other sheet and then switch back again to the first sheet.

[Mallycat]

You can refer to any cell in the other sheet by refering to it directly. Rather than me give you the code, better to help you work it out. Turn on your macro recorder, click on the destination sheet and type someing into a cell. Stop the recorder. What you will have recorded is the code you need to change a value in a cell. something like this: Sheets("SR History").range("A1").value = "yada yada".

Also, I just noticed that your 4th last line of code should not be there. This is the same issue I covered before. Your For Each loop already cycles through the selection, you do not need to use the code

CSPCells.Offset(1, 0).Select

Matt

[Stonesteel15]

Oh yeah I also removed that part "CSPCells.Offset(1, 0).Select" , uhmm in my observation earlier, I found out that the error is caused by the last row of data being extracted from the table in the web, where it catches the footer of the table, the row with (page info, @2004 SMART Communications, Inc. All Rights Reserved , Version 10.23). in the code I made, when error occurred, it will go to the error handler, then will set the object to nothing, then the last row will be replaced by the text "--END--", then will proceed to the next CSPCells, then when the error occurred again, it will now prompt a dialog and can't go through the error handler, is there a code that when error occurred, it will replaced the last row and will resume the next loop of execution of codes? when I tried using On Error Resume Next, it only keeps on looping in the last page of the table in the web and does not proceed to the next CSPCells.

[Stonesteel15]

Hi Mallycat! Congratulate me! I have successfully conquered my problem! nyahaha. After a couple of hours reading the On Error Statement and a lot of imagination, I finally got an idea on how to reset the error. Since my worklist is full of ****, err I mean errors, I have formulated a series of codes on how will I proceed to the execution of codes upon getting an error. here is what I have made, and upon testing, the code runs successfully and finished without prompting any errors.

Please Login or Register  to view this content.