Excel Copy on Certain Criteria Question

[giveitago]

I'm look to copy cells automatically using Worksheet_Change

As an example a simple cell copy...

Range("A1").Copy Destination:=Range("B1")

Can someone clarify the syntax for me to do something like this:

Time between 15:00 to 15:10 (say time in C1), copy cell A1 to B1.

Thanks.

[giveitago]

Ok, have worked out how to select a destination and copy a cell. BUT when doing an IF on the countdown timer so I can take snapshots every 30 seconds the IF statement doesn't seem to work. Its as if it's seeing the hour and not the minute part. Is there a certain way to format an IF if you're checking an HH:MM value?

[nicrag9]

Hi, There is a VBA command "Timevalue". With that you can compare the countdown timer with strings of text. If you compare against the Betangel sheet F4 countdown it would be phrased something like

if Worksheets(1).Range("F4")<=Timevalue("00:10:00") and worksheets(1).range("F4")>=Timevalue("00:09:30") then

That would all be one line of course, and you can add and subtract time this way. As that single line is it will repeatedly copy, so it would be worth setting flags for each 30 second period to show which ones had been done, you would need to check against that too in the if statement

Nic

[giveitago]

Hi Nic,

Thanks for that!

I'm still having issues getting my IF statement recognised correctly. I've been doing the following simple testing to get things working, but with this both of the copies are carried out even if the value in A1 doesn't match C3 or D3. Am I doing something really obviously wrong here?

I've also attached the Excel sheet for this.

If (C3 = A1) Then
Range("A2").Copy Destination:=Range("C4")
Else
End If
If (D3 = A1) Then
Range("A2").Copy Destination:=Range("D4")
End If

Thanks,
Simon

[nicrag9]

Hi,
I have just had a look at your sheet, and have uploaded a revised copy which I think is doing pretty much what you want. I have put the code into the sheet's code section not the module, and I have put it in a Change_selection section of the sheet's code. Have a look and see if it works for you.

Nic