Unable to set FormulaArray for a Transpose solution

[ibud00]

I have an 'array' that has been convoluted into a single column. Each of the original rows is now in repeating blocks of 7 vertically contiguous cells. I can manually convert therm with a series of Transpose statements derived from a recorded macro. I got the "Unable to set the FormulaArray property of the Range class" when I attempted to reduce the multiple statements into a loop. Please see below"

    Dim iVR As Integer ' Current row for column A
    Dim iHR As Integer ' Current row for columns B:H
    Dim iLstRow As Integer
    
    iHR = 2
    iLstRow = ActiveSheet.UsedRange.Row - 1 _
 ____ActiveSheet.UsedRange.Rows.Count
    
    For iVR = 7 To iLstRow Step 7
    Range("B" & iHR & ":H" & iHR).Select
    Selection.FormulaArray = "=TRANSPOSE(R[ivr-2]C[-1]:R[ivr+4]C[-1])"
    iHR = iHR + 1
    Next
    
    '   Below is the  first of the recorded macro statements
    '    Range("B2:H2").Select
'    Selection.FormulaArray = "=TRANSPOSE(R[5]C[-1]:R[11]C[-1])"
'    Range("B3:H3").Select


End Sub

 

[Marcelo Branco]

Maybe this...

Sub aTest()
    Dim iVR As Long ' Current row for column A
    Dim iHR As Long ' Current row for columns B:H
    Dim iLstRow As Long
    
    iHR = 2
    iLstRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For iVR = 7 To iLstRow Step 7
        Range("B" & iHR & ":H" & iHR).FormulaArray = _
           [COLOR=#0000ff] "=TRANSPOSE(R[" & iVR - iHR & "]C[-1]:R[" & iVR - iHR + 6 & "]C[-1])"
[/COLOR]        iHR = iHR + 1
    Next
End Sub

Hope this helps

M.

 

[Marcelo Branco]

Or a simpler code

Sub Simpler()
    Dim iVR As Long ' Current row for column A
    Dim iHR As Long ' Current row for columns B:H
    Dim iLstRow As Long
    
    iHR = 2
    iLstRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For iVR = 7 To iLstRow Step 7
        Range("B" & iHR & ":H" & iHR).FormulaArray = _
            [COLOR=#008000]"=TRANSPOSE(A" & iVR & ":A" & iVR + 6 & ")"[/COLOR]
        iHR = iHR + 1
    Next
End Sub

M.

 

[ibud00]

Right On! that did it. I'm using the simpler version. The recorded version messed up the mind of someone with a minimum of understanding. Again, thanks.

Bud