Sheets.ActiveCell.Offset

**Forty** · 09-27-2011, 08:56 AM

I had in code...

Please Login or Register  to view this content.

...but it didn't work, so I declared the value of the cell in sheet "opravy" as a variable. But is there some better statement to solve this link to another sheet ?

**arlu1201** · 09-27-2011, 08:58 AM

What are you trying to achieve?

**Forty** · 09-27-2011, 09:02 AM

find a value from another sheet (sheet "opravy" and there offset cell) in this activated sheet

**Andrew-R** · 09-27-2011, 09:05 AM

It's worth noting that the Find method in VBA isn't the same as the Find worksheet function.

Generally in VBA if you want to find one string within another then Instr is what you want.

Also, the sheets object doesn't contain an ActiveCell object, so if you want to find the ActiveCell on another sheet you'll have to activate it. If you want to deal with the cell on another sheet which matches the position of the Active cell on the active sheet you'll need something along the lines of:

Please Login or Register  to view this content.

**Forty** · 09-27-2011, 09:41 AM

okey, now better:

I want to find value from sheet(2) in range from A3 to about A7000 in sheet(1), which has range from A3 to about A13000, so one value from 7000 of sheet(2) could be found in 13000 cells in sheet(1). If there is a match, excel have to prove next accordance, which is in column B on both sheets, so therefore the statement, which I wrote above with "Offsets"...

P.S. Ignore the "Left" function, cause there is a "Mid" function too and the code is more complicated.

**Andrew-R** · 09-27-2011, 10:15 AM

I'm still not sure quite what you're after, but how about this?

Please Login or Register  to view this content.

Does that point you in the right direction?

**Forty** · 09-29-2011, 03:41 AM

yep, I have ca. the same code. It's more complicated, cause there are blank cells too and the find procedure can't stop on this cell and must continue with finding on next row, but this I have already solved.

My point in this thread was only to solve, how to get value from offset cell on another sheet and as you said, it could work when using "rngCurrentCell As Range". So thank you very much and we can mark this thread as solved.