| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referring to a portion of a column | Excel Discussion (Misc queries) | |||
| Sum portion of column based on row content | Excel Discussion (Misc queries) | |||
| Sort on only portion of a column | Excel Discussion (Misc queries) | |||
| how do i remove a portion of data from a column | Excel Discussion (Misc queries) | |||
| Buttons in lower portion of workbook appear in upper portion | Excel Programming | |||
![apandbp[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
