run-time error 13: type mismatch

bryan.lugo · April 18th, 2006, 01:41 PM

Not sure whats going on here. I keep getting this error but everything looks good to me. Any help would be appreciated - lines causing error are in itallics. This code is attached to my form:

Option Explicit
Private Data As Variant
Private RangeData As Range

Public Cancelled As Boolean

Private Sub cmdClear_Click()
  Cancelled = True
  Me.Hide
End Sub

Private Sub cmdSubmit_Click()

  Cancelled = False
  SaveRecord
End Sub

Private Sub ExpForm_Initialize()

With Range("expenditures")
      Set RangeData = .Rows(5)
End Sub

Private Sub ExpForm_QueryClose(Cancel As Integer, _
  CloseMode As Integer)

  If (CloseMode = vbFormControlMenu) Then
    Cancel = True
    Beep
  End If
End Sub

Private Sub SaveRecord()
   'Copy values from ExpenditureForm controls to Data array
        Data(1, 1) = txtSEQ.Value
        Data(1, 2) = cboOrgShp.Value
        Data(1, 3) = txtNsn.Value
        'Data(1, 4) = VLookup(.txtNsn, "Stock_num:AF_noun_tx", 2)
        Data(1, 5) = txtDoc.Value
        Data(1, 6) = txtLot.Value
        Data(1, 7) = txtQty.Value
        Data(1, 8) = txtCatCode.Value

   'Assign Data array values to current record in expenditure form
   RangeData.Value = Data
   Me.Hide
End Sub

Arc

maccas · April 19th, 2006, 02:36 AM

Bryan,

You need to declare the Data variable as some sort of array. You can do this in two ways; either as a fixed size array or a variable sized array.

With a fixed size array you merely need to declare how big each dimension of the array needs to be when declaring the variable, as follows:

Code:

Option Base 1

Private Data(1, 8) As Variant

or

Code:

Private Data(1 To 1, 1 To 8) As Variant

Alternatively you could declare a variable sized array, which you can reseize during run-time. This is a bit more flexible if you don't know what the size of your data may be in each run. I've attached an extract from your code including the variable sized array set-up.

Code:

Private Data() As Variant
Private RangeData As Range

Private Sub SaveRecord()
'Copy values from ExpenditureForm controls to Data array

    Set RangeData = ThisWorkbook.Sheets("Sheet1").Range("A1:H1")

    ReDim Preserve Data(1 To 1, 1 To 8)

    Data(1, 1) = "a" 'txtSEQ.Value
    Data(1, 2) = "b" 'cboOrgShp.Value
    Data(1, 3) = "c" 'txtNsn.Value
    Data(1, 4) = "d" 'Application.WorksheetFunction.VLookup(txtNsn.Value, "Stock_num!AF_noun_tx", 2, False)
    Data(1, 5) = "e" 'txtDoc.Value
    Data(1, 6) = "f" 'txtLot.Value
    Data(1, 7) = "g" 'txtQty.Value
    Data(1, 8) = "h" 'txtCatCode.Value


   'Assign Data array values to current record in expenditure form
   RangeData.Value = Data

End Sub

Hope this helps,
Maccas

bryan.lugo · April 19th, 2006, 04:39 AM

Thanks for the help!