Shazam
01-07-2006, 03:10 PM
Hi everyone,
I have this code below that if a cell in column C has the word:
"NCR1 Grand Total" it will place the formula in column X that coresponds to the word in column C. I also want to look for these words as well and place the formulas in coulmn X:
"OCR1 Grand Total"
"NCR2 Grand Total"
But it only works to a certain point. Because I download this file from a database daily so I need to run this code daily. My data fluctuates daily. The hard thing about it is to have the ranges of the formulas to stay in by shifts. Please look at the ranges of the formulas. I attach the workbook below it will give you a better explanation. If someone could modified this code to suite my needs that will be great.
Thanks!: pray2:
Sub Place_Formula()
Dim C As Range
With ActiveSheet.Columns("C:C")
Set C = .Find("NCR1 Grand Total", LookIn:=xlValues, lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
Do
C.Offset(0, 16).Formula = "=SUMPRODUCT(1-SUBTOTAL(3,OFFSET(R[-65]C:R[-1]C," & _
"ROW(R[-65]C:R[-1]C)-ROW(R[-65]C),0,1)),--(R[-65]C:R[-1]C>0),R[-65]C:R[-1]C)"
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
End Sub
I have this code below that if a cell in column C has the word:
"NCR1 Grand Total" it will place the formula in column X that coresponds to the word in column C. I also want to look for these words as well and place the formulas in coulmn X:
"OCR1 Grand Total"
"NCR2 Grand Total"
But it only works to a certain point. Because I download this file from a database daily so I need to run this code daily. My data fluctuates daily. The hard thing about it is to have the ranges of the formulas to stay in by shifts. Please look at the ranges of the formulas. I attach the workbook below it will give you a better explanation. If someone could modified this code to suite my needs that will be great.
Thanks!: pray2:
Sub Place_Formula()
Dim C As Range
With ActiveSheet.Columns("C:C")
Set C = .Find("NCR1 Grand Total", LookIn:=xlValues, lookat:=xlWhole)
If Not C Is Nothing Then
firstAddress = C.Address
Do
C.Offset(0, 16).Formula = "=SUMPRODUCT(1-SUBTOTAL(3,OFFSET(R[-65]C:R[-1]C," & _
"ROW(R[-65]C:R[-1]C)-ROW(R[-65]C),0,1)),--(R[-65]C:R[-1]C>0),R[-65]C:R[-1]C)"
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
End With
End Sub