Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |