VBA Code

[Closed]
Report
-
Posts
12
Registration date
Saturday August 30, 2008
Status
Member
Last seen
March 31, 2009
-
Hello,

I am very very new to VBA, (although I did some VB in college), and am attempting to create an Excel Macro that will allow me to search through all values in one column, and based on those values, enter a corresponding (but not equal) value into the adjacent column.

For the purpose of example (although these are not exactly the numbers I am working with), let's say that column H is named "zip-code." There are thousands of rows, and perhaps 50 unique zipcodes. I want to find every occurrence of 30 of those zipcodes, and in the adjacent column enter the state for each occurence of those zipcodes. For the remaining 20 or so zipcodes I don't need, I want to delete the entire row associated with a cell containing one of those unneeded zipcodes.
EXAMPLE:
ZIP CODE       STATE
98335            WA
90210            CA
64504            MO
75044            TX
75078            TX
90210            CA


Is there a way to do this with a macro? And if so, how? I need to do this for a report I've been generating monthly, and it takes me several hours to do by hand.

THANK YOU!

1 reply

Posts
12
Registration date
Saturday August 30, 2008
Status
Member
Last seen
March 31, 2009
4
hi,
try this:

Set r = [A1] 'First cell of column with names to look for
Set r2 = [B1:B100] 'First cell of column with names to look in
Set r3 = [C1] 'New column created with names that do not exist in column B

Do Until r = ""
For Each rFound In r2
If rFound.Value = r.Value Then
NameFound = ""
Exit For
End If

If rFound.Value <> r.Value Then
NameFound = r.Value
End If
Next rFound

If NameFound <> "" Then
r3.Value = NameFound
Set r3 = r3.Offset(1, 0)
End If

NameFound = ""
Set r = r.Offset(1, 0)

Loop

i hope that it works for you.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month