VBA doesn't use the worksheet functions directly like that, though there is
a group within VBA known as WorksheetFunctions, so you could use something
like:
Application.WorksheetFunction.VLookup(....)
I just put in the dots as you would use your arguments in place of those
dots.
A few more tips on how to use VBA coding to help aid your learning curve.
As a general rule, don't use Active<Object> or Activate method as these
things in general present issues that you will run into later on down the
road as you get into coding. These items do have their purposes, but should
be rarely used in nature.
Prequalify your your objects and variables to avoid ambigities. I.e.
Thisworkbook.Worksheets("PHD").Cells(4,1)
For objects being reference to more than once, generally wouldn't be a bad
idea to assign to a variable.
When naming variables, first 3 characters should be lower case and indicate
what type of variable it is, then the rest of the variable name should
indicate what it is that the variable is containing using like proper naming
casing style. This is done for readability purposes. For example, is the
variable "CellValuePHD" a numeric value or is it a string value? If it's a
numeric value, what type of numeric value is it? Date/Time counts as
numeric with a "Double" data type. In VBA, there is a Date data type, but
it's core numeric value is of "Double" data type. Therefore, in VBA, you
can use either "Double" or "Date" data types for date/time data values.
Explicitly declare your variables, and preferably to a particular data type
or object type to avoid possible mistypes and to use memory more
efficiently. Also, when you are done using the object variables, close them
out or set them to "NOTHING".
Object variables must use the "Set" keyword in front of the variable name
when being set. All other variables implicitly uses the "Let" keyword,
which can be, but isn't required to be put in front of the variable name
when it is being let to be the same value as the result of the expression on
the right side of the equation.
I know in the example below, some may say I am going overboard on
prequalifying, but it's to avoid possible conflicts as other COM objects may
be referenced down the road, which then can interfere with the various
variables, if proper qualifications isn't put into place, and the COM(s) has
the same names for references. Left, Top, Width, and Height are 4 such
common properties that can mean to so many different things. This generally
isn't as much of an issue with Excel, but has been a very big issue using
VBA in Access. That's why I do it with as much of my code as I reasonably
can. There are only certain ones that I don't prequalify, which for VBA is
mostly dealing with things that's hard coded into VBA such as the data
conversion functions, as they seem to error out with the prequalifications.
I'm going to assume that the CellValuePHD is a numeric value.
'----------START OF CODE----------
'Module Settings
Option Explicit
Sub IdenticalMinLimits()
'Method Variable Declaration
Dim wbkPhdXansDataSort As Excel.Workbook, wbkPhdXansSolCompare As
Excel.Workbook
Dim wshPhdDay1 as Excel.Worksheet, wshPHD as Excel.Worksheet
Dim rngPhdDataSort As Excel.Range, lngPhdDay1 as Long, lngPhdResult as
Long
'Method Variable Initialization
Set wbkPhdXansSolCompare = Workbooks("PHD_XANS_SOL_Comparison.xls")
Set wshPhdDay1 = wbkPhdXansSolCompare.Worksheets("Day1")
lngPhdDay1 = wshPhdDay1.Range("S7").Value2
Set wbkPhdXansDataSort = Workbooks("PHD_XANS_DATA_SORT.xls")
Set wshPHD = wbkPhdXansDataSort.Worksheets("PHD")
Set rngPhdDataSort = wshPHD.Range(Cells(4,1).Address(False, False, xlA1,
False), _
Cells(4,15).End(xlDown).Address(False, False, xlA1, False))
'Perform search
On Error Resume Next
lngPhdResult = Application.WorksheetFunction.VLookup(lngPhdDay1,
rngPhdDataSort, 0)
If Err.Number <> 0 Then
Err.Clear
MsgBox "VLookup could not locate a proper value to return.", 48
Else
MsgBox CStr(lngPhdResult), 48
End If
'Clean Up Process
Set rngPhdDataSort = Nothing
Set wshPHD = Nothing
Set wbkPhdXansDataSort = Nothing
Set wshPhdDay1 = Nothing
Set wbkPhdXansSolCompare = Nothing
End Sub
'-----------END OF CODE-----------
Sincerely,
Ronald R. Dodge, Jr.
Master MOUS 2000
