英文:
Loop through column B in multiple worksheets
问题
我明白,这是您的VBA代码的一部分,我将翻译其中的注释和字符串内容:
Function MerchantsPrefillVol() As String
Dim i, j As Double, dCount As Double, strMerchants As String
Dim strToelichtingMerchant, strUrl As String
Dim wbMerchantBieb As Workbook
Dim wsTemplate, ws As Worksheet
Application.ScreenUpdating = False
Set wsTemplate = ThisWorkbook.Worksheets("Template")
Set wbMerchantBieb = Application.Workbooks.Open(cStrMerchantBieb)
With wsTemplate
'Count merchants
For i = 0 To 20
If .Range("antMerchantNaam").Offset(0, i).Value <> "" Then
MerchantsPrefillVol = MerchantsPrefillVol & "Merchant " & .Range("antMerchantNaam").Offset(0, i).Value
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Crypto & Trading").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Crypto & Trading").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Moneytransfer").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Moneytransfer").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Gambling").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Gambling").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Donation").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Donation").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("High Risk Terrorism Activity").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("High Risk Terrorism Activity").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Whitelist").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Whitelist").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("High Risk Terrorism Country").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("High Risk Terrorism Country").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = ""
End If
End If
End If
End If
End If
End If
If LCase(.Range("antMerchantNaam").Offset(1, i).Value) = "ja" Then
MerchantsPrefillVol = MerchantsPrefillVol & " is opgenomen in de Merchant bibliotheek. Hierin staat het volgende: " & Chr(10) & strToelichtingMerchant & Chr(10) & Chr(10) & .Range("antMerchantNaam").Offset(2, i).Value & Chr(10) & Chr(10)
Else
MerchantsPrefillVol = MerchantsPrefillVol & Chr(10)
End If
End If
Next i
End With
End Function
希望这有所帮助。
英文:
I am new to VBA and am making a template, in which the user selects upto 20 merchant names. The first merchant name range is called antMerchantNaam
, as you can see here: Template
What I want is to loop through column B in every sheet in another workbook which is called wbMerchantBieb
in my code. This workbook looks like this:
And I want to retrieve the offset value next to it from column C and column D and store that in the variable strToelichtingMerchant
I have already tried to use the VLookup, but this gives me error '9': Subscript out of range:
Function MerchantsPrefillVol() As String
Dim i, j As Double, dCount As Double, strMerchants As String
Dim strToelichtingMerchant, strUrl As String
Dim wbMerchantBieb As Workbook
Dim wsTemplate, ws As Worksheet
Application.ScreenUpdating = False
Set wsTemplate = ThisWorkbook.Worksheets("Template")
Set wbMerchantBieb = Application.Workbooks.Open(cStrMerchantBieb)
With wsTemplate
'Count merchants
For i = 0 To 20
If .Range("antMerchantNaam").Offset(0, i).Value <> "" Then
MerchantsPrefillVol = MerchantsPrefillVol & "Merchant " & .Range("antMerchantNaam").Offset(0, i).Value
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Crypto & Trading").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Crypto & Trading").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Moneytransfer").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Moneytransfer").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Gambling").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Gambling").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Donation").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Donation").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("High Risk Terrorism Activity").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("High Risk Terrorism Activity").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Whitelist").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("Whitelist").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("High Risk Terrorism Country").Range("B:C"), 2, False) & Chr(10) & Chr(10) & _
Application.VLookup(.Range("antMerchantNaam").Offset(0, i).Value, wbMerchantBieb.Worksheets("High Risk Terrorism Country").Range("B:D"), 3, False)
If IsError(strToelichtingMerchant) Then
strToelichtingMerchant = ""
End If
End If
End If
End If
End If
End If
End If
If LCase(.Range("antMerchantNaam").Offset(1, i).Value) = "ja" Then
MerchantsPrefillVol = MerchantsPrefillVol & " is opgenomen in de Merchant bibliotheek. Hierin staat het volgende: " & Chr(10) & strToelichtingMerchant & Chr(10) & Chr(10) & .Range("antMerchantNaam").Offset(2, i).Value & Chr(10) & Chr(10)
Else
MerchantsPrefillVol = MerchantsPrefillVol & Chr(10)
End If
End If
Next i
End With
End Function
答案1
得分: 0
以下是您要翻译的代码部分:
Function MerchantsPrefillVol() As String
Dim i, j As Double, dCount As Double, strMerchants As String
Dim strToelichtingMerchant, strUrl As String, cStrMerchantBieb
Dim wbMerchantBieb As Workbook, merch, c As Range
Dim wsTemplate, ws As Worksheet, arrWsNames, wsName, rngVL As Range, m
'cStrMerchantBieb = "C:\Temp\Test.xlsx"
Application.ScreenUpdating = False
Set wsTemplate = ThisWorkbook.Worksheets("Template")
Set wbMerchantBieb = Application.Workbooks.Open(cStrMerchantBieb)
arrWsNames = Array("Crypto & Trading", "Moneytransfer", "Gambling", _
"Donation", "High Risk Terrorism Activity", _
"Whitelist", "High Risk Terrorism Country")
For Each c In wsTemplate.Range("antMerchantNaam").Resize(1, 20).Cells
merch = c.Value
If Len(merch) > 0 Then
MerchantsPrefillVol = MerchantsPrefillVol & "Merchant " & merch
If LCase(c.Offset(1).Value) = "ja" Then
strToelichtingMerchant = ""
For Each wsName In arrWsNames
Set rngVL = wbMerchantBieb.Worksheets(wsName).Columns("B") 'lookup column
m = Application.Match(merch, rngVL, 0) 'look for match
If Not IsError(m) Then 'got match?
strToelichtingMerchant = rngVL.Cells(m).Offset(0,1).Value & vbLf & vbLf & _
rngVL.Cells(m).Offset(0,2).Value
Exit For
End If
Next wsName
MerchantsPrefillVol = MerchantsPrefillVol & _
" is opgenomen in de Merchant bibliotheek. Hierin staat het volgende: " & _
Chr(10) & strToelichtingMerchant & Chr(10) & Chr(10) & _
c.Offset(2).Value & Chr(10) & Chr(10)
Else
MerchantsPrefillVol = MerchantsPrefillVol & Chr(10)
End If
End If
Next c
End Function
请注意,我已经将代码部分提供给您,而不进行翻译。
英文:
Maybe try a loop-based approach.
This is untested, but should be pretty close if I've understood your posted code correctly.
Function MerchantsPrefillVol() As String
Dim i, j As Double, dCount As Double, strMerchants As String
Dim strToelichtingMerchant, strUrl As String, cStrMerchantBieb
Dim wbMerchantBieb As Workbook, merch, c As Range
Dim wsTemplate, ws As Worksheet, arrWsNames, wsName, rngVL As Range, m
'cStrMerchantBieb = "C:\Temp\Test.xlsx"
Application.ScreenUpdating = False
Set wsTemplate = ThisWorkbook.Worksheets("Template")
Set wbMerchantBieb = Application.Workbooks.Open(cStrMerchantBieb)
arrWsNames = Array("Crypto & Trading", "Moneytransfer", "Gambling", _
"Donation", "High Risk Terrorism Activity", _
"Whitelist", "High Risk Terrorism Country")
For Each c In wsTemplate.Range("antMerchantNaam").Resize(1, 20).Cells
merch = c.Value
If Len(merch) > 0 Then
MerchantsPrefillVol = MerchantsPrefillVol & "Merchant " & merch
If LCase(c.Offset(1).Value) = "ja" Then
strToelichtingMerchant = ""
For Each wsName In arrWsNames
Set rngVL = wbMerchantBieb.Worksheets(wsName).Columns("B") 'lookup column
m = Application.Match(merch, rngVL, 0) 'look for match
If Not IsError(m) Then 'got match?
strToelichtingMerchant = rngVL.Cells(m).Offset(0,1).Value & vbLf & vbLf & _
rngVL.Cells(m).Offset(0,2).Value
Exit For
End If
Next wsName
MerchantsPrefillVol = MerchantsPrefillVol & _
" is opgenomen in de Merchant bibliotheek. Hierin staat het volgende: " & _
Chr(10) & strToelichtingMerchant & Chr(10) & Chr(10) & _
c.Offset(2).Value & Chr(10) & Chr(10)
Else
MerchantsPrefillVol = MerchantsPrefillVol & Chr(10)
End If
End If
Next c
End Function
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论