• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • 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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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