How can I loop this index match?

Question

Ok, so I have this macro here that is filling cells with an index match function in columns AB and AC. (this part is fine)

Then it is meant to format those values in column AC into their correct currencies based on the information in a range called AR_Curr in another workbook by matching them to the PL numbers for those shipments (this part is not working and just generates either "Run-time error '1004': Unable to get the Index property of the WorksheetFunction class" or "Run-time error '1004': Unable to get the Match property of the WorksheetFunction class" whenever I try to run it.

Finally it copys all the values now populating the cells and pastes them in as values and centers them.

Could someone please tell me how I can make this select case function work dynamically by searching for an index match to determine the associated currency? I'm at my wits end here.

Sub Payment_YN_Call()
'
' Payment_YN_Call Macro
'
Dim answer1 As Integer
Dim answer2 As Integer
Dim answer3 As Integer

answer1 = MsgBox("Did you update the named ranges for INV_Nums on this sheet?", vbYesNo + vbQuestion, "Payment Update")
If answer1 = vbYes Then
    answer2 = MsgBox("Do you have the AR Balance sheet open?", vbYesNo + vbQuestion, "Payment Update")
    If answer2 = vbYes Then
        answer3 = MsgBox("Did you update the AR_PL_Nums, AR_Paid, and AR_Unpaid Ranges on the AR Balance Sheet?", vbYesNo + vbQuestion, "Payment Update")
        If answer3 = vbYes Then

                Dim i As Integer
                Dim Rng1 As Range
                Dim Rng2 As Range
                Dim ARwkb As Excel.Workbook
                Dim ARwks As Excel.Worksheet
                Dim Samwkb As Excel.Workbook
                Dim Samwks As Excel.Worksheet

                Set Samwkb = Excel.Workbooks("Samples - one sheet")
                Set Samwks = Samwkb.Worksheets("samples shipment")
                Set ARwkb = Excel.Workbooks("AR balance.xlsx")
                Set ARwks = ARwkb.Worksheets("Total Trading")

                For i = 6 To Range("INV_Nums").Count + 5

                    If IsEmpty(Range("AB" & i)) Then
                        Range("AB" & i).Select
                            ActiveCell.FormulaR1C1 = _
                                "=IFERROR(IF(INDEX('[AR balance.xlsx]Total trading'!AR_Unpaid,MATCH(RC[-2],'AR balance.xlsx'!AR_Invoice_Nums,0))=0,""PAID"",""UNPAID""),"""")"
                    End If

                If IsEmpty(Range("AC" & i)) Then
                    Range("AC" & i).Select
                        ActiveCell.FormulaR1C1 = _
                            "=IFERROR(IF(RC[-1]=""PAID"",INDEX('AR balance.xlsx'!AR_Paid,MATCH(RC[-3],'AR balance.xlsx'!AR_Invoice_Nums)),""""),"""")"
                End If

                Next i


                Set Rng1 = ARwks.Range("AR_Curr")
                Set Rng2 = ARwks.Range("AR_PL_Nums")
                Dim lastrow As Long, x As Long
                lastrow = Range("INV_Nums").Count + 5
                    For y = 5 To lastrow
                        Select Case Range(Application.WorksheetFunction.Index(Rng1, Application.WorksheetFunction.Match(Range("F" & y), Rng2, 0)))

                            Case "USD"
                                Samwks.Range("AB" & y).NumberFormat = "$#,##0.00_);($#,##0.00)"
                            Case "RMB"
                                Samwks.Range("AB" & y).NumberFormat = "[$¥-zh-CN]#,##0.00;[$¥-zh-CN]-#,##0.00"
                            Case "EUR"
                                Samwks.Range("AB" & y).NumberFormat = "[$€-x-euro2] #,##0.00_);([$€-x-euro2] #,##0.00)"
                            Case "GBP"
                                Samwks.Range("AB" & y).NumberFormat = "[$£-en-GB]#,##0.00;-[$£-en-GB]#,##0.00"
                            Case "HKD"
                                Samwks.Range("AB" & y).NumberFormat = "[$HK$-zh-HK]#,##0.00_);([$HK$-zh-HK]#,##0.00)"
                            Case "JPY"
                                Samwks.Range("AB" & y).NumberFormat = "[$¥-ja-JP]#,##0.00;-[$¥-ja-JP]#,##0.00"
                        End Select
                    Next y



                Columns("AB:AC").Select
                    Selection.Copy
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False

                Columns("AB:AC").Select
                    Application.CutCopyMode = False
                    With Selection
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlCenter
                        .Orientation = 0
                        .AddIndent = False
                        .IndentLevel = 0
                        .ShrinkToFit = False
                        .ReadingOrder = xlContext
                    End With



Else
End If
End If
End If



End Sub

You have a stray `Else` at the bottom of your code. That is basically a typo which answers the question in your title. You seem to have an unrelated question as well, but please don't try to ask multiple questions at once. — John Coleman, Jul 18 '18 at 11:00

Martin Morse · Answer 1 · 2018-07-18T18:02:27.953

1

Create a range holding the currency and format,such as

And name the range "currencies". Then change the Select Case section of your macro to...

For y = 5 To lastrow
    Set thisCurrency = Range(Application.WorksheetFunction.Index(Rng1, Application.WorksheetFunction.Match(Range("F" & y), Rng2, 0)))
    Samwks.Range("AB" & y).NumberFormat = Application.WorksheetFunction.VLookup(thisCurrency, Range("currencies"), 2, False)
Next y

This has the advantage that you can add new currencies or change formats without making any changes in the macro.

edited Jul 18 '18 at 18:02

answered Jul 18 '18 at 12:03

Martin Morse

26
5

Oh, thats slick creating a variable for "thisCurrency"...thanks. I'll give that a go when I get into the office tomorrow! – Kyle Cranfill Jul 18 '18 at 14:28
That does not seem to have worked.....still getting the same error regarding the index function. – Kyle Cranfill Jul 19 '18 at 01:11
It may be breaking if the PL number listed in column F is not one in the range called AR_PL_Nums. In that instance, the `Match` function will return an error and therefore cause the `index` function to fail. You should either check that all PL numbers in column F can be found in AR_PL_Nums, or simply ignore. A simple solution to this would be to add `On Error Resume Next` just above the `For y...` line, and `On Error Goto 0` on the line after `Next y`. – Martin Morse Jul 19 '18 at 08:16

How can I loop this index match?

1 Answers1