Macro to retrieve data and to return in tabular form

[Madzark]

Hello guys,

I would like to run a macro that allows me to group some data that is scattered into tabular form. In particular, the information to be retrieved is "anticipated" by a number label contained in the cell immediately to the left of the relevant data to be retrieved. In addition, these labels are contained in definied columns.

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3.000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4.000[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]10.000[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1.000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2.000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3.000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4.000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10.000[/TD]
[/TR]
</tbody>[/TABLE]

In this case, the macro should search all the value reported between the labels from 1 to 5 and return these values in a different sheet with the relevant labels.

This would really help me a lot because I have more than 500 labels (and as much information) that I should convert into the table format.

Many thanks!

 

[DanteAmor]

You are right. There is a jump from 107 to 111. I tried to make a new retrieve_data() macro starting with n=111, but it doesn't work. How can I solve this?

Two options:

- Can you at least say in which initial column the data will start?That is, in your example the initial column is E, it will always start in E. With that I could change the way to search, then the macro would search by columns, if 3 does not exist, it does not matter, the macro would continue with the next column.

- Or put a finite number, which starts at 1 and ends at 1000. That way if there is no number that continues until it reaches 1000.


Try second option:
Change 1000 for the maximum number to reach

Sub retrieve_data()
  Dim sh2 As Worksheet, i As Long, f As Range
  Application.ScreenUpdating = False
  Set sh2 = Sheets("Sheet2")
  sh2.Cells.ClearContents
  For i = 1 To [COLOR=#0000ff][B]1000[/B][/COLOR]
    Set f = Sheets("Sheet1").Cells.Find(i, , xlValues, xlWhole)
    If Not f Is Nothing Then
      If f.Offset(, 1).Value <> "" Then
        sh2.Cells(i, "A").Value = i
        sh2.Cells(i, "B").Value = f.Offset(, 1)
      End If
    End If
  Next
End Sub

 

[Madzark]

Wonderful, Option 2 just works fine!!

Thank you very much, have a nice day

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.