Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variant array with formula strings to range formulae | Excel Programming | |||
Excal, Variant and Range | Excel Programming | |||
range and variant | Excel Programming | |||
How do I assign range to variant and use | Excel Discussion (Misc queries) | |||
Assigning range to variant | Excel Programming |