PDA

View Full Version : [SOLVED] Saving worksheet range without formula to array



nikki333
04-22-2018, 11:27 AM
Hi Folks

Trying to save an entire worksheet to an array (in order to trim the values), however, excluding cells that contain any formula.

So, my try was like so:



For i = numSektionProdukte + 3 To numZeilenProdukte
For j = 3 To numSpaltenProdukte
If Not .Cells(i, j).HasFormula Then
arrProdukte(i - (numSektionProdukte + 2), j - 2) = .Cells(i, j) 'The first i = 9 and the first j is 3, so the first arrProdukte(1,1) would be 1,1 (Option base 1)
End If
Next j
Next i

However, as soon as it comes to saving any value into the array, i get an error 9, Index out the range...

Any ideas :)

Bob Phillips
04-22-2018, 02:47 PM
Dim you dimension arrProdukte before the loop? If so, to what size? It worked for me when I did so, but an alternative approach is load the array and clear the formula values


arrProdukte = .Range(.Cells(numSektionProdukte + 3, 3), .Cells(numZeilenProdukte, numSpaltenProdukte))
For i = numSektionProdukte + 3 To numZeilenProdukte

For j = 3 To numSpaltenProdukte

If .Cells(i, j).HasFormula Then

arrProdukte(i - (numSektionProdukte + 2), j - 2) = Empty
End If
Next j
Next i
End With

nikki333
04-23-2018, 01:50 AM
Thank you that works