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.
