Find a date 3

[airchris]

Hi eveyone,

I'm on a trouble with another Excel project...
I made an userform with a listbox containing a list of days. When I choose a day and I push on a command button named "OK", I want Excel to find the date in the worksheet("BackUp&quot, range (A11,A65536), and change the color of the cell for yellow... I can't make it...
Help please!!!!!!

AirChris

 

[SkipVought]

chris,

Find a day? Do you mean a DATE or a DAY? I'm guessing a DATE.

Are you having trouble finding the date or changing the color?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Here's what you need...

1. your RowSource must be valid Excel dates -- NOT TEXT
2. the code for your "OK" CommandButton...

Private Sub CommandButton1_Click()
    Dim idx
    idx = Application.Match(ListBox1.Value, Range("DateList"), 0)
    If Not IsError(idx) Then
        Range("DateList")(idx, 1).Interior.ColorIndex = 33
    End If
End Sub

PS I named my range containing the lookup dates, DateList. That's your range, A11,A65536, although I seriously doubt that you have dates to the bottom of the sheet.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[airchris]

Hi,

thanks for the reply one more time! As you said I change my range with Range("A12", Range("A12&quot.End(xlDown).
Your code is working perfectly but I still have a little problem. I tried to had to your answer some code to make excel try to find if there is 2 times the same date... means these 2 cells or more have to al be yellow... but I'm in trouble about this point to... Anyway I still need some help about this please!

Airchris

 

[xlbo]

You'll probably need to use the FIND method and FINDNEXT then:

Private Sub CommandButton1_Click()
Dim fCell, fWhat, firstAddress
fWhat = format(Listbox1.value,Range("DateList&quot(2).numberformat) 'assumes header as 1st cell in "DateList"
With Range("DateList&quot
Set fCell = .Find(fWhat, lookin:=xlValues)
If Not fCell Is Nothing Then
firstAddress = fCell.Address
Do
fCell.interior.colorindex = 33
Set fCell = .FindNext(fCell)
Loop While Not fCell Is Nothing And fCell.Address <> firstAddress
End If
End With
End Sub


Skip - for reference...if you are interested...
the line
format(Listbox1.value,Range(&quot;DateList&quot(2).numberformat)

uses a technique I've just picked up from xl MVP Bob Umlas where Range(&quot;DateList&quot(2) refers to the 2nd cell in the range &quot;DateList&quot;

indexing like this goes left to right then down so in a 2 col range, (1) would refer to the top left cell, (2) to the top right cell, (3) to the 2nd cell down on the left etc etc

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Loomah]

Hi
This is an adaptation of what Skip started. Always had problems with finding dates! (Will have a look at xlbo's code sometime to see what's what!!)

Private Sub CommandButton1_Click()
Dim c
Dim lRow As Long
On Error GoTo errexit:
'Get the last row in the range
lRow = Range("daterange").Find(what:="*", SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
c = Application.Match(ListBox1.Value, Range("daterange"), 0)
Do
    Range("daterange")(c, 1).Interior.ColorIndex = 36
    c = Application.Match(ListBox1.Value, Range(Cells(c + 1, 3), _
        Cells(lRow, 3)), 0) + c
Loop
errexit:
End Sub

I've tested this with multiple dates and no dates and, although it ain't pretty, it seems to work!

xlbo
is that indexing thing the same as Skip used and that I've managed to adapt, but here we have
Range(&quot;daterange&quot(c, 1)??????
I didn't understand it but assume it references a specific part of a range (assume?!?!?? that's what it does but I'd never seen it before and don't understand!)
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Loomah]

BIG PS

forgot to change this but I was referencing dates in column C hence the cell refs referring to column 3

Should be

c = Application.Match(ListBox1.Value, Range(Cells(c + 1, 1), _
Cells(lRow, 1)), 0) + c

for columnA

Ooops


If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[xlbo]

Loomah - D'oh - didn't even notice that in Skip's post
Your assumption is correct - you can use it either way

Range(&quot;DateRange&quot(1,2)
will reference the 1st row in the 2nd column of &quot;DateRange&quot;
- just like the cells syntax - Cells(row,col)

If you just use
Range(&quot;DateRange&quot(2)

you will get the 2nd cell in &quot;DateRange&quot; - which cell that is will be dependant on
1: Whether it is a single column or multi column range
2: Whether the &quot;Default Direction&quot; in Tools>Options is set as Left to right or Right to Left


Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Loomah]

Cheers xlbo
I think it's a purple pointy pip day today
Skip for making me wonder what the wotsit was going on and you for explaining!

BTW it was nice to see someone beat you to the post with a response yesterday!!!!!!!!!!!!!!!!!!!!!!!!!! Now you know how I feel!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

Hi All,

Please take care using these odd syntaxes.

An explicit Range is just like any other well-behaved collection (a collection of cells) and can take a single index to identify a member. The following loops all do the same, but I do find the second format (which is just a shortened form of the third) a bit clumsy and of limited use.

Dim myCell As Range
For Each myCell In Range("myRange")
    MsgBox myCell.Address
Next

Dim myIndex As Long
For myIndex = 1 To Range("myRange").Count
    MsgBox Range("myRange")(myIndex).Address
Next 

Dim myIndex As Long
For myIndex = 1 To Range("myRange").Cells.Count
    MsgBox Range("myRange").Cells(myIndex).Address
Next

HOWEVER, the next two, which you might think should be similar, just serve to muddy the water.

Dim myIndex As Long
For myIndex = 1 To Columns(1).Cells.Count
    MsgBox Columns(1).Cells(myIndex).Address
Next

Dim myIndex As Long
For myIndex = 1 To Columns(1).Count
    MsgBox Columns(1)(myIndex).Address
Next

The second snippet above seems utterly useless to me. The syntax:

Columns(i)(j)

... is shorthand for:

Columns(i).Offset(, j-1)

... and returns a column.

Applying multiple indexes to a range is an extension of this and gives you a cell relative to the start of the range (unless you specify otherwise) but not restricted to the range, so …

Range("myRange")(2,2)

gives the cell offset 1 row and 1 column from the first cell in the range, which is the cell in the second row and second column of the range if it extends that far.

Range("myRange")(2)(2,2)

gives the cell offset 1 row and 1 column from the second cell in the range; which cell it is exactly depends on the shape of the range.

Enjoy,
Tony

 

[xlbo]

Thanks for that tony - didn't realise that it could take you OUT of the specified range. However - I think it could be quite useful - say you have a changing range and you want to go to the last cell in that range (not necessarily the last cell on the sheet :

range(&quot;MyRange&quot(Range(&quot;MyRange&quot.cells.count).select
will select the last cell in the range...

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[TonyJollans]

Thanks, Geoff,

You are right, of course. The end of a range is an awkward one to identify and that's as neat as any way of doing it.

Enjoy,
Tony