find number string, activate the cell

[aelmore]

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.

 

[onlyadrafter]

Hello,

try this 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.

 

[VoG]

Hello and welcome to MrExcel.


Try

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

 

[aelmore]

Hello and welcome to MrExcel.


Try

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!

 

[VoG]

This worked beautifully! Many many thanks!

Good

You might want to include a couple of error traps:

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

 

[aelmore]

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?

 

[VoG]

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

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

 

[aelmore]

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