Resize to last entry in row and copy to sheet 1

[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

 

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

 

[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