MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find/then place formula in column C


Posted by Andonny on January 18, 2001 2:00 AM

Hi,
I found the code below and it deletes all rows with the number 13 in culumn A. I was hoping somebody would be able to modify it that whenever it finds 13 in culumn A it puts the formula =A-B in column C instead of deleting the row.
Thanks a lot in advance.

Sub DelRowInCulumn_C()
Dim RowNum As Integer
Dim LastRow, LastCol As Integer


With Worksheets("Sheet1")
LastRow = .UsedRange.Rows.Count
LastCol = .UsedRange.Columns.Count


' Now delete rows if formula in column A is thirteen
For RowNum = LastRow To 1 Step -1
If Range("A" & RowNum).Value = 13 Then
Rows(RowNum).Select
Selection.Delete Shift:=xlUp
End If
Next RowNum


Range("A1").Select
End With
End Sub


Posted by Dave Hawley on January 18, 2001 2:38 AM

Hi Andonny

Just change the loop to this:


' Now delete rows if formula in column A is thirteen
For RowNum = LastRow To 1 Step -1
If Range("A" & RowNum) = 13 Then
Range("C" & RowNum) = "=RC[-2]-RC[-1]"
End If
Next RowNum

Dave


OzGrid Business Applications

Posted by Andonny on January 18, 2001 3:27 AM

Hi,
For some reason excel crashes and closes down when I chnage the loop.

Sorry
Andonny

Dave


Posted by Mark W. on January 18, 2001 1:56 PM

You really don't need a macro to accomplish this!
Just do the following:

1. Apply an AutoFilter on column A that filters
for rows containing 13.
2. With only rows containing 13 displayed, select
the corresponding cells in column C.
3. Type "=A:A-B:B" into the 1st corresponding cell
in column C.
4. Copy this formula down to the last corresponding
cell in column C.
5. Remove the AutoFilter.