I created a sheet where users inputs a number of data to define the spacing and the number of elements inside a surface (they are pile that will be driven in the soil). The macro then calculate the position of every element within the surface and order it in column. Each column then have a variable number of element, each assigned a specific value of X and Y. I now wish to plot these elements using a scatter plot. I try to set an array with Range defined by the X and Y column calculated using the macro in a for loop. However, I keep getting error and I cannot seem to add the range value and stock them in array to do my graph. I don't understand why.

I did search over the site, did not find specifics answer to my problem (or was not able to understand maybe?). Been looking for two days using google without success either.

Dim e As Integer
e = Cells(6, 1).Value
Dim Nbr_Range() As Long, size As Integer, w As Integer
size = Cells(6, 1).Value
ReDim Nbr_Range(size)
For i = 1 To e
Cells(3, 23 + 2 * i).Value = "X"
Cells(3, 24 + 2 * i).Value = "Y"
Cells(2, 23 + 2 * i).Value = i
Range(Cells(2, 23 + 2 * i), Cells(2, 24 + 2 * i)).Merge
Cells(3, 23 + 2 * i).HorizontalAlignment = xlCenter
Cells(3, 24 + 2 * i).HorizontalAlignment = xlCenter
Cells(2, 23 + 2 * i).HorizontalAlignment = xlCenter
Cells(3, 23 + 2 * i).Borders(xlEdgeBottom).LineStyle = xlContinuous
Cells(3, 24 + 2 * i).Borders(xlEdgeBottom).LineStyle = xlContinuous
Value = Cells(6, 2 + i).Value
For w = 1 To Value
Cells(3 + w, 23 + 2 * i).Value = Cells(14, 2 + i)
cond = Cells(6, 2 + i).Value
If cond Mod 2 = 1 Then
Delta = (cond - 1) * 0.5
Cells(3 + w, 24 + 2 * i).Value = Delta * Cells(10, 2 + i).Value * -1 + (w - 1) * Cells(10, 2 + i).Value
End If
Next w
Next i

'---work as expected untill here----

Dim test As Range
test = Range(Cells(4, 25), Cells(4, 26))''' <-- Return "Empty", why?

I would like to do something like

for q = 1 to e
Value = Cells(6, 2 + i).Value
test(q) = Range(Cells(4, 23+2*q), Cells(4+Value, 24+2*q))
next q

'--Then plot the graph using the stocked range value---

Currently I get Run-time error '91': Object variable or with block variable not set

I also got some other error depending on the type I use to define the variable test.

0 Answers