macro cut and paste

H

hey

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
 
B

Bob Phillips

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)
 
J

JE McGimpsey

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
 
H

hey

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.
 
J

JE McGimpsey

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
 
H

hey

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

Thanks
 
T

Tom Ogilvy

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 said:
Now you confused me, is this exactly what I must copy and insert? Where
Click to expand...
are the ranges I had before with the lookup?
 
J

JE McGimpsey

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.
 
T

Tom Ogilvy

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 said:
Actually, the more I think about it, it would be nice if nothing got
Click to expand...
deleted and it was just copied and pasted. How is this done. Thanks again
 
Top