Rearrange data in a table according to column headers using .Match in Excel VBA

[VRodDom]

Hi - my first post on this forum - just starting on VBA and finding it quite exciting and very useful, but, I´m stuck with this.

I´m trying to take the data on some rows, and copy it on the same row but on the column where the header matches the value on the specific cells. In particular, I want to copy the data from cells G2:K4 (source) into the table on A1:E4 (target), in the column where de source data would match the headers on the target table:

19633

I have written the following code, but it fails when I try to use the result of Application.Match as the column number in the value setting sentence:

Sub Organiza_Info()

Dim VAR_Column_Number As Variant
Dim RNG_Datos_Job As Range

Set RNG_Datos_Job = Sheets("Example1").Range("AB2:AE4")

For Each CellBox In RNG_Datos_Job.Cells
VAR_Column_Number = Application.Match(CellBox.Value(), "a1:z1", 0)
Worksheets("example1").Cells(CellBox.Row, VAR_Column_Number).Value = CellBox.Value
Next CellBox


End Sub



the error I get is Run-time error '13': Type mismatch.

I have tried to move the value on VAR_Colum_Number to an integer, a double, etc., without success.

I´m pretty sure I´m doing something dumb somewhere, misunderstanding the variant type, or maybe the reference to the cell I want to update is not correct, but, as said, I´m just starting on VB, and I haven't found much specific info on this surfing the web.

I also attach the example .xlsm file with the VBA code - Any help would be really and greatly thanked!

[mdmackillop]

You missed Range from the code

VAR_Column_Number = Application.Match(CellBox.Value(), Range("a1:z1"), 0)
Also, you need to cater for blank cells in your data
Either

For Each CellBox In RNG_Datos_Job.Cells
If CellBox <> "" Then



or

For Each CellBox In RNG_Datos_Job.SpecialCells(xlCellTypeConstants)

[p45cal]

Sub Organiza_Info()
Dim VAR_Column_Number As Variant
Dim RNG_Datos_Job As Range

Set RNG_Datos_Job = Sheets("Example1").Range("G2:K4")
For Each CellBox In RNG_Datos_Job.Cells
VAR_Column_Number = Application.Match(CellBox.Value(), Range("a1:z1"), 0)
If Not IsError(VAR_Column_Number) Then Worksheets("example1").Cells(CellBox.Row, VAR_Column_Number).Value = CellBox.Value
Next CellBox
End Sub
Same code with highlighted changes:
Sub Organiza_Info()
Dim VAR_Column_Number As Variant
Dim RNG_Datos_Job As Range

Set RNG_Datos_Job = Sheets("Example1").Range("G2:K4")
For Each CellBox In RNG_Datos_Job.Cells
VAR_Column_Number = Application.Match(CellBox.Value(), Range("a1:z1"), 0)
If Not IsError(VAR_Column_Number) Then Worksheets("example1").Cells(CellBox.Row, VAR_Column_Number).Value = CellBox.Value
Next CellBox
End Sub

[VRodDom]

Great thanks! It works perfectly!

[VRodDom]

Thanks a lot! So silly mistake! And good catch the blank cells stuff!