macro help column A to column B based on list

[PhoneBoy]

I'm sure that the answer is here, but I don't know enough about it to search intelligently.

Problem: I get a huge spreadsheet (10,000 lines or so) each month that I have to divide up and pay people. I have been using a method that I saw in this post

http://www.mrexcel.com/board2/viewtopic.php?t=167633&highlight=macro+replace+text

which Erik Van Geit proposed. I rename my worksheet as data and create a second worksheet called list where I put my replacement text. It works beatifully especially because I have to do this each month and my list changes as we open new stores.

What I am wanting to do is instead of replacing the text, I would like to create a new column with the info. For example, I am currently replacing 8857 with NC. I would like to keep 8857 in column B and add NC in column C. The if/then statements will not work due to the number of replacements that I have (I think).

Any help would be appreciated.

Thanks,
Scott

 

[jindon]

Hi

you need to use Find method..like

Dim r As Range
For I = 2 To LR
   Set r = RW.Columns("b").Find(LW.Cells(I,1),,,xlWhole)
   If Not r Is Nothing Then
      ff = r.Address
      Do
         r.Offset(,1).Value = LW.Cells(I,2).Value
         Set r = RW.Columns("b").FindNext(r)
      Loop Until ff = r.Address
   End If
Next

 

[PhoneBoy]

jindon,
thanks for the reply. Like I said, I'm not very good with this. How do I apply what you are telling me to my spreadsheet.

Further information:
In column A, I have
8857
8630
24680
24838
9300
15230
92112
and about 50 others.

I would like column B to have
NC
NC
VA
NG
KX
ET
NT
there are 12 of these

based on who 8857, 8630, etc. belongs to.

Do I need to create a worksheet with list like I was doing before?

Thanks again for the reply

 

[agihcam]

when would be the column B becomes NC, VA etc?

 

[PhoneBoy]

Yeah, I'm sure that I'm confusing everyone. I'll try to give you a little more info and, again, I appreciate your help.

In this 10,000 line spreadsheet, I have about 50 different values in column A. So 8857 will be repeated a bunch as well as 4797, 8630, 24838, 9300, etc.

8857 always belongs to NC, 24838 always belongs to NG, etc. This will never change. What does change is the number of different values that show up on my spreadsheet each month. Next month, 74838 will be there and it isn't today.

What I was doing was using the find/replace macro that Erik Van Geit suggested. The problem (for my purposes) is that it replaces the text and it is important for me to know not only that it is NC, but that it was 8857 and not 8630. This is why I was trying to keep the data in column A and have column B based on what is in Column A.

I was using a separate worksheet called list similar to this:
FIND REPLACE
8857 NC
8630 NC
24838 NG
15112 ET
9300 KX
9161 AL

Can I keep this same structure but instead of replacing the text, it puts the result in column B

Again, I appreciate your help

 

[jindon]

PhoneBoy

I wrote that code fully based on what Erik did in the linked thread,
so I was assuming you have set up the list of "What" and "Replace".

If you follow the lined thread and change the For Next Loop Part,
the code should work.

If you don't understnad what I'm saying, please tell us about your
problem with detailed example.

 

[PhoneBoy]

Jindon,
I'm sorry. You are dealing with the Excel intellectual equivalent of a 3 year old.

Here are the examples:

this is the current code of the macro that I am using

Option Explicit

Sub replace_list()
'Erik Van Geit
'050910
'using 2 columns

Dim LR As Long
Dim RW As Worksheet
Dim LW As Worksheet
Dim I As Integer

Set RW = Sheets("data") 'worksheet where you want to replace items
Set LW = Sheets("list") 'worksheet with replace-lists

LR = LW.Cells(65536, 1).End(xlUp).Row

For I = 2 To LR
RW.Cells.Replace what:=LW.Cells(I, 1), Replacement:=LW.Cells(I, 2), LookAt:=xlWhole
Next I

End Sub

This is my current list

what replacement
19083 NC
4797 NC
72883 NC
8857 NC
74333 NC
14704 NC
8630 NC
14699 NC
71628 NC
70016 AL
72601 AL
9161 AL
72792 CF
72793 CF
79284 CF
71626 EC
74384 EC
76104 EC
76192 EC
82069 EC
15230 ET
60420 KY
60422 KY
60423 KY
60424 KY
60425 KY
60426 KY
60428 KY
60456 KY
62112 KY
74665 KY
74666 KY
74667 KY
74668 KY
74946 KY
75093 KY
75094 KY
76406 KY
76582 KY
76599 KY
76692 KY
76694 KY
76695 KY
76696 KY
76760 KY
9300 KX
9301 KX
9310 KX
18112 KX
24838 NG
73137 NT
73089 NT
74948 OR
76405 OR
75684 OR
21444 SG
27215 SG
72600 SG
24679 VA
24680 VA
24681 VA
8613 VA


[/img][/code]

 

[jindon]

Tyr this and if this takes too long, just post back, I will give you faster one.

Option Explicit

Sub replace_list()
'Erik Van Geit
'050910
'using 2 columns

Dim LR As Long
Dim RW As Worksheet
Dim LW As Worksheet
Dim I As Integer
Dim r As Range, ff As String

Set RW = Sheets("data") 'worksheet where you want to replace items
Set LW = Sheets("list") 'worksheet with replace-lists

LR = LW.Cells(65536, 1).End(xlUp).Row

For I = 2 To LR
   Set r = RW.Columns("b").Find(LW.Cells(I, 1).Value,,,xlWhole)
   If Not r Is Nothing then
      ff = r.Address
      Do
         r.Offset(,1).Value= LW.Cells(I,2).Value
         Set r = RW.Columns("b").FindNext(r)
      Loop Until ff = r.Address
   End If
Next I

End Sub

 

[PhoneBoy]

I got a compile error: sub or function not defined

St r = RW.Columns("b").Find(LW.Cells(I, 1).Value, , , xlWhole)

 

[PhoneBoy]

Again, thanks for your help on this.