I need help with a "Run-Time Error 13: Type Mismatch"

C

criggs

Hi group. Im getting an error that I dont understand with this macro
I wrote for excel. Im a begineer to VBA but this code also stumped an
experienced programmer that i showed and I would really like to know
what the problem is. If anybody would care to read through it (I
tried to comment it appropriately so that its easier to understand
what Im doing, and the line that produces the error has a row of
asterisks before it) I would be much appreciative.






Sub FileOpen()
Dim fname As String
Dim strTemp As String
Dim endStr As String
Dim testNumber As String
Dim Rownum As Integer

' OK, here is the basic function and context of this code.
' From Excel, the Workbook in which this code is contained is a
compiliation of data from multiple subjects in a psychology
experiment.
' Each subject has his data saved in a workbook which is output from a
heart rate monitor program.
' The general format of these workbooks are the same. Naming is
####_HR.xls, with #### being the ID number
' The subject's workbook is split into three sheets, "Basline",
"Task", and "Recovery"
' The purpose for this macro is to simply copy relevant data from the
subject's workbook into this compliation in its proper location.
' The code was working great until I added a button to activate this
code and saved the workbook.
' After the error I deleted the button and all its associated code and
the error simply never went away.
' I will admit I'm not overly familiar with neccessity for declaring
things global and such,
' and I feel like the error is going to be something simple like that.
' Something interesting to note is that the problem started when I
added an ActiveX control to execute this macro.
' After I saved the workbook the error never went away.



'Basic file opening, to select the Excel file that contains the data
from the desired subject
fname = Application.GetOpenFilename("Excel Files (*.xls), *.xls)")

If InStr(fname, "False") = 0 Then
Workbooks.Open fname
End If


' This block of code is basically just determining the file name and
the ID number of the subject from the file name.
' Maybe there is a prettier way of doing it but for a beginner string
manipulation seemed like the way to go.
endStr = ".xls"
slash = "\"
i = InStrRev(fname, slash) + 1
j = InStr(fname, endStr)

testNumber = Mid(fname, i, j - i - 3)
fname = Mid(fname, i, Len(fname))



' Selecting the column that contains all the ID Numbers
' But you alreadyk knew that
Windows("HR and HRV.xls").Activate
Columns("A:A").Select

' Searching through the selected column to find the occurrence of the
ID number derived from the file name
Selection.Find(What:=testNumber, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

' Once found, selecting the whole row of the found ID number
Rownum = ActiveCell.Row

' All the following code was generating recording a macro, so its not
anything close to being efficient or pretty
' It's all very repetitive and there probably is not much reason to
read it all.
' I tried to bring out the highlights with asterisk lines.
' More of it is hard coded than I would like, but once I get the code
debugged I will shift my focus to flexibility
Windows(fname).Activate
Sheets("Baseline").Select
Windows(fname).Activate
Sheets("Baseline").Range("C30").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [4]).Select
ActiveSheet.Paste

' Macro works fine when I comment out the following block of code
starting here...
Windows(fname).Activate
Sheets("Baseline").Range("C34").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
'
******************************************************************************************
' THE FOLLOWING LINE IS THE PROBLEM CODE THAT RETURNS THE RUN-TIME
ERROR '13' TYPE MISTMATCH
'
******************************************************************************************
Cells([Rownum], [5]).Paste
' ... and ending here

