Macro Efficiency

[dturgel]

Hi,

I saw in JWalk's 2013PPw/VBA the following macro:

Sub Macro5()
ActiveCell.Resize(,6)=Array("Jan","Feb","Mar","Apr","May","Jun")
End Sub

I then wanted to reduce the following code in similar fashion:

Sub AuthorizedShare()
        With ActiveWorkbook.Worksheets("Summary Quoted")        
        .Range("T103").Formula = "=MCTSI2QuotedShareL1"
        .Range("T104").Formula = "=MCTSI2QuotedShareL2"
        .Range("T105").Formula = "=MCTSI2QuotedShareL3"
        .Range("T106").Formula = "=MCTSI2QuotedShareL4"
        .Range("T107").Formula = "=MCTSI2QuotedShareL5"
        .Range("T117").Formula = "=MCTSI2QuotedShareL1"
        .Range("T118").Formula = "=MCTSI2QuotedShareL2"
        .Range("T119").Formula = "=MCTSI2QuotedShareL3"
        .Range("T120").Formula = "=MCTSI2QuotedShareL4"
        .Range("T121").Formula = "=MCTSI2QuotedShareL5"
        End With
End Sub

So I tried this:

Sub AuthorizedShare()
   With ActiveWorkbook.Worksheets("Summary Quoted")
     .Range("T103:T107").Formula = Array("=MCTSI2QuotedShareL1", "=MCTSI2QuotedShareL2", "=MCTSI2QuotedShareL3", "=MCTSI2QuotedShareL4", "=MCTSI2QuotedShareL5")
    End With
End Sub

Problem is in each cell T103:T107 I got "=MCTSI2QuotedShareL1" instead of the five separate ones I wanted. Any idea how I can change the Array formula coding to get it right?

Thanks!

Daniel

 

[Jerry Sullivan]

Hi Daniel, The array needs to be transposed if it is to entered as a single column in the worksheet instead of a single row.

To modify JWalk's example it would look like...

ActiveCell.Resize(6) = Application.Transpose(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun"))

 

[jolivanes]

Just a quick question.
Are the L1, L2, L3 etc Cell addresses?
If so, I think you need an exclamation mark (!) before the Cell Addresses like "=MCTSI2QuotedShare!L1"

 

[jolivanes]

Just for the heck of it.

Sub Maybe()
    Dim adrArr, shareArr, j As Long
    adrArr = Array("T103", "T104", "T105", "T106", "T107", "T117", "T118", "T119", "T120", "T121")
    shareArr = Array(1, 2, 3, 4, 5, 1, 2, 3, 4, 5)
    For j = LBound(adrArr) To UBound(adrArr)
        Range(adrArr(j)).Formula = "=MCTSI2QuotedShare!L" & shareArr(j)
    Next j
End Sub

 

[dturgel]

Jerry, many thanks! I combined your formula with mine to get the following which worked:

.Range("T103:T107").Formula = Application.Transpose(Array("=MCTSI2QuotedShareL1", "=MCTSI2QuotedShareL2", "=MCTSI2QuotedShareL3", "=MCTSI2QuotedShareL4", "=MCTSI2QuotedShareL5"))

Really appreciate it!

Daniel