VBA Code Closed

Question

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!

melnisson · Answer

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.

VBA Code

1 response

Similar discussions

Subscribe To Our Newsletter!