Vba bug fix

[adpatel]

Hello everyone, I have a VBA code which is very simple, but still it's not working as it should be, so i think there is little bug in code, pls help to find it out

Requirement:-

If cell value is 0 or time is less than 15:29:00

than do nothing

else, 'Main code ll be run in this case'

My code:-

Dim LTime As Date

   Sheets("AD").Select
    
    If Range("J2").Value = 0 Or LTime < "15:29:00" Then
    Range("K3").Select
    
    Else


Sheets("AD").Select

 Range("J2:AF3").Select
    Selection.Copy
 
    Range("J6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("J6").Select
   
       
      End If

Any help ll be appriciated

 

[Zot]

Time is this line is just viewed as text, not the time itself

If Range("J2").Value = 0 Or LTime < "15:29:00" Then

In Excel time is stored as as fractional number. Try

If Range("J2").Value = 0 Or LTime < TimeValue("15:29:00") Then

 

[adpatel]

Time is this line is just viewed as text, not the time itself

If Range("J2").Value = 0 Or LTime < "15:29:00" Then

In Excel time is stored as as fractional number. Try

If Range("J2").Value = 0 Or LTime < TimeValue("15:29:00") Then

I have tried this solution but didn't worked my full code mentioning here belw

Private Sub Workbook_BeforeClose(Cancel As Boolean)


Application.ScreenUpdating = False

Dim LTime As Date

   Sheets("AD").Select
   
    If Range("J2").Value = 0 Or LTime < TimeValue("11:29:00") Then
    Range("K3").Select
   
    Else


Sheets("AD").Select

Range("J2:AF3").Select
    Selection.Copy

    Range("J6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("J6").Select
  
      
      End If
       
    Sheets("calenders").Select
   
    Application.ScreenUpdating = True
   
    If Me.Saved = False Then Me.Save
   
End Sub

 

[Zot]

How the worksheet looks like? Where LTime comes from?

 

[adpatel]

How the worksheet looks like? Where LTime comes from?

Ltime comes from nowhere, it's just mentioned in VBA code, want to check current time so I guess I am missing on that

 

[Zot]

Ltime comes from nowhere, it's just mentioned in VBA code, want to check current time so I guess I am missing on that

You can just use Now() perhaps

 

[adpatel]

You can just use Now() perhaps

ok, got it now it looks like

Dim LTime As Date
LTime = TimeValue("03:29:00 PM")

   Sheets("AD").Select
   
    If Range("J2").Value = 0 Or TimeValue(Now) < LTime Then
    Range("K3").Select
   
    Else

 

[Zot]

ok, got it now it looks like

I think no need to use TimeValue(Now) since now is in Excel time format already. TimeValue is to convert time is from string representation

 

[adpatel]

I think no need to use TimeValue(Now) since now is in Excel time format already. TimeValue is to convert time is from string representation

As currently it's working fine, not making any changes into it, if required in future ll make changes according to you suggestion, Thx a tone @Zot for your prompt replies

 

[MARK858]

@Zot you do need to use the Timevalue part as Now includes the date, so it includes the Integer part of the number.

For instance here it is 06:57 which is 0.289583333333333 without the date part.

Now would give 44210.289583333333333 which would be greater than TimeValue("03:29:00 PM") which is 0.645138888888889.