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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
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,216
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,216
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,193
Messages
5,546,479
Members
410,742
Latest member
WalterSil
Top