Excel : If statement using time

up vote
0
down vote

favorite

I'm trying to set up something in excel wherein if time is 6 pm (18:00) then N20 (N20 is a web query and the value increases/decreases during random intervals) will copy the cell content/value to O20

edited Nov 11 at 14:11

asked Nov 11 at 13:38

Nexus237

You have now edited out your attempts which may earn you down/close votes.
– QHarr
Nov 12 at 7:16

add a comment |

up vote
0
down vote

favorite

I'm trying to set up something in excel wherein if time is 6 pm (18:00) then N20 (N20 is a web query and the value increases/decreases during random intervals) will copy the cell content/value to O20

edited Nov 11 at 14:11

asked Nov 11 at 13:38

Nexus237

You have now edited out your attempts which may earn you down/close votes.
– QHarr
Nov 12 at 7:16

add a comment |

up vote
0
down vote

favorite

I'm trying to set up something in excel wherein if time is 6 pm (18:00) then N20 (N20 is a web query and the value increases/decreases during random intervals) will copy the cell content/value to O20

edited Nov 11 at 14:11

asked Nov 11 at 13:38

Nexus237

I'm trying to set up something in excel wherein if time is 6 pm (18:00) then N20 (N20 is a web query and the value increases/decreases during random intervals) will copy the cell content/value to O20

excel

edited Nov 11 at 14:11

asked Nov 11 at 13:38

Nexus237

edited Nov 11 at 14:11

asked Nov 11 at 13:38

Nexus237

edited Nov 11 at 14:11

asked Nov 11 at 13:38

Nexus237

asked Nov 11 at 13:38

Nexus237

asked Nov 11 at 13:38

Nexus237

You have now edited out your attempts which may earn you down/close votes.
– QHarr
Nov 12 at 7:16

add a comment |

You have now edited out your attempts which may earn you down/close votes.
– QHarr
Nov 12 at 7:16

You have now edited out your attempts which may earn you down/close votes.
– QHarr
Nov 12 at 7:16

add a comment |

1 Answer
1

active

oldest

votes

up vote
0
down vote

accepted

You might actually want to use a workbook_open event to run a sub at the specified time that manages the transfer.

In ThisWorkbook code pane:

Option Explicit
Private Sub Workbook_Open()

 Application.OnTime TimeValue("18:00:00"), "Module1.GetValueAtSixPm"

End Sub

In standard module 1 put:

Public Sub GetValueAtSixPm()
 Dim ws As Worksheet
 Set ws = ThisWorkbook.Worksheets("Sheet2") ' <== Change as required
 With ws
 .Range("O20") = .Range("N20")
 End With
End Sub

edited Nov 12 at 10:32

answered Nov 11 at 14:07

QHarr

27.7k81839

Basically, there are 2 cells that are being used 1) N20 2) O20 N20 - gets the value using a web query and updates every 4 min. what I'm trying to do is, when it is 18:00, the value (value at 6 pm) of N20 is copied to O20
– Nexus237
Nov 11 at 14:16

Will the workbook be open and does the value have to be retained?
– QHarr
Nov 11 at 14:17

The workbook will be opened the entire time and the specific value at 6 pm is to be retained. N20 may still increase later, but i only want the value to be copied to O20 when it is exact 6 pm
– Nexus237
Nov 11 at 14:20

Ill try it out now. I have a doubt tho, why did you put the time value as 14:22? Also in this case, will the value be copied automatically or only when i copy it?
– Nexus237
Nov 12 at 4:06

While manually running the macro, it works. But it doesnt seem to run automatically, it says "Cannot run the macro....." The macros are enabled in the settings tho
– Nexus237
Nov 12 at 10:26

|
show 3 more comments

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53249302%2fexcel-if-statement-using-time%23new-answer', 'question_page');

);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
0
down vote

accepted

You might actually want to use a workbook_open event to run a sub at the specified time that manages the transfer.

In ThisWorkbook code pane:

Option Explicit
Private Sub Workbook_Open()

 Application.OnTime TimeValue("18:00:00"), "Module1.GetValueAtSixPm"

End Sub

In standard module 1 put:

Public Sub GetValueAtSixPm()
 Dim ws As Worksheet
 Set ws = ThisWorkbook.Worksheets("Sheet2") ' <== Change as required
 With ws
 .Range("O20") = .Range("N20")
 End With
End Sub

edited Nov 12 at 10:32

answered Nov 11 at 14:07

QHarr

27.7k81839

Basically, there are 2 cells that are being used 1) N20 2) O20 N20 - gets the value using a web query and updates every 4 min. what I'm trying to do is, when it is 18:00, the value (value at 6 pm) of N20 is copied to O20
– Nexus237
Nov 11 at 14:16

Will the workbook be open and does the value have to be retained?
– QHarr
Nov 11 at 14:17

The workbook will be opened the entire time and the specific value at 6 pm is to be retained. N20 may still increase later, but i only want the value to be copied to O20 when it is exact 6 pm
– Nexus237
Nov 11 at 14:20

Ill try it out now. I have a doubt tho, why did you put the time value as 14:22? Also in this case, will the value be copied automatically or only when i copy it?
– Nexus237
Nov 12 at 4:06

While manually running the macro, it works. But it doesnt seem to run automatically, it says "Cannot run the macro....." The macros are enabled in the settings tho
– Nexus237
Nov 12 at 10:26

|
show 3 more comments

up vote
0
down vote

accepted

You might actually want to use a workbook_open event to run a sub at the specified time that manages the transfer.

In ThisWorkbook code pane:

Option Explicit
Private Sub Workbook_Open()

 Application.OnTime TimeValue("18:00:00"), "Module1.GetValueAtSixPm"

End Sub

In standard module 1 put:

