Pulling data from a row and insert into a column in a single column array format in VBA

[swajas]

Hello all,

I have my excel sheet data(i converted into array format) which looks like following

1st row......['one', , , , 'Folder', 'Folder', 'Extended Data', 'Extended Data', 'Extended Data','Extended Data' ],
2nd row.....['ID', 'Label', 'Longitude', 'Latitude', 'Country', 'City', 'Inventory', 'Safety stock', 'weight', 'hdsjka'],
3rd row......['AFKBL', 'Kabul, Afghanistan', 69.136749, 34.53091, 'Afghanistan', 'Kabul', 12, 1845, 12, 1845],
4th row......['AFKDH', 'Kandahar, Afghanistan', 65.700279, 31.61087, 'Afghanistan', 'Kandahar', 18, 1193, 18, 1193], ....etc etc

I want to pull all the values in the 2nd row that comes under 'Extended Data' ( which is in 1st row)
and write it into a single column array in a different file..

I want to use this column array for creating a control wrapper in google charts.

I would really appreciate if anybody could help me on this..

 

[swajas]

Can somebody please help me with a macro for the above task..
Thanks

 

[AlphaFrog]

The description vague and confusing.

I know what an array is, but I don't know what you mean by "Array Format", that can mean several things. Are these values in sheet in columns A:J?

Why list the rows 3 and 4 data when you want values from just row 2 based on row 1?

Are the headers always the same in row 1 (columns G:J)? If not, explain. Are "Extended Data" always consecutive?

Can you expand on this; "and write it into a single column array in a different file"?

 

[swajas]

Sorry for the vague question..
Let me explain clearly on this..
I am only interested in the first two rows..please ignore 3rd and 4th row data .
My spread sheet will be dynamic and users can select any number of folders and any number of extended data ( the extended data columns are always consecutive)
The header names will be the same in row 1 ,(In the above example there are 2 folders and 4 extended data columns)
So, I have to capture all the column labels under extended data ( in the above data i have to capture "inventory", "safety stock", "weight" ...)
For that i need to find , first cell that has "Extended Data" string and then print the value of cell in the next row...and repeat it until last column
I need to write this in an array format because i have to use this array to develop a dropdown in google charts.my required format would be
[[inventory],
[Safety stock],
[weight],...]

I hope i am a bit clearer than before.
Thanks for helping me out.. Please respond back if you have any questions

 

[swajas]

Can Anybody please help me writing a macro for the above task? Thanks in advance

 

[AlphaFrog]

Sorry for the vague question..
Let me explain clearly on this..
I am only interested in the first two rows..please ignore 3rd and 4th row data .
My spread sheet will be dynamic and users can select any number of folders and any number of extended data ( the extended data columns are always consecutive)
The header names will be the same in row 1 ,(In the above example there are 2 folders and 4 extended data columns)
So, I have to capture all the column labels under extended data ( in the above data i have to capture "inventory", "safety stock", "weight" ...)
For that i need to find , first cell that has "Extended Data" string and then print the value of cell in the next row...and repeat it until last column
I need to write this in an array format because i have to use this array to develop a dropdown in google charts.my required format would be
[[inventory],
[Safety stock],
[weight],...]

I hope i am a bit clearer than before.
Thanks for helping me out.. Please respond back if you have any questions

This will find the first and last Extended Data headers in Row 1 and copy the values below from Row 2 to column A on Sheet2. Change the destination to suit.

[color=darkblue]Sub[/color] Extended_Data()
    [color=darkblue]Dim[/color] rngED [color=darkblue]As[/color] Range
    
    [color=darkblue]Set[/color] rngED = Rows(1).Find("Extended Data", , , 1, 1, 1, 0)   [color=green]'1st Ext data[/color]
    [color=darkblue]Set[/color] rngED = Range(rngED, Rows(1).Find("Extended Data", , , 1, 1, 2, 0)) [color=green]'1st to last Ext data[/color]
    
    [color=green]'Output[/color]
    [B]Sheets(2).Range("A1")[/B].Resize(rngED.Columns.Count).Value = Application.Transpose(rngED.Offset(1))
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[swajas]

Thanks for your response!!
I am getting "Method 'Range' of object_Global failed
at Set rngED = Range(rngED, Rows(1).Find("Extended Data", , , 1, 1, 2, 0))
Please help
Thanks

 

[AlphaFrog]

Thanks for your response!!
I am getting "Method 'Range' of object_Global failed
at Set rngED = Range(rngED, Rows(1).Find("Extended Data", , , 1, 1, 2, 0))
Please help
Thanks

Try this...

[color=darkblue]Sub[/color] Extended_Data()
    [color=darkblue]Dim[/color] rngED [color=darkblue]As[/color] Range
    
    [color=darkblue]Set[/color] rngED = Rows(1).Find("Extended Data", , , 2, 1, 1, 0)   [color=green]'1st Ext data[/color]
    [color=darkblue]If[/color] [color=darkblue]Not[/color] rngED [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
    [color=darkblue]Set[/color] rngED = Range(rngED, Rows(1).Find("Extended Data", , , 2, 1, 2, 0)) [color=green]'1st to last Ext data[/color]
    
    [color=green]'Output[/color]
    Sheets(2).Range("A1").Resize(rngED.Columns.Count).Value = Application.Transpose(rngED.Offset(1))
    [color=darkblue]Else[/color]
        MsgBox "Cannot find any 'Extended Data' headers in row 1.", vbExclamation, "Copy Canceled"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[swajas]

Thanks for your response!!
No, I am getting "Method 'Range' of object_Global failed error at
Set rngED = Rows(1).Find("Extended Data", , , 2, 1, 1, 0)

Thanks for your effort

 

[AlphaFrog]

Thanks for your response!!
No, I am getting "Method 'Range' of object_Global failed error at
Set rngED = Rows(1).Find("Extended Data", , , 2, 1, 1, 0)

Thanks for your effort

I edited the code in #8, try it again.
Is the sheet with the Extended Data" headers the Active sheet?
Are the headers exactly like this "Extended Data"; spelling and no leading or trailing spaces?