need to create a macro program for find:(

[mariellove]

hi guys,

good day.

i have been doing this program for two days now, and i know i'm doing bad.

i have a little knowledge about VBA, all i do is get the codes from searching from google.

well, this is what i want to do:

search and compare all items under column Letter (column B) and get all corresponding number under column Number (Column A) be reflected to column Number1~2(Column D~E).

i tried doing a nested if statement inside a for-next, but i can't do it right, then i tried the find function of excel, but it seems to be more complicated.

i hope someone could help me on this.

this is a sample format, under Number1 and Number2 columns are the supposedly outputs.

number letter Number1 Number2
1 a 1 7
2 b 2
3 c 3 6
4 d 4 5
5 d 5 4
6 c 6
7 b 7 2
find.xls

[ragulduy]

Please Login or Register  to view this content.

I think

[mariellove]

hi yudlugar,

thank you so much for your reply, your code worked fine.

but for cases like:

• the last row for column Number (Column A) and column Letter (Column B) are not defined, and may reach thousands of rows.
• column Letter (Column B) is 5~7 characters long.

what would be the revision?

i saw that you declared count1 and count2, but as what type? is it string?

please see attachment for another example

find2.xls

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[ragulduy]

Please Login or Register  to view this content.

variables are declared as variants. You could declare them as integers if you prefer.

[mariellove]

Hi John,

thank you for your response. your code works. thank you so much

Hi yudlugar,

the revised code works as well...i tried running with 8000 rows, i waited for around 15minutes, but it's worth the wait. i got what i wanted...haha

thank you so much...salamat salamat

[JOHN H. DAVIS]

You're welcome. Glad to help out, and thanks for the feedback.