Copying data to another worksheet gives "Type Mismatch" error

[TB]

I am trying to copy data from one worksheet to another, but I keep getting a
"Type Mismatch" (error 13) message. I've tried a bunch of different ways to
code this, but I end up with the same error every time. In desperation, the
following was the last attempt:

Dim vHours as Integer
Dim vdate as Date

If Range("C10").Value > 0 Then
vHours = Range("C10").Value
vDate = Range("G5").Value - 6
Sheets("ws2").Range("B" & Range("B65536").End(xlUp).Rows + 1).Value =
vHours
Sheets("ws2").Range("A" & Range("A65536").End(xlUp).Rows + 1).Value =
vDate
End If

What am I missing? This is driving me crazy. HELP!!!

 

[Dave Ramage]

Range("B65536").End(xlUp).Rows returns a range object
consisting of the first non-blank cell from the bottom of
column B.

What you need is Range("B65536").End(xlUp).Row, which will
return the row number of that cell.

One letter can make all the difference!

For info, a better method may be:
Sheets("ws2").Range("B65536").End(xlUp).Offset(1,0).Value
= vHours

Cheers,
Dave.

 

[John Green]

Change Rows to Row.

Sheets("ws2").Range("B" & Range("B65536").End(xlUp).Row + 1).Value = vHours

 

[TB]

Thanks Dave. I had already tried that. The problem was that I'm doing the
routine for a range of data (cells C10 through C16). The problem I kept
coming up with was that the first set of data transferred OK, but the second
set (from C11) caused a double entry (one from C10 again, and one from C11).
A third set entered three sets of data... and so on. What seemed so simple
has turned out to be a major headache. Any ideas?

 

[Dave Ramage]

So you don't have the Type Mismatch error after changing
Rows to Row?

Not sure what code you are using for the C10 through C16
bit- post the code section and I'd be happy to take a
look...

Cheers,
Dave.

 

[TB]

Until I get the thing working, I've just been using 7 If... Then statements.
I didn't want to complicate things with For... Next loops at this point.
Actually, I took your advice and went back to using

Sheets("ws2").Range("B65536").End(xlUp).Offset(1,0).Value = vHours

At least that basically worked, even if it did duplicate dates. I thought it
might be easier to debug that problem than deal with the errors I was
getting. Guess what? First time I ran it with the above code, it worked as
before, then for some reason it stopped working altogether (no changes to
the rest of the code). Right now, I'm totally confused about what's
happening. I'm running 7 instances of the following code, and it doesn't
seem to do anything anymore:

If Range("C10").Value > 0 Then
vHours = Range("C10").Value
vDate = Range("G5").Value - 6
Sheets("ws2").Range("B65536").End(xlUp).Offset(1,0).Value = vHours
Sheets("ws2").Range("A65536").End(xlUp).Offset(1,0).Value = vDate
End If
If Range("C11").Value > 0 Then
vHours = Range("C11").Value
vDate = Range("G5").Value - 5
Sheets("ws2").Range("B65536").End(xlUp).Offset(1,0).Value = vHours
Sheets("ws2").Range("A65536").End(xlUp).Offset(1,0).Value = vDate
End If

and so on... Any help appreciated.

Thanks
Tom

 

[TB]

Thanks John. That stopped the errors, but then the code didn't do anything.
I'm confused??!!!