Below what you gave me last week doesnt work. It doesnt like the last end if

Thank

sub foo(
dim look_rng as rang
dim source_rng as rang
dim target_rng as rang
dim ret_valu
with activeshee
set look_rng = .range("A1"
set target_rng = .rnage("B1"
set source_rng = .range("C1:D100"
end wit
ret_value=application.vlookup(look_rng,source_rng, 2,0
if iserror(ret_value) the
ret_value="
end i
target_rng=ret_valu
end i

Looks like that End If should be End Sub.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hey" wrote in message
...
Below what you gave me last week doesnt work. It doesnt like the last
end if?

Thanks


sub foo()
dim look_rng as range
dim source_rng as range
dim target_rng as range
dim ret_value
with activesheet
set look_rng = .range("A1")
set target_rng = .rnage("B1")
set source_rng = .range("C1:D100")
end with
ret_value=application.vlookup(look_rng,source_rng, 2,0)
if iserror(ret_value) then
ret_value=""
end if
target_rng=ret_value
end if

Tried that, doesnt work either!

OK, end sub does work if I correct the misspelling of the word rnage to range
But, this isnt quit what I want to do. Close but not quit. I would like it to find the lookup but then cut and paste it into cell b1 so that all formats come with. Dont ask why it would be a long explaination. Is it possible

Thanks for any help.

One way:

Public Sub test()
Dim rFound As Range
Set rFound = Columns(3).Find( _
What:=Range("A1").Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then _
rFound.Offset(0, 1).Copy Destination:=Range("B1")
End Sub



In article ,
hey wrote:

OK, end sub does work if I correct the misspelling of the word rnage to
range.
But, this isnt quit what I want to do. Close but not quit. I would like it to
find the lookup but then cut and paste it into cell b1 so that all formats
come with. Dont ask why it would be a long explaination. Is it possible?

Thanks for any help.

Now you confused me, is this exactly what I must copy and insert? Where are the ranges I had before with the lookup

Thanks

Sub foo()
Dim look_rng As Range
Dim source_rng As Range
Dim target_rng As Range
Dim rFound As Range

With ActiveSheet
Set look_rng = .Range("A1")
Set target_rng = .Range("B1")
Set source_rng = .Range("C1:C100")
End With
Set rFound = source_rng.Find( _
What:=look_rng.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then
rFound.Offset(0, 1).Copy Destination:=target_rng
rFound.Resize(1, 2).Delete Shift:=xlShiftUp
End If
End Sub

The above deletes the cells in columns C and D for the row where the value
was found.

If you only want the cell in column D deleted (this is the cell copied)
rFound.Offset(0, 1).Delete Shift:=xlShiftUp

If youonly want the cell in column C deleted (this is the cell found)
rFound.Delete Shift:=xlShiftUp

--
Regards,
Tom Ogilvy


"hey" wrote in message
...
Now you confused me, is this exactly what I must copy and insert? Where
are the ranges I had before with the lookup?

Thanks

Yes - that's what you should enter. I assumed you were looking up over
all of column C. If you'd rather have just C1:C100 as Frank had, instead
of

Set rFound = Columns(3).Find( _

use

Set rFound = Range("C1:C100").Find( _


Since you didn't reply to a previous post, I can't tell if those are the
ranges you had before - I assumed Frank used your ranges.

In article ,
hey wrote:

Now you confused me, is this exactly what I must copy and insert? Where are
the ranges I had before with the lookup?

The end should have

End Su

--
Message posted from http://www.ExcelForum.com

Frank never tested the code he posted - there are a couple of errors.

Try:

Public Sub foo()
Dim look_rng As Range
Dim source_rng As Range
Dim target_rng As Range
Dim ret_Value As Variant
With ActiveSheet
Set look_rng = .Range("A1")
Set target_rng = .Range("B1")
Set source_rng = .Range("C1:D100")
End With
ret_Value = Application.VLookup(look_rng, source_rng, 2, 0)
If IsError(ret_Value) Then ret_Value = ""
target_rng = ret_Value
End Sub


In article ,
hey wrote:

Below what you gave me last week doesnt work. It doesnt like the last end
if?

Thanks


sub foo()
dim look_rng as range
dim source_rng as range
dim target_rng as range
dim ret_value
with activesheet
set look_rng = .range("A1")
set target_rng = .rnage("B1")
set source_rng = .range("C1:D100")
end with
ret_value=application.vlookup(look_rng,source_rng, 2,0)
if iserror(ret_value) then
ret_value=""
end if
target_rng=ret_value
end if

see my comments above gh1984

Besides end sub,
set target_rng = .rnage("B1")


--
Don Guillett
SalesAid Software

"hey" wrote in message
...
Below what you gave me last week doesnt work. It doesnt like the last
end if?

Thanks


sub foo()
dim look_rng as range
dim source_rng as range
dim target_rng as range
dim ret_value
with activesheet
set look_rng = .range("A1")
set target_rng = .rnage("B1")
set source_rng = .range("C1:D100")
end with
ret_value=application.vlookup(look_rng,source_rng, 2,0)
if iserror(ret_value) then
ret_value=""
end if
target_rng=ret_value
end if