Run portion of loop when value in column changes.

A

apandbp

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 m
macro. It should contain all of the pertinent information.

On the first line of the code it runs a macro named PULL_ACCT. I onl
need to run the PULL_ACCT portion of the macro when the value in colum
A changes (ie row’s 5, 6, 8, 9 and 11) in our example. How can this b
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 Su
 
P

Per Jessen

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
 
A

apandbp

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

apandbp

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top