Results 1 to 10 of 10

Thread: Getting an Error when data entered is 4 letters but three or less works perfectly.

  1. 03-01-2012, 02:02 PM #1
    dkassin
    dkassin is offline
    VBAX Regular
    Joined
    Feb 2012
    Posts
    11
    Location

    Getting an Error when data entered is 4 letters but three or less works perfectly.

    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?
    Reply With Quote Reply With Quote
  2. 03-01-2012, 03:53 PM #2
    JKwan
    JKwan is offline
    VBAX Expert
    Joined
    Aug 2004
    Posts
    818
    Location
    How can anyone help you without seeing the code?
    Reply With Quote Reply With Quote
  3. 03-01-2012, 06:15 PM #3
    wakdafak
    wakdafak is offline
    VBAX Regular
    Joined
    Jan 2012
    Posts
    24
    Location
    please provide sample code
    tq
    Reply With Quote Reply With Quote
  4. 03-02-2012, 06:32 AM #4
    dkassin
    dkassin is offline
    VBAX Regular
    Joined
    Feb 2012
    Posts
    11
    Location
    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
    Red text is where the debugger says the error occurs
    Last edited by Aussiebear; 01-01-2025 at 11:28 AM.
    Reply With Quote Reply With Quote
  5. 03-02-2012, 06:56 AM #5
    JKwan
    JKwan is offline
    VBAX Expert
    Joined
    Aug 2004
    Posts
    818
    Location
    what you posted gives us very little to go on. can you post the workbook, strip out sensitive info if need to?
    Reply With Quote Reply With Quote
  6. 03-02-2012, 06:57 AM #6
    dkassin
    dkassin is offline
    VBAX Regular
    Joined
    Feb 2012
    Posts
    11
    Location
    The problem seems to be occurring when the userform tries to import data from the workbook.
    Reply With Quote Reply With Quote
  7. 03-02-2012, 07:01 AM #7
    dkassin
    dkassin is offline
    VBAX Regular
    Joined
    Feb 2012
    Posts
    11
    Location
    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
    Last edited by Aussiebear; 01-01-2025 at 11:44 AM.
    Reply With Quote Reply With Quote
  8. 03-02-2012, 05:14 PM #8
    toddbailey
    toddbailey is offline
    VBAX Newbie
    Joined
    Mar 2012
    Posts
    2
    It would be helpful to know the value of the cell and the data type of the array
    Reply With Quote Reply With Quote
  9. 03-02-2012, 06:47 PM #9
    Paul_Hossler
    Paul_Hossler is offline
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,886
    Location
    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?

    tbhs.Value = Cells(2, 68).Value
    Other than that, you really will need to post a workbook that shows the error condition

    Paul
    Last edited by Aussiebear; 01-01-2025 at 11:45 AM.
    Reply With Quote Reply With Quote
  10. 03-02-2012, 07:31 PM #10
    Kenneth Hobs
    Kenneth Hobs is offline
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,954
    Location
    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.
    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
    Last edited by Aussiebear; 01-01-2025 at 11:46 AM.
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules