Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in reverse
Hi,
I need to change the value of cells found via application.vlookup in vba Is there a simple way to do this from a userform? Thanx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in reverse
Assume you Vlookup looks as A1:F10 of the activesheet using the value in
Textbox1. Then to alter the cell in column A that matches the value in Textbox1: Dim rng as Range, rng1 as Range, res as Variant set rng = Range("A1:A10") res = Application.Match(Userform1.Textbox1.Text,rng,1) if not iserror(res) then set rng1 = rng(res) rng1.clearcontents end if to alter the value in column E (column 5) of that row Dim rng as Range, rng1 as Range, res as Variant set rng = Range("A1:A10") res = Application.Match(Userform1.Textbox1.Text,rng,1) if not iserror(res) then set rng1 = rng(res,5) rng1.clearcontents end if -- Regards, Tom Ogilvy "Newbie" wrote: Hi, I need to change the value of cells found via application.vlookup in vba Is there a simple way to do this from a userform? Thanx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in reverse
Option Explicit
sub testme() dim myRng as range dim res as variant dim myVal as variant with worksheets("Sheet999") set myrng = .range("A1",.cells(.rows.count,"A").end(xlup)) myVal = "something to match" res = application.match(myval,myrng,0) if iserror(res) then 'msgbox not found else myrng(res).offset(0,5).value = "new value" end if end with end sub the .offset(0,5) says to go 5 columns to the left of the column to be checked. Newbie wrote: Hi, I need to change the value of cells found via application.vlookup in vba Is there a simple way to do this from a userform? Thanx -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup in reverse | Excel Discussion (Misc queries) | |||
Any Way to Use Vlookup in Reverse? | Excel Discussion (Misc queries) | |||
REVERSE VLOOKUP | Excel Discussion (Misc queries) | |||
Reverse VLOOKUP | Excel Discussion (Misc queries) | |||
Reverse VlookUp | Excel Discussion (Misc queries) |