Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data to another worksheet gives "Type Mismatch" error
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data to another worksheet gives "Type Mismatch" error
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. -----Original Message----- 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!!! . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data to another worksheet gives "Type Mismatch" error
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" wrote in message ... 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. -----Original Message----- 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!!! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data to another worksheet gives "Type Mismatch" error
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. -----Original Message----- 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" wrote in message ... 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. -----Original Message----- 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!!! . . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data to another worksheet gives "Type Mismatch" error
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 anymo 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 "Dave Ramage" wrote in message ... 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. -----Original Message----- 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" wrote in message ... 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. -----Original Message----- 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!!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data to another worksheet gives "Type Mismatch" error
Change Rows to Row.
Sheets("ws2").Range("B" & Range("B65536").End(xlUp).Row + 1).Value = vHours -- John Green - Excel MVP Sydney Australia "TB" wrote in message ... 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!!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying data to another worksheet gives "Type Mismatch" error
Thanks John. That stopped the errors, but then the code didn't do anything.
I'm confused??!!! John Green wrote: Change Rows to Row. Sheets("ws2").Range("B" & Range("B65536").End(xlUp).Row + 1).Value = vHours "TB" wrote in message ... 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
when copying from one spreadsheet to another I get "#VALUE!" error,how can I eliminate it? | Excel Discussion (Misc queries) | |||
"Type mismatch" when I try to fill an Array variable with "+" | Excel Discussion (Misc queries) | |||
multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)" | Excel Discussion (Misc queries) | |||
VBA setting formula for a cell causes "Wrong data type" error | Excel Discussion (Misc queries) | |||
Where is the toolbar with the "bold type", "font type", options | New Users to Excel |