• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA mid function with DateTime

R

Ria

Member
Hi all:

Here is my problem with vba code using Excel 2003. I get data in text file (text file has date as first column in following format:
31/12/2015 4:00:00 PM,120.45,120.7,120.42,120.62. Current date is at end of file.
Then I import this data into Excel to plot charts. Date in excel is shown as format: 21-Sep-15 (however underlying it is: 21/09/2015 10:00:00 AM).
What I want in excel, in last data row, date cell, check last date/current date min and sec portion with following condition:
if sec = 00 and min = 15 or 30 or 45 or 00 then
do nothing
else
select last line of data and delete entire lines data.
I did write some code, it gives me error 13, type mismatch. Any help would be appreciated. Attached is my code:
Code: 
Sub CheckLastDataRowIntraDay()
'For Intra-day, check Min & Sec in Date column,
    'if Sec = 00 and Min = cell value e.g. 15 or 30 or 45 or 0, then do nothing. Else select last line and delete entire line.
    Dim TestValue As Integer, LR As Integer, myDate As String

    LR = Range("CA" & Rows.Count).End(xlUp).Row
    Range("CA1").Value = LR
    Range("CA" & LR).Select
    Selection.NumberFormat = "mm/dd/yyyy hh:mm:ss"
    TestValue = Range("AG55").Value
    myDate = Range("CA" & LR).Value
    'myDate = Range("CA" & Rows.Count).End(xlUp).Value
    If Mid(myDate, 18, 2) = "00" And _
    (Abs(Mid(myDate, 15, 2)) Mod (TestValue) = 0) Then
    'do nothing
    Else
        ActiveSheet.Range("CA" & LR & ":CM" & LR).Select
        Selection.ClearContents
    End If
End Sub
__________________________________________________________________
Mod edit : post moved to proper forum …​
 
@Ria
syntax: =MID( Input_string, Start_position, Number_of_characters )
You're working with date not string!
You try to check
minutes =minutes(myDate) and
seconds =second(myDate)
 
Hi ,

I am able to reproduce your error , when the value in the column CA and the last row is a date without a time component ; when the time component is 00:00:00 , Excel does not store it , as a result of which , the following portion of the code generates an error :

Mid(myDate, 15, 2)

If there is a time component in myDate , then there is no error.

This problem will not happen if , as Marc has suggested , you use the following line of code :

myDate = Range("CA" & LR).Text

instead of your existing :

myDate = Range("CA" & LR).Value

Narayan
 
@Marc L - Yes .Text works too!
It's good to learn everyday.

@NARAYANK991
I tested myDate with and without a time component
functions =minutes(myDate) and =second(myDate),
my Excel-version (2011) didn't give any error.
 
You must log in or register to reply here.
Back
Top