ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   assign formula to variant range of cells (https://www.excelbanter.com/excel-programming/412682-assign-formula-variant-range-cells.html)

Ira
assign formula to variant range of cells
 
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
assign formula to variant range of cells
 
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

Ira
assign formula to variant range of cells
 
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
assign formula to variant range of cells
 
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

Ira
assign formula to variant range of cells
 
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
assign formula to variant range of cells
 
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


All times are GMT +1. The time now is 09:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com