Adding Two Arrays in VBA

[henrik2h]

Hi
I am trying to add two arrays with numbers in VBA and finally I will paste/export that summed up array to an excel sheet.

It is possible, I am not experience in VBA? Basically I want to do the same as taking a range and pastespecial add values onto another range but only keep the range in VBA to save time.

Here is my code if it helps. Get error Runtime 424 Object required. Any help appreciated.

Dim i As Integer, j As Integer, vbAntal_kontrakt As Integer, vbTenant_CF As Range, vbPortfolio_CF As Variant, vbTenant_record As Range    Sheets("CASH_FLOW").Range("CFport_clear").ClearContents
    j = 0
    vbAntal_kontrakt = Sheets("Input_tenant_specific").Range("Antal_kontrakt").Value
    For i = 1 To vbAntal_kontrakt
        j = i + 7
        Sheets("Input_tenant_specific").Range(Cells(j, "A").Address, Cells(j, "BV").Address).Copy
        Sheets("Input_tenant_specific").Range("A4:bv4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False 'Copy each tenant's data to the top row, which then generates monthly cash flows
        Set vbTenant_CF = Sheets("Lease_analysis").Range("Tenant_CF")
        Set vbPortfolio_CF = vbPortfolio_CF.Value + vbTenant_CF.Value 'THIS IS NOT WORKING
    Next i
vbPortfolio_CF.Copy Destination:=Sheets("CASH_FLOW").Range("Portfolio_CF") = vbPortfolio_CF.Value 'DO NOT KNOW IF THIS IS WORKING

[snb]

Without a file I have no idea what you are after.

[Paul_Hossler]

Like snb said, it's hard to be specific without having a sample sheet with the before and after.

Since you asked about arrays ...

As a GUESS and a suggestion, this takes two ranges and put them into arrays, then adds them togeather into a third array, and then writes the third array to a worksheet

Option Explicit
Sub Guessing()
    Dim vFirst As Variant, vSecond As Variant
    Dim aSum() As Double
    Dim i As Long
    
    
    
    'need to use Transpose to have one dimensional array
    vFirst = Application.WorksheetFunction.Transpose(ActiveSheet.Range("A1:A10").Value)
    vSecond = Application.WorksheetFunction.Transpose(ActiveSheet.Range("C1:C10").Value)


    'make the Sum array same size as the data array
    ReDim aSum(LBound(vFirst) To UBound(vFirst))


    'sum and put in Sum array
    For i = LBound(vFirst) To UBound(vFirst)
        aSum(i) = vFirst(i) + vSecond(i)
    Next I

    'put sum array on to sheet as row
    'need to have enough cells
    ActiveSheet.Range("K1").Resize(1, UBound(aSum)).Value = aSum

End Sub

[Aflatoon]

You haven't assigned a range to vbPortfolio_CF, which is why you get the 424 error. But even having done that you won't be able to add the two ranges like that. Once you have assigned a valid range to that variable, you can use:

    With vbPortfolio_CF
        .Value = .Parent.Evaluate(.Address & "+'" & vbTenant_CF.Parent.Name & "'!" & vbTenant_CF.Address)
    End With

[Paul_Hossler]

More specifically, you use 'Set' to assign to an Object

Set vbPortfolio_CF = vbPortfolio_CF.Value + vbTenant_CF.Value 'THIS IS NOT WORKING

As Aflatoon says, once the referenced object has been Set you can refer to it

Set vbPortfolio_CF = activeSheet.Range("A1")

vbPortfolio_CF.value = vbPortfolio_CF.Value + vbTenant_CF.Value

I am trying to add two arrays with numbers in VBA and finally I will paste/export that summed up array to an excel sheet.

Also, you specifically said 'Arrays'. Did you mean a group of worksheet cells? There is a VBA array that can be used (my first answer) but is probably unnecessary for what you want to do

[henrik2h]

Thank you guys for your efforts. It is hard to extract the relevant parts of the file and I don't want to send the whole thing.

Apparently my question was not that easy to understand, sorry for that I am new to this.

I am trying to do the following:
On sheet Lease_analysis I calculate a number of cash flows generated from input number 1. I get a range of output values (say A1:AB20) "Tenant_CF" on sheet "CASH_FLOW" in code above.
I take this range and pastespecial add values into an equal size range on a summary sheet (range Portfolio_CF in code above).
I then take input number 2 and calculate a new range of output values. This range is copied and pastespecial again onto the same range on the summary sheet (CASH_FLOW).
After all input is looped through I have the total sum on the summary sheet ("CASH_FLOW" in code above)

Instead of pastespecial onto the CASH_FLOW sheet for each input cash flows I wanted to store the values in a range within VBA and then only at the end paste the range onto the summary sheet (CASH_FLOW). So I don't want to reference vbPortfolio_CF to a range on a sheet, I want it to be a stored range in VBA. Does it make sense?

The ranges are always the same size.

[Aflatoon]

So I don't want to reference vbPortfolio_CF to a range on a sheet, I want it to be a stored range in VBA. Does it make sense?

No - a Range must exist on a sheet. I don't really see the issue, since you have a range on the sheet - the one you are pasting to. If you assign that to the variable, you can use the code I mentioned to add the new values to the existing ones.

[Paul_Hossler]

On sheet Lease_analysis I calculate a number of cash flows generated from input number 1. I get a range of output values (say A1:AB20) "Tenant_CF" on sheet "CASH_FLOW" in code above.
I take this range and pastespecial add values into an equal size range on a summary sheet (range Portfolio_CF in code above).
I then take input number 2 and calculate a new range of output values. This range is copied and pastespecial again onto the same range on the summary sheet (CASH_FLOW).
After all input is looped through I have the total sum on the summary sheet ("CASH_FLOW" in code above)

Instead of pastespecial onto the CASH_FLOW sheet for each input cash flows I wanted to store the values in a range within VBA and then only at the end paste the range onto the summary sheet (CASH_FLOW). So I don't want to reference vbPortfolio_CF to a range on a sheet, I want it to be a stored range in VBA. Does it make sense?

The ranges are always the same size.

Too many unknowns:

What is "input number 1"?
What is "input number 2"?
What is a "stored range in VBA"?
What is "all input is looped through"?

etc.

It really would be faster to help if you could mockup a small before and after workbook clearly showing what you're starting with and where you want to end up, as well as a couple of the steps in your workflow

[SamT]

Sumpin similir 2 ???

For j = 1 to 7
For i = 1 to Range("A1:BV1").Count
CF(i) = CF(i) + .Cells(j, i)
Next
Next
Range(X) = CF

IMO "VBA Range" = Array or other variable.

[henrik2h]

Paul, yes my terminology is probably way off and that makes it harder to explain/understand

Yes I know very little of VBA and that makes it harder to see if you answered the question I meant to ask, or if it was only answering something else, your code did not make me understand unfortunately, perhaps because it is not possible to do but you provided me an alternative solution. It was transposing, resizing and then looping so I didn't realize how to actually make use of it.

Yes I am probably in the wrong forum

But I will try one last time, writing new example code and make a file will take me hour(s) and I don't see how that would help since I don't know how to write that piece of code in the first place.

I see that some of you have suggested a loop that add values one by one, is that the only way to do it?

Lets try and see if I can make another example instead.
Sheet1!A1:A4 i.e. has all ones i.e. an array of values (1,1,1,1) lets define (yes there is probably another term for this in VBA terms) this as VBA_Array1

Sheet1!B1:B4 all has number two, i.e. (2,2,2,2), lets define as VBA_Array2

How do I do this: VBA_Array3=VBA_Array1 + VBA_Array2 with VBA

VBA_Array3 should hold values (3,3,3,3), however so far VBA_Array3 only exist in VBA not on a worksheet
I don't want to loop through cell by cell, that will most likely take longer time than my original way of using Pastespecial on a worksheet.
I don't want VBA_Array3 to be placed on a worksheet until I say so. How do I place it there?
The arrays are always the same size.

[Paul_Hossler]

How do I do this: VBA_Array3=VBA_Array1 + VBA_Array2 with VBA

The ONLY way to do it is by looping

For I = LBound(VBA_Array1) To UBound(VBA_Array1)
    VBA_Array3(I) = VBA_Array1(I) + VBA_Array2(I)
Next I

I don't want to loop through cell by cell, that will most likely take longer time than my original way of using Pastespecial on a worksheet.

You can use the technique in #3 to put the values in a Range into a Variant, which is in essence an Array at that point

I don't want VBA_Array3 to be placed on a worksheet until I say so. How do I place it there?

As it is now, the data in a VBA array will go away when you close Excel or the workbook.

Sheet1!A1:A4 i.e. has all ones i.e. an array of values (1,1,1,1) lets define (yes there is probably another term for this in VBA terms) this as VBA_Array1

I think that this is a Range on the Worksheet
You can make it in to a VBA array using the technique in Post#3, use in it calculations, and then put the data back on a worksheet if you want

[SamT]

I don't want to loop through cell by cell, that will most likely take longer time than my original way of using PasteSpecial on a worksheet.

Not so. All of excel is in memory as a multidimensional array. Updating the screen (monitor) is what takes time.

A small loop like in my previous post, if j and i are both =< 100 would run before you could notice.

How do I place it there?

PAul_Hossler is better than me, but IIRC

Range("A1:BV1"). PasteSpecial(Transpose) arrTotalSum

PAUL!
Tell me again how to paste an array into a Range?

[Paul_Hossler]

grrr

[Paul_Hossler]

@Sam -- Post#3 towards the end (I think I learned it from the much more experienced guys here)

Here's an extract of the way I do it, might not be the best, but I'm used to it

Option Explicit
Sub SamT()
    Dim v0 As Variant, v1 As Variant
    
    'makes v0 a 1D array (1 to 9)
    v0 = Application.WorksheetFunction.Transpose(ActiveSheet.Range("A1:A9").Value)
    'makes v1 a 2D array (1 to 9, 1 to 1) -- not used, just for indo
    v1 = ActiveSheet.Range("A1:A9").Value
    
    '1 row x N columns
    ActiveSheet.Range("C1").Resize(1, UBound(v0)).Value = v0
    
    
    'N rows x 1 column
    ActiveSheet.Range("C1").Resize(UBound(v0), 1).Value = Application.WorksheetFunction.Transpose(v0)
    
    
    Stop

End Sub

PS -- It's nice to see you back

[Paul_Hossler]

@henrik2h -- as SamT said, the loop will not be the performance issue

I would suggest forgetting about messing with arrays and use Excel as Excel. Keep it as simple as possible

As example, this macro does not use arrays, Transpose(), or anything too complicated. It just puts data into worksheets where its supposed to go

Since there is no sample workbook you can provide, all anyone can do is suggest possible techniques for you to modify

Option Explicit

Sub OneMoreTime()

    Dim iRow As Long, iCol As Long
    Dim R1 As Range, R2 As Range
    
    Set R1 = Worksheets("Sheet1").Cells(1, 1).CurrentRegion
    Set R2 = Worksheets("Sheet2").Cells(1, 1).CurrentRegion
    
    Application.ScreenUpdating = True
    
    For iCol = 1 To R1.Columns.Count
        For iRow = 1 To R1.Rows.Count
            Worksheets("Sheet3").Cells(iCol, iRow).Value = R1.Cells(iRow, iCol).Value * R2.Cells(iRow, iCol).Value
        Next iRow
    Next iCol
        
        
    Application.ScreenUpdating = False
End Sub

Look at the attachment and follow the code

[henrik2h]

Thank you, this is exactly what I am after. Looping is the only way and a generic small code to do it.

Thank you both!

[snb]

Sub M_snb()
 range("A1:AB20").name="snb_001"

 for j=1 to 10
  [snb_001]=[index(snb_001+snb_001,)]
 next

  sheets(2).cells(1).resize(ubound(sn),ubound(sn,2))=[snb_001]
End Sub

To see more:

http://www.snb-vba.eu/VBA_Arrays_en.html

[henrik2h]

Are you actually adding the array multiple times, or exponentially really? However, I can't get that piece of code to work (Run-time error 13, Type mismatch). It looks like adding two arrays is possible, what does that last comma do in this line and would you be able to explain a little bit how that works?

[snb_001]=[index(snb_001+snb_001,)]

I had a look at your homepage and believe I will learn a lot from it, thanks!

[snb]

See the attachment