How do I loop around loops VBA

See more:

MS-Excel

VBA

Hi,

I really appreciate your help in this as I am new to VBA. I have an Infopath form that basically stores data that users fill in, in XML format. Then, it will be passed to Excel to treat the data. Some data remains constant throughout e.g. Names and Date. However, as the form is trying to accommodate multiple transactions to be recorded within the same date, some data is unique to each transaction e.g. Price/Currency.

As such I am trying to create a code such that data that is unique to each transaction will be looped over from 1 to 10 (max amount of transactions that can be recorded within the same form) and they are now renamed as 'Price 1', 'Price 2' etc. All this while, data that is the same (e.g. name and date) still remains in the table and each transaction is saved into the Database before moving on to the next transaction's data. If there is no more transaction, the code should stop running.

When i click run, it prompts me for the name of the macro and does not run at all. I'm not sure how to go about editing it too. Will really appreciate your help. Thank you!



The other codes that is calling this code are:

The other code that is calling this code is this: 
    Sub sub_form (dicData As Dictionary)
    
    Dim dicTransaction As Dictionary
    Dim key As Variant
    Dim key2 As Variant
    For Each key In dicData.Keys
        blnWait = True
        If InStr(key, REPEAT_KEY) Then
            Set dicTransaction = dicData(key)
            dicTransaction("exchangeRate") = dicTransaction("exchangeRate") / 100
            
            Call sub_PreLoad(dicTransaction)
         End If
    Next
    
    Set dicTrade = Nothing
    Set key = Nothing
    Set key2 = Nothing


The sub_Preload procedure looks like this: 

    Sub sub_PreLoad(dicData As Dictionary)
       
    Dim i As Integer: i = 1
    Dim j As Integer
    Dim no As Integer
    
    Dim vTemp As Variant
    vTemp = Range(Range("rInputStart").Offset(1, 1), _ 
    fnc_LastCellInCol(Range("rinputstart")).Offset(-1).Offset(0, 1)).value
    Dim vKey As Variant
    
    For i = LBound(vTemp, 1) To UBound(vTemp, 1)
        If Not vTemp(i, 1) = vbNullString Then
            If no>1 Then
                vFormKey = Split(vTemp(i, 1), "|")
                For j = LBound(vKey) To UBound(vKey)
                    If dicData.Exists(vKey(j)) Then
                        Range("rInputStart").Offset(i, 2).value = dicData_
                        (vKey(j))
                    End If
                Next j
            Else: Call sub_inputData
        End If
    Next i
    
    Set vTemp = Nothing
    Set vKey = Nothing
    
    End Sub

(P.s: I have cross-posted in other forums because I am really anxious to seek others' experts help. I'm so sorry about that.

What I have tried:

I have tried the following code:

Sub sub_inputData(dicData AsDictionary)
Dim ws As Worksheet: Set ws =ActiveSheet
Dim i As Integer
Dim j As Integer
Dim vTemp As Variant
Range("rInputStart").Parent.Calculate
vTemp =Range(Range("rInputStart").Offset(1),_
Range("rInputStart").End(xlDown).Offset(0,2)).value

Dim price as Long  
Dim currency As String: currency = vbNullString
Dim exchangeRate as String: exchangeRate = vbNullString 
Dim remark as String: remark = vbNullString 

For j =1To10

price = price & dicData ("price" & CStr (j))&"|"
price = price ("rPriceManual").value 'and at this point, other functions will be called to convert the prices to different currencies etc,aslongas the prices are inserted into the table correctly

currency = currency & dicData("dl_currency"&CStr(j))&"|"
exchangeRate =(exchangeRate & dicData("exchange_rate"&CStr(j))&"|")/100 
Remark= remark & dicData("remarks"&CStr(j))&"|"

  For i =LBound(vTemp,1)ToUBound(vTemp,1)
   If vTemp(i,1)="currency"And dicData(dl_currency)<> vbNullString Then
      vTemp(i,3)= currency
   Endif 
   If vTemp(i,2)="remark"Then
     vTemp(i,3)=Remark
   EndIf
   If vTemp(i,2)="exchangeRate"Then
     vTemp(i,3)= exchangeRate
   EndIf
  Next i
Next j
End Sub