VRodDom
06-29-2017, 05:46 PM
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!
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!