Range Select from another worksheet

[rybussell]

Please Login or Register  to view this content.

This only works when Sheet1 is selected. If Sheet2 is selected, it returns an error of

1004
Select method of Range class failed.

Sheet1 and Sheet2 are the only sheets in my entire book... When I debug, it goes to the c.Select line...

I've tried referencing the Sheet1 exactly with Sheets("Sheet1").Range("A:A") and every other possible combination I can think of...
I do NOT want Sheet1 to need to be selected or even visible necessarily.
Also the Vals variable is set earlier in the code and returns the correct information...

[Norie]

Why do you want to select c?

[rybussell]

I use some ActiveCell.Offset codes later on in the code...

Let me edit the first post and add the entire code... Give me a moment...

[rybussell]

Mind you, this isn't all my code.. I'm actually in the process of trying to clean it up... It used to be about 14 times this length.

[Norie]

Can't you use c instead if ActiveCell in the later code?

[rybussell]

I imagine I could when I get down that far... But I need to figure out why it only works when Sheet1 is selected and not when Sheet2 is selected...

[Norie]

If you mean why doesn't this work,

Please Login or Register  to view this content.

then it's because you can't select a range on a sheet unless the sheet is selected.

The rest of the code should work just fine without Sheet1 being selected.

[rybussell]

So it's not possible to grab data from a separate sheet in VBA while using With?

[Norie]

Not sure what you mean.

If you wanted to get the value of a cell/range that you have a reference to all you need is the reference, you don't need With, you don't need to select the sheet the range is on.

What are you trying to do in the first part of the code you posted?

It looks like you are trying to find every occurence of Vals in column A of Sheet1, but you don't seem to be doing anything else.

[rybussell]

It's supposed to search the entire column A on sheet 1 and find the value sent to it by a userform called AccessCredentials. There will be no duplicates in column A.
It then needs to apply the Range in A where it finds that value from AccessCredentials to a variable.

From that variable, I should be able to do the Offsets and everything. And like I said, this isn't my code mostly. I'm just trying to make some changes to make it a little more informative. It's a bigger part of a program, but being able to find the value in the column A on Sheet1 and giving a variable a range value is the key to most of my changes.

[Norie]

If there's only one occurence of Vals you don't need a loop or FindNext.

This should be all you need.

Please Login or Register  to view this content.

[rybussell]

Appears to be working... I'll have to check to make sure it didn't affect the other codes in this book. I thank you for the help. Do you have a paypal? I'd like to send you a couple bucks because you have helped me here several times.