Consulting

Results 1 to 6 of 6

Thread: Help needed: looping within loops

  1. #1

    Help needed: looping within loops

    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!

    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
         End if 
         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
    Last edited by Bob Phillips; 06-29-2018 at 03:34 AM. Reason: Added code tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It looks to me that your problem is that your subroutine requires a parameter value. You either need t get rid of it and find another way to get the parameter value, or have another sub that set the dictionary and passes it to your sub.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    You can't run a sub (via F5 or Alt-F8) if it requires a parameter
    Last edited by Paul_Hossler; 06-29-2018 at 08:29 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Before anyone spends significant time on this one, be aware that this has been cross posted to at least 3 other sites.

  5. #5
    I'm so sorry, didn't mean to cross-post as I am really anxious to rope in other experts' help. So sorry about that.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Posting in other forums is fine, but they (like VBAexpress) just like to know if you posted other places

    Look at the link from #4 in my sig
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •