Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
runtime error 13 - type mismatch error in Excel 97 on Citrix | Excel Programming | |||
Conditional Formatting - Run Time Error '13' Type Mismatch Error | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming |