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
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
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks