|
replacing value by formula
HI, My column heading (Y9:AF9) a Mar-04 Apr-04 May-04 Jun-04 Q4 2004 Q1 2005 Year 2004 Sum Year 2005 Sum I have to have a YEar SUm of 2004 and 2005, which i am doing by the code below. With this logic i am getting the final value and formala. Instead of this logic I want to have =SUM(Y10:AC10) in each cell. Before having a total I want to check whether it lies in 2004 or 2005. IS it possible to replace the cell value by the formula as =SUM(Y10:AC10)? thanks in advance monika 'INSERTING VALUES FOR YEARLY SUM'S COLUMNS Dim sysYr, fSumYr1, fSumYr2 As Long Dim QYR As Long sysYr = Year(Date) j = LNewRng.Row + 1 k = LNewRng.Row While j <= LastCellNum i = lastCol + 1 'REINITIALIZING FOR EVERY NEW ROW fSumYr1 = 0 'REINITIALIZING FOR EVERY NEW ROW fSumYr2 = 0 'REINITIALIZING FOR EVERY NEW ROW While i <= colEnd If IsDate(Cells(k, i).Value) = True Then 'SUM FOR DATE CASES yr1 = Year(Cells(k, i).Value) If yr1 = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else 'Stop fSumYr2 = fSumYr2 + Cells(j, i).Value End If ElseIf Mid(Cells(k, i).Value, 1, 1) = "Q" Then 'QUARTER SUM CALCULATION QYR = Right(Cells(k, i).Value, 4) If QYR = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else fSumYr2 = fSumYr2 + Cells(j, i).Value End If End If i = i + 1 Wend Cells(j, colEnd).Value = fSumYr1 Cells(j, colEnd + 1).Value = fSumYr2 j = j + 1 Wend |
replacing value by formula
Hi Monika,
Struggling a bit to see what is happening without any data, but is this what you want Cells(j, colEnd).FormulaR1C1 = "=SUMPRODUCT(--(RC" & lastCol & ":RC" & colEnd - 1 & ")=" & sysYr & ")" Cells(j, colEnd + 1).FormulaR1C1 = "=SUMPRODUCT((LEFT(RC" & lastCol & ":RC" & colEnd - 1 & ",1)=""Q"")*" & _ "(RIGHT(RC" & lastCol & ":RC" & colEnd - 1 & ",4)=""" & sysYr & """))" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "monika" wrote in message ... HI, My column heading (Y9:AF9) a Mar-04 Apr-04 May-04 Jun-04 Q4 2004 Q1 2005 Year 2004 Sum Year 2005 Sum I have to have a YEar SUm of 2004 and 2005, which i am doing by the code below. With this logic i am getting the final value and formala. Instead of this logic I want to have =SUM(Y10:AC10) in each cell. Before having a total I want to check whether it lies in 2004 or 2005. IS it possible to replace the cell value by the formula as =SUM(Y10:AC10)? thanks in advance monika 'INSERTING VALUES FOR YEARLY SUM'S COLUMNS Dim sysYr, fSumYr1, fSumYr2 As Long Dim QYR As Long sysYr = Year(Date) j = LNewRng.Row + 1 k = LNewRng.Row While j <= LastCellNum i = lastCol + 1 'REINITIALIZING FOR EVERY NEW ROW fSumYr1 = 0 'REINITIALIZING FOR EVERY NEW ROW fSumYr2 = 0 'REINITIALIZING FOR EVERY NEW ROW While i <= colEnd If IsDate(Cells(k, i).Value) = True Then 'SUM FOR DATE CASES yr1 = Year(Cells(k, i).Value) If yr1 = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else 'Stop fSumYr2 = fSumYr2 + Cells(j, i).Value End If ElseIf Mid(Cells(k, i).Value, 1, 1) = "Q" Then 'QUARTER SUM CALCULATION QYR = Right(Cells(k, i).Value, 4) If QYR = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else fSumYr2 = fSumYr2 + Cells(j, i).Value End If End If i = i + 1 Wend Cells(j, colEnd).Value = fSumYr1 Cells(j, colEnd + 1).Value = fSumYr2 j = j + 1 Wend |
replacing value by formula
Hi Bob,
i am not able to understand your code properly. I want year 2004 sum to be in "Year Sum 2004" Column and year 2005 sum to be in year sum 2005 column. In my current rolling forecast for the month March i have following column Headings: Mar-04 Apr-04 May-04 Jun-04 Q4 2004 Q1 2005 Year 2004 Sum Year 2005 Sum so my columns headings from mar-04 to may-04 are in date format. But Q4 2004 and Q5 2001 are not in date format. So what i am doing is first checking if the column heading is a date then I further check is its a current or next year. As these month will change with years in coming time. So ifs its current year (2004) then i want the sum to be in year 2004 Sum column heading. So i want total of columns which has 2004 volumes. Now in this case i have to Q4 2004... which is not a date. so i check if the 1st letter is "Q"; if yes then i check last 4 letters...its its current year then i have to add the total in year sum 2004. and if its 2005 then in year sum 2005 column. In this case I have only one 2005 column. I hope this is feasible? thanks tremendously Monika "Bob Phillips" wrote in message ... Hi Monika, Struggling a bit to see what is happening without any data, but is this what you want Cells(j, colEnd).FormulaR1C1 = "=SUMPRODUCT(--(RC" & lastCol & ":RC" & colEnd - 1 & ")=" & sysYr & ")" Cells(j, colEnd + 1).FormulaR1C1 = "=SUMPRODUCT((LEFT(RC" & lastCol & ":RC" & colEnd - 1 & ",1)=""Q"")*" & _ "(RIGHT(RC" & lastCol & ":RC" & colEnd - 1 & ",4)=""" & sysYr & """))" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "monika" wrote in message ... HI, My column heading (Y9:AF9) a Mar-04 Apr-04 May-04 Jun-04 Q4 2004 Q1 2005 Year 2004 Sum Year 2005 Sum I have to have a YEar SUm of 2004 and 2005, which i am doing by the code below. With this logic i am getting the final value and formala. Instead of this logic I want to have =SUM(Y10:AC10) in each cell. Before having a total I want to check whether it lies in 2004 or 2005. IS it possible to replace the cell value by the formula as =SUM(Y10:AC10)? thanks in advance monika 'INSERTING VALUES FOR YEARLY SUM'S COLUMNS Dim sysYr, fSumYr1, fSumYr2 As Long Dim QYR As Long sysYr = Year(Date) j = LNewRng.Row + 1 k = LNewRng.Row While j <= LastCellNum i = lastCol + 1 'REINITIALIZING FOR EVERY NEW ROW fSumYr1 = 0 'REINITIALIZING FOR EVERY NEW ROW fSumYr2 = 0 'REINITIALIZING FOR EVERY NEW ROW While i <= colEnd If IsDate(Cells(k, i).Value) = True Then 'SUM FOR DATE CASES yr1 = Year(Cells(k, i).Value) If yr1 = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else 'Stop fSumYr2 = fSumYr2 + Cells(j, i).Value End If ElseIf Mid(Cells(k, i).Value, 1, 1) = "Q" Then 'QUARTER SUM CALCULATION QYR = Right(Cells(k, i).Value, 4) If QYR = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else fSumYr2 = fSumYr2 + Cells(j, i).Value End If End If i = i + 1 Wend Cells(j, colEnd).Value = fSumYr1 Cells(j, colEnd + 1).Value = fSumYr2 j = j + 1 Wend |
replacing value by formula
Hi Monika,
What I did was post some code to dynamically create formulae for adding it up. Is this not what you wanted, do you just want the sum in the cell rather than a formula? Can you post some data and explain what LNewRng is and where it gets set, colEnd, and LastCellNum? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "monika" wrote in message ... Hi Bob, i am not able to understand your code properly. I want year 2004 sum to be in "Year Sum 2004" Column and year 2005 sum to be in year sum 2005 column. In my current rolling forecast for the month March i have following column Headings: Mar-04 Apr-04 May-04 Jun-04 Q4 2004 Q1 2005 Year 2004 Sum Year 2005 Sum so my columns headings from mar-04 to may-04 are in date format. But Q4 2004 and Q5 2001 are not in date format. So what i am doing is first checking if the column heading is a date then I further check is its a current or next year. As these month will change with years in coming time. So ifs its current year (2004) then i want the sum to be in year 2004 Sum column heading. So i want total of columns which has 2004 volumes. Now in this case i have to Q4 2004... which is not a date. so i check if the 1st letter is "Q"; if yes then i check last 4 letters...its its current year then i have to add the total in year sum 2004. and if its 2005 then in year sum 2005 column. In this case I have only one 2005 column. I hope this is feasible? thanks tremendously Monika "Bob Phillips" wrote in message ... Hi Monika, Struggling a bit to see what is happening without any data, but is this what you want Cells(j, colEnd).FormulaR1C1 = "=SUMPRODUCT(--(RC" & lastCol & ":RC" & colEnd - 1 & ")=" & sysYr & ")" Cells(j, colEnd + 1).FormulaR1C1 = "=SUMPRODUCT((LEFT(RC" & lastCol & ":RC" & colEnd - 1 & ",1)=""Q"")*" & _ "(RIGHT(RC" & lastCol & ":RC" & colEnd - 1 & ",4)=""" & sysYr & """))" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "monika" wrote in message ... HI, My column heading (Y9:AF9) a Mar-04 Apr-04 May-04 Jun-04 Q4 2004 Q1 2005 Year 2004 Sum Year 2005 Sum I have to have a YEar SUm of 2004 and 2005, which i am doing by the code below. With this logic i am getting the final value and formala. Instead of this logic I want to have =SUM(Y10:AC10) in each cell. Before having a total I want to check whether it lies in 2004 or 2005. IS it possible to replace the cell value by the formula as =SUM(Y10:AC10)? thanks in advance monika 'INSERTING VALUES FOR YEARLY SUM'S COLUMNS Dim sysYr, fSumYr1, fSumYr2 As Long Dim QYR As Long sysYr = Year(Date) j = LNewRng.Row + 1 k = LNewRng.Row While j <= LastCellNum i = lastCol + 1 'REINITIALIZING FOR EVERY NEW ROW fSumYr1 = 0 'REINITIALIZING FOR EVERY NEW ROW fSumYr2 = 0 'REINITIALIZING FOR EVERY NEW ROW While i <= colEnd If IsDate(Cells(k, i).Value) = True Then 'SUM FOR DATE CASES yr1 = Year(Cells(k, i).Value) If yr1 = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else 'Stop fSumYr2 = fSumYr2 + Cells(j, i).Value End If ElseIf Mid(Cells(k, i).Value, 1, 1) = "Q" Then 'QUARTER SUM CALCULATION QYR = Right(Cells(k, i).Value, 4) If QYR = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else fSumYr2 = fSumYr2 + Cells(j, i).Value End If End If i = i + 1 Wend Cells(j, colEnd).Value = fSumYr1 Cells(j, colEnd + 1).Value = fSumYr2 j = j + 1 Wend |
replacing value by formula
LNewRng is the row where i need to start putting in the values. Like the
column headings are in the 9th row. So in my case lNewrng.row will be the 10th row. I set this value in the beginning of my logic. lastCellnum is the last cell of the sheet. In my case its 293. I run the loop till the last cell of the sheet. colEnd here is the "Year Sum 2004" column number colEnd + 1 is hte "Year Sum 2005" Column Number I can pass u the data if u need further??? I hope i am clear Thanks Monika "Bob Phillips" wrote in message ... Hi Monika, What I did was post some code to dynamically create formulae for adding it up. Is this not what you wanted, do you just want the sum in the cell rather than a formula? Can you post some data and explain what LNewRng is and where it gets set, colEnd, and LastCellNum? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "monika" wrote in message ... Hi Bob, i am not able to understand your code properly. I want year 2004 sum to be in "Year Sum 2004" Column and year 2005 sum to be in year sum 2005 column. In my current rolling forecast for the month March i have following column Headings: Mar-04 Apr-04 May-04 Jun-04 Q4 2004 Q1 2005 Year 2004 Sum Year 2005 Sum so my columns headings from mar-04 to may-04 are in date format. But Q4 2004 and Q5 2001 are not in date format. So what i am doing is first checking if the column heading is a date then I further check is its a current or next year. As these month will change with years in coming time. So ifs its current year (2004) then i want the sum to be in year 2004 Sum column heading. So i want total of columns which has 2004 volumes. Now in this case i have to Q4 2004... which is not a date. so i check if the 1st letter is "Q"; if yes then i check last 4 letters...its its current year then i have to add the total in year sum 2004. and if its 2005 then in year sum 2005 column. In this case I have only one 2005 column. I hope this is feasible? thanks tremendously Monika "Bob Phillips" wrote in message ... Hi Monika, Struggling a bit to see what is happening without any data, but is this what you want Cells(j, colEnd).FormulaR1C1 = "=SUMPRODUCT(--(RC" & lastCol & ":RC" & colEnd - 1 & ")=" & sysYr & ")" Cells(j, colEnd + 1).FormulaR1C1 = "=SUMPRODUCT((LEFT(RC" & lastCol & ":RC" & colEnd - 1 & ",1)=""Q"")*" & _ "(RIGHT(RC" & lastCol & ":RC" & colEnd - 1 & ",4)=""" & sysYr & """))" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "monika" wrote in message ... HI, My column heading (Y9:AF9) a Mar-04 Apr-04 May-04 Jun-04 Q4 2004 Q1 2005 Year 2004 Sum Year 2005 Sum I have to have a YEar SUm of 2004 and 2005, which i am doing by the code below. With this logic i am getting the final value and formala. Instead of this logic I want to have =SUM(Y10:AC10) in each cell. Before having a total I want to check whether it lies in 2004 or 2005. IS it possible to replace the cell value by the formula as =SUM(Y10:AC10)? thanks in advance monika 'INSERTING VALUES FOR YEARLY SUM'S COLUMNS Dim sysYr, fSumYr1, fSumYr2 As Long Dim QYR As Long sysYr = Year(Date) j = LNewRng.Row + 1 k = LNewRng.Row While j <= LastCellNum i = lastCol + 1 'REINITIALIZING FOR EVERY NEW ROW fSumYr1 = 0 'REINITIALIZING FOR EVERY NEW ROW fSumYr2 = 0 'REINITIALIZING FOR EVERY NEW ROW While i <= colEnd If IsDate(Cells(k, i).Value) = True Then 'SUM FOR DATE CASES yr1 = Year(Cells(k, i).Value) If yr1 = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else 'Stop fSumYr2 = fSumYr2 + Cells(j, i).Value End If ElseIf Mid(Cells(k, i).Value, 1, 1) = "Q" Then 'QUARTER SUM CALCULATION QYR = Right(Cells(k, i).Value, 4) If QYR = sysYr Then fSumYr1 = fSumYr1 + Cells(j, i).Value Else fSumYr2 = fSumYr2 + Cells(j, i).Value End If End If i = i + 1 Wend Cells(j, colEnd).Value = fSumYr1 Cells(j, colEnd + 1).Value = fSumYr2 j = j + 1 Wend |
| All times are GMT +1. The time now is 01:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com