Dealing with Unruly Columns

oric

New Member
Joined
Jul 19, 2005
Messages
36
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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Artemus

Board Regular
Joined
Dec 30, 2004
Messages
92
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

New Member
Joined
Jul 19, 2005
Messages
36
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

Board Regular
Joined
Aug 17, 2005
Messages
151
Use the Cell.Find function to get a range:
Code:
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

New Member
Joined
Jul 19, 2005
Messages
36

ADVERTISEMENT

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

Board Regular
Joined
Aug 17, 2005
Messages
151
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
Code:
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

New Member
Joined
Jul 19, 2005
Messages
36
For some reason I'm still getting that error.

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

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

Watch MrExcel Video

Forum statistics

Threads
1,118,057
Messages
5,569,954
Members
412,300
Latest member
Chaneycr
Top