reformatting data extract

[ajm]

folks,

i need to reformat a report extracted from our ERP. the report gives me accommodation revenue by fee type for each particular room type. if printed from the ERP, the report is formatted to print each accommodation class on new page with a column for each fee type. The number of bed days and accommodation classification are above the revenue table. Below the table is another table for Previous Months Adjustments.

1	Classification : ABC PATIENT FUND TOTALS
2	------- ---- ------
3
4	Current Month : Accom. Profess. Other Accom. Profess. Other Totals
5	Fees Fees
6
7	Bed Days : 456
8
9	Amount Raised 0.00 0.00 0.00 0.00 0.00 0.00 0.00
10	Pre-Admission Deposits 0.00 0.00 0.00 0.00 0.00 0.00 0.00
11	Receipts 0.00 0.00 0.00 0.00 0.00 0.00 0.00
12	Pre-Admission Refunds 0.00 0.00 0.00 0.00 0.00 0.00 0.00
13	Refunds 0.00 0.00 0.00 0.00 0.00 0.00 0.00
14	JNL:MONTH/S PRIOR ADJUSTMENT 0.00 0.00 0.00 0.00 0.00 0.00 0.00
15	---------- ---------- ---------- ---------- ---------- ---------- -----------
16	0.00 0.00 0.00 0.00 0.00 0.00 0.00
17	==================================================================================================================================
18
19	Previous Months Adjustments: Accom. Profess. Other Accom. Profess. Other Totals
20	Fees Fees
21
22	Bed Days : 108
23
24	Amount Raised 0.00 0.00 0.00 0.00 0.00 0.00 0.00
25	Pre-Admission Deposits 0.00 0.00 0.00 0.00 0.00 0.00 0.00
26	Receipts 0.00 0.00 0.00 0.00 0.00 0.00 0.00
27	Pre-Admission Refunds 0.00 0.00 0.00 0.00 0.00 0.00 0.00
28	Refunds 0.00 0.00 0.00 0.00 0.00 0.00 0.00
29	---------- ---------- ---------- ---------- ---------- ---------- -----------
30	0.00 0.00 0.00 0.00 0.00 0.00 0.00
31	==================================================================================================================================

<tbody>
</tbody>

I have included row numbering to indicate the standard number of rows between each data row.

What I want to do is to separate the fee amounts into columns and to use the Classification, month and number of bed days as columns themselves. Below is the end result i want to create.

Classification	Month	Bed Days	Current Month :	Accom.	Profess.	Other	Accom.	Profess.	Other	Totals
			<tbody> </tbody>		Fees			Fees
ABC	Current Month :	456	Amount Raised	0	0	0	0	0	0	0
ABC	Current Month :	456	Pre-Admission Deposits	0	0	0	0	0	0	0
ABC	Current Month :	456	Receipts	0	0	0	0	0	0	0
ABC	Current Month :	456	Pre-Admission Refunds	0	0	0	0	0	0	0
ABC	Current Month :	456	Refunds	0	0	0	0	0	0	0
ABC	Previous Months Adjustments:		Amount Raised	0	0	0	0	0	0	0
ABC	Previous Months Adjustments:		Pre-Admission Deposits	0	0	0	0	0	0	0
ABC	Previous Months Adjustments:		Receipts	0	0	0	0	0	0	0
ABC	Previous Months Adjustments:		Pre-Admission Refunds	0	0	0	0	0	0	0
ABC	Previous Months Adjustments:		Refunds	0	0	0	0	0	0	0
			=================================	==============	==============	==============	==============	==============	==============	=============

<tbody>
</tbody>

The text to columns part is easy:

Dim sFileName As String    'Show the open dialog and pass the selected file name to the String variable "sFileName"
    sFileName = Application.GetOpenFilename
    'They have cancelled.
    If sFileName = "False" Then Exit Sub
    Workbooks.OpenText Filename:=sFileName, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(33, 1), Array(47, 1), Array(61, 1), Array(64, 1), Array(75, 1), Array(89 _
        , 1), Array(103, 1), Array(117, 1)), TrailingMinusNumbers:=True

its the moving of Classification, Month and Bed days that is vexing me.

