Find String in Range and Select it - unexpected results

**TC1980** · 06-03-2013, 07:17 AM

Hi,

Hoping someone might be able to shed some light on an annomaly I've come across when finding a string in a specified range. I have found a workaround but not really happy with it and want to understand why Excel / VBA is behaving this way as it's not as I would expect. I'm using Excel 2003.

The following code is a simplified version of what I'm using within a bigger project:

Please Login or Register  to view this content.

The FindString will be a currency (e.g. "EUR") which I want it to find within the range B6:B170 specifically. As I step through the fxRng variable displays "EUR" and the fxRngAdd variable shows $B$6 when I hover the mouse over it. However when it's selected, the cell C11 is the one that's actually selected not B6!?!
I can work around this by changing the Range to "B:B" where it selects C6 instead of B6 then offset back one, but I'd really rather not do it this way - just seems clumsy.

Anyone have any ideas why it's behaving this way? Have I done something dumb in the .Find bit or setting the variables?

Any help appreciated - bugging me.

**EDIT** Added Example Workbook

Thanks, TC

**JapanDave** · 06-03-2013, 07:19 AM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

But , my instincts are saying that you need to declare this like this,

Please Login or Register  to view this content.

**JapanDave** · 06-03-2013, 07:25 AM

I just saw this.

Please Login or Register  to view this content.

It needs to be this,

Please Login or Register  to view this content.

**TC1980** · 06-03-2013, 07:40 AM

Thanks JapanDave, just added workbook.

I cannot Dim fxRng as Range (VBA doesn't like it). fxRng holds the CCY being searched for - the "EUR" in this case - not a valid range.
The fxRngAdd takes the Address of the cell where "EUR" is found (fxRng), $B$6, again VBA does not like this being set as a Range. Read somewhere this is because of limitations in passing strings as ranges, i.e. you can't convert it from one type to the other.

Will check re the "." and post back - though it was needed within a With statement.

Thanks for your help on this. TC

**TC1980** · 06-03-2013, 07:42 AM

Well, you were right and the removal of the "." fixed it! Now selects the CCY specified rather than offsetting by 1, 5. Such a small thing!
Still don't really understand why it would behave this way, but I'll take it!

Thanks again for your prompt help. TC

**JapanDave** · 06-03-2013, 07:48 AM

Sorry no workbook.

Yes, within with statements you do that, but in the code provided there is no 'with' and that will screw with the outcome.

As for the range variable, when you are using the find method and 'setting' the variable ,VBA will classify it a range and is not considered a string so to speak. So it should be set as one other wise you have the potential to run into problems