Find() in VBA not working as planned

Hi all,

Here is my code:

Sub FormatForCommitments()
'
'   This macro removes only Wrapped Text and Merged Cells
'   from the entire worksheet, then finds the first RMS
'   Ordered Works commitment, then inputs a formula into
'   column G.
'
'   Set Variables
    Dim i As Long
'   Dim j As Long
    Dim LastRow As Long
'   Change formatting to remove merged and wrap text, then add simple column headings
    With Range("A1", ActiveCell.SpecialCells(xlLastCell))
        .WrapText = False
        .MergeCells = False
    End With
    Range("A1").Value = "1"
    Range("B1").Value = "2"
    Range("C1").Value = "3"
    Range("D1").Value = "4"
    Range("E1").Value = "5"
    Range("F1").Value = "6"
    Range("G1").Value = "7"
'   Find and set last row number into i
    LastRow = Range("F" & Rows.Count).End(xlUp).Row
'   Select Column A, find each Job Number with the format 15???, then use a loop to add the formula to column G
    Columns(1).Select
    For i = 9 To LastRow
        'Find the first Ordered Work Commitment
        Selection.Find(What:="15???", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        Range("G" & i).Formula = "=(F" & i & "+1)"
    Next i
End Sub

The first routines work well. The Formula routine certaily adds a formula, but it adds it to every cell in column G.

The routine should:

• Find the first cell in column A which is in the format 15???
• Go across to column G and input a formula which returns the number in colum F, but the next row

Instead of this, it is doing the following:

• Finding the first used cell in column A
• Input a formula in column G of that same row, but which returns the value of the adjacent cell in column F
• And adds one to that value

Is anyone able to help me here?

Thank you,

Neil

Thanks Mumps!

That worked great!

I'll study this to find out how everything works.

BTW, I usually leave putting ScreenUpdating in until I have the macro working - found that out the hard way, LOL!

There is a follow up request to this same procedure, should I put it here, or begin a new Topic?

Thanks,

Neil

Hi Mumps,

Your code worked really well, and I like the use of an Array() to populate the Row.

There is an extra step required, however, but I really needed to know how to fix my initial Find() problem.

Here is a cut down version of my sheet:

[ATTACH]n1218834[/ATTACH]

As you can see, there are some times when there are multiple rows under the Account heading. While 98% of them will work great with your formula, I need to know how to account for these multiple rows.

Thank you,

Neil