Solved: Another Array Question

[Djblois]

I am starting to find more uses for Arrays. Currently I add a few cell contents to the heading of worksheet like this:

.LeftHeader = "&""Arial,Bold""&12" & Range("A5").Value & Range("B5").Value _
& Range("C5").Value & Range("D5").Value & Range("E5").Value

However, I want to try to change it into an array. This is what I tried:

.LeftHeader = "&""Arial,Bold""&12" & Array(Range("A5:E5").Value)

However, this is giving me an error.

[Bob Phillips]

It would be nice, but I think you would need to do some messing with it to get it as a string here.

[Djblois]

xld,

What about this one I tried changing this:

comAddNumFormat Range("D1:D" & reorderFinalRow), "#,##0;[Red]-#,##0"
'Range("E1").FormulaR1C1 = "Inventory Available"
comAddNumFormat Range("E1:E" & reorderFinalRow), "#,##0;[Red]-#,##0"
Range("E1:E" & reorderFinalRow).Font.Bold = True
'Range("F1").FormulaR1C1 = "In Transfer"
comAddNumFormat Range("F1:F" & reorderFinalRow), "#,##0;[Red]-#,##0"
'Range("G1").FormulaR1C1 = "FDA Hold"
comAddNumFormat Range("G1:G" & reorderFinalRow), "#,##0;[Red]-#,##0"
'Range("H1").FormulaR1C1 = "Docked Qty"
comAddNumFormat Range("H1:H" & reorderFinalRow), "#,##0;[Red]-#,##0"
Range("H1:H" & reorderFinalRow).Font.Bold = True
'Range("I1").FormulaR1C1 = "In Transit"
comAddNumFormat Range("I1:I" & reorderFinalRow), "#,##0;[Red]-#,##0"
'Range("J1").FormulaR1C1 = "Earliest ETA"
comAddNumFormat Range("J1:J" & reorderFinalRow), "mm/dd/yy;@"
Range("J1:J" & reorderFinalRow).HorizontalAlignment = xlCenter
'Range("K1").FormulaR1C1 = "Total Supply"
comAddNumFormat Range("K1:K" & reorderFinalRow), "#,##0;[Red]-#,##0"
Range("K1:K" & reorderFinalRow).Font.Bold = True
'Range("L1").FormulaR1C1 = "Last-Mo Sales 1-30"
comAddNumFormat Range("L1:L" & reorderFinalRow), "#,##0;[Red]-#,##0"
'Range("M1").FormulaR1C1 = "AVG-Mo Sales 31-120"
comAddNumFormat Range("M1:M" & reorderFinalRow), "#,##0;[Red]-#,##0"
'Range("N1").FormulaR1C1 = "AVG-Mo Sales 121-210"
comAddNumFormat Range("N1:N" & reorderFinalRow), "#,##0;[Red]-#,##0"
'Range("O1").FormulaR1C1 = "AVG-Monthly Trnsfr-Out"
comAddNumFormat Range("O1:O" & reorderFinalRow), "#,##0;[Red]-#,##0"
'Range("P1").FormulaR1C1 = "Coverage [Months]"
comAddNumFormat Range("P1:P" & reorderFinalRow), "#,##0.00;-#,##0.00"
Range("P1:P" & reorderFinalRow).Font.Bold = True
'Range("Q1").FormulaR1C1 = "Lead-Time [Months]"
comAddNumFormat Range("Q1:Q" & reorderFinalRow), "#,##0.00;-#,##0.00"
'Range("R1").FormulaR1C1 = "INSTR'D"
comAddNumFormat Range("R1:R" & reorderFinalRow), "#,##0;[Red]-#,##0"

into this:

aColNumber = Array(4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 18)
For lngColNumber = LBound(aColNumber, 1) + 1 To UBound(aColNumber, 1)
comAddNumFormat Range(Cells(1, lngColNumber), Cells(reorderFinalRow, lngColNumber)), "#,##0;[Red]-#,##0"
Next

When I stepped throuch the code watching lngColNumber it counted from 1-12 instead of the values that I put in.

[Bob Phillips]

lngColNumber is the loop index, so it increments by 1 each iteration (unless you tell it otherwise). It starts at the Lbound + 1 so will be 1,2,3, ... etc.

I think you want



aColNumber = Array(4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 18)
For lngColNumber = LBound(aColNumber, 1) To UBound(aColNumber, 1)
comAddNumFormat Range(Cells(1, aColNumber(lngColNumber)), _
Cells(reorderFinalRow, aColNumber(lngColNumber))), "#,##0;[Red]-#,##0"
Next

[Djblois]

that worked perfectly