Start Condition For Macro

[sb1989]

Hello,
I am new with VBA/Macros.

I am currently working on a macro and I need the macro to go down an entire column and run everytime the number '2002' is in column R. I have a button set up, but it is currently only working when I am on an active cell. Below is the formula from VBA.

Private Sub CommandButton1_Click()
'
ActiveCell.FormulaR1C1 = _
"=INDEX(R3C3:R8C8,MATCH(RC[-3],R3C1:R8C1,0),MATCH(RC[-2],R1C3:R1C8,0))+IFERROR(INDEX(R3C3:R8C8,MATCH(RC[-2],R3C1:R8C1,0),MATCH(RC[-1],R1C3:R1C8,0)),0)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A2"), Type:= _
xlFillDefault
ActiveCell.Range("A1:A2").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.ClearContents
ActiveCell.FormulaR1C1 = _
"=INDEX(R3C3:R8C8,MATCH(R[-1]C[-2],R3C1:R8C1,0),MATCH(RC[-2],R1C3:R1C8,0))+IFERROR(INDEX(R3C3:R8C8,MATCH(RC[-2],R3C1:R8C1,0),MATCH(RC[-1],R1C3:R1C8,0)),0)"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub


Any help will be greatly appreciated!

Thanks,
Sam

 

[ranman256]

If you are starting in Col R

range("R2").select
while activecell.value <> ""      'continue untill the rows end
     
     'put your code here  BUT DO NOT USE THE COMMAND:   cell.SELECT
  

     ActiveCell.Offset(1, 0).select       'move to the next row, 
wend

 

[jasonb75]

Your code makes no sense, try describing what you want to do where when a cell in column R = 2002.

 

[sb1989]

Your code makes no sense, try describing what you want to do where when a cell in column R = 2002.

I am trying to automate the calculating of distance in for a truck driver.

When a cell in column R = 2002 I want macro to run (this is the home store). When the macro runs it looks up numbers in a matrix and calculates distance for each store the driver delievers to. The beginning store will always be 2002, which is why everytime 2002 is in column R I want the macro to begin.
Here is an example of the trip:

[TABLE="width: 144"]
<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Start[/TD]
[TD="width: 64, bgcolor: transparent"]Store[/TD]
[TD="width: 64, bgcolor: transparent"]End[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2002[/TD]
[TD="bgcolor: transparent, align: right"]2005[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2006[/TD]
[TD="bgcolor: transparent, align: right"]2008[/TD]
[/TR]
</TBODY>[/TABLE]
and here is a sample of the matrix


[TABLE="width: 384"]
<COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY>[TR]
[TD="class: xl161, width: 64, bgcolor: #dbe5f1"]Store #[/TD]
[TD="class: xl161, width: 64, bgcolor: #dbe5f1"] [/TD]
[TD="class: xl161, width: 64, bgcolor: #dbe5f1, align: right"]2002[/TD]
[TD="class: xl161, width: 64, bgcolor: #dbe5f1, align: right"]2004[/TD]
[TD="class: xl161, width: 64, bgcolor: #dbe5f1, align: right"]2005[/TD]
[TD="class: xl161, width: 64, bgcolor: #dbe5f1, align: right"]2006[/TD]
[TD="class: xl161, width: 64, bgcolor: #dbe5f1, align: right"]2007[/TD]
[TD="class: xl161, width: 64, bgcolor: #dbe5f1, align: right"]2008[/TD]
[/TR]
[TR]
[TD="class: xl161, bgcolor: #dbe5f1"] [/TD]
[TD="class: xl162, bgcolor: #b8cce4"]Postal Code[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L6V 1B7[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]1747 countryside drive, brampton[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L6Z 1Y4[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L6T 4G8[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L7E 4Z8[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L7G 4B1[/TD]
[/TR]
[TR]
[TD="class: xl161, bgcolor: #dbe5f1, align: right"]2002[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L6V 1B7[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]0.00[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]12.40[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]7.56[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]6.74[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]25.40[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]18.35[/TD]
[/TR]
[TR]
[TD="class: xl161, bgcolor: #dbe5f1, align: right"]2004[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]1747 countryside drive, brampton[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]12.40[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]0.00[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]6.06[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]9.45[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]16.60[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]20.23[/TD]
[/TR]
[TR]
[TD="class: xl161, bgcolor: #dbe5f1, align: right"]2005[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L6Z 1Y4[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]8.08[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]6.60[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]0.00[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]10.76[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]21.33[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]16.34[/TD]
[/TR]
[TR]
[TD="class: xl161, bgcolor: #dbe5f1, align: right"]2006[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L6T 4G8[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]6.75[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]9.45[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]10.21[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]0.00[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]23.72[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]22.19[/TD]
[/TR]
[TR]
[TD="class: xl161, bgcolor: #dbe5f1, align: right"]2007[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L7E 4Z8[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]25.39[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]16.59[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]20.76[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]23.23[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]0.00[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]32.23[/TD]
[/TR]
[TR]
[TD="class: xl161, bgcolor: #dbe5f1, align: right"]2008[/TD]
[TD="class: xl162, bgcolor: #b8cce4"]L7G 4B1[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]18.36[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]20.23[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]16.53[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]22.16[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]32.26[/TD]
[TD="class: xl163, bgcolor: transparent, align: right"]0.00[/TD]
[/TR]
</TBODY>[/TABLE]

Please let me know if you need any more clarification.

 

[jasonb75]

That makes sense, I would assume from the formula in your code that the matrix range is $A$1:$H$8 (including the headings).

Would I be right to assume Start in column R, Store in Column S, End in Column T?

If there were more stores in the trip list, would the layout follow

Start Store End 
2002  2004
      2005 
      2006  2008

After finding the relevant data from the matrix, how and where should it appear in relation to the trip list?

 

[sb1989]

That makes sense, I would assume from the formula in your code that the matrix range is $A$1:$H$8 (including the headings).

Would I be right to assume Start in column R, Store in Column S, End in Column T?

If there were more stores in the trip list, would the layout follow

Start Store End 
2002  2004
      2005 
      2006  2008

After finding the relevant data from the matrix, how and where should it appear in relation to the trip list?

Yes you are right for the assumptions about the matrix range, Column R,S,T and the layout when more stores are in the list.

After finding the relevant data from the matrix, I just want it to appear in column S.

I am beginning to look into IF statements for the conditional start (when 2002 appears in column R), let me know if you think this is a good method to approach this.

Thanks for your help!

 

[jasonb75]

I am beginning to look into IF statements for the conditional start (when 2002 appears in column R), let me know if you think this is a good method to approach this.

As a formula approach or vba if statement?

A formula should work, I've already been thinking on that line, but it could be a bit messy, I'll look into it a bit more and see what I come up with.

 

[sb1989]

As a formula approach or vba if statement?

A formula should work, I've already been thinking on that line, but it could be a bit messy, I'll look into it a bit more and see what I come up with.

I was thinking VBA IF statement. Thanks again for the help

 

[jasonb75]

Just to keep you updated, I have an 'almost working' vba solution, because of the way your data is laid out in the sheet, I have encountered a scenario that I have not seen before so a bit of research is required.

As soon as I have it figured out, I'll post the code for you, but I might not have it done until morning (uk time).

 

[sb1989]

Just to keep you updated, I have an 'almost working' vba solution, because of the way your data is laid out in the sheet, I have encountered a scenario that I have not seen before so a bit of research is required.

As soon as I have it figured out, I'll post the code for you, but I might not have it done until morning (uk time).

Sounds great.

I do not want to complicate it anymore, but I will also be adding a loop to the formula so that the index/match for the stores (column S) will repeat until there is a blank (no more delieveries to make). You do not have to add that into the code, but if you could tell what loop (do while, until, for) you think would work best for this, that would be helpful so I am moving in the right direction.

Thank you for all your help and time Jason!