Public Sub GetValueAtSixPm()
 Dim ws As Worksheet
 Set ws = ThisWorkbook.Worksheets("Sheet2") ' <== Change as required
 With ws
 .Range("O20") = .Range("N20")
 End With
End Sub

edited Nov 12 at 10:32

answered Nov 11 at 14:07

QHarr

27.7k81839

Basically, there are 2 cells that are being used 1) N20 2) O20 N20 - gets the value using a web query and updates every 4 min. what I'm trying to do is, when it is 18:00, the value (value at 6 pm) of N20 is copied to O20
– Nexus237
Nov 11 at 14:16

Will the workbook be open and does the value have to be retained?
– QHarr
Nov 11 at 14:17

The workbook will be opened the entire time and the specific value at 6 pm is to be retained. N20 may still increase later, but i only want the value to be copied to O20 when it is exact 6 pm
– Nexus237
Nov 11 at 14:20

Ill try it out now. I have a doubt tho, why did you put the time value as 14:22? Also in this case, will the value be copied automatically or only when i copy it?
– Nexus237
Nov 12 at 4:06

While manually running the macro, it works. But it doesnt seem to run automatically, it says "Cannot run the macro....." The macros are enabled in the settings tho
– Nexus237
Nov 12 at 10:26

|
show 3 more comments

up vote
0
down vote

accepted

You might actually want to use a workbook_open event to run a sub at the specified time that manages the transfer.

In ThisWorkbook code pane:

Option Explicit
Private Sub Workbook_Open()

 Application.OnTime TimeValue("18:00:00"), "Module1.GetValueAtSixPm"

End Sub

In standard module 1 put:

Public Sub GetValueAtSixPm()
 Dim ws As Worksheet
 Set ws = ThisWorkbook.Worksheets("Sheet2") ' <== Change as required
 With ws
 .Range("O20") = .Range("N20")
 End With
End Sub

edited Nov 12 at 10:32

answered Nov 11 at 14:07

QHarr

27.7k81839

You might actually want to use a workbook_open event to run a sub at the specified time that manages the transfer.

In ThisWorkbook code pane:

Option Explicit
Private Sub Workbook_Open()

 Application.OnTime TimeValue("18:00:00"), "Module1.GetValueAtSixPm"

End Sub

In standard module 1 put:

Public Sub GetValueAtSixPm()
 Dim ws As Worksheet
 Set ws = ThisWorkbook.Worksheets("Sheet2") ' <== Change as required
 With ws
 .Range("O20") = .Range("N20")
 End With
End Sub

edited Nov 12 at 10:32

answered Nov 11 at 14:07

QHarr

27.7k81839

edited Nov 12 at 10:32

answered Nov 11 at 14:07

QHarr

27.7k81839

answered Nov 11 at 14:07

QHarr

27.7k81839

answered Nov 11 at 14:07

QHarr

27.7k81839

Basically, there are 2 cells that are being used 1) N20 2) O20 N20 - gets the value using a web query and updates every 4 min. what I'm trying to do is, when it is 18:00, the value (value at 6 pm) of N20 is copied to O20
– Nexus237
Nov 11 at 14:16

Will the workbook be open and does the value have to be retained?
– QHarr
Nov 11 at 14:17

The workbook will be opened the entire time and the specific value at 6 pm is to be retained. N20 may still increase later, but i only want the value to be copied to O20 when it is exact 6 pm
– Nexus237
Nov 11 at 14:20

Ill try it out now. I have a doubt tho, why did you put the time value as 14:22? Also in this case, will the value be copied automatically or only when i copy it?
– Nexus237
Nov 12 at 4:06

While manually running the macro, it works. But it doesnt seem to run automatically, it says "Cannot run the macro....." The macros are enabled in the settings tho
– Nexus237
Nov 12 at 10:26

|
show 3 more comments

Basically, there are 2 cells that are being used 1) N20 2) O20 N20 - gets the value using a web query and updates every 4 min. what I'm trying to do is, when it is 18:00, the value (value at 6 pm) of N20 is copied to O20
– Nexus237
Nov 11 at 14:16

Will the workbook be open and does the value have to be retained?
– QHarr
Nov 11 at 14:17

The workbook will be opened the entire time and the specific value at 6 pm is to be retained. N20 may still increase later, but i only want the value to be copied to O20 when it is exact 6 pm
– Nexus237
Nov 11 at 14:20

Ill try it out now. I have a doubt tho, why did you put the time value as 14:22? Also in this case, will the value be copied automatically or only when i copy it?
– Nexus237
Nov 12 at 4:06

While manually running the macro, it works. But it doesnt seem to run automatically, it says "Cannot run the macro....." The macros are enabled in the settings tho
– Nexus237
Nov 12 at 10:26

Basically, there are 2 cells that are being used 1) N20 2) O20 N20 - gets the value using a web query and updates every 4 min. what I'm trying to do is, when it is 18:00, the value (value at 6 pm) of N20 is copied to O20
– Nexus237
Nov 11 at 14:16

Will the workbook be open and does the value have to be retained?
– QHarr
Nov 11 at 14:17

The workbook will be opened the entire time and the specific value at 6 pm is to be retained. N20 may still increase later, but i only want the value to be copied to O20 when it is exact 6 pm
– Nexus237
Nov 11 at 14:20

Ill try it out now. I have a doubt tho, why did you put the time value as 14:22? Also in this case, will the value be copied automatically or only when i copy it?
– Nexus237
Nov 12 at 4:06

While manually running the macro, it works. But it doesnt seem to run automatically, it says "Cannot run the macro....." The macros are enabled in the settings tho
– Nexus237
Nov 12 at 10:26

|
show 3 more comments

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Odtnhj