loop question

[choice]

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

[Jezebel]

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" <[email protected]> wrote in message
news:[email protected]...
>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
>
>

[chijanzen]

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
>
>

[Nigel]

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> >
>
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[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?


"Nigel" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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
>> >
>> >
>>
>>
>
>

[Norman Jones]

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

[Nigel]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>