Resize to last entry in row and copy to sheet 1


L

L. Howard

The commented out line is giving me fits.
The MsgBox line is correct but I'm muddled on making the resize/copy work.

Thanks,
Howard

Sub MeFindIt()
Dim rngFound As Range
Dim myFnd As String
myFnd = Sheets("Sheet1").Range("B2")
Set rngFound = Sheets("Sheet2").Range("B:B").Find(What:=myFnd, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFound Is Nothing Then

'rngFound.Offset(, 1).Resize.Cells(1, Columns.Count).End(xlToLeft).Column.Copy Sheets("Sheet1").Range("C2")

MsgBox "Match for " & """" & myFnd & """" & " in cell " & rngFound.Address

Else
MsgBox "No match found. "
End If
End Sub
 
Ad

Advertisements

G

GS

The commented out line is giving me fits.
The MsgBox line is correct but I'm muddled on making the resize/copy
work.

Thanks,
Howard

Sub MeFindIt()
Dim rngFound As Range
Dim myFnd As String
myFnd = Sheets("Sheet1").Range("B2")
Set rngFound = Sheets("Sheet2").Range("B:B").Find(What:=myFnd, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows,
_
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rngFound Is Nothing Then

'rngFound.Offset(, 1).Resize.Cells(1,
Columns.Count).End(xlToLeft).Column.Copy Sheets("Sheet1").Range("C2")

MsgBox "Match for " & """" & myFnd & """" & " in cell " &
rngFound.Address

Else
MsgBox "No match found. "
End If
End Sub

You're missing the row/col args for Resize()!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
L

L. Howard

You're missing the row/col args for Resize()!

Could not see the forest for the trees!

rngFound.Offset(, 1).Resize(1, Cells(1, Columns.Count).End(xlToLeft).Column).Copy _
Sheets("Sheet1").Range("C2")This does it.

Thanks, Garry.

Howard
 
Ad

Advertisements

G

GS

Could not see the forest for the trees!

Happens to the best of us!<g> I've spent the last few days tweaking my
FileSystem.bas procedures and had to ask my more times than I'll admit
to.., "what was I thinking when I wrote this?" OR.., "who the hell
wrote this?"!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top