CODEWITHSUNDEEP
vbaexcel
Michał M
Michał M

Reputation: 618

Excel, VBA find date in column

I have calendar in excel like this: enter image description here

What i'm trying to do. I want to write a macro that after mark cell and press button, Macro get string (here: user1), then back 1 column to get time and go up to get date (in this case 3). BUT I've already got user1 and date, but I can not get the date

Here's a code

Dim cell As Object
Dim client As String
Dim date As Date
Dim hour As Date
Dim rowc As Long
Dim colc As Long
    For Each cell In Selection
       client = cell.Value
       colc = ActiveCell.Column
       rowc = ActiveCell.Row
    Next cell

    hour = Cells(rowc, colc - 1).Value
Cells(4, 17).Value = hour 'works to this point

    t = rowc
    colc = colc-1 
    For i = t To 0

    If IsDate(Cells(i, colc).Value) = True Then

     date= Cells(i, colc).Value

    Else
    rowc = rowc - 1
    End If
    Next i



 'Cells(3, 17).Value = date
 Cells(5, 17).Value = client

Explanation of line : colc = colc-1 - Every date (31, 1, 2...) it is a 2 cells merged together. If user1 cell address is f.e. 8,14 so 3-11-2016 cell column is not 8 but 7.

Any suggestions?

EDIT:

It seems that this loop does not even loops. When i change true for false, msgbox did not appear.

Upvotes: 0

Views: 793

Answers (1)

Limak
Limak

Reputation: 1521

The For loop does not work that way. If t = 10, and you have For i = t To 0, loop will not iterate even once, because 10 > 0. As far as I have understood your request, you want to make i to go bacward. Try the same with Do While loop:

i = t
Do while i > 0
    If IsDate(Cells(i, colc).Value) = True Then
        date= Cells(i, colc).Value
    Else
        rowc = rowc - 1
    End If
    i = i - 1
Loop

Upvotes: 1

Related Questions