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

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

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