Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Software Development Software Development
Search Search
Search for:
Tech Support Guy > > >

Excel Macro to select specific cells


(!)

vmpsbiz's Avatar
vmpsbiz vmpsbiz is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Jan 2011
Experience: Beginner
16-Jan-2011, 08:22 PM #1
Excel Macro to select specific cells
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.
gyclone's Avatar
gyclone gyclone is offline
Computer Specs
Member with 119 posts.
 
Join Date: Jan 2011
Location: Everett, WA, USA
Experience: Intermediate
17-Jan-2011, 06:28 AM #2
Try this...
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's Avatar
vmpsbiz vmpsbiz is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Jan 2011
Experience: Beginner
18-Jan-2011, 01:18 AM #3
Excel Macro to select specific cells
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.
gyclone's Avatar
gyclone gyclone is offline
Computer Specs
Member with 119 posts.
 
Join Date: Jan 2011
Location: Everett, WA, USA
Experience: Intermediate
18-Jan-2011, 02:02 AM #4
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's Avatar
vmpsbiz vmpsbiz is offline
Computer Specs
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Jan 2011
Experience: Beginner
18-Jan-2011, 01:33 PM #5
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
gyclone's Avatar
gyclone gyclone is offline
Computer Specs
Member with 119 posts.
 
Join Date: Jan 2011
Location: Everett, WA, USA
Experience: Intermediate
18-Jan-2011, 03:38 PM #6
Here you go ...
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
File Type: xlsm Test_Macro_for_vmpsbiz.xlsm (15.4 KB, 85 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
Solved: Macro to select data "no ordered" gpiquer Business Applications 3 01-Feb-2010 12:31 PM
Solved: Excel macro to separate entries in a single cell mrincognito Business Applications 11 05-Jun-2009 03:14 PM
Excel Macro to Copy a Row in a Spreadsheet newmac Business Applications 7 21-Nov-2008 05:36 AM
Solved: Excel macro to delete last character Paul Wit Bard Business Applications 4 31-Jul-2008 08:22 AM
Solved: need macro to select specific cells Leslie Scooter Business Applications 20 08-Jun-2008 07:15 PM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