Alternative To FIND to determine Column Number

**Jenn68** · 03-26-2019, 08:24 AM

Hi folks,

Please Login or Register  to view this content.

I am using this code to find the column number of the cell containing the value resulting from FIND. It works, but I have discovered it's not accurate. For example, if alias = "Rich", if will find the first instance of "Rich", whether it's "Rich E", or "Rich F" or just "RICH".

Can anyone suggest an alternative to finding the corresponding column number(cfnd) based on the successful finding of a value (alias) found within a specified row (ref_date_row)

**Keebellah** · 03-26-2019, 08:47 AM

You have to also tell it how to look, where to look and if case sensitive

Please Login or Register  to view this content.

Just typed it in withou testing but this would be the idea, the Matchase if omitted will search both upper an lower,
Hope this helps
This of course will only find the first instance

**Jenn68** · 03-26-2019, 08:56 AM

Thank you Keebellah! I will take your solution as a good educational opportunity.

I experimented and found my own solution. Perhaps although effective, may prove problematic later on?

Please Login or Register  to view this content.

**bakerman2** · 03-26-2019, 09:09 AM

Please Login or Register  to view this content.

Dim your variable as Variant because it must be able to either hold an Integer or an errorcode.

After that you can check with IsError which one of the 2 it is and take appropriate action.

**Jenn68** · 03-26-2019, 09:19 AM

Hi Bakerman2 ... yes, my solution, dim'ed as a range did result in an error. (Object variable or With block variable not set.)
After trying to find a solution, I substituted with Hans' code which works also.

**bakerman2** · 03-26-2019, 09:23 AM

Now you have two working solutions.

Thanks for rep+.

**romperstomper** · 03-26-2019, 09:40 AM

Originally Posted by bakerman2

Please Login or Register  to view this content.

Dim your variable as Variant because it must be able to either hold an Integer or an errorcode.

After that you can check with IsError which one of the 2 it is and take appropriate action.

Shouldn't that be:

Please Login or Register  to view this content.

**bakerman2** · 03-26-2019, 10:24 AM

In this context Yes because Match returns number relative to Range applied.

Maybe better to use

Please Login or Register  to view this content.

to avoid that issue.

**romperstomper** · 03-26-2019, 10:36 AM

I was really commenting on the fact you used rng_cfnd.Column

**bakerman2** · 03-26-2019, 10:45 AM

Oeps, that was just Copy-Paste without really thinking. (:

Alternative To FIND to determine Column Number

LinkBack

Thread Tools

Rate This Thread

Display

Alternative To FIND to determine Column Number

Re: Alternative To FIND to determine Column Number

Re: Alternative To FIND to determine Column Number

Re: Alternative To FIND to determine Column Number

Re: Alternative To FIND to determine Column Number

Re: Alternative To FIND to determine Column Number

Re: Alternative To FIND to determine Column Number

Re: Alternative To FIND to determine Column Number

Re: Alternative To FIND to determine Column Number

Re: Alternative To FIND to determine Column Number

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Determine last number in a column that contains numbers, and strings

[SOLVED] determine column number and sum

Determine lowest number of every other column in a row

Vlookup column number alternative

How do I determine Table Column Index Number?

Determine Row Number of Last Value in column

Bookmarks

Bookmarks

Posting Permissions