Type mismatch when using Application.WorksheetFunction.Match

[bostenson]

Hope I'm not abusing my privileges of using this forum as much as I do, but I do hope the scenario I offer will be a lot clearer than the last time I posted. So thanks, in advance, for your patience.

In my workbook, there is a sheet named shtGeneralLedger where a list of general ledger names are saved and to view a list of those names is a listbox in frmGeneralLedger. A name is selected in the listbox and the Edit command is clicked to edit the general ledger details in frmEditGeneralLedger, and the boxes are populated when the form is initialized. However, I am having trouble with what appears to be the match function when I click on the save command and it results in "Run-time error '13': Type mismatch". Below is the code I have written for this function:


Dim GL As Worksheet
Set GL = ThisWorkbook.Sheets("GeneralLedger")

Dim GLr As Long

GLr = Application.WorksheetFunction.Match(CLng(Me.txbOrigGLName.Value), GL.Range("A:A"), 0)


txbOrigGLName holds the original general ledger name and is used to match with the original g/l name in shtGeneralLedger in case the original name is changed. It appears correct to me, but obviously not, so what am I missing? Thanks again.


- Ben

[Paul_Hossler]

Nothing jumps out (at me anyways)

Put a breakpoint on the GLr = line and run the macro

when it breaks, go to the Immediate window and enter


?Application.WorksheetFunction.Match(CLng(Me.txbOrigGLName.Value), GL.Range("A:A"), 0)


to see what it is. Might provide a hint


(and, 'No', you're not abusing your privileges here - :thumb)

[snb]

Sub M_snb()
sn=Sheets("GeneralLedger").columns(1).specialcells(2)
msgbox application.match(Textbox1.text,sn,0)
End Sub

NB. use application.match( ); it is simpler, better and sufficient.
The .worksheetfunction. in Excel's VBA isn't reliable.

[Paul_Hossler]

NB. use application.match( ); it is simpler, better and sufficient.
The .worksheetfunction. in Excel's VBA isn't reliable.

I think that .WorksheetFunction is reliable, it just acts a little differently than the same line without it

A, B, C, D, and E in in A1:A5

27944

When you use it, if the WS function fails you get a run time 1004 error that must be handled. I just normally use On Error Resume Next to catch it

When you do not use it, if the WS function fails you get a VBA error must be handled.

To me, it's 6 of one .5 dozen of the other

[snb]

If you omit .worksheetfunction. no error occurs.
I am talking about all Excel formulae, not just this one 'match', e.g. vlookup, sumproduct, etc.

[bostenson]

Okay, here is the result when I hover the curser over the code:

GLr=0
CLng(Me.txbOrigGLName.Value)=<Type mismatch>
however, by itself, Me.txbOrigGLName.Value = is the G/L name that populates the textbox

I removed CLng, but I still have the same error.

[bostenson]

If it helps, here is the entire code for the command:


Private Sub cmdSaveGLEdit_Click()
' Validation

If Me.cmbEditGLCategory.Value = "" Then
MsgBox "Please enter a G/L Category.", vbCritical
Exit Sub
End If

If Me.txbEditGLName.Value = "" Then
MsgBox "Please enter a General Ledger Name.", vbCritical
Exit Sub
End If


' Declarations


Dim GL As Worksheet
Set GL = ThisWorkbook.Sheets("GeneralLedger")

Dim RN As Worksheet
Set RN = ThisWorkbook.Sheets("RegisterNames")

Dim GLr As Long
Dim RNr As Long

GLr = Application.WorksheetFunction.Match(Me.txbOrigGLName.Value, GL.Range("A:A"), 0)
RNr = Application.WorksheetFunction.Match(Me.txbOrigGLName.Value, RN.Range("A:A"), 0)

Dim a As Integer
Dim i As Long
a = ThisWorkbook.Worksheets.Count

Dim msgValue As VbMsgBoxResult

' Edit General Ledger entries


GL.Range("A" & GLr).Value = Me.cmbEditGLCategory.Value
GL.Range("B" & GLr).Value = Me.txbEditGLName.Value
GL.Range("C" & GLr).Value = Me.txbEditGLBeginBal.Value
GL.Range("D" & GLr).Value = Me.txbEditGLMonthTotalDue.Value
GL.Range("E" & GLr).Value = Me.txbEditGLCreditLimit.Value
GL.Range("F" & GLr).Value = Me.cmbEditGLTermCode.Value


If cbxEditGLAutoWithdrawal.Value = True Then
GL.Range("G" & GLr).Value = "X"
Else
GL.Range("G" & GLr).Value = ""
End If


' Sort values in sheet GeneralLedger by Category and then by name

GL.UsedRange.Sort key1:=GL.Columns("A"), order1:=xlAscending, key2:=GL.Columns("B"), order2:=xlAscending, Header:=xlYes

' Edit GL Names in Sheet RegisterNames and sort RegisterNames

If Me.cmbEditGLCategory.Value = "Checking" Then
RN.Range("A" & RNr).Value = Me.txbEditGLName.Value
RN.UsedRange.Sort key1:=RN.Cells, order1:=xlAscending, Header:=xlYes
ElseIf Me.cmbEditGLCategory.Value = "Credit Card" Then
RN.Range("A" & RNr).Value = Me.txbEditGLName.Value
RN.UsedRange.Sort key1:=RN.Cells, order1:=xlAscending, Header:=xlYes
ElseIf Me.cmbEditGLCategory.Value = "Credit Line" Then
RN.Range("A" & RNr).Value = Me.txbEditGLName.Value
RN.UsedRange.Sort key1:=RN.Cells, order1:=xlAscending, Header:=xlYes
ElseIf Me.cmbEditGLCategory.Value = "Expense" Then
' !Do nothing!
ElseIf Me.cmbEditGLCategory.Value = "Liability" Then
RN.Range("A" & RNr).Value = Me.txbEditGLName.Value
RN.UsedRange.Sort key1:=RN.Cells, order1:=xlAscending, Header:=xlYes
Else
Me.cmbEditGLCategory.Value = "Saving"
RN.Range("A" & RNr).Value = Me.txbEditGLName.Value
RN.UsedRange.Sort key1:=RN.Cells, order1:=xlAscending, Header:=xlYes
End If

' Edit Sheets


For i = 1 To a

If Worksheets(i).Name = txbOrigGLName.Value Then
Worksheets(i).Name = txbEditGLName.Value
ActiveSheet.Range("H2").Value = txbEditGLBeginBal
ActiveSheet.Range("H2").Style = "Comma"
End If
Next


' Confirm G/L Edit


msgValue = MsgBox("Do you want to update General Ledger information?", vbYesNo + vbInformation, "Confirm G/L Edit")
If msgValue = vbNo Then Exit Sub
If msgValue = vbYes Then MsgBox "General Ledger information has been updated.", vbInformation, "G/L has been edited"


Call userform_initialize


End Sub

[Paul_Hossler]

The problem is that no one can test it without creating all the userforms and worksheets that are required, and I would be surprised if wanted to go to all that trouble

Just looking at the code would be a guess

It'd be better if you attached a small workbook with all the parts and pieces and macros that shows the problem.

[bostenson]

The problem is that no one can test it without creating all the userforms and worksheets that are required, and I would be surprised if wanted to go to all that trouble

Just looking at the code would be a guess

It'd be better if you attached a small workbook with all the parts and pieces and macros that shows the problem.

Sorry about that, I knew that before I even posted it, the workbook is attached.

27953

[Paul_Hossler]

Wrong column I think




GLr = Application.WorksheetFunction.Match(Me.txbOrigGLName.Value, GL.Range("B:B"), 0)