Windows(fname).Activate
Sheets("Baseline").Range("C38").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [6]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Baseline").Range("C41").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [7]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Baseline").Range("C31").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [8]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Baseline").Range("C35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [9]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Baseline").Range("C39").Select
ActiveSheet.Paste
Windows("HR and HRV.xls").Activate
Cells([Rownum], [10]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Baseline").Range("C42").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
'
*********************************************************************************
' Another interesting tidbit, the following line ALSO gave me the same
error when I
' tried to add another button after commenting out the first block of
problem code
'**********************************************************************************
Cells([Rownum], [11]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Baseline").Range("B10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [12]).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=5
Windows(fname).Activate
Sheets("Baseline").Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [13]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Baseline").Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [14]).Select
ActiveSheet.Paste
Windows(fname).Activate
Windows("HR and HRV.xls").Activate


Windows(fname).Activate
Sheets("Task").Select
Sheets("Task").Range("C30").Select
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [15]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Task").Range("C34").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [16]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Task").Range("C38").Select
ActiveSheet.Paste
Windows("HR and HRV.xls").Activate
Cells([Rownum], [17]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Task").Range("C41").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [18]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Task").Range("C31").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [19]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Task").Range("C35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [20]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Task").Range("C39").Select
ActiveSheet.Paste
Windows("HR and HRV.xls").Activate
Cells([Rownum], [21]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Task").Range("C42").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [22]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Task").Range("B10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [23]).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=5
Windows(fname).Activate
Sheets("Task").Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [24]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Task").Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [25]).Select
ActiveSheet.Paste
Windows(fname).Activate
Windows("HR and HRV.xls").Activate


Windows(fname).Activate
Sheets("Recovery").Select
Sheets("Recovery").Range("C30").Select
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [26]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Recovery").Range("C34").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [27]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Recovery").Range("C38").Select
ActiveSheet.Paste
Windows("HR and HRV.xls").Activate
Cells([Rownum], [28]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Recovery").Range("C41").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [29]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Recovery").Range("C31").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [30]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Recovery").Range("C35").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [31]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Recovery").Range("C39").Select
ActiveSheet.Paste
Windows("HR and HRV.xls").Activate
Cells([Rownum], [32]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Recovery").Range("C42").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [33]).Select
ActiveSheet.Paste
Windows(fname).Activate
Sheets("Recovery").Range("B10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [34]).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll ToRight:=5
Windows(fname).Activate
Range("B11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [35]).Select
ActiveSheet.Paste
Windows(fname).Activate
Range("B12").Select
Application.CutCopyMode = False
Selection.Copy
Windows("HR and HRV.xls").Activate
Cells([Rownum], [36]).Select
ActiveSheet.Paste
Windows(fname).Activate
Windows("HR and HRV.xls").Activate




End Sub
 
J

Jim Cone

Change... Cells([Rownum], [5]).Paste
To... Cells([Rownum], [5]).PasteSpecial
'---
I would also make some other changes...
The brackets "[ ]" are not required when using the cells method, so...
Cells(Rownum, 5).PasteSpecial '(is the way to do it)

Use Option Explicit as the first line in your module.
(I and j will have to be declared when you do that)

Declare Rownum as a Long not an Integer
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
wrote in message
Hi group. Im getting an error that I dont understand with this macro
I wrote for excel. Im a begineer to VBA but this code also stumped an
experienced programmer that i showed and I would really like to know
what the problem is. If anybody would care to read through it (I
tried to comment it appropriately so that its easier to understand
what Im doing, and the line that produces the error has a row of
asterisks before it) I would be much appreciative.

-snip-
 
D

Dave Peterson

When I did this, I didn't that "type mismatch" error. I got a 438, object
doesn't support this property or method.

But I couldn't come up with a better suggestion...

Jim said:
Change... Cells([Rownum], [5]).Paste
To... Cells([Rownum], [5]).PasteSpecial
'---
I would also make some other changes...
The brackets "[ ]" are not required when using the cells method, so...
Cells(Rownum, 5).PasteSpecial '(is the way to do it)

Use Option Explicit as the first line in your module.
(I and j will have to be declared when you do that)

Declare Rownum as a Long not an Integer
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

<[email protected]>
wrote in message
Hi group. Im getting an error that I dont understand with this macro
I wrote for excel. Im a begineer to VBA but this code also stumped an
experienced programmer that i showed and I would really like to know
what the problem is. If anybody would care to read through it (I
tried to comment it appropriately so that its easier to understand
what Im doing, and the line that produces the error has a row of
asterisks before it) I would be much appreciative.

-snip-
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help me5 1
Using a string variable to change a file name 3
VBA Filter 2
VBA for Dependents shortcut menu 0
cannot insert row in vba 1
Find copy data from a cell in Personal workbook and copy it to a cellin current workbook 14
Code not working as expected 2
Help with activating window from Application.GetOpenFilename 4

Top