<!-- Table easily created from Excel with ASAP Utilities (https://www.asap-utilities.com) -->

 

[ajm]

no takers?

a bit more information. its a long report and i was mistaken about the number of rows per classification being static. In both the Current Month and Previous Months Adjustment sections there can be extra rows inserted which contain other financial information. The report header, which i didn't include in the extract above, signifies the start of each new page in the report. each page contains only one class of accommodation. the current month details are easy enough to pick up as they are always the same number of rows below the header. the Prev Months then is the harder part.

so, approach today is to see if i can write a loop that looks for each header then offsets down two rows to pick up the accomm class, down another three rows for the Current Month title, then down another three rows for the bed days, and finally another two rows for the amounts. then, do a find to get "Previous Months Adjustments" location and repeat the offsets.

its going to be ugly if i can get it to work. but, i've been called worse things

 

[ajm]

this so far:

Sub test()

Dim CurrMth As String, Class As String, Beds As String, CMAmtRsd As String, PMBedDays As String, PMAmtRsd As String


Dim Rng As Range
Dim c As Range, d As Range
Dim pasteCell As Range
Dim DestSht As Worksheet


'Dim sFileName As String    'Show the open dialog and pass the selected file name to the String variable "sFileName"
    'sFileName = Application.GetOpenFilename
    'They have cancelled.
    'If sFileName = "False" Then Exit Sub
   ' Workbooks.OpenText Filename:=sFileName, Origin:=xlMSDOS, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array( _
        Array(0, 1), Array(33, 1), Array(47, 1), Array(61, 1), Array(64, 1), Array(75, 1), Array(89 _
        , 1), Array(103, 1), Array(117, 1)), TrailingMinusNumbers:=True
 Set DestSht = Worksheets("AmtsRaised")
        
  CurrMth = Trim(Right(ActiveSheet.Range("c2"), 6))
  
  Set Rng = ActiveSheet.Range("a1", Range("a1048576").End(xlUp))
With Rng
    Set c = .Find("DEBT2.P33", LookIn:=xlValues)
    Set d = .Find("Previous Months Adjustments:", LookIn:=xlValues)
            
    If Not c Is Nothing Then
        firstAddress = c.Address
        
        Do
        Class = Trim(Right(c.Offset(2, 0).Value, Len(c.Offset(2, 0).Value) - InStr(c.Offset(2, 0).Value, ":")))
        Beds = Trim(Right(c.Offset(8, 0).Value, Len(c.Offset(8, 0).Value) - InStr(c.Offset(8, 0).Value, ":")))
        CMAmtRsd = Trim(c.Offset(10, 7).Value)
        PMBedDays = Trim(Right(d.Offset(3, 0).Value, Len(d.Offset(3, 0).Value) - InStr(d.Offset(3, 0).Value, ":")))
        PMAmtRsd = Trim(d.Offset(5, 7).Value)
             
      '///Set the variable to the first empty cell in Row 2
     
     With DestSht
     Set pasteCell = .Range("b1048576").End(xlUp).Offset(1, 0)

     'Copy and paste
     pasteCell.Value = CurrMth
     pasteCell.Offset(0, 1).Value = Beds
     pasteCell.Offset(0, 2).Value = Class
     pasteCell.Offset(0, 3).Value = CMAmtRsd
     pasteCell.Offset(1, 0).Value = CurrMth
     pasteCell.Offset(1, 1).Value = PMBedDays
     pasteCell.Offset(1, 2).Value = Class
     pasteCell.Offset(1, 3).Value = PMAmtRsd
     
     End With
     '///Sheets("Sheet1").Range("A2:B10").Copy Destination:=pasteCell
        
            Set c = .FindNext(c)
            Set d = .FindNext(d)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With


End Sub

I have changed the final result to pulling just the amounts raised per accommodation class and posting to the next page:

	Month	Bed Days	Classification	Amount
Current Month	43664	63	ABC	0
Previous Months Adjustments:	43664		ABC	0

<tbody>
</tbody>

However, when i am having trouble with the find inside a find, i think. the first run through gives perfect results but when resetting c and d, they both become the same. what have i done wrong?

I am going to post a new question focusing on this double use of FindNext so it doesn't disappear in this thread.