application vlookup error runtime error 1004, unable to get the vlookup property of the.."

sorry norie, i forgot to un protect the project, the password is "password".

let me know if that doesn't work.

so i changed the code to match what you guys have written and still getting an error on the vlookup line. see entire code below:

Please Login or Register  to view this content.

so the error message is still run time error 1004, unable to get the vlookup property of the worksheetfunction class

thoughts?

thanks rorya, i got that fixed, apprecitate you letting me know.

so here is the updated code, still getting run time error 1004 - unable to get the vlookup property of the worksheetfunction class.

what am i doing wrong?

Please Login or Register  to view this content.

ok so after some thought, i realized that my hash sub is correctly making the hashchar but it bails when the char1 is zero "0". see code below, and i will upload the workbook to see if that helps in debugging my issues.

Please Login or Register  to view this content.

so it bails when hash_char is 0 (probably any number), i tried to use CStr to convert it to a string but it doesn't work. here is the file:

so rorya, when i declare char1 as a variant (and not a string), then the macro fails and doesn't display anything.

if i leave it to string, then the output works as expected till it hits "0", then it bails and the output stops and doesn't display the correct letter/number.

so i set up a page "sheet2" under Hash Formula and i get the correct letters until it hits zero "0" and then it stops from there. i tried any number from 1 to 9 and it works. it only stops when char1 is a zero "0"

what am i doing wrong?

so last thing. on my sheet2, i have the hashname split up into different cells that contain one letter/number. i was able to write a formula (ifna) to add the correct letter, so it works on that sheet. the question is how to make my formula work in the module.

EDIT: i am almost there. the following code works, except it is printing a blank line after substituting I for 0. how can i fix this?

Please Login or Register  to view this content.

so after some thought, i can tell that something isn't quite right.

in sheet2, i have a formula in M2 that is: =LEFT($O$2,1) where O2 is a string of numbers and letters
in cell N2, i have the following formula: =VLOOKUP(M2,G:I,3,FALSE) where G:I contains the table of letters that i am substituting.

so anything in column M is just one character (from O2) and everything in column N should be one character as well.

so if M2 is letter, then N2 shows the appropriate hashvalue. but if M2 is a number (even though it tests as a string), then i get an #NA in column N. im sure that this is why my sub hash is not printing the appropriate values.

can someone just tell me why this is happening?

so as with all things excel, there are ways around issues and so i just rewrote my code to not be a MID string and just parsed out the letters in a range. so i got this working and it seems to be fine.

so i have a question about how to find a row number within a range

the range is on sheet "hdn_Codes" and is found in A1:A4

i have looked at match and find and i can't get them working for some reason. here is the code below, just a few lines.

Please Login or Register  to view this content.

so i tried what you said and i get an error message:

run time error 1004, unable to get the match property of the worksheetfunction class.

how can i fix this.

let me upload it. i know that the city is found in A1:A4 of the hdn_Codes sheet.

to display the hdn_Codes sheet, goto Mainsheet, select Unhide Cities by password, type in "master" and when the password textbox opens up, type in "Password". that will unhide that sheet.

please let me know what you find.