2

I have a file that I would like to run a Find and Replace on using data from another Excel file.

I have this so far, what am I doing wrong?

Sub LegalName()
    Dim NameListWB As Workbook
    Dim NameListWS As Worksheet
    Set NameListWB = Workbooks.Open("File.xlsx")
    Set NameListWS = NameListWB.Worksheets("Sheet1")
    Dim rng As Range

    Set rng = NameListWS.Range("A:B").Select
     Do Until IsEmpty(ActiveCell)
             Worksheets("Sheet1").Columns("F").Replace _
            What:=ActiveCell.Value, Replacement:=ActiveCell.Offset(0, 1).Value, _
            SearchOrder:=xlByColumns, MatchCase:=False
         ActiveCell.Offset(1, 0).Select
     Loop
End Sub
ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
svicino
  • 163
  • 1
  • 2
  • 9
  • 1
    What is wrong with the code example posted? Does it throw an error? If so on which line? – Gareth Sep 16 '14 at 14:17
  • It looks like you're swapping columns? Could you explain what it is doing when you run it, or are you getting an error? – Jimmy Smith Sep 16 '14 at 14:17
  • 1
    Where are you replacing and from where? – Siddharth Rout Sep 16 '14 at 14:18
  • I am searching Column F to see if it has any words that match anything in File.xlsx column A, if it matches I wanted to replace the words with what is in column B of File.xlsx. – svicino Sep 16 '14 at 14:34

1 Answers1

3

I see that you started by declaring your objects but missed out on few. Also, you need to avoid the use of .Select Interesting Read

Is this what you are trying (UNTESTED)?

Sub Sample()
    Dim NameListWB As Workbook, thisWb As Workbook
    Dim NameListWS As Worksheet, thisWs As Worksheet
    Dim i As Long, lRow As Long

    '~~> This is the workbook from where your code is running
    Set thisWb = ThisWorkbook
    '~~> Change this to the sheet name where you want to replace
    '~~> in Column F
    Set thisWs = thisWb.Sheets("Sheet1")

    '~~> File.xlsx
    Set NameListWB = Workbooks.Open("C:\File.xlsx")
    Set NameListWS = NameListWB.Worksheets("Sheet1")

    With NameListWS
        '~~> Find last row in Col A of File.xlsx
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Loop though Col A
        For i = 1 To lRow
            '~~> Do the replace
            thisWs.Columns(6).Replace What:=.Range("A" & i).Value, _
                                      Replacement:=.Range("B" & i).Value, _
                                      SearchOrder:=xlByColumns, _
                                      MatchCase:=False
        Next i
    End With
End Sub
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Yes! It seems I have a lot more to lean in VBA but this does work perfectly! Thank you. – svicino Sep 16 '14 at 14:49
  • I am adding this to the PERSONAL.XLSB and it is giving me an error for referencing the sheets by there name. I replaced 'Set thisWs = thisWb.Sheets("Sheet1")' with 'Set thisWs = thisWb.Sheets(1)' as well as the reference below. It does not give me an error any more but it does not work. – svicino Sep 17 '14 at 14:13