VBA Type mismatch error
 

I am having trouble where cells that look empty have a space in them.



The watch window in VBA shows the value for
UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value as value: " ", type:
Variant/String



When the cell ((Row + 7), Column has a value of " " the line:

Tags.SegParam_3.Value = UserForm1.Spreadsheet1.Cells(TempRow, Column).Value
+ Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value)

Causes a <Type mismatch error when it runs. If I do a delete on the cell or
put a number in the cell the code will run fine for a while then the space
finds its way back into the cell and the problems starts again.



I have tried:

Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value)

CDbl(Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value))

And a few other things with no joy



I am calling VBA from a program called InteractX by CTC and this happens on
machines using Excel 2003 and 2007



If there is a more appropriate group to post this to please let me know.



Thanks In Advance

Chris

VBA Type mismatch error
 

Couple of things you can try...
If IsNumeric(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value) Then
'add them up
-or-
Tags.SegParam_3.Value = _
UserForm1.Spreadsheet1.Cells(TempRow, Column).Value + _
Val(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value)

Also, "Column" and "Row" are properties of the Range object,
so Excel already uses them. You might want to use different names.
--
Jim Cone
Portland, Oregon USA



"Chris H"
wrote in message
I am having trouble where cells that look empty have a space in them.

The watch window in VBA shows the value for
UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value as value: " ", type:
Variant/String
When the cell ((Row + 7), Column has a value of " " the line:

Tags.SegParam_3.Value = UserForm1.Spreadsheet1.Cells(TempRow, Column).Value
+ Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value)

Causes a <Type mismatch error when it runs. If I do a delete on the cell or
put a number in the cell the code will run fine for a while then the space
finds its way back into the cell and the problems starts again.
I have tried:

Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value)

CDbl(Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value))

And a few other things with no joy
I am calling VBA from a program called InteractX by CTC and this happens on
machines using Excel 2003 and 2007
If there is a more appropriate group to post this to please let me know.
Thanks In Advance
Chris

VBA Type mismatch error
 

You can't call UserForm1.Spreadsheet1.Cells...etc, the userform is not
an object or property of the spreasdhseet (sheet or sheets), the
userform will have either comboboxes, listboxes or textboxes that will
hold values.

Chris H;246471 Wrote:
I am having trouble where cells that look empty have a space in them.

The watch window in VBA shows the value for

Code:
--------------------

UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value as value: " ", type:
Variant/String

--------------------


When the cell ((Row + 7), Column has a value of " " the line:


Code:
--------------------

Tags.SegParam_3.Value = UserForm1.Spreadsheet1.Cells(TempRow, Column).Value
+ Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value)

--------------------

Causes a <Type mismatch error when it runs. If I do a delete on the
cell or
put a number in the cell the code will run fine for a while then the
space
finds its way back into the cell and the problems starts again.

I have tried:

Code:
--------------------


Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value)

CDbl(Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value

--------------------
))

And a few other things with no joy

I am calling VBA from a program called InteractX by CTC and this
happens on
machines using Excel 2003 and 2007

If there is a more appropriate group to post this to please let me
know.

Thanks In Advance

Chris


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=68796

VBA Type mismatch error
 

"Spreadsheet1" is the default name for the Microsoft Spreadsheet Control.
It can be found in the "additional controls" option on the VBE Toolbox.
--
Jim Cone
Portland, Oregon USA



"Simon Lloyd"

wrote in message
You can't call UserForm1.Spreadsheet1.Cells...etc, the userform is not
an object or property of the spreasdhseet (sheet or sheets), the
userform will have either comboboxes, listboxes or textboxes that will
hold values.
--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)