VBA Type mismatch error

[Chris H]

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

 

[Simon Lloyd]

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

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

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

When the cell ((Row + 7), Column has a value of " " the line
Code
-------------------
Tags.SegParam_3.Value = UserForm1.Spreadsheet1.Cells(TempRow, Column).Valu
+ Trim(UserForm1.Spreadsheet1.Cells((Row + 7), Column).Value
-------------------
Causes a <Type mismatch> error when it runs. If I do a delete on th
cell o
put a number in the cell the code will run fine for a while then th
spac
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).Valu
-------------------

And a few other things with no jo

I am calling VBA from a program called InteractX by CTC and thi
happens o
machines using Excel 2003 and 200

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

Thanks In Advanc

Chri

--
Simon Lloy

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

 

[Jim Cone]

"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"
<[email protected]>
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)