• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trouble with VBA Vlookup code

M

MYY

New Member
Hi all, I hope you can help me with the following problem that has me stuck. I'm not very experienced with VBA, so I may be making a simple mistake.

I have a spreadsheet to work out performance allocations for a hedge fund. One sheet contains the hurdle marks for each account (hurdle mark = the amount which the investor was promised no fee until that amount is reached). But when my VBA code tries to read in the number and store it in a variable, using VLOOKUP, I get a type mismatch error, which I realize is because the VLOOKUP is failing and returning an error (which, of course, is a type mismatch with the double variable that I'm trying to assign it to). I don't understand why the VLOOKUP is failing though. If someone can help me, I would really appreciate it. The offending line is:

HurdleMark = Application.VLookup(DateChosen, HurdleMarkTable, _
HurdleMarkColumnNumber)​

HurdleMark is dimmed as a double. DateChosen is a date passed in to the function from a DateControl in a UserForm. HurdleMarkTable is a listobject that was previously set to the HurdleMark table on the Hurdle Mark worksheet. HurdleMarkColumnNumber is a variable that was previously set to the column of interest within a For Each loop.

Looking at the Watch and Immediate window, DateChosen, HurdleMarkTable, and HurdleMarkColumnNumber all appear to be set to the proper variables (12/31/2014, HurdleMark, and 2, respectively). I checked that the first column in the HurdleMark table on the worksheet is properly formatted to dates, not text. (If it makes any difference, it is custom formatted to mm/dd/yyyy, instead of the defaut date format.) I also ran the VLOOKUP formula directly on the worksheet, and as long as I used Datevalue or Date to turn the first argument into a date, it worked. I thought that for some reason maybe VBA was passing VLOOKUP the DateChosen variable formatted as a text string, so I tried Clng(DateChosen) and passing that on, and it still didn't work. I am at my wits end. Any help would be appreciated. I've pasted the full procedure below if that helps at all. Thank you for your time.

Code: 
Sub PerformanceAllocationForMonth(DateChosen As Date)

Dim CapitalAccountsSummaryTable As ListObject
Dim BeginningOfYear As Date
Dim FirstRowNumber As Integer
Dim PresentRowNumber As Integer
Dim BeginningOfYearRowNumber As Integer
Dim AllocationRowNumber As Integer
Dim HurdleMarkTable As ListObject
Dim HighWaterMarkTable As ListObject
Dim FirstHurdleColumnNumber As Integer
Dim FirstHighWaterColumnNumber As Integer
Dim HurdleMarkColumnNumber As Integer
Dim HighWaterMarkColumnNumber As Integer
Dim Header As String
Dim AccountColumn As ListColumn
Dim AllocationType As Integer
Dim HurdleMark As Double
Dim HighWaterMark As Double
Dim PresentAmount As Double
Dim BeginningOfYearAmount As Double
Dim AllocationCell As Range
Const GeneralPartners As Integer = 0
Const OriginalInvestorM As Integer = 1
Const OriginalInvestorRl As Integer = 2
Const NewDealM As Integer = 3
Const NewDealR As Integer = 4
Const SInvestor As Integer = 5

Set CapitalAccountsSummaryTable = Worksheets("Capital Accounts Summary").ListObjects("CapitalAccountsSummary")
FirstRowNumber = CapitalAccountsSummaryTable.Range.Row - 1
PresentRowNumber = CapitalAccountsSummaryTable.ListColumns(1).Range.Find _
  (What:=DateChosen, LookIn:=xlValues, lookat:=xlPart, _
  searchorder:=xlByRows, searchdirection:=xlNext).Row _
  - FirstRowNumber
BeginningOfYear = DateSerial(Year(DateChosen), 1, 1)
BeginningOfYearRowNumber = CapitalAccountsSummaryTable.ListColumns(1).Range.Find _
  (What:=BeginningOfYear, LookIn:=xlValues, lookat:=xlPart, _
  searchorder:=xlByRows, searchdirection:=xlNext).Row - FirstRowNumber
AllocationRowNumber = PresentRowNumber + 3
Set HurdleMarkTable = Worksheets("Hurdle Mark").ListObjects("HurdleMark")
Set HighWaterMarkTable = Worksheets("High Water Mark").ListObjects("HighWaterMark")
FirstHurdleColumnNumber = HurdleMarkTable.Range.Column - 1
FirstHighWaterColumnNumber = HighWaterMarkTable.Range.Column - 1

For Each AccountColumn In CapitalAccountsSummaryTable.ListColumns

  Header = AccountColumn.Range.Rows(1).Value
  If Right(Header, 1) <> "b" Then
  GoTo NextIteration
  End If
  If Header = "b" Then
  GoTo NextIteration
  End If

  Header = Left(Header, 3)
  HurdleMarkColumnNumber = HurdleMarkTable.HeaderRowRange.Find(What:=Header, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, _
  searchdirection:=xlNext).Column - FirstHurdleColumnNumber
  HighWaterMarkColumnNumber = HighWaterMarkTable.HeaderRowRange.Find(What:=Header, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, _
  searchdirection:=xlNext).Column - FirstHighWaterColumnNumber
  HurdleMark = Application.VLookup(DateChosen, HurdleMarkTable, _
  HurdleMarkColumnNumber)
  HighWaterMark = Application.VLookup(DateChosen, HighWaterMarkTable, _
  HighWaterMarkColumnNumber)
  PresentAmount = AccountColumn.Range.Resize(1, 1).Offset(RowOffset:= _
  PresentRowNumber, ColumnOffset:=-1).Value
  BeginningOfYearAmount = AccountColumn.Range.Resize(1, 1).Offset(RowOffset:= _
  BeginningOfYearRowNumber, ColumnOffset:=-1).Value
  Set AllocationCell = AccountColumn.Range.Resize(RowSize:=1) _
  .Offset(RowOffset:=AllocationRowNumber, ColumnOffset:=0)
  AllocationType = Application.VLookup(Header, Worksheets("Capital Accounts Info") _
  .ListObjects("CapitalAccountsInfo"), 6)
 
  Select Case AllocationType
  Case Is = GeneralPartners
  Case Is = OriginalInvestorM
  Call AllocateOriginalInvestorM(PresentAmount, BeginningOfYearAmount, _
  HurdleMark, HighWaterMark, AllocationCell)
  Call ResetHurdleMarkHurdle21(HurdleMark, HurdleMarkColumnNumber, DateChosen, _
  AllocationCell)
  Call ResetHighWaterMark(HighWaterMark, HighWaterMarkColumnNumber, DateChosen, _
  AllocationCell)
  Case Is = OriginalInvestorR
  Call AllocateOriginalInvestorR(PresentAmount, BeginningOfYearAmount, _
  HurdleMark, HighWaterMark, AllocationCell)
  Call ResetHurdleMarkHurdle21(HurdleMark, HurdleMarkColumnNumber, DateChosen, _
  AllocationCell)
  Call ResetHighWaterMark(HighWaterMark, HighWaterMarkColumnNumber, DateChosen, _
  AllocationCell)
  Case Is = NewDealM
  Call AllocateNewDealM(PresentAmount, BeginningOfYearAmount, _
  HurdleMark, HighWaterMark, AllocationCell)
  Call ResetHurdleMarkNoHurdle(HurdleMark, HurdleMarkColumnNumber, DateChosen, _
  AllocationCell)
  Call ResetHighWaterMark(HighWaterMark, HighWaterMarkColumnNumber, DateChosen, _
  AllocationCell)
  Case Is = NewDealR
  Call AllocateNewDealR(PresentAmount, BeginningOfYearAmount, _
  HurdleMark, HighWaterMark, AllocationCell)
  Call ResetHurdleMarkNoHurdle(HurdleMark, HurdleMarkColumnNumber, DateChosen, _
  AllocationCell)
  Call ResetHighWaterMark(HighWaterMark, HighWaterMarkColumnNumber, DateChosen, _
  AllocationCell)
  Case Is = SInvestor
  Call AllocateSInvestor(PresentAmount, BeginningOfYearAmount, _
  HurdleMark, HighWaterMark, AllocationCell)
  Call ResetHurdleMarkNoHurdle(HurdleMark, HurdleMarkColumnNumber, DateChosen, _
  AllocationCell)
  Call ResetHighWaterMark(HighWaterMark, HighWaterMarkColumnNumber, DateChosen, _
  AllocationCell)
  End Select

NextIteration:
Next
 
UpdateGeneralAllocations (AllocationRowNumber)

End Sub
__________________________________________________________________
Mod edit : post moved to proper forum …​
 
@MYY
Which version of Excel do You use?
I use WorksheetFunction.VLookup(a,b,c)
instead of Application.VLookup(a,b,c).
 

Hi !

Better is to use Application with a Variant variable
(no need On Error) instead of WorksheetFunction …
​
 
Yes and yes, but that doesn't actually address why this is returning an error in the first place. Why isn't this VLOOKUP working like one on the spreadsheet itself is?
 
HurdleMarkTable is a Listobject, not a range, which is what VLOOKUP requires. Also, as you are not specifying the False final argument, make sure not to include the header row - I assume your data is sorted?

I'd also recommend converting the date variable to a Long or Double for the VLOOKUP.
 
Debaser said:
HurdleMarkTable is a Listobject, not a range, which is what VLOOKUP requires. Also, as you are not specifying the False final argument, make sure not to include the header row - I assume your data is sorted?

I'd also recommend converting the date variable to a Long or Double for the VLOOKUP.
Click to expand...

You got it! I can't believe I missed that. Both items were necessary. I needed to change it to HurdleMarkTable.range and change the datereference to Clng(DateChosen). Thank you very much.

After posting my question, I also realized that I could accomplish the same thing by using Find.Offset (Find to locate the date and then offset to move to the required column). In fact, I can't think of any place that I would want to use VLOOKUP over Find.Offset other than less typing. Is one more efficient than the other?
 
Find can be very problematic with date values, especially if your regional settings are non-U.S.
 

No issue with dates and Find with text format,
looking in formulas and some others tricks …
​
 
You must log in or register to reply here.
Back
Top