Return column number? (XL97/WinNT4)

[BerylM]

Could someone tell me how I can return the column number of a cell that contains a specific string of text, please?

I have a spreadsheet that contains certain headings, but when it is used any or all of the headings might be included (according to choices made before using it) so they can be in different columns each time. I need to be able to refer to cells under that heading, so I need to be able to determine which column the heading is in this time ... does that make sense?! I hope so!

Once I have the column number it will be assigned to a variable so I can use it in calculations.

Many thanks for any help, which is much appreciated!

[HansV]

Say that A1:H1 contain headings. One of them is "Beryl" but you don't know which one.

A formula to determine the column would be

=MATCH("Beryl",A1:H1,0)

The third argument 0 tells Excel to look for an exact match.

If you need this in VBA, you can use code like this:

Dim rngHeadings As Range
Dim rngFound As Range
Dim intCol As Integer

Set rngHeadings = Range("A1:H1")
Set rngFound = rngHeadings.Find("Beryl")
If Not rngFound Is Nothing Then
intCol = rngFound.Column
End If

Of course, you can use a variable instead of "Beryl".

[BerylM]

Thanks, Hans, that was just what I needed!

Can I ask another question? How do I get it to cycle through a range by rows? I've got the following code so far, and I can't get the format of the For Each ... line to work!
<pre>Option Explicit

Dim custType As Integer, postCode As Integer, addInst As Integer, addDesc As Integer
Dim sightInd As Integer, dataBlock As Range, c As Range, rngFound As Range

Sub dataBreakdown()

Set dataBlock = ActiveWorkbook.ActiveSheet.Range(Range("A1").Curre ntRegion.Rows, _
Range("A1").CurrentRegion.Columns)

Set rngFound = dataBlock.Find("sub_customer_type_desc")
If Not rngFound Is Nothing Then custType = rngFound.Column

Set rngFound = dataBlock.Find("postal_code")
If Not rngFound Is Nothing Then postCode = rngFound.Column

Set rngFound = dataBlock.Find("addr_spec_instruc")
If Not rngFound Is Nothing Then addInst = rngFound.Column

Set rngFound = dataBlock.Find("address_description")
If Not rngFound Is Nothing Then addDesc = rngFound.Column

Set rngFound = dataBlock.Find("sight_problem_indicator")
If Not rngFound Is Nothing Then sightInd = rngFound.Column

Set dataBlock = dataBlock.Offset(1, 0).Resize(dataBlock.Rows.Count - 1, _
dataBlock.Columns.Count)

For Each c In Range(dataBlock)
With c
MsgBox c.Range(1, 1).Value
End With
Next c

End Sub
</pre>

[HansV]

1. Why don't you just use

Set dataBlock = Range("A1").CurrentRegion

2. Since dataBlock and c are already Range objects, you can use

For Each c In dataBlock
MsgBox c.Value
Next c

[BerylM]

<img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

Why is it so simple when you do it, but I can never do that myself?

Mind you, there was a little method in my madness - having ascertained the column I need to refer to (in my first post) and got the array to work, what I actually need to do now is refer to each row within the range (as opposed to each cell) - hence the bit about Range(1,1), I was trying to find out what it would do and referring to each cell, as it is doing, is not going to work!

I want to do something like: for each row, check what the entry is in column 6, and move the entire row to one of several new spreadsheets, according to column 6's value, obviously appending each row to the existing collection in the new sheet each time.

Is it possible to get the array to only run on the first column of the range, maybe? That would have the effect of checking each row, wouldn't it?

Many thanks!

[HansV]

You could use

For Each c In dataBlock.Columns(6)

This will loop through the cells in the sixth column of dataBlock, so visiting each row once. Another way would be

Dim i As Long
For i = 1 To dataBlock.Rows.Count
MsgBox dataBlock.Cells(i, 6).Value
Next i

[BerylM]

Hans, I'm getting there slowly (and if there's anything you can see to reduce the number of lines in this code, please do let me know!) but I've hit another snag.

This code worked perfectly while I ran dataBreakdown and put the filter in the end of that sub, but when I took it to the next stage and created a filter, running databreakdown from there (see filterPersonal - there are going to be a number of filters eventually) I found that although the variables were assigned perfectly, as soon as the code left databreakdown it lost them again! The code is in a module (not this workbook) in my personal.xls - what am I doing wrong this time, please?!

