find number string, activate the cell

aelmore

New Member
Joined
Sep 8, 2008
Messages
9
I have a rather large number of worksheets that contain a column with date information recorded as an integer in the format YYYYMMDD. I'm trying to create a macro that will search each sheet for a specific number string (say, 198002, which translates to Feb 1980), and will then activate the cell containing the first one found and insert a blank row above it.

The macro recorder will only record enough to insert a blank row at wherever the active cell happened to be prior to invoking the macro. It isn't doing any of the "find this" part, and I'm not getting anywhere with the Excel help on the Find Method.

Anybody have a clue?

Thanks.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hello,

try this code

Code:
Sub ADD_ROW()
For MY_SHEETS = 1 To ActiveWorkbook.Sheets.Count
    With Sheets(MY_SHEETS)
        For MY_ROWS = 1 To .Range("A65536").End(xlUp).Row
            If .Range("A" & MY_ROWS).Value = 198002 Then
                .Rows(MY_ROWS).Insert
                GoTo MY_CONT
            End If
        Next MY_ROWS
MY_CONT:
    End With
Next MY_SHEETS
End Sub

i assume you only want the first found cell to have an inserted row above it.
 
Upvote 0
Hello and welcome to MrExcel.


Try

Code:
Sub FndDt()
Dim Found As Range, LookFor As String
LookFor = InputBox("Enter Date")
Set Found = ActiveSheet.Cells.Find(what:=LookFor)
If Not Found Is Nothing Then
    Found.EntireRow.Insert
End If
End Sub
 
Upvote 0
Hello and welcome to MrExcel.


Try

Code:
Sub FndDt()
Dim Found As Range, LookFor As String
LookFor = InputBox("Enter Date")
Set Found = ActiveSheet.Cells.Find(what:=LookFor)
If Not Found Is Nothing Then
    Found.EntireRow.Insert
End If
End Sub


This worked beautifully! Many many thanks!
 
Upvote 0
This worked beautifully! Many many thanks!

Good :)

You might want to include a couple of error traps:

Code:
Sub FndDt()
Dim Found As Range, LookFor As String
LookFor = InputBox("Enter Date")
If LookFor = "" Then Exit Sub
Set Found = ActiveSheet.Cells.Find(what:=LookFor)
If Found Is Nothing Then
    MsgBox LookFor & " not found", vbCritical
Else
    Found.EntireRow.Insert
End If
End Sub
 
Upvote 0
Wow. I've got to say this is largely unintelligible to me. I'm new to programming in any environment and the "aha" moments are still relatively few and far between. What exactly is an error trap?
 
Upvote 0
What exactly is an error trap?

In this case simply checking for a null string/Cancel or the date not being found - I've commented the code below

Code:
Sub FndDt()
Dim Found As Range, LookFor As String
LookFor = InputBox("Enter Date")
If LookFor = "" Then Exit Sub '<<< If user enters nothing or presses Cancel exit the code
Set Found = ActiveSheet.Cells.Find(what:=LookFor)
If Found Is Nothing Then '<<< If the value isn't found then alert the user
    MsgBox LookFor & " not found", vbCritical
Else
    Found.EntireRow.Insert
End If
End Sub
 
Upvote 0
Ah, of course. Again, thank you so much. I've been pecking through helpfiles about this for longer than I'd like to admit...

Cheers
 
Upvote 0

Forum statistics

Threads
1,224,559
Messages
6,179,513
Members
452,921
Latest member
BBQKING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top