Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    comparing dates within cells to system date (Visual Basic 6.0 and excel 2000 )

    Hans - Could you possibly assist me please.

    I'm using an excel 2000 spreadsheet, row 2 is my date field (each column a different day of the month I.E. 08/07/2005).

    What I'm trying to do is compare the system date with the date in row 2 if its the same date perform a task else end

    I have reviewed and reviewed Contents / Answer wizard/ and the Index of both Excel 2000 and Visual Basic 6.0 for the past week and still cannot come up with anything. Could you please help out.

    Thank s

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: comparing dates within cells to system date (Visual Basic 6.0 and exce

    Can you provide some details about the task you want to perform?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: comparing dates within cells to system date (Visual Basic 6.0 and exce

    sure...

    I'm importing data from another worksheet called Import Data into a new spreedsheet called month of year ( I.E. June , July etc...) the data fits in column D4 through D34 for the date of june 1st., same for June 2nd, 3rd etc... The Imported data gets updated every morning at 2am. which means that every time they click on the button that runs the macro (using visual basic) it will fill in the next column and stop. I need it to stop in case the user clicks on the button that runs the Macro more than once.
    I also need it to check the computers system date to ensure that the correct date column gets filled in with the correct date data.
    after all that is said and done I need row D41 to calculate any cells in that current days data that is below 99%.
    Thats it in a nut shell.

    Unfortanetly I cannot download a copy of the spreadsheet.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: comparing dates within cells to system date (Visual Basic 6.0 and exce

    Is something like this what you are looking for:

    <code>
    Public Sub FindColumn()
    Dim lLastCol As Long, J As Long
    lLastCol = Range("IV2").End(xlToLeft).Column - 1
    For J = 0 To lLastCol
    If Range("A2").Offset(0, J).Value = Date Then Exit For
    Next J
    If J >= lLastCol Then
    MsgBox "Today's date not found"
    Else
    MsgBox "Today's column is " & Range("A2").Offset(0, J).Address
    End If
    End Sub
    </code>

    The above assumes that the dates in row 2 are Excel Date values, not text values that look like dates.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: comparing dates within cells to system date (Visual Basic 6.0 and exce

    The code could look like this. It is supposed to be run while the month sheet is active.

    Sub FillToday()
    Dim r As Integer, c As Integer
    ' Today's day number
    c = Day(Date)
    ' Check if already imported
    If Not Cells(4, c + 3) = "" Then
    MsgBox "Data for today already imported.", vbExclamation
    Exit Sub
    End If
    ' Import
    For r = 4 To 34
    ' Modify as needed
    Cells(r, c + 3) = Worksheets("ImportData").Cells(r, 1)
    Next r
    ' Count items less than 99%
    Cells(41, c + 3).Formula = "=COUNTIF(" & Cells(4, c + 3).Address & _
    ":" & Cells(34, c + 3).Address & ",""<99%"")"
    End Sub

    You must, of course, modify the line that copies data from ImportData to suit your needs.

  6. #6
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: comparing dates within cells to system date (Visual Basic 6.0 and exce

    thank you hans I will try that tonight.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: comparing dates within cells to system date (Visual Basic 6.0 and exce

    Hans - I have the program pretty well set and working to almost perfection. the problem I'm having now is that when I press the populate button to populate column B, then press the populate button again it populates column C ETC.... What I'm looking for is someway to stop the program once the column B has Been updated. Another words once the column is filled to stop. Below is a Portion of my code:

    ColumnB:
    If Cells(2, 2).Value = "" Then
    Cells(2, 2) = Mydate
    Call MyRoutine
    With wks1
    Set rngcopy = .Range(.Range("AR4"), .Range("AR11"))
    End With
    Set rngpaste = wks2.Range("B41").End(xlToLeft).Offset(0, 1)
    rngcopy.Copy
    rngpaste.PasteSpecial xlPasteValues
    GoTo Line1000
    ElseIf Mydate = enddate Then MsgBox "ERROR!, Date Past End Of Month on 4-5-4 Calendar, Please Use Next Available Month!!"
    Exit Sub
    ************************** All of the above gets repeated 30 times ************************************************** **********************


    Line1000:

    If Worksheets("import Data").[AM5] <> 0 Then MsgBox "Please Create PMR For - Signing LRT Server Process Below 99%"
    If Worksheets("import Data").[AM6] <> 0 Then MsgBox " Please Create PMR For - Signing LRT Server Process Below 99%"
    If Worksheets("import Data").[AM7] <> 0 Then MsgBox " Please Create PMR For - Signing LRT Server Process Below 99%"
    end if
    end sub



    Sub MyRoutine()
    Dim LastCol As Long
    Dim LastCol1 As Long
    Dim LastCol2 As Long
    LastCol = Worksheets("June").Range("An34").End(xlToLeft).Col umn + 1
    Worksheets("Import Data").[b10:b24].Copy Destination:=Worksheets("June").Cells(20, LastCol)
    LastCol1 = Worksheets("June").Range("AM17").End(xlToLeft).Col umn + 1
    Worksheets("Import Data").[b74:b87].Copy Destination:=Worksheets("June").Cells(4, LastCol)
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: comparing dates within cells to system date (Visual Basic 6.0 and exce

    The code I posted will only populate the column for the current date, and display a message if that has already been done.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •