vlookup in vb
 

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

vlookup in vb
 

Option Explicit
Sub testme()

Dim res As Variant
Dim LookupRng As Range
Dim myCell As Range

With Worksheets("database")
Set LookupRng = .Range("b:b")
End With

Set myCell = Worksheets("main").Range("B1")

res = Application.Match(myCell.Value, LookupRng, 0)
If IsError(res) Then
MsgBox "Not found!"
Else
With LookupRng.Parent.Cells(res, "CB")
If IsNumeric(.Value) Then
.Value = .Value - 1
Else
MsgBox "Not numeric!"
End If
End With
End If
End Sub


wrote:

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

--

Dave Peterson

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

vlookup in vb
 

On Sep 13, 8:58*pm, John_John
wrote:
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

Thanks the first one worked great