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]

Sorry the line should read

Set r = ....

 

[PhoneBoy]

Getting closer. I did not get any errors this time, but there was no resultant text in column B. Ideas?

 

[jindon]

try this one first and return with message if you get...

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, msg As String, flg As Boolean

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 : flg = True
      Do
         r.Offset(,1).Value= LW.Cells(I,2).Value
         Set r = RW.Columns("b").FindNext(r)
      Loop Until ff = r.Address
   Else
      msg = msg & LW.Cells(I,2).Value & vbLf
   End If
Next I
If Len(msg) Then
   If Not flg Then 
      msg = "Nothing Found check the data" & vbLf & msg
   Else
      msg = "Following data not found" & vbLf & msg
   end If
   MsgBox msg
End If
End Sub

 

[PhoneBoy]

I got a pop up box that says nothing found check the data with:

NC
NC
NC
NC
NC
NC
NC
NC
NC
AL
AL
etc.

Basically, it looks like what I want to be in column B, but it doesn't seem to be able to match it based off of column A

Thanks again. I'm sure that you have better stuff to do

 

[jindon]

OK
try change one line that is

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

to

Set r = RW.Columns("b").Find(Lw.Cells(I,1).Value,,,xlPart)

and see what you get..

 

[PhoneBoy]

It didn't change anything. Same error message. I tried it with a blank column B that I inserted and the normal column B that is there. I appreciate your patience. Thanks again.

 

[jindon]

does this make any change?

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, msg As String, flg As Boolean

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,,xlValues,xlWhole)
   If Not r Is Nothing then
      ff = r.Address : flg = True
      Do
         r.Offset(,1).Value= LW.Cells(I,2).Value
         Set r = RW.Columns("b").FindNext(r)
      Loop Until ff = r.Address
   Else
      msg = msg & LW.Cells(I,1).Value & vbLf
   End If
Next I
If Len(msg) Then
   If Not flg Then 
      msg = "Nothing Found check the data" & vbLf & msg
   Else
      msg = "Following data not found" & vbLf & msg
   end If
   MsgBox msg
End If
End Sub

 

[PhoneBoy]

same error except this time instead of getting
NC
NC
NC
AL
etc.

I got
19083
8857
4797
etc

 

[agihcam]

Phoneboy -

that code will work if the data was in columnsB, It seems that your data was in columnsA, if that should be the case, then just change the code from;

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

to

Set r = RW.Columns("a").Find(LW.Cells(I, 1).Value,,xlValues,xlWhole)

and

Set r = RW.Columns("b").FindNext(r)

to

Set r = RW.Columns("a").FindNext(r)

 

[jindon]

PhoneBoy

you have 2 sheets, "data" & "list"

sheet("list") has "what" in column.A & "replace" in Column.C

Then find each "what" in column B of sheet("data") & if find, "replace" will be in column.C of "data"

the code is doing this.

Am I missing something?