Dear Timbo
Place this condition on top of each section. This will return the number of
instances of your search string
If WorksheetFunction.CountIf(Columns(1), "205") 0 Then
'Wirte code here
End If
If this post helps click Yes
-------------
Jacob Skaria
"Timbo" wrote:
Every week I recieve a spreadsheet which I have to sort and analyse.
The code below is very basic and does what I want it to but I need it
to be more flexible.
In Column A there are personal ID Numbers. In the first part of code
below 201 refers to a person I have called Fred, in the second part 205
refers to a person I have called Bill.
I sort the worksheet by ID Number so all of the 201's are at the top
906 is at the bottom and loads of numbers are missing in bewteen.
Variables: There are varying numbers of entries for each code so there
could be 100 201's and 50 205's this week and 30 201's and 20 205's next
week.
Some weeks some ID Numbers may not appear at all i.e. this week 205 may
be in the worksheet next week it might not.
This is my problem the macro works fine until there is a week when an
ID Number doesn't appear then it falls over.
Can anyone suggest a way around please or some alternative code?
Code:
--------------------
Selection.Find(What:="201", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
ActiveCell.Offset(Row_No + 1, 0).Select
ActiveCell.Value = "FRED"
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Find(What:="205", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = "BILL"
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Font.Bold = True
Selection.Font.Underline = xlUnderlineStyleSingle
ActiveCell.Columns("A:A").EntireColumn.Select
--------------------
--
Timbo
------------------------------------------------------------------------
Timbo's Profile: http://www.thecodecage.com/forumz/member.php?userid=24
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=77492