UtterAccess Discussion Forums > Range help needed...

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

adaytay

Jan 15 2007, 06:54 AM

Hi all,

Further to my last... having a few problems converting the lookup value to a range...

Here's what I have so far:

CODE

Application.WorksheetFunction.VLookup(sheets("Revised Data").cells(i, 1), Range("'[New Credit Control Register.xls]Data'!$A$1:$R$1430"), 18, 0)

I need to convert the first bit (Sheets("Revised Data").cells(i, 1)) to a range... but aren't sure of the syntax.

Can someone help?

Cheers,

Ad

adaytay

Jan 15 2007, 07:02 AM

Oh I am going to have to stop doing this...

It's always going to be the figure in column A, so I've replaced it with this:

Range("'Revised Data'!A" & i)

I must stop solving my own threads....

KingMartin

Jan 15 2007, 07:43 AM

Hi Adam,

even if you autohelp yourself, still those threads may be useful to others...

Martin

adaytay

Jan 15 2007, 08:05 AM

That's what I thought Martin

norie

Jan 15 2007, 12:11 PM

Adam

The first argument in VlookUp in VBA is not a range.

It's the value to lookup, just like the worksheet function.

adaytay

Jan 16 2007, 11:53 AM

Actually... it has to be a range.

I tried it with the value but it needed to be converted to the range on the spreadsheet that contained the lookup value.

Ad

NateO

Jan 16 2007, 06:23 PM

Hi Ad,

The Find Method will return a Range:

http://msdn2.microsoft.com/en-us/library/a...office.11).aspx

adaytay

Jan 17 2007, 04:40 AM

Hey Nate

Thanks for that - it's sorted now but that's worth remembering in case I need to do this again in the future!

Cheers,

Ad

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.