VBA Macro that finds and replace the column name

January 22, 2022

I am new to macro I am trying to find and replace the column name. I have the code that works but I have a doubt. I am confused with For X = loop. I want the macro to loop in the first row only but my codes are like it loops the entire sheet which I dont want

I need to fix the for loop that should read/loop only the first row not the entire sheet.

below are my codes

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

Set sht = ActiveWorkbook.Worksheets("Final Exclusion")

fndList = Array("Enter one or more MSOs or ESOs to be excluded.  Separate multiple values using a semicolon.", _
                "Select the datasets from which you wish to exclude these MSOs:", _
                "Select the reason code for the exclusion:", _
                "Submitter's CWSID")
                
rplcList = Array("MSO", "SOCS", "Reason's", "Submitter")

With sht

'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("Exp")
        sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
          lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
     ' Next sht
  
  Next x
  
End With

Please help me fix this "For x = LBound(fndList) To UBound(fndList)" where I want to code only for the first row A1: till end of 1st row. For loop should only loop and find and replace the array value from first Row A1 till end of 1 row

>Solution :

Please, try the next way. It will try matching the first array elements on the first row headers and replace the found one with the corresponding string from the second:

Sub ReplaceHeaders()
  Dim shT As Worksheet, lastCol As Long, fndList, rplcList, rngHd As Range, mtch, i As Long
  
  Set shT = ActiveWorkbook.Worksheets("Final Exclusion")
  lastCol = shT.cells(1, shT.Columns.count).End(xlToLeft).Column
  
  fndList = Array("Enter one or more MSOs or ESOs to be excluded.  Separate multiple values using a semicolon.", _
                "Select the datasets from which you wish to exclude these MSOs:", _
                "Select the reason code for the exclusion:", _
                "Submitter's CWSID")

 rplcList = Array("MSO", "SOCS", "Reason's", "Submitter")
 Set rngHd = shT.Range(shT.cells(1, 1), shT.cells(1, lastCol))
 For i = 0 To UBound(fndList)
    mtch = Application.match(fndList(i), rngHd, 0)
    If Not IsError(mtch) Then
        rngHd(1, mtch).Value = rplcList(i)
    End If
 Next i
End Sub