Snasui Excel Forum

Code: Select all

Sub test0()
    Dim a(999, 2) As Variant
    Dim i As Integer, strHd As String
    Dim rngAll As Range, rng As Range
    With Worksheets("Input")
        Set rngAll = .Range("b16:b59")
        For Each rng In rngAll
            If rng.Offset(0, -1).Font.Bold Then
                strHd = rng.Offset(0, -1).Value
                i = i + 1
            End If
            If rng.Value <> "" Then
                a(i - 1, 0) = strHd
                a(i, 0) = rng.Offset(0, -1).Value
                a(i, 1) = rng.Value
                a(i, 2) = rng.Offset(0, 1).Value
                i = i + 1
            End If
        Next rng
        .Range("f15").Resize(i, 3) = a
    End With
End Sub

Code: Select all

Dim l As Integer
'Other code
l = 0
For Each AmountCell In WI.Range("InputHospitalCharges").Cells
    If AmountCell <> "" Then
        WF.Cells(HeadingRow, 1) = "Hospital Charges"
        WF.Cells(HeadingRow, 1).Font.Bold = True
        WF.Cells(HeadingRow, 1).Font.Underline = True
        WF.Cells(HeadingRow, 5) = ""
        CurrentRow = CurrentRow + 1
        WF.Cells(CurrentRow, 1) = AmountCell.Offset(0, -2)
        WF.Cells(CurrentRow, 1).Font.Underline = False
        WF.Cells(CurrentRow, 5) = AmountCell.Text
        If InStr(WF.Cells(CurrentRow, 1), "Package") Then
            l = l + 1
            WF.Cells(CurrentRow, 1).Resize(1, 5).Cut
            WF.Cells(HeadingRow + l, 1).Resize(1, 5).Insert
        End If
    End If
Next
'Other code