macro help column A to column B based on list

PhoneBoy

New Member
Joined
Oct 2, 2006
Messages
12
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

you need to use Find method..like
Code:
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

New Member
Joined
Oct 2, 2006
Messages
12
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
 

PhoneBoy

New Member
Joined
Oct 2, 2006
Messages
12

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Oct 2, 2006
Messages
12

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Tyr this and if this takes too long, just post back, I will give you faster one.
Code:
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

New Member
Joined
Oct 2, 2006
Messages
12
I got a compile error: sub or function not defined

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

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,730
Members
410,702
Latest member
clizama18
Top