Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Find a date over & over

  Asked By: Lewis    Date: Dec 15    Category: MS Office    Views: 964
  

I have a spreadsheet that has several dates the same. i want to insert a full
row after each
occurrence (by calling a subroutine). The range is B1 to B500 and I
succesfully find the first occurrence , 'insertarowbelow' (sub) and set the
activecell (part of insertarow below sub) to the next cell in B. But whatever
loop I use it just seems to start again from B1 - ie just getting one added row
below the first date. Any ideas?

Public Sub FINDdate()
Dim egg As Date
egg = "26/7/2007"

Do (?)

Range("b1:b500").Find(What:=egg, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False
_
, SearchFormat:=False).Activate

Call insertarowbelow

Loop (?)

--------------------------------------------------------------------------------\
--------------------------------------------
Public Sub insertarowbelow()
ActiveCell.Offset(0, -1).Select
Selection.EntireRow.Insert
ActiveCell.Offset(1, 1).Select
End Sub

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Bodhi Smith     Answered On: Dec 15

I believe the find  function has an "after" parameter. Check the help.

 
Answer #2    Answered By: Charlotte Brown     Answered On: Dec 15

I found this code on ozgrid.com, it looks like what you are looking
for. This code finds each instance of the word "Cat" in column A and
sets the font to Bold. It should be easy for you to adapt this to
search for a date  in column B and insert  a row. You might have to
change the loop to step backwards.

Sub BoldCat()
Dim iLoop As Integer
Dim rNa As Range
Dim i As Integer

iLoop = WorksheetFunction.CountIf(Columns(1), "Cat")
Set rNa = Range("A1")

For i = 1 To iLoop
Set rNa = Columns(1).Find(What:="Cat", After:=rNa, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True)
rNa.Font.Bold=True

Next i

End Sub

 
Answer #3    Answered By: Abbie Hughes     Answered On: Dec 15

will give your ideas a try and let you know
how I get on.

 
Answer #4    Answered By: Myrtle Wilson     Answered On: Dec 15

Code works well in identifying each date  and setting font to bold.
It even calls the sub to insert  a row. Problem is instead of it
inserting a row below the next instance it inserts the row below the
one its just done. There must be something I need to do in the loop
to stop it returning to the first instance and repeating the insert?

 
Answer #5    Answered By: Cain Smith     Answered On: Dec 15

Can you post the code you are using?

 
Answer #6    Answered By: Aaeedah Khan     Answered On: Dec 15

Sorry, it sounds like you took the code I posted and added  in the
parts from your original code. You are using a separate subroutine  to
select the row and do the inserting. That is probably why it
is "restarting." You don't need to select the row to act on it, you
can use the cell  properties to manipulate the data. I tested the code
below and it worked.



Sub FindDate()
'
'
'
Dim iLoop As Integer
Dim rNa As Range
Dim i As Integer

iLoop = WorksheetFunction.CountIf(Columns(1), "26/7/2007")
Set rNa = Range("A1")

For i = 1 To iLoop
Set rNa = Columns(1).Find(What:="26/7/2007", After:=rNa, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True)
rNa.EntireRow.Insert
Next i

End Sub

 
Answer #7    Answered By: Lucio Ferrrari     Answered On: Dec 15

Ok - think I've cracked it - well seems to work OK.

Just replaced the rNa.Font.Bold = True
with rNa.EntireRow.Insert

rather than calling  a sub to do the insert

thanks for your time guys

 
Didn't find what you were looking for? Find more on Find a date over & over Or get search suggestion and latest updates.




Tagged: