Match a cell with dynamic range (multiple columns)

**bgonen** · 08-19-2011, 07:16 PM

With the code below I am able to match cell A1 in worksheet:"DataByCC" with values range on column A to the last used cell on that column (col A)
If there is a match then I copy the entire row on the other Workshhets to DataByCC

The only Edit/Change I am trying to do is do the match (cell A1 in DataByCC) with columns A through F,,,if there is a match just copy the entire row to "DataByCC"

I hope someone can help
Thanks
bg

Please Login or Register  to view this content.

**p24leclerc** · 08-19-2011, 10:39 PM

try this: I'm not really able to test it as I cant reproduce your workbook but it should work or give you the idea to use:

Please Login or Register  to view this content.

**DonkeyOte** · 08-20-2011, 03:04 AM

Originally Posted by bgonen

With the code below I am able to match cell A1 in worksheet:"DataByCC" with values range on column A to the last used cell on that column (col A)

The above is not strictly true (first sheet only) but the logic is sound in so far as every value in DataByCC!A:A currently matches that of DataByCC!A1 and thus the Match against DataByCC!A:A is effectively the same as testing =DataByCC!A1

Regards your question, without wishing to modify your approach too much, if the intention is to test only against DataByCC!A1 why not invert the Logic such that you match DataByCC!A1 against A:F on the sheet/row being iterated ?

Please Login or Register  to view this content.

Persisting with your existing Match:

Please Login or Register  to view this content.

given the new A:F test is likely to lead to erroneous results as not all the values in DataByCC!A:A will match that of DataByCC!A1

**DonkeyOte** · 08-20-2011, 11:39 AM

bgonen,

Your post does not comply with Rule 8 of our Forum RULES.

Cross-posting is when you post the same question in other forums on the web.

You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post(s).

Read this to understand why we ask you to do this

**bgonen** · 08-22-2011, 02:09 PM

Thank you very much

I have made few tests and I'm pretty sure your revised suggestion work fine
Thank you so much for the quick help
bg

**bgonen** · 08-28-2011, 08:13 AM

Hello again DonkeyOte

I was trying to learn your code: vTemp but could not find any material about it

looking specifically to the code: If IsNumeric(Application.Match(vTemp, .Resize(, 6), 0)) Then,,,

Can you point me to specific reference/s or maybe shortly explain the code

Thanks again for your very useful help
bg

**DonkeyOte** · 08-29-2011, 05:34 AM

vTemp is a variable (variant type) to which the value in DataByCC!A1 is assigned

Originally Posted by bgonen

looking specifically to the code: If IsNumeric(Application.Match(vTemp, .Resize(, 6), 0)) Then,,,

Can you point me to specific reference/s or maybe shortly explain the code

The above tests to see if vTemp can be found (numeric result if found) in ws!An:Fn where ws is the worksheet being iterated and n the row being iterated.