Well, I'm confused <vbg>.
So AMRTZRge will be a single column range.
Let's say it starts in M2 (headers in M1) and ends in the lastrow--based on the
data in column A.
Then you could use:
Dim AMRTZRge as range
dim LastRow as long
with worksheets("Hdwre & Misc")
'change to the column that you know has data
lastrow = .cells(.rows.count,"A").end(xlup).row
set amrtzrge = .range("M2:M" & lastrow)
End with
amrtzrge.formular1c1 = "=IF(SUMIF(C[-4],RC[-4],C[-6])>20000,RC[-6]/12,RC[-6])"
=====
If this doesn't help, how do you know what row it starts in and how many rows to
use?
Ira wrote:
>
> The biggest problem is that the range will be different every run. It might
> start from different raw with different numbers of records. So, I did (not
> sure if this is correct):
>
> Dim AMRTZRge As Range
> Set AMRTZRge = Worksheets("Hdwre & Misc").Range("AMRTZ")
> I want to assign value to AMRTZ column based on formula.
> I hope I don't sound confusing. I very much appreciate your help, Dave.
>
> "Dave Peterson" wrote:
>
> > Hmmm.
> >
> > This doesn't match up with what you wrote earlier:
> >
> > > > Dim AMRTZ As Long
> >
> >
> > I would have expected:
> >
> > Dim AMRTZ as Range
> > 'then some assignment
> > Set AMRTZ = worksheets(....).range(....)
> >
> > Then
> >
> > AMRTZ.formular1c1 = "=IF(SUMIF(C[-4],RC[-4],C[-6])>20000,RC[-6]/12,RC[-6])"
> >
> > But that's still a guess.
> >
> >
> > Ira wrote:
> > >
> > > AMRTZ is a range. I want to apply formula to every cell in that range.
> > > Thank you Dave for looking into it
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Maybe...
> > > >
> > > > Worksheets("Hdwre & Misc").Cells(RwCt, 20).formular1c1 _
> > > > = "=IF(SUMIF(C[-4],RC[-4],C[-6])>20000,RC[-6]/12,RC[-6])"
> > > >
> > > > But I'm not sure what this is supposed to do:
> > > >
> > > > * AMRTZ = Worksheets("Hdwre & Misc").Cells(RwCt, 20).Select
> > > >
> > > > Did you want to retrieve the value from that cell and put it into AMRTZ or just
> > > > select that cell so that you could plop the formulaR1C1 into it.
> > > >
> > > >
> > > > Ira wrote:
> > > > >
> > > > > I Need help to assign formula to variant range of cells. I added column to
> > > > > Excel form. This column value is based on group value.
> > > > > Here is the code I wrote:
> > > > > Dim Chng As Integer
> > > > > Dim FDOCRge As Range
> > > > > Dim LDOCRge As Range
> > > > > Dim FREQRge As Range
> > > > > Dim RCFMOS As Range
> > > > > Dim RwCt As Variant
> > > > > Dim MnthCl As Variant
> > > > > Dim Mnth As Date
> > > > > Dim FDOC As String
> > > > > Dim LDOC As String
> > > > > Dim FREQ As String
> > > > > Dim YrMo As Integer
> > > > > Dim MoMo As Integer
> > > > > Dim YrFDOC As Integer
> > > > > Dim MoFDOC As Integer
> > > > > Dim YrLDOC As Integer
> > > > > Dim MoLDOC As Long
> > > > > Dim Chrg As Long
> > > > > Dim T As String
> > > > > Dim MoChrg As Long
> > > > > Dim AMRTZ As Long
> > > > > Dim AMRTZRge As Range
> > > > >
> > > > > On Error GoTo ErrorHandler
> > > > >
> > > > > Set FDOCRge = Worksheets("Hdwre & Misc").Range("FDOC")
> > > > > Set LDOCRge = Worksheets("Hdwre & Misc").Range("LDOC")
> > > > > Set FREQRge = Worksheets("Hdwre & Misc").Range("FREQ")
> > > > > Set RCFMOS = Worksheets("Hdwre & Misc").Range("RCFMOS")
> > > > > Set AMRTZRge = Worksheets("Hdwre & Misc").Range("AMRTZ")
> > > > >
> > > > > For RwCt = FDOCRge.Row + 1 To FDOCRge.Row + FDOCRge.Rows.Count - 1
> > > > >
> > > > >
> > > > > FDOC = Worksheets("Hdwre & Misc").Cells(RwCt, 13).Value
> > > > > LDOC = Worksheets("Hdwre & Misc").Cells(RwCt, 14).Value
> > > > > FREQ = Worksheets("Hdwre & Misc").Cells(RwCt, 17).Value
> > > > > Chrg = Worksheets("Hdwre & Misc").Cells(RwCt, 16).Value
> > > > > * AMRTZ = Worksheets("Hdwre & Misc").Cells(RwCt, 20).Select
> > > > > * ActiveCell.Formula =
> > > > > "=IF(SUMIF(C[-4],RC[-4],C[-6])>20000,RC[-6]/12,RC[-6])"
> > > > >
> > > > >
> > > > >
> > > > > I have trouble with last 2 lines. Can anyone help me? Thank you in advance.
> > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >
--
Dave Peterson
|
Similar Threads
