index/match dynamic range

[MiGon]

Hi all,
I have coded my index match but I used formula instead of function, also my code has set ranges instead of dynamic ones.
Clumns A,C,F changes everyday so need to implement index/match functions and dynamic ranges. Is there a chance someone could kindly help me with this, please.
Thank you
Michal

*****Static Code*****
'index match
  
    Range("B4").Select
    ActiveCell.FormulaR1C1 = "=INDEX(C[4],MATCH(RC[-1],C[1],0))"
    Range("B4").Select
    LRow = Range("B4").End(xlDown).Row
    Selection.AutoFill Destination:=Range("B4:B" & LRow)
    Range("B4:B" & LRow).Select
    Columns("B:B").Select
    Selection.copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Replace What:="#N/A", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
  
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub MiGon()
   With Range("B4:B", Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=INDEX(C[4],MATCH(RC[-1],C[1],0))"
      .Value = .Value
      .Replace "#N/A", "-", xlPart, , , , False, False
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .WrapText = False
   End With
End Sub

 

[MiGon]

Hi & welcome to MrExcel.
How about

Sub MiGon()
   With Range("B4:B", Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=INDEX(C[4],MATCH(RC[-1],C[1],0))"
      .Value = .Value
      .Replace "#N/A", "-", xlPart, , , , False, False
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .WrapText = False
   End With
End Sub

Hi,
thank you for the quick response but getting 'Run-time error 1004', Method Range of object _Global failed

 

[Fluff]

Oops, it should be

Sub MiGon()
   With Range("B4:B" & Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=INDEX(C[4],MATCH(RC[-1],C[1],0))"
      .Value = .Value
      .Replace "#N/A", "-", xlPart, , , , False, False
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .WrapText = False
   End With
End Sub

 

[MiGon]

Oops, it should be

Sub MiGon()
   With Range("B4:B" & Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=INDEX(C[4],MATCH(RC[-1],C[1],0))"
      .Value = .Value
      .Replace "#N/A", "-", xlPart, , , , False, False
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .WrapText = False
   End With
End Sub

Thanks Very much!

 

[Fluff]

You're welcome & thanks for the feedback.