1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Macro to select specific cells

Discussion in 'Software Development' started by vmpsbiz, Jan 16, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. vmpsbiz

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

    gyclone

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

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

    gyclone

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

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

    gyclone

    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
    
    
     

    Attached Files:

  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975111