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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,615
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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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
 

aelmore

New Member
Joined
Sep 8, 2008
Messages
9
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!
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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
 

aelmore

New Member
Joined
Sep 8, 2008
Messages
9
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

Legend
Joined
Jun 19, 2002
Messages
63,651
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
 

aelmore

New Member
Joined
Sep 8, 2008
Messages
9
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,578
Messages
5,487,668
Members
407,609
Latest member
stellaa9x

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top