Hi Lakshitha
There are many ways to find last row, one of them is using End method
of range.
Here is modified code and try let us know, if it works
-------------------------------------------------------------------------------------------
Sub Macro1()
Dim count As Integer
Dim lstRow As Integer
Dim fCell As Range 'First Cell Found
Dim fAdd As String 'Adress of First Cell
'To determine the Last Row in the column B
lstRow = Range("B65536").End(xlUp).Row
Range("B1:B" & lstRow).Select
On Error GoTo ErrorH
Selection.Find(What:="sep", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
fAdd = ActiveCell.Address
Do
Selection.FindNext(After:=ActiveCell).Activate
Range("DU10").Offset(0, count).Value = ActiveCell.Offset(0,
1).Value
count = count + 1
Loop While ActiveCell.Address <> fAdd
ErrorH:
End Sub
-------------------------------------------------------------------------------------------
Try to understand the logic and post back, if need clarifications.
________________________________________
Thanks & Regards
Ashish Jain
McKinsey India Knowledge Center
(Microsoft Certified Application Specialist)
(Microsoft Certified Professional)
http://www.excelitems.com
http://www.openexcel.com
________________________________________
On Nov 11, 2:12 pm, Lakshitha <[email protected]> wrote:
> i have a column filled with month names. but not in order here and
> there. not in a list.
>
> I want to search for all "sep" in that whole column and put the values
> next to it another column. but How to end the loop once it reach the
> last "sept" in the column. In other words howt to stop it from endless
> looping
>
> ---------------------------------------------------------------------------
> -----------
> Sub Macro1()
> Dim count As Integer
>
> Columns("B:B").Select
> Selection.Find(What:="sep", After:=ActiveCell, LookIn:=xlFormulas,
> _
> LookAt:=xlPart, SearchOrder:=xlByRows,
> SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
>
> Do While ActiveCell <> ""
> Selection.FindNext(After:=ActiveCell).Activate
> ActiveSheet.Range("DU10").Offset(count, 0).Value =
> ActiveCell.Offset(0, 5).Value
> count = count + 1
>
> Loop
>
> ---------------------------------------------------------------------------
> -----------
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
