draw shape based on cell values

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?

Is the sheet is proteced?


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

*** Sent via Developersdex http://www.developersdex.com ***

Hi Shailesh

No, neither sheet is protected.

Phil

"Shailesh Shah" wrote:

>
> Is the sheet is proteced?
>
>
> Regards,
> Shah Shailesh
> http://in.geocities.com/shahshaileshs/
> (Excel Add-ins Page)
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

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

"Defoes Right Boot" <[email protected]> wrote in
message news:[email protected]...
> 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?

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

"Peter T" wrote:

> 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
>
> "Defoes Right Boot" <[email protected]> wrote in
> message news:[email protected]...
> > 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?
>
>
>

Originally Posted by Defoes Right Boot

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