Determine if columns exist

[strippy1701]

Hi,

i'm a bit new to using vba in excel and i ran into a small problem. I am using Excel 2003..

I have 10 product groups consisting of three columns (Product brand, Product model and Serial number). The column headers have fixed names and they will not change.

I am filtering data based on Product brand columns. The problem is that these columns are dynamic. There is maximum of 10 Product brands and at time there can be i.e. 5 product brands or 7 or 2.

So i need to create a filter that will first look if the column exists (i.e. Product brand 1) and then if yes check if there are values and if also yes copy data into table on Sorted sheet. If the column does not exist or there is no data then go to the next Product brand column and check if it exist and so on till Product brand 10 column.

I appreciate any help you can give me.

And thank you in advance

Here is the structure of the table on Data sheet:

<table border="0" cellpadding="0" cellspacing="0" width="512"><col style="width:48pt" span="8" width="64"> <tbody><tr style="height:52.5pt" height="70"> <td class="xl24" style="height:52.5pt;width:48pt" height="70" width="64">ID</td> <td class="xl25" style="border-left:none;width:48pt" width="64">Customer</td> <td class="xl25" style="border-left:none;width:48pt" width="64">City</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Product Brand 1</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Product model 1</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Serial number product 1</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Country</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Type of store</td> </tr> <tr style="height:13.5pt" height="18"> <td style="height:13.5pt" align="right" height="18">1</td> <td>store 1</td> <td>berlin</td> <td>hp</td> <td>notebook</td> <td align="right">5688</td> <td>germany</td> <td>mall</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2</td> <td>store 2</td> <td>bonn</td> <td>
</td> <td>
</td> <td>
</td> <td>germany</td> <td>discount</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3</td> <td>store 3</td> <td>wien</td> <td>
</td> <td>
</td> <td>
</td> <td>austria</td> <td>shop</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">4</td> <td>store 4</td> <td>london</td> <td>asus</td> <td>notebook</td> <td align="right">4697</td> <td>uk</td> <td>mall</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">5</td> <td>store 5</td> <td>barcelona</td> <td>
</td> <td>
</td> <td>
</td> <td>spain</td> <td>discount</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">6</td> <td>store 6</td> <td>roma</td> <td>
</td> <td>
</td> <td>
</td> <td>italy</td> <td>shop</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">7</td> <td>store 7</td> <td>roma</td> <td>
</td> <td>
</td> <td>
</td> <td>italy</td> <td>mall</td> </tr> </tbody></table>

and here is the structure of the table on Sorted data sheet:

<table border="0" cellpadding="0" cellspacing="0" width="512"><col style="width:48pt" span="8" width="64"> <tbody><tr style="height:39.75pt" height="53"> <td class="xl24" style="height:39.75pt;width:48pt" height="53" width="64">ID</td> <td class="xl25" style="border-left:none;width:48pt" width="64">Customer</td> <td class="xl25" style="border-left:none;width:48pt" width="64">City</td> <td class="xl27" style="border-left:none;width:48pt" width="64">Country</td> <td class="xl27" style="border-left:none;width:48pt" width="64">Type of store</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Product Brand</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Product model</td> <td class="xl26" style="border-left:none;width:48pt" width="64">Serial number product</td> </tr> <tr style="height:13.5pt" height="18"> <td style="height:13.5pt" align="right" height="18">1</td> <td>store 1</td> <td>berlin</td> <td>germany</td> <td>mall</td> <td>hp</td> <td>notebook</td> <td align="right">5688</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1</td> <td>store 1</td> <td>berlin</td> <td>germany</td> <td>mall</td> <td>dell</td> <td>desktop</td> <td align="right">23569</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1</td> <td>store 1</td> <td>berlin</td> <td>germany</td> <td>mall</td> <td>hp</td> <td>notebook</td> <td align="right">598777</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3</td> <td>store 3</td> <td>wien</td> <td>austria</td> <td>shop</td> <td>dell</td> <td>notebook</td> <td align="right">526332</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3</td> <td>store 3</td> <td>wien</td> <td>austria</td> <td>shop</td> <td>hp</td> <td>notebook</td> <td align="right">365544</td> </tr> </tbody></table>


Also i am attaching vba that i have assembled till now:

Public Sub test()

Worksheets("Sorted data").Range("A2:H1000").Select
Selection.Delete

Dim k As Integer
Dim red As Integer
Dim co1 As Integer
Dim co2 As Integer
Dim co3 As Integer
Dim co4 As Integer
Dim co5 As Integer
Dim co6 As Integer
Dim co7 As Integer
Dim co8 As Integer
Dim co9 As Integer
Dim co10 As Integer
Dim co99 As Integer
Dim co999 As Integer

'these are the last two columns
co99 = WorksheetFunction.Match("Country", Sheets("Data").Rows(1), 0)
co999 = WorksheetFunction.Match("Type of store", Sheets("Data").Rows(1), 0)


co1 = WorksheetFunction.Match("Product brand 1", Sheets("Data").Rows(1), 0)
co2 = WorksheetFunction.Match("Product brand 2", Sheets("Data").Rows(1), 0)
co3 = WorksheetFunction.Match("Product brand 3", Sheets("Data").Rows(1), 0)
'co4 = WorksheetFunction.Match("Product brand 4", Sheets("Data").Rows(1), 0)
co5 = WorksheetFunction.Match("Product brand 5", Sheets("Data").Rows(1), 0)
co6 = WorksheetFunction.Match("Product brand 6", Sheets("Data").Rows(1), 0)
'co7 = WorksheetFunction.Match("Product brand 7", Sheets("Data").Rows(1), 0)
'co8 = WorksheetFunction.Match("Product brand 8", Sheets("Data").Rows(1), 0)
'co9 = WorksheetFunction.Match("Product brand 9", Sheets("Data").Rows(1), 0)
co10 = WorksheetFunction.Match("Product brand 10", Sheets("Data").Rows(1), 0)


k = 2

For red = 2 To 100
If Worksheets("Data").Cells(red, co1).Value <> "" Then

Worksheets("Sorted data").Cells(k, 1).Value = Worksheets("Data").Cells(red, 1).Value
Worksheets("Sorted data").Cells(k, 2).Value = Worksheets("Data").Cells(red, 2).Value
Worksheets("Sorted data").Cells(k, 3).Value = Worksheets("Data").Cells(red, 3).Value
Worksheets("Sorted data").Cells(k, 6).Value = Worksheets("Data").Cells(red, co1).Value
Worksheets("Sorted data").Cells(k, 7).Value = Worksheets("Data").Cells(red, co1).Offset(0, 1).Value
Worksheets("Sorted data").Cells(k, 8).Value = Worksheets("Data").Cells(red, co1).Offset(0, 2).Value
Worksheets("Sorted data").Cells(k, 4).Value = Worksheets("Data").Cells(red, co99).Value
Worksheets("Sorted data").Cells(k, 5).Value = Worksheets("Data").Cells(red, co999).Value
k = k + 1
End If
Next red

For red2 = 2 To 100
If Worksheets("Data").Cells(red2, co2).Value <> "" Then

Worksheets("Sorted data").Cells(k, 1).Value = Worksheets("Data").Cells(red2, 1).Value
Worksheets("Sorted data").Cells(k, 2).Value = Worksheets("Data").Cells(red2, 2).Value
Worksheets("Sorted data").Cells(k, 3).Value = Worksheets("Data").Cells(red2, 3).Value
Worksheets("Sorted data").Cells(k, 6).Value = Worksheets("Data").Cells(red2, co2).Value
Worksheets("Sorted data").Cells(k, 7).Value = Worksheets("Data").Cells(red2, co2).Offset(0, 1).Value
Worksheets("Sorted data").Cells(k, 8).Value = Worksheets("Data").Cells(red2, co2).Offset(0, 2).Value
Worksheets("Sorted data").Cells(k, 4).Value = Worksheets("Data").Cells(red2, co99).Value
Worksheets("Sorted data").Cells(k, 5).Value = Worksheets("Data").Cells(red2, co999).Value
k = k + 1
End If
Next red2


For red3 = 2 To 100
If Worksheets("Data").Cells(red3, co3).Value <> "" Then

