Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Return column number? (XL97/WinNT4)

    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!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return column number? (XL97/WinNT4)

    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".

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Return column number? (XL97/WinNT4)

    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>

    Beryl M


  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return column number? (XL97/WinNT4)

    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

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Return column number? (XL97/WinNT4)

    <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!
    Beryl M


  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return column number? (XL97/WinNT4)

    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

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Return column number? (XL97/WinNT4)

    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>

    Beryl M


  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return column number? (XL97/WinNT4)

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

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Return column number? (XL97/WinNT4)

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


  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return column number? (XL97/WinNT4)

    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 ....

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Return column number? (XL97/WinNT4)

    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?
    Beryl M


  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return column number? (XL97/WinNT4)

    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>

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Return column number? (XL97/WinNT4)

    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!)
    Beryl M


  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Return column number? (XL97/WinNT4)

    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.

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Return column number? (XL97/WinNT4)

    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 ...
    Beryl M


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •