Using lookup

[CAM]

Hello,

I have a worksheet called "ChartOfAccounts", which contains chart of
accounts - account number and description. I have another worksheet called
"MonthlyExpense" containing expenses for the month. What I want is to do a
lookup matching the worksheet "MonthlyExpense" with "ChartOAccounts.
Example: In the "MonthlyExpense" worksheet there is an account number
"AT2543050509" I want to match with the corresponding account number from
the "ChartOfAccount" worksheet called "Dues and subscription" account
number "ATXX43050509" the XX in the "ChartOfAccounts" is for the department
number, which varies. I want to somehow match "AT2543050509" from the
"MonthlyExpense" worksheet with "ATXX43050509" from the "ChartOfAccount"
worksheet. How do I do that? My problem is that the 3rd and 4th digit in
the "ChartOfAccounts" contains the "XX". Any tips will be appreciated. I
am using Excel 2007. Thank you in advance.

Cheers

 

[Nigel]

Arrange to scan your ChartOAccounts list for each MonthAccount and use a
version of the following logic to test for a matching string using the like
function.... example code below


Dim sTestString As String
Dim sMonthAccount As String
Dim sChartOAccounts As String

sMonthAccount = "AT1234567890"
sChartOAccounts = "ATXX34567890"

sTestString = Left(sMonthAccount, 2) & _
"**" & _
Mid(sMonthAccount, 5, Len(sMonthAccount) - 4)

If sChartOAccounts Like sTestString Then

' code for a match

End If