I'm afraid the database is our customer information so I can't put any data here - sorry!
<pre>Option Explicit
Public custType As Integer, postCode As Integer, addInst As Integer
Public addDesc As Integer, sightInd As Integer, fullName As Integer
Public dataBlock As Range, c As Range, rngFound As Range, custNo As Integer
Public d As Long

Sub dataBreakdown()

'Set the range for the data, including the headings
Set dataBlock = Range("A1").CurrentRegion

'Find which column the required sort/filter fields are in
Set rngFound = dataBlock.Find("full_name")
If Not rngFound Is Nothing Then fullName = rngFound.Column

Set rngFound = dataBlock.Find("Customer_number")
If Not rngFound Is Nothing Then custNo = rngFound.Column

Set rngFound = dataBlock.Find("sub_customer_type_desc")
If Not rngFound Is Nothing Then custType = rngFound.Column

Set rngFound = dataBlock.Find("postal_code")
If Not rngFound Is Nothing Then postCode = rngFound.Column

Set rngFound = dataBlock.Find("addr_spec_instruc")
If Not rngFound Is Nothing Then addInst = rngFound.Column

Set rngFound = dataBlock.Find("address_description")
If Not rngFound Is Nothing Then addDesc = rngFound.Column

Set rngFound = dataBlock.Find("sight_problem_indicator")
If Not rngFound Is Nothing Then sightInd = rngFound.Column

'Set the range for the data, without the headings
Set dataBlock = dataBlock.Offset(1, 0).Resize(dataBlock.Rows.Count - 1, _
dataBlock.Columns.Count)

End Sub

Sub sortDataByFullname()
dataBlock.Sort Key1:=Range(dataBlock.Cells(1, fullName).Address), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Sub sortDataByCustno()
dataBlock.Sort Key1:=Range(dataBlock.Cells(1, custNo).Address), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Sub filterPersonal()
dataBreakdown
ActiveSheet.Cells(1, 1).EntireRow.Select
Selection.Copy
Sheets.Add
ActiveSheet.Name = "Personal"
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select

d = 1

For d = 1 To dataBlock.Rows.Count
If Left(dataBlock.Cells(d, custType).Value, 8) = "PERSONAL" Then
dataBlock.Cells(d, 1).EntireRow.Select
Selection.Cut
Sheets("Personal").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets("sheet1").Select
End If
Next d

sortDataByFullname

End Sub
</pre>

[HansV]

Could you make a small demo with a few rows of dummy data?

[BerylM]

Does this help? The headings are absolutely standard (produced by the database source).

[HansV]

When the For d = 1 To dataBlock.Rows.Count loop starts in filterPersonal, the new sheet Personal is the active sheet. The range dataBlock cannot be selected because it is on Sheet1. Insert a line

Sheets("Sheet1").Select

immediately above For d = 1 To ....

[BerylM]

Thanks (although it worked before - I'll check it when I get that far again!), but the problem I'm having at the moment is that when I run filterPersonal, as soon as it finishes running dataBreakdown and goes back to filterPersonal, it drops all the variables and therefore refuses to continue, claiming it doesn't know what datablock is, or custtype, or ...!

Does the fact that you got as far as the For loop mean that it *didn't* drop the variables when you did it?

[HansV]

I got the error about dataBlock too. I tried to explain why dataBlock was not available in the code as posted by you. Adding the line to activatate Sheet1 remedied this, and the code completed without errors. Whether it actually does what you want, I don't know <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[BerylM]

Ah - I see what you're getting at now! Unfortunately it doesn't fix it on mine ...

To demonstrate what I mean, add one or more of the variables (fullName, custNo, whatever) to the Watches window and put a breakpoint on the last line of code in databreakdown ('Set datablock ...'), and run filterPersonal. When it stops, the variables will have values in the Watches window. Now F8 over the end of dataBreakdown - and you'll see the variables are dropped the moment it leaves dataBreakdown!

Assuming it does the same as it does on mine, of course!

(What it should end up with is two spreadsheets, with the one named Personal containing all the lines where the sub_customer_type is "PERSONAL" and everything else left in the original sheet!)

[HansV]

After the addition I proposed, the code runs fine, the module-level variables keep their values and there are two sheets. I have attached the result (after running filterPersonal)

So perhaps there is a problem with your data that does not occur with the dummy data in testingsample.xls. Unfortunately, you'll have to check that yourself.

[BerylM]

Now I'm really confused, because you're right - it's running fine now, and I'd swear I didn't change anything!!

<img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>

I want to go home ...