Hi Trond,

The file download does not work, I can't see your code.

But my guess is that it's not the For Next loop, if you use Do...Loop you will have the same problem, because not the loop is slowing down the code, it's the writing to cells operation that takes time.

Any cell operation, even selecting cells , not just writing to cells will slow down the loop. The only way out is to reduce the number of Write to cells operations.

I usually load the range into an array, do the calculations and write the new values in array, then paste the modified array back into the original range.

It will be only 1 write operation.

dim ArrData as Variant

ArrData=Range(A1:D100) 'this will not be a zero based array, the lower bounds will be 1: Lbound(ArrData,1), Lbound(ArrData,2)

'loop through array, not sheet cells:

For i=Lbound(ArrData,1) to UBound(ArrData,1)

'make calculations, write results in last column of the array:

ArrData(i,UBound(ArrData,2))=ArrData(i,1) + ArrData(i,2)

'you can even read cells for calculation from sheet, not from array:
ArrData(i,UBound(ArrData,2))=Range("A" & i)+Range("B" & i)

'note: ArrData(i,1) refers to the same cell from Range("A" & i). If your range does not start from column A, like Range("S1:T15"), in this case ArrData(i,1) refers to column S, which is the first column in the range. Same for rows, they will not match if the range does not start from row 1 of the sheet.

Next

After you finish calculations, just write data back to sheet:

Range(A1:D100).Value=ArrData

If used in a Loop, use Erase ArrData to clear the array before loading data.

Excel matrix uploaded (again), with corresponding VBA loop procedure in TXT file attached as well.

Colors in Excel file;
- yellow sheet = input (3 of 12 months in total illustrated)
- pink sheet = control panel, important for easy adjustment of underlying calculations
- orange sheet = output

Not sure what you expect me to do.

I provided a detailed description of what you need to do, have you tried that?

(the text file is still not downloadable, not sure why, will have to check)

Hi Catalin,

Unfortunately I'm not into arrays like you are. But see what you say about "writing takes time" to Excel, using If Then and Do While procedures.

Do you think it's same time consume existent when using WorksheetFunction's in VBA, like Index and Match combination, testing a range for cells where values <>0 , and giving me Column Number in return for each cell where values <>0? Perhaps used in combination For Each Cell in Cells procedure?

I'm hoping some kind of effective "Jumping Jack" formula/procedure would help me implement previous post, if not your array more easy to understand/use.

Btw;

Attaching a sample from working file, with same Lart types as in matrix example with some amounts, for a selection of 3 random test employee numbers.

