Application.GoTo

[ArtZ]

I am trying to use GoTo address a cell on a different worksheet. I used GoTo because I didn't want to make sheet2 active. The code used was:

Please Login or Register  to view this content.

The variable CellLoc come form a sub that returns the cell address if a value is found in a range. The code is shown below:

Please Login or Register  to view this content.

The value CellLoc is returned properly and the code compiles correctly, however, I receive an error at:

Please Login or Register  to view this content.

when I run the sub. Does anyone have a suggestion to fix this?

Thanks,

Art

[davesexcel]

Where are you placing the goto code?

[pike]

hi Artz
try using

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[ArtZ]

Where are you placing the goto code?

Dave,

Thanks for your response. The code is placed in a large sub on sheet 1.

Art

[ArtZ]

hi Artz
try using

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Thanks for your response. Both of your code segments work. Thanks. However, there is one caveat: I want the activesheet to remain sheet1. Your code switches the activesheet to sheet2. I just want the selection of the cell on sheet2 to be in the "background" with sheet1 remaning the active sheet.

Is there a way to do this?

Thanks,

Art

[protonLeah]

Unfortunately your answers to our questions are incomplete. But, CellLoc is declared and set in Sub X(); therefore, it does not exist outside of that sub. It appears that the line: Application.Goto Reference:=Worksheets("Sheet2").Range(CellLoc) is part of a different sub, thought you haven't specified.

Are these subs in the same module? If so, then you can declare CellLoc as Public variable at the top of the module before any Subs.

Also, you should place the line Option Explicit as the very first line of the module.

Please Login or Register  to view this content.

Please explain/show what you are trying to do with the GOTO line, there are probably other options.

[pike]

maybe

Please Login or Register  to view this content.

[royUK]

CellLoc should be declared as a string, not a Variant and, as Ben says placed at the top of the code so that it is Public & can be used by other Procedures within the Module. Also no speech marks are required because it is a String.

Please Login or Register  to view this content.

[ArtZ]

All,

Thanks for comments and suggestions. I didn't present the overal view of my "project" because I thought that this was going to be a simple fix and I would be done with it. Apparently not.

More details:

When my code runs a web query, data meeting certain criteria are stored in the next unused row on sheet 2. On subsequent queries, if the same data are returned, they are rejected if they are either in the current list in column B on sheet2 or were previously deleted and are tracked in column L on sheet 2.

What I need help with is updating current values on the list. When TradeFound = True, I need to find the row in column B on sheet 2 which contains the value (text) from sheet 1 cell B4 and:

– Copy the value from sheet 1, cell AQ22 into sheet 2, column A
– Copy the value from sheet 1, cell H4 into sheet 2, column F

These values would be copied to the rows of column A and column F corresponding to the matching text between sheet 1, cell B4 and whatever row in column B on sheet 2 where the sheet 1, B4 value is found.

Sorry if I'm being redundant, I'm just trying to be clear.

Hope that this helps to clear up what I need to get coded.

Thanks,

Art

[ArtZ]

All,

With everyone's suggestions and a little hacking of my own, I got it figured out.

After the code snippet below runs, the SetFocus fixes the issue:

Please Login or Register  to view this content.

The sub to set the focus:

Please Login or Register  to view this content.

Again many thanks to all who made suggestions.

Art

[royUK]

Why a separate sub to activate the combobox?

[ArtZ]

Why a separate sub to activate the combobox?

The values in the combobox are used in a web query. I have a sub which activates the web query and when the query starts and always want to make sure that the cursor is active in the combobox. In the case of using the GoTo expression, the focus shifted to Sheet2. That is OK as long as the focus can be shifted back to the combobox (on Sheet1) in time for the next web query.- Art

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save