index/match dynamic range

MiGon

New Member
Joined
Oct 27, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
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

VBA Code:
*****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
 

Attachments

  • Capture.PNG
    Capture.PNG
    14.3 KB · Views: 2
Last edited by a moderator:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Oct 27, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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
 

Attachments

  • Capture1.PNG
    Capture1.PNG
    2.4 KB · Views: 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
Oops, it should be
VBA Code:
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

New Member
Joined
Oct 27, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows
Oops, it should be
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,935
Messages
5,545,100
Members
410,656
Latest member
Hydraulics
Top