VB to search a value in an array from a sheet then write another c

[bakbuk]

I have problem that has bugged me for the last 2 weeks which looks like can
not be handled by excel function, since excel formula can not facilitate to
write a value to another cell.

# Condition
-> workbook consist of 5 sheets
--> sheet1 contain list of text composed of 4-5 characters (AAAA, ASDF,
ABCD3, ...etc) listed vertically
....|...A...|
1 | ASDF
2 | AHGI9
3 | AERT
4 | ...
....
--> sheet2 contain list of 4-chars possibilities from AAAA-AZZZ.
[] A.......B.........C
==========================================
1|......|...A...|...B...| ...
-------------------------------------------------------------------
2|AAA |AAAA |AAAB |
3|AAB |AABA |AABB |
4|AAC |AACA |...
5|AAD |AADA |...
....
--> sheet3 contain list of 5-chars possibilities from AAAA3-AZZZ9
[] A..........B.........C
==========================================
1|........|....3....|....4....| ...
-------------------------------------------------------------------
2|AAAA |AAAA3 |AAAA4 |
3|AAAB |AAAB3 |AAAB4 |
4|AAAC |AAAC3 |...
5|AAAD |AAAD3 |...
....
--> sheet4 similar format with sheet2 but without the list of possibilities
--> sheet5 similar format with sheet3 but without the list of possibilities

# What I need
-> check each text listed in sheet1 cell ranging from A1 to A65536 whether
composed of 4-chars or 5-chars
-> then check each text whether listed in sheet2 (for 4-chars) or sheet3
(for 5-chars) and get the cell address that corresponds (if existed)
-> write an information to a cell in sheet4 or sheet5 that has same cell
address to indicate that the text is listed. For example:
--> in sheet1, AAAA is listed
--> since it's composed of 4 chars, it has to be searched within sheet2
--> found AAAA is in cell B2 within sheet2
--> in sheet 4 (since it's 4-chars), write "Used" in cell B2
-> loop continue for all text listed in sheet1

any help are appreciated, i am a complete newbie at VB...