Pull Row 1 of Specified Column RRS feed

  • Question

  • I need to pull row 1 for any column where there is a zero value.
    I want to set the variable "datevar1" to the date for every instance, which is located in the same column, but in row 1.

    I tried to use Range(CStr(Cell.Column) & "1") but I get the error "Method 'Range' of object '_Global' failed."

    Any ideas? The part of the code is below.


    Sub email_0( )
    Set Check_Range = Range("B4:G6")

    Dim Row As Range
    Dim Cell As Range
    Dim datevar1 As Variant

    'IF statement to send ONLY if NO FORM/ATTACHMENT SUBMITTED
    For Each Cell In Check_Range
        If Cell.Value = "0" Then
            Set datevar1 = Range(CStr(Cell.Column) & "1")

    • Edited by Brett N 13 Friday, December 2, 2011 9:48 PM
    Friday, December 2, 2011 9:24 PM


  • Several things:

    1. If datevar1 is not a range, we don't need Set
    2. Cell.Column is giving you a number and Range needs somthing like "A1"

    Perhaps we need something like:


    datevar1 = cells(1,Cell.Column).Value

    • Proposed as answer by Shasur Tuesday, December 6, 2011 9:08 AM
    • Marked as answer by danishani Thursday, January 12, 2012 1:25 AM
    Saturday, December 3, 2011 1:48 AM