• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

VBA Code

Hi Experts,

Will anyone help me with piece of code which execute “Copy Trigger data” Module in attached WB.
If below Condition Match.
If time is between 9:40 to 15:30 & Column L <>”” & Column M<>”Posted”
Range for looping = L2: L1699 & M2:M1699
Flow like this if say L2<>”” i.e. in attached it is “Testing” & M2 <>”Posted” & time is between 9:40 – 15:30
Then Execute Module “Copy Trigger Data”
         Put “Posted” in Cell M2.
If above AND condition doesn't match then exit.
Code run on real time basis.

Thanks
EE-Demo.xlsm
0
Naresh Patel
Asked:
Naresh Patel
  • 4
  • 3
1 Solution
 
Ejgil HedegaardCommented:
Use the function Timevalue to check for time.

To match the conditions, replace
If c.Value <> "" Then

Open in new window

with
If c.Value <> "" And TimeValue(Now()) >= TimeValue("09:40:00") And TimeValue(Now()) <= TimeValue("15:30:00") And c.Offset(0, 1) <> "Posted" Then
            c.Offset(0, 1) = "Posted"

Open in new window

0
 
Naresh PatelTraderAuthor Commented:
May i have Sample WB please!!!
0
 
Naresh PatelTraderAuthor Commented:
Mr.Ejgil Hedegaard,

Did what you said - this is the error messageError Message
Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ejgil HedegaardCommented:
Here is the WB.
EE-Demo-A.xlsm
0
 
Naresh PatelTraderAuthor Commented:
Perfect - Just need to confirm this Code will run on real time basis without human intervention? as row 2 to 1699 data updated on real time basis & column L have referencing formula to these real time data as and when condition match, column L produce some string else it will "". i guess you got my point.


Thanks
0
 
Ejgil HedegaardCommented:
The conditions for the row processed (2 to 1699) are
- The cell in column L must have a value, anything will do
- The cell in column M must not have the text "Posted"
- The real time (Now) is between 9:40 and 15:30
Then the text "Posted" is put in the cell in column M

And the rest of the code add values and formulas to the first empty row below 1700 for the processed row.
Did not check what, just that it happens.
0
 
Naresh PatelTraderAuthor Commented:
Yes it is happen ...I thought for Real time I have to ask new question .....Thank You Very Much
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now