|
Subject:
|
run-time error 13: type mismatch
|
|
Posted By:
|
bryan.lugo
|
Post Date:
|
4/18/2006 1:41:50 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
|
|
Reply By:
|
maccas
|
Reply Date:
|
4/19/2006 2:36:16 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:
Option Base 1
Private Data(1, 8) As Variant
or
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.
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
|
|
Reply By:
|
bryan.lugo
|
Reply Date:
|
4/19/2006 4:39:13 AM
|
Thanks for the help!
|
|
