Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Return Column Number to use in a Range VBA

    I am trying to get this to give me the column letter so that I can use it to hardcode based on a lookup. E5 contains the date I am looking for while columns f:AB is where I am looking for the date. Any help would be much appreciated. Thanks.

    Code:
    Sub Hardcode_days_values()
    
    Dim colNum As Long
    Cell As Range
    
    
    colNum = WorksheetFunction.Match(Range("E5"), ActiveWorkbook.Sheets(Comp1).Range("6:40"), 0)
    For Each Cell In range(Colnum & "9":Colnum & "27")
    
    Next Cell
    For Each Cell In range(Colnum & "39":Colnum & "57")
    Next Cell
    For Each Cell In range(Colnum & "65":Colnum & "66")
    Next Cell
    End Sub

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,379
    Thanks
    207
    Thanked 829 Times in 762 Posts
    Balla,

    Try using the Cells() function in your Range().

    Ex: Range(Cells(9,ColNum).Address(,,xlA1) & ":" Cells(27,ColNum).address(,,xlA1))

    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    balla506 (2014-07-29)

  4. #3
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    This kicks a compile error saying Expected list seperator or )...any ideas...I'm pretty sure my colnum= equation may not return the letter. Thanks.
    Code:
    Sub Hardcode_days_values()
    
    Dim colNum As Long
    Cell As Range
    
    
    
    
    
    colNum = WorksheetFunction.Match(Range("E5"), ActiveWorkbook.Sheets(Comp1).Range("6:40"), 0)
    For Each Cell In Range(Cells(9,ColNum).Address(,,xlA1) & ":" & Cells(27,ColNum).address(,,xlA1))
    Last edited by RetiredGeek; 2014-07-28 at 16:21.

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,379
    Thanks
    207
    Thanked 829 Times in 762 Posts
    Balla,

    Sorry I missed typing an & in the formula. I fixed it in your code block of the previous post so you can copy it. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    I am erroring out again with the unable to get the match property of worksheetfunction class. I did a little more research and was wondering if it would be smarter to use the find function in VBA. I do not know the best way to handle this. Since I am using dates I believe it only still looks at serial number and therefore it should match. Thanks.


    Code:
    Sub Hardcode_days_values()
    
    Dim colNum As Long
    Dim cell As Range
    
    'is there some way to use this instead and then strip off the letter or letters?
        Dim rngFound As Range
    
        Set rngFound = Sheet2.Range("F5:AB5").Find(Range("E5"), , xlValues, xlWhole)
    
    
    colNum = WorksheetFunction.Match(Range("E5"), ActiveWorkbook.Sheets("Case").Range("6:40"), 0)
    For Each cell In Range(Cells(9, colNum).Address(, , xlA1) & ":" & Cells(27, colNum).Address(, , xlA1))
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    Next cell
    
    
    End Sub

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Or just use:

    Range(Cells(9,ColNum),Cells(27,ColNum))

    Steve

  8. #7
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Still popping a message that says object required on the colnum= line
    Code:
    Sub Hardcode_days_values()
    
    Dim colNum As Long
    Dim cell As Range
    
    
    
    
    colNum = Worksheet.Match(Range("E5"), ActiveWorkbook.Sheets("Case").Range("6:40"), 0)
    For Each cell In Range(Cells(9, colNum), Cells(27, colNum))
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    
    Next cell
    
    
    End Sub

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    It is not "Worksheet.Match" but:
    Application.WorksheetFunction.Match ....

    Steve

    PS MATCH only looks in 1 row or 1 column, so I am not sure exactly what you are trying to match, it won't look in multiple rows...

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,379
    Thanks
    207
    Thanked 829 Times in 762 Posts
    Steve,

    Gee, I didn't even catch that! I was so busy working on the ranges knowing that match returned numbers. Forest & Trees!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  11. #10
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    I am trying to look in column F6:AB6 so only one row. How would this line of code go? I have tried messing with it multiple times to no avail. Thanks.

  12. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    colNum = Application.WorksheetFunction.Match(Range("E5"), ActiveWorkbook.Sheets("Case").Range("F6:AB6"), 0)+5

    The "+5" is to add the columns A:E to the Match value you get starting in Col F...

    Steve

  13. The Following User Says Thank You to sdckapr For This Useful Post:

    balla506 (2014-07-29)

  14. #12
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Final code below. Thanks for all the help in troubleshooting and fixing this.

    Code:
    Sub Hardcode_days_values()
    
    Dim colNum As Long
    Dim cell As Range
    
    
    
    colNum = Application.WorksheetFunction.Match(Range("E5"), ActiveWorkbook.Sheets("Case").Range("F5:AB5"), 0) + 5
    For Each cell In Range(Cells(9, colNum), Cells(27, colNum))
        cell.Formula = cell.Value
    
    Next cell
    
    
    End Sub
    Last edited by balla506; 2014-07-29 at 11:53.

  15. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    There really is no need for the loop. This would be much more efficient, just do the copy and paste values in one-fell-swoop:
    Code:
    With Range(Cells(9, colNum), Cells(27, colNum))
      .Copy
      .PasteSpecial Paste:=xlPasteValues
    End With
    Steve

  16. The Following User Says Thank You to sdckapr For This Useful Post:

    balla506 (2014-07-29)

  17. #14
    New Lounger
    Join Date
    Jul 2014
    Posts
    17
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Even Better. Thanks.

Tags for this Thread

Posting Permissions

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