Replacing Values

[rholdren]

A friend of mine asked me if I could write something that would use the names on sheet two in column be to replace the names on sheet 1 in column a. The actual sheet has about 47K lines of data and will grow as the year grows. What is listed below is completely made up. I've looked at the SWITCH and MATCH and don't believe those to be heading in the right direction. What direction should I be looking? To be hones I'm not even certain where to post this. I've tried searching using a variety of terms and phrases and I just couldn't find the right terms. So, I thought I would start fresh this morning and post, then apologize for probably not finding what I should have and also placing this in the correct location.

 

[jasonb75]

If the names are already entered as text in sheet 1 then a formula will not work. A formula can only show a result in the cell where it is entered, it can not send information or make changes to existing entries elsewhere.

If sheet1 column A already contains formulas then you should be able to edit the formula to do what is needed, we would need to see the existing formula to advise further.

Most likely you will need to use vba to emulate find and replace, cycling through the names in sheet 2 with a loop. (this would be the same as using Ctrl h to make changes but would be automated).

 

[rholdren]

If the names are already entered as text in sheet 1 then a formula will not work. A formula can only show a result in the cell where it is entered, it can not send information or make changes to existing entries elsewhere.

If sheet1 column A already contains formulas then you should be able to edit the formula to do what is needed, we would need to see the existing formula to advise further.

Most likely you will need to use vba to emulate find and replace, cycling through the names in sheet 2 with a loop. (this would be the same as using Ctrl h to make changes but would be automated).

Neither worksheet has a formula in them. They were a slight representation. The exact file has personal info on it so I just made a mini-replica. I'm okay with the looping but I'm not sure how to say find this value in column a on sheet2 and replace it on sheet1 with the value in column b on sheet2.

 

[Peter_SSs]

Give this a try with a copy of the workbook.

Sub ReplaceNames()
  Dim a As Variant
  Dim i As Long
  
  a = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)).Value2
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    For i = 1 To UBound(a)
      .Columns("A").Replace What:=a(i, 1), Replacement:=a(i, 2), LookAt:=xlWhole, MatchCase:=False
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

 

[rholdren]

Give this a try with a copy of the workbook.

Sub ReplaceNames()
  Dim a As Variant
  Dim i As Long
 
  a = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp)).Value2
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    For i = 1 To UBound(a)
      .Columns("A").Replace What:=a(i, 1), Replacement:=a(i, 2), LookAt:=xlWhole, MatchCase:=False
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

Peter_SSs Thank you again so much. In trying to understand this the .Value2 does that refer to the matching value in column b? Also still trying to get comfortable with Unbound. It's ironic that I have taken a lot of the classes online (and learned a lot) but I learn so much more from the great minds on this BB. Your help is grealty appreciated.​

 

[Peter_SSs]

In trying to understand this the .Value2 does that refer to the matching value in column b?

No, just all the values from A2 down to the bottom of the data in column B are read into the array 'a'. That is, values from both columns.

 

[rholdren]

No, just all the values from A2 down to the bottom of the data in column B are read into the array 'a'. That is, values from both columns.

Thank you for the again. I am very appreciative.

 

[Peter_SSs]

Cheers. You're welcome.