Vba data stacking

Closed
fvba Posts 8 Registration date Sunday July 19, 2009 Status Member Last seen September 3, 2009 - Aug 31, 2009 at 09:31 AM
Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 - Sep 3, 2009 at 03:16 AM
Hello,

Sub stackData(ws0 As Worksheet, ws1 As Worksheet, ws2 As Worksheet)
'Declarations :: Indexes for max number of used rows/columns
Dim locationOneRowMaxNum As Integer, locationTwoRowMaxNum As Integer
Dim locationOneColMaxNum As Integer, locationTwoColMaxNum As Integer
Dim importRowMaxNum As Integer, importColMaxNum As Integer
'Declarations :: Indexes for looping
Dim row As Integer, col As Integer, importRow As Integer, importCol As Integer, indexlbl As Integer
Dim temp As Integer

'Location 1 Sheet :: getting max number of used rows/columns
locationOneRowMaxNum = getWSUsedRowNum(ws1)
locationOneColMaxNum = getWSUsedColNum(ws1)
'Location 2 Sheet :: getting max number of used rows/columns
locationTwoRowMaxNum = getWSUsedRowNum(ws2)
locationTwoColMaxNum = getWSUsedColNum(ws2)
'Import Sheet :: getting max number of used columns
importColMaxNum = getWSUsedColNum(ws0)

'Data Stacking :: Creating label for Location 1
ws0.Range(ws0.Cells(2, 1), ws0.Cells(2, getHeaderNum(ws0, "x", 1))).Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
ActiveCell.FormulaR1C1 = "Location 1"
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2

'Data Stacking :: Location 1
For importCol = 1 To importColMaxNum 'row 1 onwards to find corresponding header

For col = 1 To locationOneColMaxNum
If ws0.Cells(1, importCol) = ws1.Cells(1, col) Then
For row = 3 To locationOneRowMaxNum + 1 'if found same header, copy data over CHANGE
ws0.Cells(row, importCol) = ws1.Cells(row - 1, col) 'CHANGE
Next
End If
Next
Next

I have this whole chunk of code but at this part
>> ws0.Range(ws0.Cells(2, 1), ws0.Cells(2, getHeaderNum(ws0, "x", 1))).Select

when i run it shows the error I dont know why is this happening, can anybody please help me?
Thank you in advance, much appreciated

1 reply

Excelguru Posts 261 Registration date Saturday April 11, 2009 Status Member Last seen June 21, 2011 307
Sep 3, 2009 at 03:16 AM
please put the procedure code of getHeaderNum in your reply to check
0