VBA, Array storing formulas

Question

I have a code that filters data, stores in an array and pastes it to another sheet. How come my array is pasting formulas and leaving me with blank values? The values Im storing in arrays are formulas but I want to paste the values.

It is supposed to store these values:

And paste them but instead pastes four cells of formulas. The formulas are =O10 which are blank and so on.

.Range("$A7:$AJ7").AutoFilter field:=35, Criteria1:="<>", Criteria2:="<>0", Criteria2:="<>-0"
    saLastRow = .Range("AI" & .Rows.Count).End(xlUp).Row

    Set sFiltered = Worksheets("BusinessDetails").Range("AI8:AI" & saLastRow).SpecialCells(xlCellTypeVisible) 'SA


    ReDim Arr(1 To sFiltered.Areas.Count)
    I = 0
    For Each V In sFiltered.Areas
        I = I + 1
        Arr(I) = V
        'Debug.Print I
   Next V
      
      sFiltered.Copy Sheets("Step 4 CM").Range("S10")

Thank you.

V will be a range and the range may not always be one cell. Area's are groups of consecutive cells. so Arr(I) = V is actually creating an array of arrays and not a simple 1d array. Also where is the code that deals with the Arr? Also copy/paste will paste the formula that it in the sheet copy area. You may want to use PasteSpecial to paste just the values. — Scott Craner
– Scott Craner, Commented Sep 21, 2020 at 19:53
Arr is probably unnecessary. Trying something like sFiltered.Copy Sheets("Step 4 CM").Range("S10").PasteSpecial Paste:=xlPasteValues but getting compile error. — excelguy
– excelguy, Commented Sep 21, 2020 at 20:08
Iterate the areas then then iterate the range that is produced, putting the value in the array. then mass load the array into the target. — Scott Craner
– Scott Craner, Commented Sep 21, 2020 at 20:13

Tragamor · Accepted Answer · 2020-09-23 09:19:49Z

The array intermediary is unnecessary to copy the data, and I wouldn't use the sFiltered range either. A direct Copy and PasteSpecial is all you need. For example:

Sub TestFilteredRangeCopy()
    With ThisWorkbook.Worksheets("Sheet1")
        Dim rTable As Range: Set rTable = .Range("C3:D11")
        
        rTable.Columns(2).SpecialCells(xlCellTypeVisible).Copy
        
        .Range("C13").PasteSpecial xlPasteFormulas
        .Range("D13").PasteSpecial xlValues
    End With
End Sub

If you did want to load an array then something like this would work:

Sub TestFilteredRangeCopy()
    With ThisWorkbook.Worksheets("Sheet1")
        Dim rTable As Range: Set rTable = .Range("C3:D11")
        Dim v As Range, i As Long: i = 1
        Dim arr() As Variant
        
        If rTable.Columns(2).SpecialCells(xlCellTypeVisible).Cells.Count > 0 Then
            ReDim arr(1 To rTable.Columns(2).SpecialCells(xlCellTypeVisible).Cells.Count)
            For Each v In rTable.Columns(2).SpecialCells(xlCellTypeVisible)
                arr(i) = v.Formula  ' .Value
                i = i + 1
            Next v
            .Range("C13").Resize(UBound(arr) - LBound(arr) + 1, 1) = WorksheetFunction.Transpose(arr)
        End If
    End With
End Sub

Collectives™ on Stack Overflow

VBA, Array storing formulas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related