Dealing with Unruly Columns

[oric]

Hi All,

I've been working on this problem for a while and I thought I better go to the experts.

I have several columns of data that have a description of the data on top.

Such as:

Time Dat1 Dat2 Dat3, etc..
## ### ### ###
## ### ### ###


These descriptions are all on the same row. Sometimes there in row 1 sometimes in row 2. One of my problems is that the data is not all in one order. Therefore Dat1 is sometimes in Col1 and Time in Col2 or some other weird combination. I have been trying to write some code that will recognize what the column is by looking at the description. Once it is recognized then it will do some arithmetic on the data below that column until it reaches the last data point in that column. Sometimes there is a blank cell between the description and the first data point. I have about 500-1000 lines per file and I have several hundred files per folder. Hence my reason for trying to automate this.

Any suggestions???

much appreciated.

 

[Artemus]

What do you mean by "it will do some arithmetic on the data below that column?" Will you be changing the original values or placing the results of calculations somewhere else?

If the descriptions are unique, you can find them by doing an ordinary search. Once you've found the description, you can find the last row with data and then run your routine on all cells in between.

 

[oric]

I will be using the values in that column and placing the results of the calculation in a different sheet. I got that part of the VBA code worked out already but my problem is with assigning the range after I have found the appropriate column by description.

Are you talking about the Search command in Excel or in VBA? I haven't found a Search method in VBA. I think there is a "Seek" one but I don't know what that does.

thx

 

[tmcfadden]

Use the Cell.Find function to get a range:

Dim rng As Range
Set rng = Sheet1.Cells.Find("Dat1")

Debug.Print "Address of 'Dat1' = " & rng.Address
Debug.Print "Column of 'Dat1' = " & rng.Column

That will set 'rng' to the cell for "Dat1".

You can then operate on 'rng'.

Hope that helps,

-Tim

 

[oric]

Thanks.

I was just searching around and I found out about the Find method. I think that is what will work. If I can find the range where the description is then I can just do a loop and search down that specific column to do my math until I reach an empty cell. Unfortunatley I don't really know how to properly code this method. When I tried using it it gave me some error about "With Block variable not set". Could someone explain a little more on how to use the Find method?

Also.. Can "Find" be used with wild characters as well??

 

[tmcfadden]

I always get the "With Block variable not set" when I forget to use "Set" before assigning a range variable (as in the [Set rng = Sheet1.Cells.Find("Dat1")] line).

As for wildcards, I've not used them but the documentation for the Find command says

To find cells that match more complicated patterns, use a For Each...Next statement with the Like operator. For example, the following code searches for all cells in the range A1:C5 that use a font whose name starts with the letters "Cour". When Microsoft Excel finds a match, it changes the font to Times New Roman.

For Each c In [A1:C5]
    If c.Font.Name Like "Cour*" Then
        c.Font.Name = "Times New Roman"
    End If
Next

That's not exactly what you're looking for though.

-Tim

 

[oric]

For some reason I'm still getting that error.

I'm trying this code to see what the output of rng is.

Sub test23()
 Dim rng As Range
Set rng = Sheet1.Cells.Find("Dat1")
MsgBox rng
End Sub