Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can any body help?
My query needs an extension to the following Macro:
Sub AAtester10() Dim sStart As String, sEnd As String Dim res As Variant, res1 As Variant Dim rng As Range Dim cng As Range sStart = InputBox("Enter Start Date") sEnd = InputBox("Enter End Date") If IsDate(sStart) And IsDate(sEnd) Then res = Application.Match(CLng(CDate(sStart)), Range("B1:B800"), 0) res1 = Application.Match(CLng(CDate(sEnd)), Range("B1:B800"), 0) If Not IsError(res) And Not IsError(res1) Then Set rng = Range(Range("B1:B800")(res), Range("B1:B800")(res1)) rng.Resize(, 31).BorderAround Weight:=xlMedium, ColorIndex:=3 Set rng = rng(1, 1).Offset(0, -1).Resize(rng.Count) rng(1, 1).FormulaR1C1 = "=R[-1]C+1" rng(1, 1).AutoFill rng rng.Copy rng.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If End If End Sub In this Macro what further I look for is a formula that can be copie into any range dependent column. As this macro uses rng and do an aut count in a column before date column. what I am looking for is a piec of code that could fill up a sum formula in column H. Lets say if th first input had a date which was in column B cell B2 (12-may-2002) an the second input had a date that was in same column B but in cell B30 in this case what I need is to copy a formula in column E (from E1 t E29), it could be any range as it is dependent on inputs. The formul should do a sum of column D values, column g contains only 1 in eac row .so from D1 to D800 there are just 1s (Ones) only, what I need thi sum formula to calculate values with an increment in row. For exampl the value in D1 was 1 (one) so the formula in E1 should show 1 a result and if the value in D2 was 1 again the formula in E2 should sho 2 and if the value in D3 was 1 again then E3 should show 3 and it wil keep on going until the each column cell is not filled with result. Column A Column B ColumnC Column D Column E 1 12-may-03 r 1 SUM(D1) 2 13-may-03 r 1 SUM(D1:D2) 3 14-may-03 r 1 SUM(D1:D3) Regards, Darn -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can any body help?
Hi Darno,
The method you are showing using SUM Worksheet Function is extremely inefficient. rather than using something like E1: =SUM(D$1:D1) E2: =SUM(D$1:D2) E1: =$D1 E2: =OFFSET(D2,-1,0)+D2 which is the same as =E1+D2 except now you can insert and delete rows without having to rewrite/correct formulas copied with the fill handle. You can use the fill handle to copy the formula down http://www.mvps.org/dmcritchie/excel/fillhand.htm More on use of OFFSET in http://www.mvps.org/dmcritchie/excel...row.htm#offset Why you do not want to use SUM in the fashion that you show http://www.mvps.org/dmcritchie/excel/slowresp.htm http://google.com/groups?threadm=ugD...40TK2MSFTNGP10 Your description (column H) does not match your example (column E). If you still need help with a macro perhaps you can simplify your question as the part you actually need. It would be most helpful if when you post a question you used a subject that describes the question. You should try to find your own answers by searching Google Newsgroups before posting. By including a more descriptive subject others who search the groups after you may find their answers without having to post. There are probably over 2000 postings per day during the work week in this newsgroup. http://www.mvps.org/dmcritchie/excel/xlnews.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "darno " wrote in message ... My query needs an extension to the following Macro: Sub AAtester10() Dim sStart As String, sEnd As String Dim res As Variant, res1 As Variant Dim rng As Range Dim cng As Range sStart = InputBox("Enter Start Date") sEnd = InputBox("Enter End Date") If IsDate(sStart) And IsDate(sEnd) Then res = Application.Match(CLng(CDate(sStart)), Range("B1:B800"), 0) res1 = Application.Match(CLng(CDate(sEnd)), Range("B1:B800"), 0) If Not IsError(res) And Not IsError(res1) Then Set rng = Range(Range("B1:B800")(res), Range("B1:B800")(res1)) rng.Resize(, 31).BorderAround Weight:=xlMedium, ColorIndex:=3 Set rng = rng(1, 1).Offset(0, -1).Resize(rng.Count) rng(1, 1).FormulaR1C1 = "=R[-1]C+1" rng(1, 1).AutoFill rng rng.Copy rng.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If End If End Sub In this Macro what further I look for is a formula that can be copied into any range dependent column. As this macro uses rng and do an auto count in a column before date column. what I am looking for is a piece of code that could fill up a sum formula in column H. Lets say if the first input had a date which was in column B cell B2 (12-may-2002) and the second input had a date that was in same column B but in cell B30 . in this case what I need is to copy a formula in column E (from E1 to E29), it could be any range as it is dependent on inputs. The formula should do a sum of column D values, column g contains only 1 in each row .so from D1 to D800 there are just 1s (Ones) only, what I need this sum formula to calculate values with an increment in row. For example the value in D1 was 1 (one) so the formula in E1 should show 1 as result and if the value in D2 was 1 again the formula in E2 should show 2 and if the value in D3 was 1 again then E3 should show 3 and it will keep on going until the each column cell is not filled with result. Column A Column B ColumnC Column D Column E 1 12-may-03 r 1 SUM(D1) 2 13-may-03 r 1 SUM(D1:D2) 3 14-may-03 r 1 SUM(D1:D3) Regards, Darno --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FORMULA HELP any body | Excel Discussion (Misc queries) | |||
Please any body help me.i need help as soon as possible | Excel Discussion (Misc queries) | |||
Any body help me out with this? | Excel Discussion (Misc queries) | |||
Any body out there smart enough??? | Excel Discussion (Misc queries) | |||
Can any body help? | Excel Programming |