Worksheets("Sorted data").Cells(k, 1).Value = Worksheets("Data").Cells(red3, 1).Value
Worksheets("Sorted data").Cells(k, 2).Value = Worksheets("Data").Cells(red3, 2).Value
Worksheets("Sorted data").Cells(k, 3).Value = Worksheets("Data").Cells(red3, 3).Value
Worksheets("Sorted data").Cells(k, 6).Value = Worksheets("Data").Cells(red3, co3).Value
Worksheets("Sorted data").Cells(k, 7).Value = Worksheets("Data").Cells(red3, co3).Offset(0, 1).Value
Worksheets("Sorted data").Cells(k, 8).Value = Worksheets("Data").Cells(red3, co3).Offset(0, 2).Value
Worksheets("Sorted data").Cells(k, 4).Value = Worksheets("Data").Cells(red3, co99).Value
Worksheets("Sorted data").Cells(k, 5).Value = Worksheets("Data").Cells(red3, co999).Value
k = k + 1
End If
Next red3


For red5 = 2 To 100
If Worksheets("Data").Cells(red5, co5).Value <> "" Then

Worksheets("Sorted data").Cells(k, 1).Value = Worksheets("Data").Cells(red5, 1).Value
Worksheets("Sorted data").Cells(k, 2).Value = Worksheets("Data").Cells(red5, 2).Value
Worksheets("Sorted data").Cells(k, 3).Value = Worksheets("Data").Cells(red5, 3).Value
Worksheets("Sorted data").Cells(k, 6).Value = Worksheets("Data").Cells(red5, co5).Value
Worksheets("Sorted data").Cells(k, 7).Value = Worksheets("Data").Cells(red5, co5).Offset(0, 1).Value
Worksheets("Sorted data").Cells(k, 8).Value = Worksheets("Data").Cells(red5, co5).Offset(0, 2).Value
Worksheets("Sorted data").Cells(k, 4).Value = Worksheets("Data").Cells(red5, co99).Value
Worksheets("Sorted data").Cells(k, 5).Value = Worksheets("Data").Cells(red5, co999).Value
k = k + 1
End If
Next red5

For red6 = 2 To 100
If Worksheets("Data").Cells(red6, co6).Value <> "" Then

Worksheets("Sorted data").Cells(k, 1).Value = Worksheets("Data").Cells(red6, 1).Value
Worksheets("Sorted data").Cells(k, 2).Value = Worksheets("Data").Cells(red6, 2).Value
Worksheets("Sorted data").Cells(k, 3).Value = Worksheets("Data").Cells(red6, 3).Value
Worksheets("Sorted data").Cells(k, 6).Value = Worksheets("Data").Cells(red6, co6).Value
Worksheets("Sorted data").Cells(k, 7).Value = Worksheets("Data").Cells(red6, co6).Offset(0, 1).Value
Worksheets("Sorted data").Cells(k, 8).Value = Worksheets("Data").Cells(red6, co6).Offset(0, 2).Value
Worksheets("Sorted data").Cells(k, 4).Value = Worksheets("Data").Cells(red6, co99).Value
Worksheets("Sorted data").Cells(k, 5).Value = Worksheets("Data").Cells(red6, co999).Value
k = k + 1
End If
Next red6

For red10 = 2 To 100
If Worksheets("Data").Cells(red10, co10).Value <> "" Then

Worksheets("Sorted data").Cells(k, 1).Value = Worksheets("Data").Cells(red10, 1).Value
Worksheets("Sorted data").Cells(k, 2).Value = Worksheets("Data").Cells(red10, 2).Value
Worksheets("Sorted data").Cells(k, 3).Value = Worksheets("Data").Cells(red10, 3).Value
Worksheets("Sorted data").Cells(k, 6).Value = Worksheets("Data").Cells(red10, co10).Value
Worksheets("Sorted data").Cells(k, 7).Value = Worksheets("Data").Cells(red10, co10).Offset(0, 1).Value
Worksheets("Sorted data").Cells(k, 8).Value = Worksheets("Data").Cells(red10, co10).Offset(0, 2).Value
Worksheets("Sorted data").Cells(k, 4).Value = Worksheets("Data").Cells(red10, co99).Value
Worksheets("Sorted data").Cells(k, 5).Value = Worksheets("Data").Cells(red10, co999).Value
k = k + 1
End If
Next red10

Columns("A:A").Select
    Range("$A:$H").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub

 

[arthurbr]

Cross post
http://www.excelforum.com/excel-programming/772541-determine-if-columns-exist.html#post2509446