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

 

[jasonb75]

For some reason your last post didn't show up when I refreshed the forum, reading back over you posts again, I'm not entirely sure if this is exactly what you want, or if I need to break it down and move things around a bit.

What this will do is sum all points of the specified trip and post the total distance. Using the example,

Start Store End 
2002  2005  
      2006 2008

The code will post the total distance, 2002 to 2005, 2005 to 2006 and 2006 to 2008, 8.08 + 10.21 + 22.16 = 40.45

Currently this result will be posted in column U, same row as the entry 2002, this can be changed as needed.

As always, I would advise testing the code on a copy of your workbook before applying it to your original.

Option Explicit

Sub test2()
Dim c As Range, subrng As Range, arr2d() As Variant, arr1d() As Variant, n As Long, x As Long, y As Long, z As Long, tmpsum As Double
For Each c In Intersect(Range("R:R"), ActiveSheet.UsedRange)
    If c.Value = 2002 Then
        If IsNumeric(c.Offset(, 2)) And Len(c.Offset(, 2)) > 0 Then
            arr2d = c.Resize(1, 3)
        Else
                arr2d = Range(c, c.Offset(, 2).End(xlDown))
        End If
            For x = LBound(arr2d, 1) To UBound(arr2d, 1)
                For y = LBound(arr2d, 2) To UBound(arr2d, 2)
                    If IsNumeric(arr2d(x, y)) And Len(arr2d(x, y)) > 0 Then
                        ReDim Preserve arr1d(0 To z)
                        arr1d(z) = arr2d(x, y)
                        z = z + 1
                    End If
                Next
            Next
        For n = LBound(arr1d) To UBound(arr1d) - 1
            tmpsum = tmpsum + Evaluate("sumproduct(($C$1:$H$1=" & arr1d(n) & ")*($A$3:$A$8=" & arr1d(n + 1) & ")*$C$3:$H$8)")
        Next
        c.Offset(, 3) = tmpsum
        tmpsum = 0
        z = 0
        Erase arr1d
    End If
Next
End Sub

Hope this is of some use.

 

[sb1989]

Thank you for the help! this was great help! I can fiddle around with this!