Populate an array directly from row or column

[Tigerexcel]

I have a macro that I use to perform bulk find and replace which works well however the codes change from time to time. I'd like to know how I can vary this macro to pick up the code straight from a worksheet into the macro because at the moment I type the information straight in which can lead to errors particularly when there are lots of entries to change.

Sub Tracker()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

fndList = Array("230", "300", "114", "470")
rplcList = Array("Business", "Chemistry", "Engineering", "Arts")

'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
      For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace what:=fndList(x), replacement:=rplcList(x), _
          Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht
  
  Next x
End sub

Next week codes could be something like:
123
560
417
200
The respective replacements would be:
Biology
Science
Philosophy
Zoology

I've simplified the data but hopefully I've made it clear

 

[DanteAmor]

How about:

Sub Tracker()
  Dim sht As Worksheet, sh1 As Worksheet
  Dim fndList As Variant
  Dim rplcList As Variant
  Dim x As Long
  
  Set sh1 = Sheets("entries")
  fndList = sh1.Range("A1", sh1.Range("A" & Rows.Count).End(3)).Value2
  rplcList = sh1.Range("B1", sh1.Range("B" & Rows.Count).End(3)).Value2
  
  'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
    For Each sht In ActiveWorkbook.Worksheets
      If sht.Name <> sh1.Name Then
        sht.Cells.Replace what:=fndList(x, 1), replacement:=rplcList(x, 1), _
          Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      End If
    Next sht
  Next x
End Sub

 

[Tigerexcel]

Thank you Dante, that works!
I would be lying if I said that I understood all your coding though.
Can I ask what does the End(3) and value2 do to the code?
Is the if sht.Name<>sh1.Name essential to the performance of the macro?
I'm very much a beginner and am trying to understand the coding that I'll be using, I'm also trying to keep it as simple as is possible so that I can understand and use it in other situations.

 

[DanteAmor]

Can I ask what does the End(3) and value2 do to the code?

End(3)
It is the same as using End(xlup), it is to find the last row with data going through the cells from bottom to top.

.Text gives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get ####
.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)
.Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
Using .Value or .Text is usually a bad idea because you may not get the real value from the cell, and they are slower than .Value2

Is the if sht.Name<>sh1.Name essential to the performance of the macro?

It is not good for macro performance but it is essential, because if you don't, then it will also go to the "entries" sheet and make the replacements.
You can change the cycle of sheets, if you put the sheet "entries" at the beginning of the sheets and start the cycle in 2 until the last sheet:

Sub Tracker()
  Dim sh1 As Worksheet
  Dim fndList As Variant
  Dim rplcList As Variant
  Dim sht As Long, x As Long
  
  Set sh1 = Sheets("entries")
  fndList = sh1.Range("A1", sh1.Range("A" & Rows.Count).End(3)).Value2
  rplcList = sh1.Range("B1", sh1.Range("B" & Rows.Count).End(3)).Value2
  
  'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
    For sht = 2 To ActiveWorkbook.Worksheets.Count
      Sheets(sht).Cells.Replace what:=fndList(x, 1), replacement:=rplcList(x, 1), _
        Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    Next sht
  Next x
End Sub

 

[Tigerexcel]

End(3)
It is the same as using End(xlup), it is to find the last row with data going through the cells from bottom to top.

.Text gives you a string representing what is displayed on the screen for the cell. Using .Text is usually a bad idea because you could get ####
.Value2 gives you the underlying value of the cell (could be empty, string, error, number (double) or boolean)
.Value gives you the same as .Value2 except if the cell was formatted as currency or date it gives you a VBA currency (which may truncate decimal places) or VBA date.
Using .Value or .Text is usually a bad idea because you may not get the real value from the cell, and they are slower than .Value2


It is not good for macro performance but it is essential, because if you don't, then it will also go to the "entries" sheet and make the replacements.
You can change the cycle of sheets, if you put the sheet "entries" at the beginning of the sheets and start the cycle in 2 until the last sheet:

Sub Tracker()
  Dim sh1 As Worksheet
  Dim fndList As Variant
  Dim rplcList As Variant
  Dim sht As Long, x As Long
 
  Set sh1 = Sheets("entries")
  fndList = sh1.Range("A1", sh1.Range("A" & Rows.Count).End(3)).Value2
  rplcList = sh1.Range("B1", sh1.Range("B" & Rows.Count).End(3)).Value2
 
  'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
    'Loop through each worksheet in ActiveWorkbook
    For sht = 2 To ActiveWorkbook.Worksheets.Count
      Sheets(sht).Cells.Replace what:=fndList(x, 1), replacement:=rplcList(x, 1), _
        Lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    Next sht
  Next x
End Sub

Thank you Dante for the explanation and love your username.

 

[DanteAmor]

Im glad to help you. Thanks for your comments, is my real name.