Find the next date in column

[dgraham62]

Hi,

I was wondering how to locate the next date in a column and then select that column . the next date would be the next date from todays date. any assistance is appreciated thank you. I did try searching here but was not able to find what I needed.

Regards

David

p.s. I have attached an image of part of the columns

[Bob Phillips]

Dim i As Long

With ActiveSheet

For i = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column

If .Cells(1, i).Value > Date Then

.Columns(i).Select
End If
Next i
End With

[dgraham62]

Thanks xld,

The code seems to be what I need but the column it's selecting is with the date 01/08/08. the columns range from dates 03/05/08 to 01/01/10.

[mdmackillop]

Sub NextDate()
Dim col As Long
col = Application.WorksheetFunction.Match(CLng(Date), Rows(1), 1)
If Cells(1, col) <> Date Then col = col + 1
Columns(col).Select
End Sub

[Bob Phillips]

Sub NextDate()
Dim col As Long
col = Application.WorksheetFunction.Match(CLng(Date), Rows(1), 1)
If Cells(1, col) <> Date Then col = col + 1
Columns(col).Select
End Sub

I thought of doing it that way, but decided against if in case today's date was later than any date in row 1 :)

[dgraham62]

Thanks again for your quick replys.

Now the code is doing nothing, I added a command button to execute the code and when I click on it nothing seems to happen.

[Bob Phillips]

Did you assign the button to a macro, the correct macro?

[mdmackillop]

Here's my version

[dgraham62]

I didn't assign the button correctly to the macro, now after doing that I get the following error.

[dgraham62]

Post #1
I think I have it figured out why, but not how to fix it. I had to delete the 1st two rows as they were blank. is there a way to use the code while keeping the rows above the dates?

Post #2
Thanks for all your help guys, I got it figured out by changing the row numbers.

Post #3
I need further help please, I can select the correct column fine, but is there a way to select only part of the column ie... from "C4:C161"?

Regards

David

Posts Merged 09/24/2009 5:02 PM EST
~Oorang

[Bob Phillips]

In Malcolm's example, change


Columns(col).Select


to



Columns(col).Cells(4, 1).Resize(158).Select

[dgraham62]

Thanks again xdl,

Thats works great. one last request please, am I able to make that selection ie.. from row 4 to 161 plus rows 326 to 382 at the same time as if I were holding down the "Ctrl" key?

Regards

David

[Bob Phillips]

You mean automatically calculate the last cell? As there is no data down the columns, I cannot see how you can determine the last cell.

[dgraham62]

Post #1
Hi,

There won't be any calculations, just want to select some rows then some more rows like I would do by using the "Ctrl" to do multiple selections, the image shows what I would like to do.

Post #2
Hi,

The image did not get attached, hope this works. I dont only need to just be able to select the cells, but also needs to reflect the early post ie... selecting the cells in the column that has the date in it

Regards

David

Posts Merged 09/24/2009 5:04 PM EST
~Oorang

[Bob Phillips]

Private Sub CommandButton1_Click()
Dim col As Long
col = Application.WorksheetFunction.Match(CLng(Date), Rows(1), 1)
If Cells(1, col) <> Date Then col = col + 1

Cells(1, col).Resize(Cells(Rows.Count, col).End(xlUp).Row).Select
End Sub

[dgraham62]

That code selects row 1 to 382, but I need to rows 4 to 161 AND 326 to 382 only. Is this possible with code as it can be done by holding down the control key when done manually>

[Bob Phillips]

I don't know how it knows those rows, and your picture didn't explain it to me.

[mdmackillop]

Seems to me like a new question. Better to start a new thread.