Excel Macro to select specific cells

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

vmpsbiz

Thread Starter
Joined
Jan 16, 2011
Messages
4
Hello, I need to figure out a macro to select certain ranges of cells depending on what is in cel 'A1'.

I have a column of dates in column 'B' and two columns of numbers in 'H'. I want to be able to enter a date into cell 'A1' and have it find that date and then select all cells in column 'H' that correspond to all dates before and including the date entered in cell 'A1'.

eg.

A1 = 1/1/2010

B2 = 11/12/2009 H2 = 2.56
B3 = 15/12/2009 H3 = 30.99
B4 = 20/12/2009 H4 = 32.54
B5 = 25/12/2009 H5 = 5.65
B6 = 31/12/2009 H6 = 3.54
B7 = 1/1/2010 H7 = 6.87
B8 = 20/1/2010 H8 = 1.25

Since A1 = 1/1/2010 I want to select all cells from H2 to H7

I do not need this to be done automatically as I will push a button to activate the macro I just need the macro to select the correct cells when i push the button.

Can someone please help me figre this out I am a complete newbie when it comes to Macro coding, but I do understand the rest of Excel very well.

Thanks.
 
Joined
Jan 16, 2011
Messages
119
Code:
Sub help_a_brother()
    ThisWorkbook.Sheets("Sheet1").Activate
    
    ' variable to hold the comparison date
    Dim checkdate As Date
    checkdate = CDate(Range("a1").Value)
    
    ' variable to collect address for the selection range
    Dim selectRange As String
    selectRange = vbNullString
   
    Dim CompareRange As Variant, cl As Variant
    Set CompareRange = ActiveWorkbook.Sheets("Sheet1").Range("b2:b8")
    For Each cl In CompareRange
        
        
        If CDate(cl.Value) <= checkdate Then ' if date in cell is less than date in A1
            If selectRange = vbNullString Then ' if this is the first cell being added to the selection
                selectRange = Range(cl.Address).Offset(0, 6).Address
            Else ' additional cells
                selectRange = selectRange & ", " & Range(cl.Address).Offset(0, 6).Address
            
            End If
            
        End If
    Next cl
    
    If Not selectRange = vbNullString Then
        Range(selectRange).Select
    End If
End Sub
This relies on fixed ranges ("b2:b8") and your "master" date being in cell A1. I can help you tweak it for dynamic column lengths, if needed. Obviously, you may need to change sheet and workbook references to match your project.
 

vmpsbiz

Thread Starter
Joined
Jan 16, 2011
Messages
4
Thanks, but that code selects all of the cells, I didn't change anything in the code other than the sheet name. I just want it to select all of the cells above and including the ones that correspond to the date put in A1. I do not want any cells that contain data that correspond to dates after the date in A1.
 
Joined
Jan 16, 2011
Messages
119
Check the formatting on the cells in your date column. The macro functions as you described on my machine, using your exact data examples, but I do remember going in and specifically setting the number format for the column to Date, English(UK). That could be the problem. It is also possible to use the macro to force the desired date format on the appropriate cells.
 

vmpsbiz

Thread Starter
Joined
Jan 16, 2011
Messages
4
The formating did not work it still selects all of the entries. Can you send me a copy of the workbook that you tested it in? Maybe that will help.

Thanks
 
Joined
Jan 16, 2011
Messages
119
I altered the macro slightly to force the date formatting to match for the comparison operations and added some very elementary error handling, in case some of the cells can't be converted to dates.

Code:
Sub help_a_brother()

'    Call test_format_dates
    
    ThisWorkbook.Sheets("Sheet1").Activate
    
    On Error Resume Next
    
    
    ' variable to hold the comparison date
    Dim checkdate As Date
    checkdate = Format(CDate(Range("a1").Value), "dd/mm/yyyy")
    If Err.Number <> 0 Then
        MsgBox ("The value in cell A1 is not a valid date!")
        Exit Sub
    End If
    
    
    ' variable to collect address for the selection range
    Dim selectRange As String
    selectRange = vbNullString
   
    Dim CompareRange As Variant, cl As Variant
    Set CompareRange = ActiveWorkbook.Sheets("Sheet1").Range("b2:b8")
    For Each cl In CompareRange
        
            On Error GoTo Skip: ' Will skip cells that can't be converted to dates
            If Format(CDate(cl.Value), "dd/mm/yyyy") <= checkdate Then ' if date in cell is less than date in A1
                If selectRange = vbNullString Then ' if this is the first cell being added to the selection
                    selectRange = Range(cl.Address).Offset(0, 6).Address
                Else ' additional cells
                    selectRange = selectRange & ", " & Range(cl.Address).Offset(0, 6).Address
                End If
           End If
Skip:
        
    Next cl
    
    If Not selectRange = vbNullString Then
        Range(selectRange).Select
    End If
    
    If Err.Number <> 0 Then
        MsgBox ("One or more of the cells in Column B are not valid dates, so some cells may have been skipped!")
        End If
        
End Sub
The second macro, "test_format_dates", will actually reformat the text in the date cells (the macro above doesn't change the formatting of the text in the cell, it just temporarily adjusts it for the comparisons). To have this macro run before the comparison macro, remove the comment mark from the line "'Call test_format_dates" in the first macro. The second macro is as follows:

Code:
Sub test_format_dates()

        Dim txtDate As String
        

        Dim rng As Range
        Set rng = ActiveWorkbook.Sheets("Sheet1").Range("a1,b2:b8")
        
        Dim cl As Variant
        
        For Each cl In rng
            txtDate = cl.Value
            txtDate = Format(txtDate, "dd/mm/yyyy") ' !! Euro style !!
            cl.Value = txtDate
        Next cl
End Sub
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top