Subtraction Of Series Of Cells' / Array Values

**PcMax** · 10-26-2012, 03:01 AM

Hi,

I have a column with the following values:
94,80
94,10
95,60
91,90
93,00
92,50
94,00
95,60
99,50
98,50
100,10
100,70

I'm using the following code

Code:

Option Explicit

Sub CTest()
    Dim r   As Long
    Dim ka, k()
    Dim i   As Long
    
    r = Range("C" & Rows.Count).End(3).Row + 1
    
    ka = Range("C3:C" & r)
    
    ReDim k(1 To UBound(ka, 1), 1 To 1)
    
    For i = 1 To UBound(ka, 1) - 1
        k(i, 1) = ka(i + 1, 1) - ka(i, 1)
    Next

    Range("O3").Resize(UBound(k, 1)) = k
End Sub

The data obtained are correct.

1 - Wonder if the procedure is properly optimized.

2 - If I use: r = Range("C" & Rows.Count).End(3).Row
Wonder how it must be interpreted

Thanks in advance

**Admin** · 10-26-2012, 08:48 AM

**Rick Rothstein** · 10-26-2012, 08:04 PM

Originally Posted by PcMax

I'm using the following code

Code:

Option Explicit

Sub CTest()
    Dim r   As Long
    Dim ka, k()
 Dim i   As Long
    
    r = Range("C" & Rows.Count).End(3).Row + 1
    
    ka = Range("C3:C" & r)
    
    ReDim k(1 To UBound(ka, 1), 1 To 1)
 
    For i = 1 To UBound(ka, 1) - 1
        k(i, 1) = ka(i + 1, 1) - ka(i, 1)
    Next

    Range("O3").Resize(UBound(k, 1)) = k
End Sub

I doubt if the time difference is measurable, but given what you are doing, you do not need the k array... just do your calculations back into the ka array itself...

Code:

Sub CTest()
    Dim r   As Long
    Dim ka
    Dim i   As Long
    
    r = Range("C" & Rows.Count).End(3).Row + 1
    
    ka = Range("C3:C" & r)
    
    For i = 1 To UBound(ka, 1) - 1
        ka(i, 1) = ka(i + 1, 1) - ka(i, 1)
    Next
    Range("O3").Resize(UBound(ka, 1)) = ka
End Sub

**PcMax** · 10-26-2012, 11:00 PM

Hi,

Thanks to the alternative code, useful to reduce the memory used in the cycle.

To avoid errors when there is a text, I added in the cycle:

For i = 1 To UBound(ka, 1) - 1
ka(i, 1) = Val(ka(i + 1, 1)) - Val(ka(i, 1))
Next

Is there an alternative to load the array: ka = Range("C3:C" & r) with only numeric values ?

I hope my request makes sense

**Rick Rothstein** · 10-26-2012, 11:09 PM

Originally Posted by PcMax

To avoid errors when there is a text, I added in the cycle:

For i = 1 To UBound(ka, 1) - 1
ka(i, 1) = Val(ka(i + 1, 1)) - Val(ka(i, 1))
Next

Is there an alternative to load the array: ka = Range("C3:C" & r) with only numeric values ?

Just double-checking... you are performing a mathematical operation on a column of cells that contain both numbers and non-numbers??? If so, can you show us an example of data in Column C that is like that and also show us the results you want to see from it?

**PcMax** · 10-26-2012, 11:27 PM

Hi,

I have an Excel spreadsheet in the column are expected values only.
There is however the possibility that the macro is interrupted due to a single value text.
Which solution is indicated I enter a:

Code:

On Error Resume Next

**Rick Rothstein** · 10-26-2012, 11:55 PM

Originally Posted by PcMax

I have an Excel spreadsheet in the column are expected values only.
There is however the possibility that the macro is interrupted due to a single value text.
Which solution is indicated I enter a:

Code:

On Error Resume Next

Straightening out your data so that non-numeric text values do not appear in Column C would be the best course of action but, assuming that is not possible for some reason, then I would go with the On Error method instead of the Val method only because the text values are retained and should stand out (they will be left-justified in the cell) in and among the numerical output (which is right-justified)... the Val method would produce incorrect numerical results for the text and be hard to spot given normal looking numbers would be output with it. One thing to note with either method... the value in the cell located in the row above the text cell's output will be incorrect.