i have range d2:d1000
if d = "Changed" then
take the value in B of the same row and put in A of the same row.
if d does not = "Changed" then
exit sub

any help on how to write this as a loop would be greatly appreciated

thanks in advance

With Worksheet
for i = 2 to 1000
if .Cells(i,4) = "Changed" then
.Cells(i,1) = .Cells(i,2)
else
exit for
end if
Next
End with



"choice" wrote in message
...
i have range d2:d1000
if d = "Changed" then
take the value in B of the same row and put in A of the same row.
if d does not = "Changed" then
exit sub

any help on how to write this as a loop would be greatly appreciated

thanks in advance

choice:
Try it

d = 2
Do While Cells(d, 4) < "Changed"
Cells(d, 1) = Cells(d, 2)
d = d + 1
Loop

--

http://www.vba.com.tw/plog/


"choice" wrote:

i have range d2:d1000
if d = "Changed" then
take the value in B of the same row and put in A of the same row.
if d does not = "Changed" then
exit sub

any help on how to write this as a loop would be greatly appreciated

thanks in advance

You need to use ActiveSheet or Worksheet("Sheet1") not With Worksheet

Also you might need to declare variable i, using Dim i as Long if the Option
Explicit is turned on (recommended) at top of module.

This procedure runs while column D contains "Changed" if it does not then
the procedure exits, if you want to check ALL values in rows 2 to 1000 then
remove the Exit For statement.
--
Cheers
Nigel



"Jezebel" wrote in message
...
With Worksheet
for i = 2 to 1000
if .Cells(i,4) = "Changed" then
.Cells(i,1) = .Cells(i,2)
else
exit for
end if
Next
End with



"choice" wrote in message
...
i have range d2:d1000
if d = "Changed" then
take the value in B of the same row and put in A of the same row.
if d does not = "Changed" then
exit sub

any help on how to write this as a loop would be greatly appreciated

thanks in advance

You could do it with a formula

A2: =IF(COUNTIF($D$2:D2,"Changed")=COUNTA($D$2:D2),B2, "")

and copy down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"choice" wrote in message
...
i have range d2:d1000
if d = "Changed" then
take the value in B of the same row and put in A of the same row.
if d does not = "Changed" then
exit sub

any help on how to write this as a loop would be greatly appreciated

thanks in advance

I don't *need* to do anything. The code is obviously schematic. If you
really wanted to be pedantic you might point out that is needs to be within
a Sub or Function, within a code module, and with some mechanism for calling
it. Or was your pomposity exhausted by that point?


"Nigel" wrote in message
...
You need to use ActiveSheet or Worksheet("Sheet1") not With Worksheet

Also you might need to declare variable i, using Dim i as Long if the
Option
Explicit is turned on (recommended) at top of module.

This procedure runs while column D contains "Changed" if it does not then
the procedure exits, if you want to check ALL values in rows 2 to 1000
then
remove the Exit For statement.
--
Cheers
Nigel



"Jezebel" wrote in message
...
With Worksheet
for i = 2 to 1000
if .Cells(i,4) = "Changed" then
.Cells(i,1) = .Cells(i,2)
else
exit for
end if
Next
End with



"choice" wrote in message
...
i have range d2:d1000
if d = "Changed" then
take the value in B of the same row and put in A of the same row.
if d does not = "Changed" then
exit sub

any help on how to write this as a loop would be greatly appreciated

thanks in advance

Hi Jezebel,

I don't *need* to do anything. The code is obviously schematic. If you
really wanted to be pedantic you might point out that is needs to be within
a Sub or Function, within a code module, and with some mechanism for
calling it. Or was your pomposity exhausted by that point?

The fact that the OP posed an elementary loop question might suggest that
your interpretation of obvious is rather optimistic.


At the risk of exposing myself to a possible charge of pomposity, I would
suggest that you use pseudo code only if there is good reason to believe
that this will be manifestly obvious to the intended recipient.
Additionally, in order to preclude responses such as Nigel's or, worse,
confusing the OP, it might be wise make an appropriate disclaimer at the
head of schematic code.



---
Regards,
Norman

Ditto...... and the response was for the OP to expand Jezebels' proposal. If
the code was truly pseudo in nature then it was mightily detailed in parts -
in fact one line away from working properly. I hope the OP benefited from
it.

--
Cheers
Nigel



"Norman Jones" wrote in message
...
Hi Jezebel,

I don't *need* to do anything. The code is obviously schematic. If you
really wanted to be pedantic you might point out that is needs to be
within
a Sub or Function, within a code module, and with some mechanism for
calling it. Or was your pomposity exhausted by that point?

The fact that the OP posed an elementary loop question might suggest that
your interpretation of obvious is rather optimistic.


At the risk of exposing myself to a possible charge of pomposity, I would
suggest that you use pseudo code only if there is good reason to believe
that this will be manifestly obvious to the intended recipient.
Additionally, in order to preclude responses such as Nigel's or, worse,
confusing the OP, it might be wise make an appropriate disclaimer at the
head of schematic code.



---
Regards,
Norman