循环遍历多个工作表中的B列。

huangapple go评论62阅读模式
英文:

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: 循环遍历多个工作表中的B列。

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(&quot;Template&quot;)
Set wbMerchantBieb = Application.Workbooks.Open(cStrMerchantBieb)

With wsTemplate
&#39;Count merchants
For i = 0 To 20
    If .Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value &lt;&gt; &quot;&quot; Then
        
        MerchantsPrefillVol = MerchantsPrefillVol &amp; &quot;Merchant &quot; &amp; .Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value
        
        strToelichtingMerchant = Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Crypto &amp; Trading&quot;).Range(&quot;B:C&quot;), 2, False) &amp; Chr(10) &amp; Chr(10) &amp; _
        Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Crypto &amp; Trading&quot;).Range(&quot;B:D&quot;), 3, False)
        
        If IsError(strToelichtingMerchant) Then
            
            strToelichtingMerchant = Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Moneytransfer&quot;).Range(&quot;B:C&quot;), 2, False) &amp; Chr(10) &amp; Chr(10) &amp; _
            Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Moneytransfer&quot;).Range(&quot;B:D&quot;), 3, False)
            
            If IsError(strToelichtingMerchant) Then
            
                strToelichtingMerchant = Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Gambling&quot;).Range(&quot;B:C&quot;), 2, False) &amp; Chr(10) &amp; Chr(10) &amp; _
                Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Gambling&quot;).Range(&quot;B:D&quot;), 3, False)
                
                If IsError(strToelichtingMerchant) Then
            
                    strToelichtingMerchant = Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Donation&quot;).Range(&quot;B:C&quot;), 2, False) &amp; Chr(10) &amp; Chr(10) &amp; _
                    Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Donation&quot;).Range(&quot;B:D&quot;), 3, False)
                
                    If IsError(strToelichtingMerchant) Then
            
                        strToelichtingMerchant = Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;High Risk Terrorism Activity&quot;).Range(&quot;B:C&quot;), 2, False) &amp; Chr(10) &amp; Chr(10) &amp; _
                        Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;High Risk Terrorism Activity&quot;).Range(&quot;B:D&quot;), 3, False)
                    
                        If IsError(strToelichtingMerchant) Then
            
                            strToelichtingMerchant = Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Whitelist&quot;).Range(&quot;B:C&quot;), 2, False) &amp; Chr(10) &amp; Chr(10) &amp; _
                            Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;Whitelist&quot;).Range(&quot;B:D&quot;), 3, False)
                        
                            If IsError(strToelichtingMerchant) Then
            
                                strToelichtingMerchant = Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;High Risk Terrorism Country&quot;).Range(&quot;B:C&quot;), 2, False) &amp; Chr(10) &amp; Chr(10) &amp; _
                                Application.VLookup(.Range(&quot;antMerchantNaam&quot;).Offset(0, i).Value, wbMerchantBieb.Worksheets(&quot;High Risk Terrorism Country&quot;).Range(&quot;B:D&quot;), 3, False)
                            
                                If IsError(strToelichtingMerchant) Then
                                    strToelichtingMerchant = &quot;&quot;
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If

        
        If LCase(.Range(&quot;antMerchantNaam&quot;).Offset(1, i).Value) = &quot;ja&quot; Then
            
            MerchantsPrefillVol = MerchantsPrefillVol &amp; &quot; is opgenomen in de Merchant bibliotheek. Hierin staat het volgende: &quot; &amp; Chr(10) &amp; strToelichtingMerchant &amp; Chr(10) &amp; Chr(10) &amp; .Range(&quot;antMerchantNaam&quot;).Offset(2, i).Value &amp; Chr(10) &amp; Chr(10)
        Else
            MerchantsPrefillVol = MerchantsPrefillVol &amp; 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
    
    &#39;cStrMerchantBieb = &quot;C:\Temp\Test.xlsx&quot;
    
    Application.ScreenUpdating = False
    
    Set wsTemplate = ThisWorkbook.Worksheets(&quot;Template&quot;)
    Set wbMerchantBieb = Application.Workbooks.Open(cStrMerchantBieb)
    
    arrWsNames = Array(&quot;Crypto &amp; Trading&quot;, &quot;Moneytransfer&quot;, &quot;Gambling&quot;, _
                       &quot;Donation&quot;, &quot;High Risk Terrorism Activity&quot;, _
                       &quot;Whitelist&quot;, &quot;High Risk Terrorism Country&quot;)
    
    For Each c In wsTemplate.Range(&quot;antMerchantNaam&quot;).Resize(1, 20).Cells
        merch = c.Value
        If Len(merch) &gt; 0 Then
            MerchantsPrefillVol = MerchantsPrefillVol &amp; &quot;Merchant &quot; &amp; merch
            
            If LCase(c.Offset(1).Value) = &quot;ja&quot; Then
                strToelichtingMerchant = &quot;&quot;
                For Each wsName In arrWsNames
                    Set rngVL = wbMerchantBieb.Worksheets(wsName).Columns(&quot;B&quot;) &#39;lookup column
                    m = Application.Match(merch, rngVL, 0) &#39;look for match
                    If Not IsError(m) Then                 &#39;got match?
                        strToelichtingMerchant = rngVL.Cells(m).Offset(0,1).Value &amp; vbLf &amp; vbLf &amp; _
                                                 rngVL.Cells(m).Offset(0,2).Value
                        Exit For
                    End If
                Next wsName
                MerchantsPrefillVol = MerchantsPrefillVol &amp; _
                    &quot; is opgenomen in de Merchant bibliotheek. Hierin staat het volgende: &quot; &amp; _
                    Chr(10) &amp; strToelichtingMerchant &amp; Chr(10) &amp; Chr(10) &amp; _
                    c.Offset(2).Value &amp; Chr(10) &amp; Chr(10)
            Else
                MerchantsPrefillVol = MerchantsPrefillVol &amp; 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
    
    &#39;cStrMerchantBieb = &quot;C:\Temp\Test.xlsx&quot;
    
    Application.ScreenUpdating = False
    
    Set wsTemplate = ThisWorkbook.Worksheets(&quot;Template&quot;)
    Set wbMerchantBieb = Application.Workbooks.Open(cStrMerchantBieb)
    
    arrWsNames = Array(&quot;Crypto &amp; Trading&quot;, &quot;Moneytransfer&quot;, &quot;Gambling&quot;, _
                       &quot;Donation&quot;, &quot;High Risk Terrorism Activity&quot;, _
                       &quot;Whitelist&quot;, &quot;High Risk Terrorism Country&quot;)
    
    For Each c In wsTemplate.Range(&quot;antMerchantNaam&quot;).Resize(1, 20).Cells
        merch = c.Value
        If Len(merch) &gt; 0 Then
            MerchantsPrefillVol = MerchantsPrefillVol &amp; &quot;Merchant &quot; &amp; merch
            
            If LCase(c.Offset(1).Value) = &quot;ja&quot; Then
                strToelichtingMerchant = &quot;&quot;
                For Each wsName In arrWsNames
                    Set rngVL = wbMerchantBieb.Worksheets(wsName).Columns(&quot;B&quot;) &#39;lookup column
                    m = Application.Match(merch, rngVL, 0) &#39;look for match
                    If Not IsError(m) Then                 &#39;got match?
                        strToelichtingMerchant = rngVL.Cells(m).Offset(0,1).Value &amp; vbLf &amp; vbLf &amp; _
                                                 rngVL.Cells(m).Offset(0,2).Value
                        Exit For
                    End If
                Next wsName
                MerchantsPrefillVol = MerchantsPrefillVol &amp; _
                    &quot; is opgenomen in de Merchant bibliotheek. Hierin staat het volgende: &quot; &amp; _
                    Chr(10) &amp; strToelichtingMerchant &amp; Chr(10) &amp; Chr(10) &amp; _
                    c.Offset(2).Value &amp; Chr(10) &amp; Chr(10)
            Else
                MerchantsPrefillVol = MerchantsPrefillVol &amp; Chr(10)
            End If
            
            
        End If
    Next c
End Function

huangapple
  • 本文由 发表于 2023年3月21日 02:55:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/75794240-2.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定