Cells(x,y) error

F

fmistry

I am using the cells function in a macro. For example,
Cells(14,10) = Cells (Row,5)
When I run the macro, it stops at this line and shows an error. Row
is defined as an Integer and has value.

What is going on?

Thanks.
 
G

Gary''s Student

How about:

Sub marine()
Dim row As Integer
row = 2
Cells(14, 10).Value = Cells(row, 5).Value
End Sub

This works, but its really bad - we should not use row as a variable. It
too confusing.
 
B

bj

"Row" is one of the terms which cannot be defined as a variable name by the
user, since it is used internally by VBA.

Change the variable name.
 
F

fmistry

How about:

Sub marine()
Dim row As Integer
row = 2
Cells(14, 10).Value = Cells(row, 5).Value
End Sub

This works, but its really bad - we should not use row as a variable. It
too confusing.
Click to expand...

Thanks for the suggestion. My macro looks similar to yours, but it
always stops at the cells statement.

Any other ideas? I am trying to take x and y data from an array and
plot them as single points, thus "animating" the data. Thank you.
 
F

fmistry

"Row" is one of the terms which cannot be defined as a variable name by the
user, since it is used internally by VBA.

Change the variable name.







- Show quoted text -
Click to expand...

I changed the Row variable to MyRow but it still doesn't work. Here
is the entire macro which uses the data on Sheet "Animation" in
spreadsheet "Projectile velocity and distance".


Sub Animate()
'
' Animate Macro - Animate Bouncing ball
' Macro recorded 4/16/2007 by Firoz R Mistry
'
Dim totalrows As Integer
Dim count As Integer
Dim MyRow As Integer

'
'

Windows("Projectile velocity and distance.xls").Activate
Sheets("Animation").Select

totalrows = ActiveSheet.UsedRange.Rows.count - 20

For MyRow = 21 To totalrows Step 1

Cells("I14").Value = MyRow

Cells("J14").Value = Cells(MyRow, 6).Value
Cells("K14").Value = Cells(MyRow, 7).Value

Next MyRow
'
End Sub

Thanks for your help.
 
D

Dave Peterson

Maybe...

Option Explicit
Sub Animate()

Dim TotalRows As Long
Dim MyRow As Long
With Workbooks("Projectile velocity and distance.xls") _
.Worksheets("Animation")

Application.Goto .Range("I4"), Scroll:=True
TotalRows = .UsedRange.Rows.Count - 20
For MyRow = 21 To TotalRows Step 1
.Cells("I14").Value = MyRow
.Cells("J14").Value = .Cells(MyRow, 6).Value
.Cells("K14").Value = .Cells(MyRow, 7).Value
Next MyRow

End With

End Sub

I wouldn't use a variable named Count, either.
 
B

bj

part of the current problem is that you (or at least I) can't use the short
hand reference in Cells()
I tried your macro and other than deactivating the sheet it worked for me
using



Cells(14, 9) = MyRow

Cells(14, 10).Value = Cells(MyRow, 6).Value
Cells(14, 11).Value = Cells(MyRow, 7).Value
 
F

fmistry

Maybe...

Option Explicit
Sub Animate()

Dim TotalRows As Long
Dim MyRow As Long
With Workbooks("Projectile velocity and distance.xls") _
.Worksheets("Animation")

Application.Goto .Range("I4"), Scroll:=True
TotalRows = .UsedRange.Rows.Count - 20
For MyRow = 21 To TotalRows Step 1
.Cells("I14").Value = MyRow
.Cells("J14").Value = .Cells(MyRow, 6).Value
.Cells("K14").Value = .Cells(MyRow, 7).Value
Next MyRow

End With

End Sub

I wouldn't use a variable named Count, either.














--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

I tried your routine but got a "Runtime error 13: Type mismatch" at
the first Cells statement
Cells("I14").Value = MyRow
When I comment out this statement, I get the same error at the next
statement which is Cells = Cells so you would expect no type mismatch.

I am at a loss to explain what is going one. Your help and patience
is truly appreciated. Thanks.
 
D

Dave Peterson

I shouldn't have copied your code <bg>.

Option Explicit
Sub Animate()

Dim TotalRows As Long
Dim MyRow As Long
With Workbooks("Projectile velocity and distance.xls") _
.Worksheets("Animation")

Application.Goto .Range("I4"), Scroll:=True
TotalRows = .UsedRange.Rows.Count - 20
For MyRow = 21 To TotalRows Step 1
.Range("I14").Value = MyRow
.Range("J14").Value = .Cells(MyRow, 6).Value
.Range("K14").Value = .Cells(MyRow, 7).Value
Next MyRow

End With

End Sub
 
F

fmistry

I shouldn't have copied your code <bg>.

Option Explicit
Sub Animate()

Dim TotalRows As Long
Dim MyRow As Long
With Workbooks("Projectile velocity and distance.xls") _
.Worksheets("Animation")

Application.Goto .Range("I4"), Scroll:=True
TotalRows = .UsedRange.Rows.Count - 20
For MyRow = 21 To TotalRows Step 1
.Range("I14").Value = MyRow
.Range("J14").Value = .Cells(MyRow, 6).Value
.Range("K14").Value = .Cells(MyRow, 7).Value
Next MyRow

End With

End Sub








--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

Great, it works. Does this mean Cells = Cells is not correct and one
should always use Range=Cells? Can Range take x,y for its arguments?

The cells Cells("J14").Value and Cells("K14").Value represent the x
and y co-ordinates in a Chart (single point plot) which does not seem
to change as the x and y values change. Is there an Update Chart
function I need to use?

This is the last question, I promise. You have been most helpful.
Thanks.
 
D

Dave Peterson

..range() takes something that looks like an address.

..range("a1") or .range("C:C")
or
..range("a1,b9,C3") for lots of cells
or
a range name (created via Insert|Name|Define)
..range("somerangenamehere")

..cells() takes a row and column argument. The nice thing is that .cells() will
accept either a letter or a number for the column argument:

..cells(17,"G") and .cells(17,7) will refer to the same location (G17).

======
I don't speak charts. You may want to repost with more information.

fmistry wrote:
 
F

fmistry

.range() takes something that looks like an address.

.range("a1") or .range("C:C")
or
.range("a1,b9,C3") for lots of cells
or
a range name (created via Insert|Name|Define)
.range("somerangenamehere")

.cells() takes a row and column argument. The nice thing is that .cells() will
accept either a letter or a number for the column argument:

.cells(17,"G") and .cells(17,7) will refer to the same location (G17).

======
I don't speak charts. You may want to repost with more information.

fmistry wrote:
Click to expand...

Thanks for all your help.
 
Top