I have macro where we enter between 1 and 4 characters into a user form. The macro is working perfect except it returns an error when 4 characters are entered.
The error reads as follows.
Run-time error '-21473552571 (80020005)':
Could not set the Value propert. Type mismatch.
What does this mean? and How do I correct it?
How can anyone help you without seeing the code?
please provide sample code
tq
Red text is where the debugger says the error occursCode:Private Sub Okbutton_Click()
' Make Sheet1 Active
Sheets(1).Activate
' Export Data to worksheet
Cells(2, 5).Value = tbunder.Value
Cells(2, 6).Value = cbunderxp.Value
Cells(2, 3).Value = tvvolbeta.Value
Cells(2, 4).Value = tbedge.Value
Cells(2, 10).Value = tbus.Value
Cells(2, 7).Value = tbuc.Value
Cells(2, 18).Value = tbup.Value
Cells(2, 34).Value = tbhedge.Value
Cells(2, 35).Value = cbhxp.Value
Cells(2, 41).Value = tbhc.Value
Cells(2, 54).Value = tbhp.Value
' Import data from work sheet
tbhs.Value = Cells(2, 68).Value
tbhcp.Value = Cells(2, 43).Value
tbhpp.Value = Cells(2, 59).Value
tbucp.Value = Cells(2, 14).Value
tbupp.Value = Cells(2, 24).Value
' Add trade to positions sheet
Sheets("Positions").Select
Rows("4:4").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A5:V5").Select
Selection.AutoFill Destination:=Range("A4:V5"), Type:=xlFillDefault
Range("A4:V5").Select
End Sub
what you posted gives us very little to go on. can you post the workbook, strip out sensitive info if need to?
The problem seems to be occurring when the userform tries to import data from the workbook.
Code:Private Sub CommandButton3_Click()
Unload Me
End Sub
Private Sub CommandButton4_Click()
' Sheet(1) Activate
Sheets(1).Activate
' Import data from work sheet
tbhs.Value = Cells(2, 68).Value
tbhcp.Value = Cells(2, 43).Value
tbhpp.Value = Cells(2, 59).Value
tbucp.Value = Cells(2, 14).Value
tbupp.Value = Cells(2, 24).Value
End Sub
Private Sub Entertradebutton_Click()
' Make Sheet1 Active
Sheets(1).Activate
' ExportData to worksheet
Cells(2, 36).Value = tbhs.Value
Cells(2, 49).Value = tbhcp.Value
Cells(2, 62).Value = tbhpp.Value
Cells(2, 17).Value = tbucp.Value
Cells(2, 27).Value = tbupp.Value
' hedge
Sheets("2").Select
Application.Run "BLPLinkReset"
Columns("A:F").Select
Range("F1").Activate
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("G1:L3").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "='1'!R[1]C[4]"
Range("D1").Select
ActiveCell.FormulaR1C1 = ""
Sheets("1").Select
Application.Run "BLPLinkReset"
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
Range("AH3").Select
Sheets("2").Select
Application.Run "BLPLinkReset"
ActiveCell.FormulaR1C1 = "='1'!R[1]C[30]"
Range("B3").Select
ActiveCell.FormulaR1C1 = "='1'!R[-1]C[71]"
Range("E3").Select
ActiveCell.FormulaR1C1 = "='1'!R[-1]C[70]"
Range("A4").Select
Sheets("1").Select
Application.Run "BLPLinkReset"
Range("BT2").Select
Selection.cOPY
Range("BX3:BY3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BX2:BY3"), Type:=xlFillDefault
Range("BX2:BY3").Select
Range("BX3").Select
ActiveCell.FormulaR1C1 = "=-1*(RC[-6]+'2'!R3C[-69])"
Range("BX2").Select
ActiveCell.FormulaR1C1 = "=-1*(RC[-6]+'2'!R3C[-75])"
Range("BY2").Select
ActiveCell.FormulaR1C1 = "=-1*(RC[-6]+'2'!R3C[-73])"
Range("BX2").Select
Selection.Copy
Sheets("2").Select
Application.Run "BLPLinkReset"
ActiveSheet.Paste
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Sheets("1").Select
Application.Run "BLPLinkReset"
Range("BX2").Select
Selection.Copy
Sheets("2").Select
Application.Run "BLPLinkReset"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("C4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=(R3C[-1]-RC[-1])*RC[-2]"
Range("D4").Select
Sheets("1").Select
Application.Run "BLPLinkReset"
Range("BY2").Select
Selection.Copy
Sheets("2").Select
Application.Run "BLPLinkReset"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("E3").Select
Application.CutCopyMode = False
Selection.Copy
Range("E4").Select
ActiveSheet.Paste
Range("E4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("F4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=(R3C[-1]-RC[-1])*RC[-2]"
Range("F5").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "='1'!R[1]C[74]"
Range("E1").Select
ActiveCell.FormulaR1C1 = "='1'!R[1]C[72]"
Range("E2").Select
Sheets("1").Select
Application.Run "BLPLinkReset"
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B1").Select
' Close form
Unload Me
End Sub
Private Sub Okbutton_Click()
' Make Sheet1 Active
Sheets(1).Activate
' Export Data to worksheet
Cells(2, 5).Value = tbunder.Value
Cells(2, 6).Value = cbunderxp.Value
Cells(2, 3).Value = tvvolbeta.Value
Cells(2, 4).Value = tbedge.Value
Cells(2, 10).Value = tbus.Value
Cells(2, 7).Value = tbuc.Value
Cells(2, 18).Value = tbup.Value
Cells(2, 34).Value = tbhedge.Value
Cells(2, 35).Value = cbhxp.Value
Cells(2, 41).Value = tbhc.Value
Cells(2, 54).Value = tbhp.Value
' Add trade to positions sheet
Sheets("Positions").Select
Rows("4:4").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A5:V5").Select
Selection.AutoFill Destination:=Range("A4:V5"), Type:=xlFillDefault
Range("A4:V5").Select
End Sub
Private Sub UserForm_Initialize()
' Underlying Security
tbunder.Value = ""
' Underlying security expiration combobox
With cbunderxp
.AddItem "'3/17/2012"
.AddItem "'4/21/2012"
.AddItem "'5/19/2012"
.AddItem "'6/16/2012"
.AddItem "'7/21/2012"
.AddItem "'8/18/2012"
.AddItem "'9/22/2012"
.AddItem "'10/20/2012"
End With
' Underlying security call
tbuc.Value = ""
' Underlying security put
tbup.Value = ""
' Underlying security size
tbus.Value = ""
' Vol Beta
tvvolbeta = ""
' Expected Edge
tbedge = ""
' Hedging Security
tbhedge = ""
' Hedging Expiriation
With cbhxp
.AddItem "'3/17/2012"
.AddItem "'4/21/2012"
.AddItem "'5/19/2012"
.AddItem "'6/16/2012"
.AddItem "'7/21/2012"
.AddItem "'8/18/2012"
.AddItem "'9/22/2012"
.AddItem "'10/20/2012"
End With
' Hedge call
tbhc = ""
' Hedge put
tbhp = ""
' Hedge size
tbhs.Value = ""
' Underlying Call Price
tbucp = ""
' Underlying Put Price
tbupp = ""
End Sub
It would be helpful to know the value of the cell and the data type of the array
My crystal ball is a little fuzzy, but is it possible that the Cells(2,68).value is a formula that is returning an error?
Other than that, you really will need to post a workbook that shows the error conditionCode:tbhs.Value = Cells(2, 68).Value
Paul
As Paul said, if you have an error like Division by 0 in the cell, that will happen. You can use On Error Resume Next or check the type of value before "importing" it or use Cstr() which would show Error 2007 in your textbox for Division by 0 errors.
e.g.
Code:Private Sub CommandButton1_Click()
TextBox1.Value = CStr(Cells(2, 68).Value)
End Sub
Private Sub CommandButton1_Click()
If Not IsError(Cells(2, 68)) Then TextBox1.Value = Cells(2, 68).Value
End Sub