I have this basic macro that I am trying to modify.

First mod I am trying to implement is to have the macro refer to a list I
have in B3:B10 and perform the copy (Line3 below) for each value in B3:B10.

2nd mod is after Line7, wait until a value is returned in H1 before
performing Line9.


1Sub Macro1()
2 Range("B3").Select
3 Selection.Copy
4
5 Range("G1").Select
6
7 ActiveSheet.Paste
8
9 Range("G1:H1").Select
10
11 Application.CutCopyMode = False
12
13 Selection.Copy
14
15 Range("B11").Select
16
17 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
18
19 :=False, Transpose:=False
20
21 End Sub

Thank You in Advance.

What does ... 2nd mod is after Line7, wait until a value is returned in H1
before
performing Line9 ... mean, that you want the macro to pause until someone
inputs?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"carl" wrote in message
...
I have this basic macro that I am trying to modify.

First mod I am trying to implement is to have the macro refer to a list I
have in B3:B10 and perform the copy (Line3 below) for each value in
B3:B10.

2nd mod is after Line7, wait until a value is returned in H1 before
performing Line9.


1Sub Macro1()
2 Range("B3").Select
3 Selection.Copy
4
5 Range("G1").Select
6
7 ActiveSheet.Paste
8
9 Range("G1:H1").Select
10
11 Application.CutCopyMode = False
12
13 Selection.Copy
14
15 Range("B11").Select
16
17 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
18
19 :=False, Transpose:=False
20
21 End Sub

Thank You in Advance.

Thank you Bob for helping.

There is a formula in H1. Sometimes the result takes 10 to 40 seconds to
appear in the cell. While calculating, the cell displays N/A.

"Bob Phillips" wrote:

What does ... 2nd mod is after Line7, wait until a value is returned in H1
before
performing Line9 ... mean, that you want the macro to pause until someone
inputs?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"carl" wrote in message
...
I have this basic macro that I am trying to modify.

First mod I am trying to implement is to have the macro refer to a list I
have in B3:B10 and perform the copy (Line3 below) for each value in
B3:B10.

2nd mod is after Line7, wait until a value is returned in H1 before
performing Line9.


1Sub Macro1()
2 Range("B3").Select
3 Selection.Copy
4
5 Range("G1").Select
6
7 ActiveSheet.Paste
8
9 Range("G1:H1").Select
10
11 Application.CutCopyMode = False
12
13 Selection.Copy
14
15 Range("B11").Select
16
17 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
18
19 :=False, Transpose:=False
20
21 End Sub

Thank You in Advance.

You can't sit waiting for that in the macro, you would never know.

What you could do is test the first part, then schedule the rest for running
say a minute later.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"carl" wrote in message
...
Thank you Bob for helping.

There is a formula in H1. Sometimes the result takes 10 to 40 seconds to
appear in the cell. While calculating, the cell displays N/A.

"Bob Phillips" wrote:

What does ... 2nd mod is after Line7, wait until a value is returned in
H1
before
performing Line9 ... mean, that you want the macro to pause until someone
inputs?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"carl" wrote in message
...
I have this basic macro that I am trying to modify.

First mod I am trying to implement is to have the macro refer to a list
I
have in B3:B10 and perform the copy (Line3 below) for each value in
B3:B10.

2nd mod is after Line7, wait until a value is returned in H1 before
performing Line9.


1Sub Macro1()
2 Range("B3").Select
3 Selection.Copy
4
5 Range("G1").Select
6
7 ActiveSheet.Paste
8
9 Range("G1:H1").Select
10
11 Application.CutCopyMode = False
12
13 Selection.Copy
14
15 Range("B11").Select
16
17 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
18
19 :=False, Transpose:=False
20
21 End Sub

Thank You in Advance.

Thanks Bob. Is it possible to build in a set delay (say 60 seconds) after
Line7 but before Line9 ?

"Bob Phillips" wrote:

You can't sit waiting for that in the macro, you would never know.

What you could do is test the first part, then schedule the rest for running
say a minute later.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"carl" wrote in message
...
Thank you Bob for helping.

There is a formula in H1. Sometimes the result takes 10 to 40 seconds to
appear in the cell. While calculating, the cell displays N/A.

"Bob Phillips" wrote:

What does ... 2nd mod is after Line7, wait until a value is returned in
H1
before
performing Line9 ... mean, that you want the macro to pause until someone
inputs?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"carl" wrote in message
...
I have this basic macro that I am trying to modify.

First mod I am trying to implement is to have the macro refer to a list
I
have in B3:B10 and perform the copy (Line3 below) for each value in
B3:B10.

2nd mod is after Line7, wait until a value is returned in H1 before
performing Line9.


1Sub Macro1()
2 Range("B3").Select
3 Selection.Copy
4
5 Range("G1").Select
6
7 ActiveSheet.Paste
8
9 Range("G1:H1").Select
10
11 Application.CutCopyMode = False
12
13 Selection.Copy
14
15 Range("B11").Select
16
17 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
18
19 :=False, Transpose:=False
20
21 End Sub

Thank You in Advance.

Check out VBA help on "wait method".

Pauses a running macro until a specified time. Returns True if the specified
time has arrived.

Important The Wait method suspends all Microsoft Excel activity and may prevent
you from performing other operations on your computer while Wait is in effect.
However, background processes such as printing and recalculation continue.


Gord Dibben MS Excel MVP

On Sat, 10 Feb 2007 09:59:00 -0800, carl wrote:

Thanks Bob. Is it possible to build in a set delay (say 60 seconds) after
Line7 but before Line9 ?

"Bob Phillips" wrote:

You can't sit waiting for that in the macro, you would never know.

What you could do is test the first part, then schedule the rest for running
say a minute later.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"carl" wrote in message
...
Thank you Bob for helping.

There is a formula in H1. Sometimes the result takes 10 to 40 seconds to
appear in the cell. While calculating, the cell displays N/A.

"Bob Phillips" wrote:

What does ... 2nd mod is after Line7, wait until a value is returned in
H1
before
performing Line9 ... mean, that you want the macro to pause until someone
inputs?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"carl" wrote in message
...
I have this basic macro that I am trying to modify.

First mod I am trying to implement is to have the macro refer to a list
I
have in B3:B10 and perform the copy (Line3 below) for each value in
B3:B10.

2nd mod is after Line7, wait until a value is returned in H1 before
performing Line9.


1Sub Macro1()
2 Range("B3").Select
3 Selection.Copy
4
5 Range("G1").Select
6
7 ActiveSheet.Paste
8
9 Range("G1:H1").Select
10
11 Application.CutCopyMode = False
12
13 Selection.Copy
14
15 Range("B11").Select
16
17 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
18
19 :=False, Transpose:=False
20
21 End Sub

Thank You in Advance.