VBA array loop nesting


Well-known Member
May 25, 2014
Hey yall. Long time no see. I must be out of practice. Can yall help me out with a thing I'm working on for my job? I shrank the code down and the input data so that it wouldn't be too much too read. I only left the relevant code and relevant input data.

Data is in Microsoft word; However, that doesn't matter because I'm using a UserForm that is available in both Word and Excel, and I haven't even gotten to the part in the code where I output the data from the form into the word document. That will come after I figure out this current issue. I run the macro showForm(), and a form pops up. I paste some data into the form and click the submit button. It will split each line into an individual element in an array. For each element, check if it says "History of Present Illness:". Once found, it will add all following data into a different array. It will stop adding once it finds "Major Problems:"

Here is some sample data that I paste into the UserForm...
Ms. Jennifer Lopez is a 51 year old female who presents for follow up on chronic kidney disease. Patient complains of for high blood pressure in dialysis and has been taking hydralazine prior to each treatment. No nausea vomiting no chest pain. She is awaiting the evaluation for transplant with Methodist Hospital in San Antonio.

History of Present Illness:
1. Hypertension
2. Coronary Artery Disease

Major Problems:

The output into getHxPresentIllness = aryHxPresentIllness() should be the following elements:
1. Hypertension
2. Coronary Artery Disease

[SIZE=2][FONT=arial]Sub showForm()[/FONT]
[FONT=arial]    frmProgressNote.Show[/FONT]
[FONT=arial]End Sub

Private Sub btnCancel_Click()[/FONT]
[FONT=arial]    txtProgressNote.Value = ""[/FONT]
[FONT=arial]    frmProgressNote.Hide[/FONT]
[FONT=arial]End Sub[/FONT]
[FONT=arial]Private Sub btnProgressNote_Click()[/FONT]
[FONT=arial]    If txtProgressNote.Value = "" Then Exit Sub[/FONT]
[FONT=arial]    Call runAllMacros(txtProgressNote.Value)[/FONT]
[FONT=arial]    txtProgressNote.Value = ""[/FONT]
[FONT=arial]    frmProgressNote.Hide[/FONT]
[FONT=arial]End Sub[/FONT]

[FONT=arial]Sub runAllMacros(progressNote)
[FONT=arial]    primaryDx = findPrimaryDx(progressNote)
    'Other code continues...
End Sub

[FONT=arial]Function findPrimaryDx(progressNote)[/FONT]
[FONT=arial]    aryHxPresentIllness = getHxPresentIllness(progressNote)    
   aryAssessments = getAssessments(progressNote) 'Does the same thing as the line above but for a different section of the input data.[/FONT]
[FONT=arial]    aryAllProblems = getAllProblems(aryHxPresentIllness, aryAssessments) 'Concatinates both arrays into 1 array.[/FONT]
[FONT=arial]    'Other code continues...
End Function

[FONT=arial]Function getHxPresentIllness(progressNote)[/FONT]
[FONT=arial]    mySplit = Split(progressNote, vbCr)[/FONT]
[FONT=arial]    Dim aryHxPresentIllness() As Variant[/FONT]
[FONT=arial]    a = 0[/FONT]
[FONT=arial]    boolFoundHxPresentIllness = False[/FONT]

[FONT=arial]    s = 0[/FONT]
[FONT=arial]    lastS = UBound(mySplit)[/FONT]
[FONT=arial]    Do Until s > lastS[/FONT]
[FONT=arial]        test = Trim(mySplit(s))[/FONT]
[FONT=arial]        s = s + 1[/FONT]
[FONT=arial]    Loop[/FONT]

[FONT=arial]    For Each myLine In mySplit[/FONT]

[FONT=arial][COLOR=#ff0000][B]        'WORKING ON THIS CODE!!![/B][/COLOR][/FONT]
[FONT=arial][COLOR=#0000ff]        If LCase(myLine) Like "*" & "history of present illness:" Then[/COLOR][/FONT]
[FONT=arial][COLOR=#0000ff]            boolFoundHxPresentIllness = True[/COLOR][/FONT]
[FONT=arial][COLOR=#0000ff]        End If[/COLOR][/FONT]
[FONT=arial][COLOR=#0000ff]        If LCase(Trim(myLine)) = "history of present illness:" Then[/COLOR][/FONT]
[FONT=arial][COLOR=#0000ff]            boolFoundHxPresentIllness = True[/COLOR][/FONT]
[FONT=arial][COLOR=#0000ff]        End If[/COLOR][/FONT]

[FONT=arial]        If boolFoundHxPresentIllness = True Then[/FONT]
[FONT=arial]            If LCase(myLine) Like "*" & "major problems:" & "*" Then[/FONT]
[FONT=arial][COLOR=#0000ff]                getHxPresentIllness = aryHxPresentIllness()[/COLOR][/FONT]
[FONT=arial]                Exit Function[/FONT]
[FONT=arial]            End If[/FONT]
[FONT=arial]            If Trim(myLine) <> "" Then[/FONT]
[FONT=arial]                ReDim Preserve aryHxPresentIllness(a)[/FONT]
[FONT=arial]                aryHxPresentIllness(a) = myLine[/FONT]
[FONT=arial]                a = a + 1[/FONT]
[FONT=arial]            End If[/FONT]
[FONT=arial]        End If[/FONT]
[FONT=arial]    Next myLine[/FONT]

[FONT=arial]    'If macro goes this far, something is wrong.  Maybe the progress note is missing _[/FONT]
[FONT=arial]    History of Present Illness: or Major Problems:[/FONT]
[FONT=arial]    msgError = MsgBox("If macro goes this far, something is wrong.  Maybe the progress note is missing" _[/FONT]
[FONT=arial]    & vbNewLine & "History of Present Illness: or Major Problems:" & vbNewLine _[/FONT]
[FONT=arial]    & "Macro must terminate immediately!")[/FONT]
[FONT=arial]    End[/FONT]
[FONT=arial]End Function[/FONT]
Last edited:


Well-known Member
May 25, 2014
The answer was to change...
mySplit = Split(progressNote, vbCr)

To this...
mySplit = Split(progressNote, Chr(11))

Guess I just needed to get some sleep to figure it out. Thanks anyway.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...