Macro generates "Type Mismatch Runtime error"

P

Paul3rd

Hello, I'm pulling data into a worksheet from a closed workbook.
Everything works OK until I change the reference from one cell to a range
of cells.
The range of data pulls into the new worksheet OK, but I get a "runtime 13"
error message that says I have a type mismatch in the line "AreaAddress =
Sheet1.Range("A1:I1").
Code is as follows:
This code works:
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Cells(1, 1)= Sheet1.UsedRange.Address
End Sub

The code for the module on the open worksheet is:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Cells(1, 1) = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Cells(1, 1)

With Sheet1.Cells(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

*This code generates "Runtime error 13, type mismatch"
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Range("A1:I1")= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet has been changed to:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Range("A1:I1") = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Range("A1:I1")

With Sheet1.Range(AreaAddress)
'If the range in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

I need some help to sort this out,
Thanks in advance
Paul
 
J

Jim Cone

Re: AreaAddress = Sheet1.Range("A1:I1")

AreaAddress is a String.
Sheet1.Range("A1:I1") is a range object

Change the line to...
AreaAddress = Sheet1.Range("A1:I1").Address
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Paul3rd"
wrote in message
Hello, I'm pulling data into a worksheet from a closed workbook.
Everything works OK until I change the reference from one cell to a range
of cells.
The range of data pulls into the new worksheet OK, but I get a "runtime 13"
error message that says I have a type mismatch in the line "AreaAddress =
Sheet1.Range("A1:I1").
Code is as follows:
This code works:
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Cells(1, 1)= Sheet1.UsedRange.Address
End Sub

The code for the module on the open worksheet is:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Cells(1, 1) = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Cells(1, 1)

With Sheet1.Cells(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

*This code generates "Runtime error 13, type mismatch"
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Range("A1:I1")= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet has been changed to:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Range("A1:I1") = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Range("A1:I1")

With Sheet1.Range(AreaAddress)
'If the range in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

I need some help to sort this out,
Thanks in advance
Paul
 
K

Kevin B

The item propert for Cells(RowNum, ColNum) is being passed as a string
because you've declared it as one and it needs to be a value.

Dim AreaAddress as Integer

AreaAddress = 1

Cells(AreaAddress, AreaAddress)

Further down you use a Range(CellAddress) to assign a value to a string.

If the range is A1 all you have to state is that AreaAddress = "A1"

Otherwise, declare a range obect variable and assign it a range value

dim r as Range

Set r = Range("A1")


--
Kevin Backmann


Paul3rd said:
Hello, I'm pulling data into a worksheet from a closed workbook.
Everything works OK until I change the reference from one cell to a range
of cells.
The range of data pulls into the new worksheet OK, but I get a "runtime 13"
error message that says I have a type mismatch in the line "AreaAddress =
Sheet1.Range("A1:I1").
Code is as follows:
This code works:
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Cells(1, 1)= Sheet1.UsedRange.Address
End Sub

The code for the module on the open worksheet is:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Cells(1, 1) = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Cells(1, 1)

With Sheet1.Cells(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

*This code generates "Runtime error 13, type mismatch"
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Range("A1:I1")= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet has been changed to:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Range("A1:I1") = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Range("A1:I1")

With Sheet1.Range(AreaAddress)
'If the range in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

I need some help to sort this out,
Thanks in advance
Paul
Click to expand...
 
D

Dave Peterson

This line:
AreaAddress = Sheet1.Range("A1:I1")
is the equivalent to:
AreaAddress = Sheet1.Range("A1:I1").Value

And since Sheet1.Range("A1:I1").Value is an array (1 row by 9 columns), you
can't assign it to a string.

I think that this is what you want.

Compiled, but not tested:

Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String
Dim myCell As Range

With Sheet1
'Refer to the UsedRange Address of Sheet1 in the closed workbook.
.Cells(1, 1).FormulaR1C1 = "= 'C:\" & "[test2.xls]Sheet1'!RC"
For Each myCell In .Range(.Cells(1, 1).Value)
AreaAddress = myCell.Address
With .Cells(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.
.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC
="""",NA(),'C:\" _
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
'but don't change formatting
.SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
On Error GoTo 0

'Change all formulas to Values only.
.Value = .Value
End With
Next myCell
End With
End Sub
Hello, I'm pulling data into a worksheet from a closed workbook.
Everything works OK until I change the reference from one cell to a range
of cells.
The range of data pulls into the new worksheet OK, but I get a "runtime 13"
error message that says I have a type mismatch in the line "AreaAddress =
Sheet1.Range("A1:I1").
Code is as follows:
This code works:
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Cells(1, 1)= Sheet1.UsedRange.Address
End Sub

The code for the module on the open worksheet is:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Cells(1, 1) = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Cells(1, 1)

With Sheet1.Cells(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

*This code generates "Runtime error 13, type mismatch"
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Range("A1:I1")= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet has been changed to:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Range("A1:I1") = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Range("A1:I1")

With Sheet1.Range(AreaAddress)
'If the range in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

I need some help to sort this out,
Thanks in advance
Paul
Click to expand...
 
P

Paul3rd

Thanks Jim, That fixed it.

Jim Cone said:
Re: AreaAddress = Sheet1.Range("A1:I1")

AreaAddress is a String.
Sheet1.Range("A1:I1") is a range object

Change the line to...
AreaAddress = Sheet1.Range("A1:I1").Address
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Paul3rd"
wrote in message
Hello, I'm pulling data into a worksheet from a closed workbook.
Everything works OK until I change the reference from one cell to a range
of cells.
The range of data pulls into the new worksheet OK, but I get a "runtime 13"
error message that says I have a type mismatch in the line "AreaAddress =
Sheet1.Range("A1:I1").
Code is as follows:
This code works:
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Cells(1, 1)= Sheet1.UsedRange.Address
End Sub

The code for the module on the open worksheet is:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Cells(1, 1) = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Cells(1, 1)

With Sheet1.Cells(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

*This code generates "Runtime error 13, type mismatch"
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Range("A1:I1")= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet has been changed to:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Range("A1:I1") = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Range("A1:I1")

With Sheet1.Range(AreaAddress)
'If the range in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

I need some help to sort this out,
Thanks in advance
Paul
Click to expand...
 
P

Paul3rd

Thanks Kevin, I changed the line to:
AreaAddress = Sheet1.Range("A1:I1").Address


Kevin B said:
The item propert for Cells(RowNum, ColNum) is being passed as a string
because you've declared it as one and it needs to be a value.

Dim AreaAddress as Integer

AreaAddress = 1

Cells(AreaAddress, AreaAddress)

Further down you use a Range(CellAddress) to assign a value to a string.

If the range is A1 all you have to state is that AreaAddress = "A1"

Otherwise, declare a range obect variable and assign it a range value

dim r as Range

Set r = Range("A1")


--
Kevin Backmann


Paul3rd said:
Hello, I'm pulling data into a worksheet from a closed workbook.
Everything works OK until I change the reference from one cell to a range
of cells.
The range of data pulls into the new worksheet OK, but I get a "runtime 13"
error message that says I have a type mismatch in the line "AreaAddress =
Sheet1.Range("A1:I1").
Code is as follows:
This code works:
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Cells(1, 1)= Sheet1.UsedRange.Address
End Sub

The code for the module on the open worksheet is:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Cells(1, 1) = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Cells(1, 1)

With Sheet1.Cells(AreaAddress)
'If the cell in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

*This code generates "Runtime error 13, type mismatch"
Code for the BeforeSave event of the closed workbook:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Put in the UsedRange Adress of Sheet1 test2.xls (this workbook).
Sheet2.Range("A1:I1")= Sheet1.UsedRange.Address
End Sub
The code for the module on the open worksheet has been changed to:
Sub PullInSheet1()
'Pulls in Range data from sheet1 of a closed workbook.
Dim AreaAddress As String

'Refrence the UsedRange Address of Sheet1 in the closed workbook.
Sheet1.Range("A1:I1") = "= 'C:\" & "[test2.xls]Sheet1'!RC"
'Pass the area Address to a String.
AreaAddress = Sheet1.Range("A1:I1")

With Sheet1.Range(AreaAddress)
'If the range in Sheet1 of the closed workbook is not empty _
'then pull in it's content, else put in an Error.

.FormulaR1C1 = "=If('C:\" & "[test2.xls]Sheet1'!RC ="""",NA(),'C:\"
& "[test2.xls]Sheet1'!RC)"

'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only.
.Value = .Value
End With
End Sub

I need some help to sort this out,
Thanks in advance
Paul
Click to expand...
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

Trouble changing Range destination 4
Save As and new workbook new name used in existing code 8
Not getting "argument type mismatch" 4
Cell values to an array, then array transpose to another workbook 8
Sheet to sheet code Type Mismatch 6
Code Help 3
Open four Wbooks, copy four columns from each to Master Wbook 16
Check if file exists / open it 2

Top