Inserting new rows base on cell value, copying cell value on the first 3 column and transpose data for the rest

[llbac]

Hi everyone,
I have a similar question with other posts but different a little bit.
I have no experience in VBA and English is not my native language, so please accept my apologize and tell me if my question is unclear.
I would like to write the VBA code that can convert Table 1 to Table 2, which are:
1/ Table 1 locate on the Sheet1, start from A1 cell.
The first rows is column's titles
There are at least 4 columns. The first 3 columns are always text. From the 4th column onwards are always numeric (integer) value.
Data on the first 4 columns are always filled. On the next column onwards, maybe have one or more numeric values.

Fomular-test.xlsx
	A	B	C	D	E	F	G	H	I
1	Title A	Title B	Title C	Title D	Title E	Title F	Title G	Title H	Title I
2	Context A1	Context B1	Context C1	Number D1	Number E1	Number F1
3	Context A2	Context B2	Context C2	Number D2	Number E2	Number F2	Number G2	Number H2	Number I2
4	Context A3	Context B3	Context C3	Number D3
5	Context A4	Context B4	Context C4	Number D4	Number E4	Number F4
6	Context A5	Context B5	Context C5	Number D5	Number E5
Sheet1

2/ Table 2 locate on the new sheet (for example, named Sum).
In short, table 2 will transpose data on the 5th column onwards (if available) to the 4th column, while the first 3 columns' cell values will be copied from their corresponding cells above.

In my real data, value of column E (the 5th) will appear randomly, however, always in order (i.e: D-E, D-E-F, or D-E-F-G-H-H-J etc.).

Fomular-test.xlsx
	A	B	C	D	E	F	G	H	I
1	Title A	Title B	Title C	Title D	Title E	Title F	Title G	Title H	Title I
2	Context A1	Context B1	Context C1	Number D1
3	Context A1	Context B1	Context C1	Number E1
4	Context A1	Context B1	Context C1	Number F1
5	Context A2	Context B2	Context C2	Number D2
6	Context A2	Context B2	Context C2	Number E2
7	Context A2	Context B2	Context C2	Number F2
8	Context A2	Context B2	Context C2	Number G2
9	Context A2	Context B2	Context C2	Number H2
10	Context A2	Context B2	Context C2	Number I2
11	Context A3	Context B3	Context C3	Number D3
12	Context A4	Context B4	Context C4	Number D4
13	Context A4	Context B4	Context C4	Number E4
14	Context A4	Context B4	Context C4	Number F4
15	Context A5	Context B5	Context C5	Number D5
16	Context A5	Context B5	Context C5	Number E5
Sum2

Thank you very much!

 

[Legacy 143009]

This could work:

If Not IsEmpty(myRange.Cells(r, c)) Then

or

If Not IsEmpty(myRange.Cells(r, c).Value2) Then

 

[Legacy 143009]

You must also say:
Set myRange = ActiveSheet.UsedRange

 

[llbac]

You must also say:
Set myRange = ActiveSheet.UsedRange

I tried both lines:

If Not IsEmpty(myRange.Cells(r, c)) Then

and

If Not IsEmpty(myRange.Cells(r, c).Value2) Then

But it always shows error '13': Type mismatch at this line:

Set myRange = ActiveSheet.UsedRange

Sub Test()
  Dim myRange As Worksheet, i As Long
  Set myRange = ActiveSheet.UsedRange
  i = 2
  With myRange
    Worksheets("Sum").Cells(1, 1).Resize(, .Columns.Count).Value2 = .Cells(1, 1).Resize(, .Columns.Count).Value2
    For r = 2 To .Rows.Count
      For c = 4 To .Columns.Count
        If Not IsEmpty(myRange.Cells(r, c).Value2) Then
          Worksheets("Sum").Cells(i, 1).Resize(, 3).Value2 = .Cells(r, 1).Resize(, 3).Value2
          Worksheets("Sum").Cells(i, 4).Value2 = .Cells(r, c).Value2
          i = i + 1
        End If
      Next
    Next
  End With
End Sub

 

[Legacy 143009]

Please fix this line:

Dim myRange As Range, i As Long

 

[llbac]

Please fix this line:

Dim myRange As Range, i As Long

Oh, I got it. Now it works as expected.
Thank you so much for your kind help!

 

[Legacy 143009]

No problem! Thanks for the feedback