+ Reply to Thread
Results 1 to 13 of 13

Loop through column headers to search from column name and get cell range

  1. #1
    Pie
    Guest

    Loop through column headers to search from column name and get cell range

    Hello,

    I've been searching all day to try to find an answer to my question.
    Unfortuneately I haven't been able to find anything specific to my
    issu. This may be because I am not using the right keywords, so
    forgive me if this is a duplicate question of another posting.

    Anyway, I have an excel spreadsheet of data that is sent to my group
    each month. I am trying to automate the process of extracting the
    data/columns that we need to import into access. 2 of the columns
    contain fullnames. I need to split these columns into LastName and
    FirstName which I have already done. However, in my code it the column
    is chosen by the column range i.e (Q:Q). However, this is not always
    true every month, so I wanted to be able to pick the column by the
    column header instead. I am using the code below, but for some reason
    it isn't moving across the column. It is moving down the rows. Can
    someone please take a look at it and tell me what I am missing?

    Thanks

    SearchValue = "GOwner" ' Set search value"

    Set rng =
    Worksheets("Sheet2").Range("A1").SpecialCells(xlCellTypeLastCell)
    'lLastRow = rng.Row
    lLastCol = rng.End(xlToRight).Column

    Dim cVal As String
    For i = 1 To lLastCol
    MsgBox "Column = " & i
    If Cells(i, "A").Value = SearchValue Then
    MsgBox "Search value found at Column: " & i
    End If
    Next i


  2. #2
    Tom Ogilvy
    Guest

    Re: Loop through column headers to search from column name and get cell range

    Dim col as Long, res as Variant
    Dim SearchValue as String
    SearchValue = "GOwner"
    res = application.Match(SearchValue,rows(1),0)
    if iserror(res) then
    msgbox "GOwner not found"
    exit sub
    End if
    col = res

    but for your question:

    For i = 1 To lLastCol
    MsgBox "Column = " & i
    If Cells(1,i).Value = SearchValue Then
    MsgBox "Search value found at Column: " & i
    End If
    Next i


    --
    Regards,
    Tom Ogilvy



    "Pie" <dmum05@msn.com> wrote in message
    news:1135803342.888333.97230@g14g2000cwa.googlegroups.com...
    > Hello,
    >
    > I've been searching all day to try to find an answer to my question.
    > Unfortuneately I haven't been able to find anything specific to my
    > issu. This may be because I am not using the right keywords, so
    > forgive me if this is a duplicate question of another posting.
    >
    > Anyway, I have an excel spreadsheet of data that is sent to my group
    > each month. I am trying to automate the process of extracting the
    > data/columns that we need to import into access. 2 of the columns
    > contain fullnames. I need to split these columns into LastName and
    > FirstName which I have already done. However, in my code it the column
    > is chosen by the column range i.e (Q:Q). However, this is not always
    > true every month, so I wanted to be able to pick the column by the
    > column header instead. I am using the code below, but for some reason
    > it isn't moving across the column. It is moving down the rows. Can
    > someone please take a look at it and tell me what I am missing?
    >
    > Thanks
    >
    > SearchValue = "GOwner" ' Set search value"
    >
    > Set rng =
    > Worksheets("Sheet2").Range("A1").SpecialCells(xlCellTypeLastCell)
    > 'lLastRow = rng.Row
    > lLastCol = rng.End(xlToRight).Column
    >
    > Dim cVal As String
    > For i = 1 To lLastCol
    > MsgBox "Column = " & i
    > If Cells(i, "A").Value = SearchValue Then
    > MsgBox "Search value found at Column: " & i
    > End If
    > Next i
    >




  3. #3
    Blue Hornet
    Guest

    Re: Loop through column headers to search from column name and get cell range


    Pie wrote:
    > Hello,
    >
    > I've been searching all day to try to find an answer to my question.
    > Unfortuneately I haven't been able to find anything specific to my
    > issu. This may be because I am not using the right keywords, so
    > forgive me if this is a duplicate question of another posting.
    >
    > Anyway, I have an excel spreadsheet of data that is sent to my group
    > each month. I am trying to automate the process of extracting the
    > data/columns that we need to import into access. 2 of the columns
    > contain fullnames. I need to split these columns into LastName and
    > FirstName which I have already done. However, in my code it the column
    > is chosen by the column range i.e (Q:Q). However, this is not always
    > true every month, so I wanted to be able to pick the column by the
    > column header instead. I am using the code below, but for some reason
    > it isn't moving across the column. It is moving down the rows. Can
    > someone please take a look at it and tell me what I am missing?
    >
    > Thanks
    >
    > SearchValue = "GOwner" ' Set search value"
    >
    > Set rng =
    > Worksheets("Sheet2").Range("A1").SpecialCells(xlCellTypeLastCell)
    > 'lLastRow = rng.Row
    > lLastCol = rng.End(xlToRight).Column
    >
    > Dim cVal As String
    > For i = 1 To lLastCol
    > MsgBox "Column = " & i
    > If Cells(i, "A").Value = SearchValue Then
    > MsgBox "Search value found at Column: " & i
    > End If
    > Next i



    If you want to search for values in, say, Row 1, then the code you want
    in your For / Next loop is:
    Cells( 1, i).Value
    The syntax is Cells( Rowindex, Columnindex)

    Chris


  4. #4
    Pie
    Guest

    Re: Loop through column headers to search from column name and get cell range

    Thank you, Thank you. I knew it was something really small that I was
    doing wrong.


  5. #5
    Pie
    Guest

    Re: Loop through column headers to search from column name and get cell range

    Thanks Chris

    I see where I went wrong. errr!


  6. #6
    Pie
    Guest

    Re: Loop through column headers to search from column name and get cell range

    Thank you, Thank you, Tom I knew it was something really small that I
    was doing wrong.


  7. #7
    Pie
    Guest

    Re: Loop through column headers to search from column name and get cell range

    Thank you, Thank you. I knew it was something really small that I was
    doing wrong.


  8. #8
    Pie
    Guest

    Re: Loop through column headers to search from column name and get cell range

    Thanks Chris

    I see where I went wrong. errr!


  9. #9
    Pie
    Guest

    Re: Loop through column headers to search from column name and get cell range

    Thank you, Thank you, Tom I knew it was something really small that I
    was doing wrong.


  10. #10
    Pie
    Guest

    Re: Loop through column headers to search from column name and get cell range

    Just realized something
    The variable i gives me my column location, but it gives me the number
    of the column. I need the letter i.e Column(Q:Q).Select. How caan I
    transpose the number to the corresponding column letter or is there a
    better way to do this/

    Thanks


  11. #11
    Tom Ogilvy
    Guest

    Re: Loop through column headers to search from column name and get cell range

    I have been programming for years and have never need the letter. You can
    always refer to it with the column number.

    But if you want the letter and it will always be in column 26 or smaller

    colLtr = chr(i+64)

    if it could be into the double letters

    colltr = left(cells(1,i).Address(0,0), 2 + (i <= 26))

    demo'd from the immediate window:

    i = 26
    ? left(cells(1,i).Address(0,0), 2 + (i <= 26))
    Z
    i = 32
    ? left(cells(1,i).Address(0,0), 2 + (i <= 26))
    AF

    --
    Regards,
    Tom Ogilvy

    "Pie" <dmum05@msn.com> wrote in message
    news:1135806308.545515.81250@z14g2000cwz.googlegroups.com...
    > Just realized something
    > The variable i gives me my column location, but it gives me the number
    > of the column. I need the letter i.e Column(Q:Q).Select. How caan I
    > transpose the number to the corresponding column letter or is there a
    > better way to do this/
    >
    > Thanks
    >




  12. #12
    Pie
    Guest

    Re: Loop through column headers to search from column name and get cell range

    If I don't need the letter thats fine, but I keep getting an error with
    the number:

    For i = 1 To lLastCol
    If Cells(1, i).Value = SearchValue Then
    cVal = i
    End If
    Next i

    Columns(cVal).Select


  13. #13
    Tom Ogilvy
    Guest

    Re: Loop through column headers to search from column name and get cell range

    i = 10
    ? columns(i).Address
    $J:$J
    i = "J"
    ? columns(i).Address
    $J:$J
    ? columns("J").Address
    $J:$J
    ? columns(10).address
    $J:$J

    All work for me as you can see from this demo from the immediate window. .

    ? columns("10").Address

    is problematic

    --
    Regards,
    Tom Ogilvy

    "Pie" <dmum05@msn.com> wrote in message
    news:1135807485.176509.235690@g43g2000cwa.googlegroups.com...
    > If I don't need the letter thats fine, but I keep getting an error with
    > the number:
    >
    > For i = 1 To lLastCol
    > If Cells(1, i).Value = SearchValue Then
    > cVal = i
    > End If
    > Next i
    >
    > Columns(cVal).Select
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1