Solved: Changing Arrays

[sooty8]

Hi All

The first array below I'm using and it works OK - I have had to change it to the 2nd Macro - question is there an easier way to change it without entering each change individually. I have to do this on a fairly regular basis.

[VBA]
Sub Macro1()
Dim arr, a
arr = Array(10, 12, 14, 16, 18, 20, 22, 24, 26, 28, 30, 32, 34, 36, 38, 40, 42, 44, 46, 48, _
50, 52, 54, 56, 58, 60, 62, 63, 65, 67, 69, 71, 72, 74, 76, 78, 80, 82, 84, 86, 88, 90, 92, 94, 96, 98, 100, 102, 104, _
106, 108, 110, 112, 114, 116, 118, 120, 122, 124, 126, 128, 130, 132, 134, 136, 138, 140, 142, 144, 146, 148, 150, _
152, 154, 156, 158, 160, 162, 164, 165, 167, 169, 170, 171, 173, 175, 177, 179, 181, 183, 185, 186, 188, 190, 192, 194, _
196, 198, 200, 202, 204, 206, 208, 210, 212, 214)
Sheets("Invoice").Select
For Each a In arr
With Range("M" & a)
.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
.Interior.Color = 10079487
End With
Next
End Sub
[/VBA]


[VBA]
Sub Macro2()
Dim arr, a
arr = Array(10, 11, 13, 15, 16, 18, 20, 22, 24, 26, 28, 30, 32, 33, 37, 39, 41, 43, 45, 47, _
49, 51, 53, 55, 57, 59, 61, 63, 65, 67, 69, 71, 73, 75, 77, 79, 81)
Sheets("Invoice").Select
For Each a In arr
With Range("M" & a)
.FormulaR1C1 = "=SUM(R[-1]C[-9]:RC[-1])"
.Interior.Color = 10079487
End With
Next
End Sub
[/VBA]

Many Thanks

Sooty 8

[mdmackillop]

You could enter an "x" in each row you want in your array, some code like this will create the string you can copy and paste to create your revised array.
[vba]
Sub Nums()
Dim txt As String, cel As Range
For Each cel In Columns("O").SpecialCells(xlCellTypeConstants)
txt = txt & cel.Row & ", "
Next
txt = Left(txt, Len(txt) - 2)
Range("O1") = txt
End Sub

[/vba]
It may be possible to do this directly from your data, if the key rows are identifiable.

[sooty8]

Hi MD

I had the columns & rows available in the data just ran the sub worked perfectly
that will save me plenty of time.

Many Thanks

Sooty8

[mdmackillop]

If you can create the array from your data, that suggests it is not required and that the formulae can be entered directly by a modification of the code.

[sooty8]

Hi MD

As usual I explained it wrong!! idiot me - from Column "O" within in the sheet all the Cells are empty - I put a CommandButton on the sheet assigned your Macro (Nums) to it and as I said before it worked perfectly thanks for your comment - however I'm happy with what you sent me and I understand how it works - many thanks

Sooty8