Как я могу зациклить это совпадение индекса?

Question

Итак, у меня здесь есть этот макрос, который заполняет ячейки функцией сопоставления индексов в столбцах AB и AC. (эта часть в порядке)

Затем предполагается отформатировать эти значения в столбце AC в правильные валюты на основе информации в диапазоне, называемом AR_Curr в другой рабочей книге, сопоставив их с номерами PL для этих отправлений (эта часть не работает и просто генерирует "Выполнить ошибка времени '1004': невозможно получить свойство Index класса WorksheetFunction "или" ошибка времени выполнения "1004": невозможно получить свойство Match класса WorksheetFunction "всякий раз, когда я пытаюсь запустить его.

Наконец, он копирует все значения, которые теперь заполняют ячейки, и вставляет их как значения и центрирует их.

Может кто-нибудь сказать мне, как я могу заставить эту функцию выбора дела работать динамически, ища совпадение индекса, чтобы определить связанную валюту? Я в своем уме остроумия здесь.

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

Answer 1

Создайте диапазон, содержащий валюту и формат, например

И назовите диапазон "валют". Затем измените раздел Select Case вашего макроса на...

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

Преимущество этого заключается в том, что вы можете добавлять новые валюты или изменять форматы, не внося никаких изменений в макрос.