Run portion of loop when value in column changes.
 

I apologize for the unclear title. This one is a bit hard to explain.
I have only included an excerpt from column A of my spreadsheet and my
macro. It should contain all of the pertinent information.

On the first line of the code it runs a macro named PULL_ACCT. I only
need to run the PULL_ACCT portion of the macro when the value in column
A changes (ie row’s 5, 6, 8, 9 and 11) in our example. How can this be
done?


A
1 Customer
2 50148501
3 50148501
4 50148501
5 70034947
6 10034932
7 10034932
8 30034953
9 70570023
10 70570023
11 60570033
12 60570033
13 60570033
14 60570033
15 60570033


Sub Post_Code()

Do
PULL_ACCT
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys ("^v"), Wait:=True
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.75)
SendKeys "s", Wait:=True

SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(0, 4).Select
ActiveCell.Range(Cells(1, 3), Cells(1, 1)).Select
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F10
Application.Wait Now() + TimeSerial(0, 0, 0.25)
SendKeys ("^v"), Wait:=True
SendKeys "{F10}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(1, -5).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))


End Sub


--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119336

Run portion of loop when value in column changes.
 

Hi

This should do it:

LastRow = Range("A1").End(xlDown).Row
TargetValue = Range("A2").Value
For r = 3 To LastRow
If Range("A" & r) < TargetValue Then
'Here goes your code
TargetValue = Range("A" & r).Value
End If
Next

Regards,
Per
"apandbp" skrev i meddelelsen
...

I apologize for the unclear title. This one is a bit hard to explain.
I have only included an excerpt from column A of my spreadsheet and my
macro. It should contain all of the pertinent information.

On the first line of the code it runs a macro named PULL_ACCT. I only
need to run the PULL_ACCT portion of the macro when the value in column
A changes (ie row's 5, 6, 8, 9 and 11) in our example. How can this be
done?


A
1 Customer
2 50148501
3 50148501
4 50148501
5 70034947
6 10034932
7 10034932
8 30034953
9 70570023
10 70570023
11 60570033
12 60570033
13 60570033
14 60570033
15 60570033


Sub Post_Code()

Do
PULL_ACCT
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys ("^v"), Wait:=True
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.75)
SendKeys "s", Wait:=True

SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(0, 4).Select
ActiveCell.Range(Cells(1, 3), Cells(1, 1)).Select
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F10
Application.Wait Now() + TimeSerial(0, 0, 0.25)
SendKeys ("^v"), Wait:=True
SendKeys "{F10}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(1, -5).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))


End Sub


--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=119336

Run portion of loop when value in column changes.
 

Do I put the whole code where you put "Here goes your code" or just the
portion I want to run when there is a value change?

Per Jessen;429556 Wrote:
Hi

This should do it:

LastRow = Range("A1").End(xlDown).Row
TargetValue = Range("A2").Value
For r = 3 To LastRow
If Range("A" & r) < TargetValue Then
'Here goes your code
TargetValue = Range("A" & r).Value
End If
Next

Regards,
Per
"apandbp" skrev i meddelelsen
...

I apologize for the unclear title. This one is a bit hard to
explain.
I have only included an excerpt from column A of my spreadsheet and
my
macro. It should contain all of the pertinent information.

On the first line of the code it runs a macro named PULL_ACCT. I
only
need to run the PULL_ACCT portion of the macro when the value in
column
A changes (ie row's 5, 6, 8, 9 and 11) in our example. How can this
be
done?


A
1 Customer
2 50148501
3 50148501
4 50148501
5 70034947
6 10034932
7 10034932
8 30034953
9 70570023
10 70570023
11 60570033
12 60570033
13 60570033
14 60570033
15 60570033


Sub Post_Code()

Do
PULL_ACCT
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys ("^v"), Wait:=True
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.75)
SendKeys "s", Wait:=True

SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(0, 4).Select
ActiveCell.Range(Cells(1, 3), Cells(1, 1)).Select
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F10
Application.Wait Now() + TimeSerial(0, 0, 0.25)
SendKeys ("^v"), Wait:=True
SendKeys "{F10}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(1, -5).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))


End Sub


--
apandbp

------------------------------------------------------------------------
apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp'
(http://www.thecodecage.com/forumz/member.php?userid=550)
View this thread:
'Run portion of loop when value in column changes. - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=119336)



--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119336

Run portion of loop when value in column changes.
 

Only the part which needs to run when the value change.

Regards,
Per

"apandbp" skrev i meddelelsen
...

Do I put the whole code where you put "Here goes your code" or just the
portion I want to run when there is a value change?

Per Jessen;429556 Wrote:
Hi

This should do it:

LastRow = Range("A1").End(xlDown).Row
TargetValue = Range("A2").Value
For r = 3 To LastRow
If Range("A" & r) < TargetValue Then
'Here goes your code
TargetValue = Range("A" & r).Value
End If
Next

Regards,
Per
"apandbp" skrev i meddelelsen
...

I apologize for the unclear title. This one is a bit hard to
explain.
I have only included an excerpt from column A of my spreadsheet and
my
macro. It should contain all of the pertinent information.

On the first line of the code it runs a macro named PULL_ACCT. I
only
need to run the PULL_ACCT portion of the macro when the value in
column
A changes (ie row's 5, 6, 8, 9 and 11) in our example. How can this
be
done?


A
1 Customer
2 50148501
3 50148501
4 50148501
5 70034947
6 10034932
7 10034932
8 30034953
9 70570023
10 70570023
11 60570033
12 60570033
13 60570033
14 60570033
15 60570033


Sub Post_Code()

Do
PULL_ACCT
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys ("^v"), Wait:=True
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.75)
SendKeys "s", Wait:=True

SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(0, 4).Select
ActiveCell.Range(Cells(1, 3), Cells(1, 1)).Select
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F10
Application.Wait Now() + TimeSerial(0, 0, 0.25)
SendKeys ("^v"), Wait:=True
SendKeys "{F10}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(1, -5).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))


End Sub


--
apandbp

------------------------------------------------------------------------
apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp'
(http://www.thecodecage.com/forumz/member.php?userid=550)
View this thread:
'Run portion of loop when value in column changes. - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=119336)



--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=119336

Run portion of loop when value in column changes.
 

Doesn't work. It scrolls puts the curser in the next column. I need it
to go into the next row down. Any other suggestions?

Per Jessen;429766 Wrote:
Only the part which needs to run when the value change.

Regards,
Per

"apandbp" skrev i meddelelsen
...

Do I put the whole code where you put "Here goes your code" or just
the
portion I want to run when there is a value change?

Per Jessen;429556 Wrote:
Hi

This should do it:

LastRow = Range("A1").End(xlDown).Row
TargetValue = Range("A2").Value
For r = 3 To LastRow
If Range("A" & r) < TargetValue Then
'Here goes your code
TargetValue = Range("A" & r).Value
End If
Next

Regards,
Per
"apandbp" skrev i meddelelsen
...

I apologize for the unclear title. This one is a bit hard to
explain.
I have only included an excerpt from column A of my spreadsheet
and
my
macro. It should contain all of the pertinent information.

On the first line of the code it runs a macro named PULL_ACCT. I
only
need to run the PULL_ACCT portion of the macro when the value in
column
A changes (ie row's 5, 6, 8, 9 and 11) in our example. How can
this
be
done?


A
1 Customer
2 50148501
3 50148501
4 50148501
5 70034947
6 10034932
7 10034932
8 30034953
9 70570023
10 70570023
11 60570033
12 60570033
13 60570033
14 60570033
15 60570033


Sub Post_Code()

Do
PULL_ACCT
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys ("^v"), Wait:=True
Shift_F3
Application.Wait Now() + TimeSerial(0, 0, 0.75)
SendKeys "s", Wait:=True

SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(0, 4).Select
ActiveCell.Range(Cells(1, 3), Cells(1, 1)).Select
Selection.Copy
SendKeys "%{TAB}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
Shift_F10
Application.Wait Now() + TimeSerial(0, 0, 0.25)
SendKeys ("^v"), Wait:=True
SendKeys "{F10}", Wait:=True
Application.Wait Now() + TimeSerial(0, 0, 0.5)
SendKeys "%{TAB}", Wait:=True
ActiveCell.Offset(1, -5).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))


End Sub


--
apandbp


------------------------------------------------------------------------
apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp'
('The Code Cage Forums - View Profile: apandbp'
(http://www.thecodecage.com/forumz/me...hp?userid=550))
View this thread:
'Run portion of loop when value in column changes. - The Code
Cage
Forums' ('Run portion of loop when value in column changes. - The
Code Cage Forums'
(http://www.thecodecage.com/forumz/sh....php?t=119336))



--
apandbp

------------------------------------------------------------------------
apandbp's Profile: 'The Code Cage Forums - View Profile: apandbp'
(http://www.thecodecage.com/forumz/member.php?userid=550)
View this thread:
'Run portion of loop when value in column changes. - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=119336)



--
apandbp
------------------------------------------------------------------------
apandbp's Profile: http://www.thecodecage.com/forumz/member.php?userid=550
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=119336