sub for reading

  • Thread starter Thread starter Fernando Duran
  • Start date Start date
F

Fernando Duran

I have a sheet, that starts in A56, B56 to A208,B208, , in other sheet,
that's the user side, the user enter the Postal code in cell called
PCODE... What I need to do, is able to read, the second column, because,
the first one is the Postal Code, the second is the Zone, I need to
extract this information, using the entry in the PCODE, then I will
manipulate this information in my main program... but forget about the
main, in working fine, I just need to get the information...

A B
A0B 10
A0H 11
etc...

I need the B column, base in the A

Thanks for the ideas

Fernando
 
use Vlookup

Using a worksheet formula

=Vlookup(pcode,Sheet1!$A$56:$B$208,2,false)

you can do it with VBA as well

res = Application.Vlookup(Range("pcode").Value,
Range("Sheet1!A56:B208"),2,False)
 
Hi Tom, thanks for your answer...I used the formula inside of a test
Sub, but I got an error "run time error 1004" if i'm correct.
But I also I forget to tell you, that column A, it's like this

A0A-B0C
B1A
B2B-C3Z
that's wright, it uses ranges, what I was planning to do, was to cut the
last and use only the first part. But that I can fixe, but any ideas
what in a small line like that, I get that error?
res = Application.VLookup(Range("Sheet1!PCODE").Value,
Range("A56:B208"), 2, False)
 
Testing from the immediate window

res = Application.VLookup(Range("Sheet1!PCODE").Value, Range("A56:B208"), 2,
False)
? res
Error 2042

Error 2042 is the same as #N/A in the worksheet meaning no match.

If I fill column B with the formula

="B"&row()
and in column A, sequentially number the cells (1, 2, 3, etc)

and PCode contains 3

res = Application.VLookup(Range("Sheet1!PCODE").Value, Range("A56:B208"), 2,
False)
? res
B58

then it returns the value in cell B58 ( A56 = 1, A57 = 2, A58 = 3)

so the match is to row 58.

So it works for me.

dim res as Variant
res = Application.VLookup(Range("Sheet1!PCODE").Value, Range("A56:B208"), 2,
False)
if iserror(res) then
msgbox "Not Found"
Else
msgbox "Results is " & res
End if

You would need to change the 4th argument to True if you want to match a
range. See help in Excel itself for how Vlookup operates.

Don't know why you would get a 1004 error. (if you use
WorksheetFunction.Vlookup, then this raises an 1004 error when no match is
made).
 
oK. Tom, I'll give a try, ASAp

Thanks again



Tom said:
Testing from the immediate window

res = Application.VLookup(Range("Sheet1!PCODE").Value, Range("A56:B208"), 2,
False)
? res
Error 2042

Error 2042 is the same as #N/A in the worksheet meaning no match.

If I fill column B with the formula

="B"&row()
and in column A, sequentially number the cells (1, 2, 3, etc)

and PCode contains 3

res = Application.VLookup(Range("Sheet1!PCODE").Value, Range("A56:B208"), 2,
False)
? res
B58

then it returns the value in cell B58 ( A56 = 1, A57 = 2, A58 = 3)

so the match is to row 58.

So it works for me.

dim res as Variant
res = Application.VLookup(Range("Sheet1!PCODE").Value, Range("A56:B208"), 2,
False)
if iserror(res) then
msgbox "Not Found"
Else
msgbox "Results is " & res
End if

You would need to change the 4th argument to True if you want to match a
range. See help in Excel itself for how Vlookup operates.

Don't know why you would get a 1004 error. (if you use
WorksheetFunction.Vlookup, then this raises an 1004 error when no match is
made).
Click to expand...
 
Back
Top