draw shape based on cell values

[Guest]

I am trying to draw a series of simple polylines based on cells containing
values calculated from the original user input.

There are up to 20 polylines to be drawn. Each has five set points which are
found in cells Sn:Zn of sheet "Drawing Calcs" where n is the row number which
depends on which polyline is being drawn (the first polylines coordinates are
on row 3)

I have the following code which I hoped would do this but doesn't.

Sub draw()
Dim sides As Integer
Dim i As Integer
i = 0
sides = Worksheets("Drawing Calcs").Range("B24").Value
Dim cilldwg(5, 2) As Single
Do
i = i + 1
Worksheets("Drawing Calcs").Select
cilldwg(1, 1) = Cells(i + 2, 19).Value
cilldwg(1, 2) = Cells(i + 2, 20).Value
cilldwg(2, 1) = Cells(i + 2, 21).Value
cilldwg(2, 2) = Cells(i + 2, 22).Value
cilldwg(3, 1) = Cells(i + 2, 23).Value
cilldwg(3, 2) = Cells(i + 2, 24).Value
cilldwg(4, 1) = Cells(i + 2, 25).Value
cilldwg(4, 2) = Cells(i + 2, 26).Value
cilldwg(5, 1) = Cells(i + 2, 19).Value
cilldwg(5, 2) = Cells(i + 2, 20).Value
Worksheets("Drawing").Select
Worksheets("Drawing").Shapes.AddPolyline cilldwg
Loop Until i = sides
End Sub

This brings up "Run-time error '1004'; application-defined or object-defined
error."

Excel help is not much use... can anyone tell me what I need to change?

 

[Shailesh Shah]

Is the sheet is proteced?


Regards,
Shah Shailesh
http://in.geocities.com/shahshaileshs/
(Excel Add-ins Page)

 

[Guest]

Hi Shailesh

No, neither sheet is protected.

Phil

 

[Peter T]

If you have not set Option Base 1 at the head of your module, try changing

Dim cilldwg(5, 2) As Single

to

Dim cilldwg(1 to 5, 1 to 2) As Single

I notice you are switching sheets back & forth in your loop. Instead, first
select the sheet you want to add the shapes ("Drawing") and try

With Worksheets("Drawing Calcs")
cilldwg(1, 1) = .Cells(i + 2, 19).Value
'etc note the dot before .Cells(
End With

Regards,
Peter T

 

[Guest]

Excellent, that works. Thanks very much Peter.

Only thing I've got to sort out now is that the drawings now indicate that
my calculations for the co-ordinates are totally wrong!!!! But at least I've
got it drawing something!

Thanks again

Phil

 

[avveerkar]

I am trying to draw a series of simple polylines based on cells
containing
values calculated from the original user input.

There are up to 20 polylines to be drawn. Each has five set points
which are
found in cells Sn:Zn of sheet "Drawing Calcs" where n is the row number
which
depends on which polyline is being drawn (the first polylines
coordinates are
on row 3)

I have the following code which I hoped would do this but doesn't.

Sub draw()
Dim sides As Integer
Dim i As Integer
i = 0
sides = Worksheets("Drawing Calcs").Range("B24").Value
Dim cilldwg(5, 2) As Single
Do
i = i + 1
Worksheets("Drawing Calcs").Select
cilldwg(1, 1) = Cells(i + 2, 19).Value
cilldwg(1, 2) = Cells(i + 2, 20).Value
cilldwg(2, 1) = Cells(i + 2, 21).Value
cilldwg(2, 2) = Cells(i + 2, 22).Value
cilldwg(3, 1) = Cells(i + 2, 23).Value
cilldwg(3, 2) = Cells(i + 2, 24).Value
cilldwg(4, 1) = Cells(i + 2, 25).Value
cilldwg(4, 2) = Cells(i + 2, 26).Value
cilldwg(5, 1) = Cells(i + 2, 19).Value
cilldwg(5, 2) = Cells(i + 2, 20).Value
Worksheets("Drawing").Select
Worksheets("Drawing").Shapes.AddPolyline cilldwg
Loop Until i = sides
End Sub

This brings up "Run-time error '1004'; application-defined or
object-defined
error."

Excel help is not much use... can anyone tell me what I need to change?

vb array declaration is 0 based ( when you say Dim A(2) it creates
array of 3 elements to be addressed as 0,1,2) but EXCEL has 1 based
declaration. There is a mismatch between DIM cilldwg(5,2) declaration
and AddPolyline cilldwg statement. Change your Dim declaration to 1
base explicitly by changing to

Dim cilldwg(1 To 5, 1 To 2) As Single

A V Veerkar