macro cut and paste
 

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

macro cut and paste
 

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

macro cut and paste
 

The end should have

End Su

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

macro cut and paste
 

Tried that, doesnt work either!

macro cut and paste
 

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

macro cut and paste
 

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.

macro cut and paste
 

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

macro cut and paste
 

see my comments above gh1984

macro cut and paste
 

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.

macro cut and paste
 

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

Thanks

macro cut and paste
 

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

macro cut and paste
 

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?

macro cut and paste
 

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
End If
End Sub


--
Regards,
Tom Ogilvy


"HEY" wrote in message
...
Actually, the more I think about it, it would be nice if nothing got
deleted and it was just copied and pasted. How is this done. Thanks again