Hi all,

Here is my code:

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