As you see, code is running slow because of the loop (I'm not just having a slow computer).

Can you upload the code you are using?

Try archiving the text file.

VBA attached as zip file instead of txt.

Don't know why failing to upload attachments, but seems to happen when editing post.

I've made my first Array now, and understand the point with it. However a bit unsure if this is a 1D or 2D reference: Arr = Range("B5:O5)  , I suppose it's a 1D?

Also having trouble with this relative reference NOT working: Set rg = Sheets(Sh.Name).Range(Cells(i, 46), Cells(i, b)) 'i is number of employees (in a loop)

'-- Array code goes like this (in debug.print mode):

Dim dynLart As Long 'number of dynamic LART
dynLart = Sheets("dLart").Cells(Rows.Count, 1).End(xlUp).Row

Dim a As Long 'just a dummy variable

Dim b As Long 'number of columns in Uboand
b = 46 + (dynLart - 4)

Dim Arr As Variant

'supposed to give on row reference for each i: Arr = Sheets(Sh.Name).Range("AT5:BZ5").Value :
Set rg = Sheets(Sh.Name).Range(Cells(i, 46), Cells(i, b))

Arr = rg.Value

Debug.Print "a", "b", "Value"

For a = LBound(Arr, 1) To UBound(Arr, 1)
For b = LBound(Arr, 2) To UBound(Arr, 2)
If Arr(a, b) > 0 Then
Debug.Print a, b, Arr(a, b)
End If
Next b
Next a

Hi Trond,

When you assign a range to an array, it will always have 2 dimensions  (rows and columns)

Use Arr=Sh.Range(....), not Set Arr=Sh.Range(...) (Arr will be a range instead of an array of values)

No idea why you said that it works very slow, because the code is not functional at all. Do you have a functional version?

You have to start a loop through worksheets first:

Dim Sh as worksheet (your code is missing this declaration)

For each Sh in thisworkbook.worksheets

'(now you can use the Sh variable)

'exclude some sheets from the loop:

If not Sh.Name like "Output" and not Sh.Name like "dLart" then

'start looping through current sh sheet

For i= to 1000

Next i

End If

Next Sh

Hi Catalin,

Thx for being here, trying to help me a bit.. Also know just part of code is uploaded, entire workbook is well over 20mb and to much to upload. But working and functional with Sh declared as worksheet too (as public variable).

Therefore just pasted the problem code here, witch still not giving any values into: Arr = Sheets(Sh.Name).Range(Cells(i, 46), Cells(i, b)).value

I first wrote this as a range with Set rg = .... , and the Arr = rg.value , witch I supposed was doing same thing.

According to this link: https://excelmacromastery.com/excel-vba-array/ it seems there is a both 1D and 2D arrays. But I have to get hold on the column position in array when looping through after testing values <>0 inside array, as column position will be my input further down in the loop procedure. Therefore I don't know if possible to use 1D array, as the column seems to "show itself" in the second part of Lbound/Ubound loop.

What is very strange is that this fixed (not the relative) array works, reading values inside array:  Arr = Sheets(Sh.Name).Range("AT5:BZ5").Value

There are 1 D , 2 D, and multidimensional arrays, no doubt about this.

If an array comes from a range, it will have 2 dimensions, even if you use 1 column. Arr=Sh.Range(A1:A100) has 1 column and 100 rows, and that makes 2 dimensions: rows and columns. Even a single cell still has 2 dimensions. It will be a base 1 array, not a zero based array.

Not seeing anything strange in Arr = Sheets(Sh.Name).Range("AT5:BZ5").Value

cell AT5 will become Arr(1,1), AT6 is Arr(2,1) and so on.

Catalin Bombea said
Not seeing anything strange in Arr = Sheets(Sh.Name).Range("AT5:BZ5").Value

Point is, the fixed reference is working (for i = 5, but then no more loop becuase...it's fixed).

However as said, same reference but relativ is not working: Arr = Sheets(Sh.Name).Range(Cells(i, 46), Cells(i, b)).value

where b=BZ in this example

So why is not relative reference working?

Hi Trond,

What is not working?

Sub test()
Dim arr As Variant
Dim i As Byte
For i = 1 To 2
arr = Range(Cells(i, 1), Cells(i, 2)).Value
MsgBox  arr(1, 1)  & ", " &  arr(1, 2)

Next
End Sub

Put some values in cells A1:B2, and you will see them in the pop up messages if you use the above code.

It does not make sense to go through sheet ranges row by row and read the values into an array then read the array. Are you going to write results to sheet in your loop, from the array? You will still write to sheet at each row iteration, it will be no increase in speed. The point is to write to sheet in a single operation, outside the loop (after you finish iterating through rows), not writing to cells within the loop.

Read all sheet values into an array from the beginning, then loop through the array, not in sheet.

The above code should be:

Sub test2()
Dim arr As Variant
Dim i As Byte
arr = Range(Cells(1, 1), Cells(2, 2)).Value
For i = LBound(arr, 1) To UBound(arr, 1)
MsgBox arr(i, 1) & ", " & arr(i, 2)

arr(i,1)=24

arr(i,2)=35 'change the values from array
Next

'After you finish calculations, paste the array back to sheet:

Range(Cells(1, 1), Cells(2, 2)).Value=arr
End Sub

So, you grab all data from sheet in a single operation, loop through array, make calculations, paste data back to sheet. Will be much faster than writing calculation results cell by cell.

Catalin Bombea said
Hi Trond,

What is not working?

It does not make sense to go through sheet ranges row by row and read the values into an array then read the array. Are you going to write results to sheet in your loop, from the array? You will still write to sheet at each row iteration, it will be no increase in speed. The point is to write to sheet in a single operation, outside the loop (after you finish iterating through rows), not writing to cells within the loop.

Read all sheet values into an array from the beginning, then loop through the array, not in sheet.

Hi Catalin,

Thanks for nice code and information, tried it and working. What's still not working; this isn't: Arr = Sheets(Sh.Name).Range(Cells(i, 46), Cells(i, b)).value , where i and b are known input values. Seems to be a "reading problem" in Excel somehow, from range where all values are made from formula SUMIFS.

Point with 1D array for each loop, is my workbook filters away some employee's (i) when running, regarding their status (active/not a month). Therefore I don't think having all salary types in one array at same time will be a good ide. But the matrix is about 90% zeros and 10% values <>0, so filtering arr<>0 each loop could still make a time benefit. Only those 10% values <>0 is supposed to let trough the filter and into my waiting VBA code for processing.

Trond Liavik said
Therefore I don't think having all salary types in one array at same time will be a good ide.

Why?

It will be 1000 times faster then any other way...

This code:

Is still inefficient, as it writes to sheet at each iteration.

"What's still not working; this isn't: Arr = Sheets(Sh.Name).Range(Cells(i, 46), Cells(i, b)).value , where i and b are known input values. Seems to be a "reading problem" in Excel somehow, from range where all values are made from formula SUMIFS."

Provide an example file with such a code not working. I just gave you samples of similar functional codes.