vlookup in vb
Hi!
You are looking for something like the function below:
Option Explicit
Private Function MyVLOOKUP( _
ByVal LookUpValue As Variant, _
ByVal rngTableArray As Range, _
Optional intCol As Integer = 1, _
Optional fRangeLookUp As Boolean) As Range
Dim lngRow As Long
On Error GoTo LookUpError
With rngTableArray
lngRow = Application.WorksheetFunction.Match( _
Arg1:=LookUpValue, _
Arg2:=.Range("A1").EntireColumn, _
arg3:=fRangeLookUp)
Set rngTableArray = .Cells(lngRow, intCol)
End With
Set MyVLOOKUP = rngTableArray
ExitProc:
Exit Function
LookUpError:
Err.Raise Err.Number, Err.Source, Err.Description
Err.Clear
Resume ExitProc
End Function
Test it with this makro:
Sub SubtractValue()
Dim varLookUpValue As Variant
Dim rngRet As Variant
Dim rngTable As Range
varLookUpValue = Sheets("main").Range("B1").Value
Set rngTable = Sheets("database").Range("A:CB")
On Error Resume Next
Set rngRet = MyVLOOKUP(varLookUpValue, rngTable, rngTable.Columns.Count)
If rngRet Is Nothing Then
MsgBox "Value not found!", vbExclamation
ElseIf Not IsNumeric(rngRet) Then
MsgBox rngRet & " in " & rngRet.Parent.Name _
& " is not numeric!", vbExclamation
Else
rngRet.Value = rngRet.Value - 1
MsgBox "The new value of " & rngRet.Address(False, False) _
& " in " & rngRet.Parent.Name & " is " & rngRet.Value,
vbInformation
End If
End Sub
--
John
Ο χρήστης " *γγραψε:
Ok here is what I am trying to do. I have a worksheet called "main"
and another called "database''
I want to lookup the value in cell B1 in "main'' worksheet in the
"database'' worksheet which is in column A.
Once found I want it to go to column CB in that same row and subtract
1 from the value there.
I know how to use vlookup to find a value but I figured to subtract 1
from that value a must use a macro, and I am stumped
How can I do this
Thanks in advance
|