PDA

View Full Version : [SOLVED] Adding Two Arrays in VBA



henrik2h
05-13-2015, 01:50 PM
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
05-13-2015, 02:19 PM
Without a file I have no idea what you are after.

Paul_Hossler
05-13-2015, 05:05 PM
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
05-14-2015, 01:10 AM
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
05-14-2015, 06:28 AM
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
05-14-2015, 06:48 AM
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
05-14-2015, 08:52 AM
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
05-14-2015, 11:01 AM
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
05-14-2015, 12:42 PM
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
05-14-2015, 01:39 PM
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
05-14-2015, 02:52 PM
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
05-14-2015, 03:18 PM
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
05-14-2015, 04:00 PM
grrr

Paul_Hossler
05-14-2015, 04:34 PM
@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
05-14-2015, 04:58 PM
@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
05-14-2015, 11:12 PM
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
05-15-2015, 01:05 AM
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
05-17-2015, 02:41 PM
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
05-18-2015, 12:56 AM
See the attachment