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?