Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning column letter
Here is the complete code
The line between ========= is the one giving me fits. Thanks. Sub CompareMonthly() Dim SYear As Long 'year of report Dim LYear As Long 'month of report Dim Shname As String 'worksheet name Dim startrow As Long ' start row of report from shname Dim lastrow As Long ' last row of report from shname Dim dt As Date ' date for report Dim sumfor As String 'formula holder Dim d As Long 'counter Dim c As Long 'counter Dim RngWithMonths As Range Dim RngToTotal As Range Dim x As Long Dim totalsales As String Dim r As Long Application.ScreenUpdating = False Application.ActiveWorkbook.Unprotect Worksheets("Report").Range("a:j").ClearContents 'clear report worksheet Do SYear = CLng(Application.InputBox(Prompt:="Enter a year between 2005 and 2050", Default:=Year(Date), Type:=1)) ' get year for report If SYear = 0 Then Exit Sub 'give the user a way out?? Else If SYear = 2005 And SYear <= 2050 Then Exit Do End If End If Loop Do LYear = CLng(Application.InputBox(Prompt:="Enter a year between 2005 and 2050", Default:=Year(Date), Type:=1)) ' get year for report If LYear = 0 Then Exit Sub 'give the user a way out?? Else If LYear = 2005 And LYear <= 2050 Then Exit Do End If End If Loop Shname = "sales " & SYear Worksheets("Report").Select Range("a1").Value = "Compare Monthly Sales" & Chr(10) & SYear & " to " & LYear With Range("a2") .Offset(0, 0).Value = "Year" 'set heading c = 1 While c <= (LYear + 1) - SYear 'set column year headings .Offset(0, c).Value = SYear + c - 1 c = c + 1 'SET SUM OF COLUMNS '================================================= = 'this is line giving me fits. .Columns,Address needs to return column letter only. .Offset(13, c).Formula = "=sum(" & .Columns.Address & "3:" & Columns.Address & "14)" '================================================= Wend c = 1 While c <= 12 'set month row headings .Offset(c, 0).Value = MonthName(c) c = c + 1 Wend x = 1 'MONTH NUMBER c = 1 ' offset number r = 1 While SYear <= LYear Shname = "sales " & SYear With Worksheets(Shname) lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row Set RngWithMonths = .Range("A2:A" & lastrow) Set RngToTotal = .Range("B2:D" & lastrow) End With While x <= 12 totalsales = "=sum(if(Month(" & RngWithMonths.Address(external:=True) & ")=" & x & "," & RngToTotal.Address(external:=True) & "))" .Offset(c, r).FormulaArray = totalsales ' total sales for the month c = c + 1 x = x + 1 Wend x = 1 r = r + 1 c = 1 SYear = SYear + 1 Wend End With Application.ActiveWorkbook.Protect Application.ScreenUpdating = True Range("a2").Select End Sub "Tony29" wrote in message ... Without knowing what it is that causes "x" to be "known" by you or the procedure, consider the following ... Use relative references in the formula that you are posting back to the spreadsheet; it doesn't matter what the current reference style is set to in the spreadsheet eg. ActiveCell.Offset(0, 1).Range("A1").Select MyCell.Formula = "=SUM(R[-13]C[-4]:R[-2]C[-4])" Based on the logic of your procedure you just need to know what the current active cell is and how far away it is from the range which is the target of the SUM Dim iRow as single Dim iCol as single With ActiveCell iRow = .Row iCol = .Column End With "C Brehm" wrote: What I want to do is return the formula =sum(x3:x14) where x is the column letter .. columns.address returns the complete cell address and all I need is the letter of the column ..Offset(13, c).Formula =_ "=sum(" & .Columns.Address & "3:"_ & .Columns.Address & "14)" Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup between sheets, finding a match, and returning a letter | Excel Worksheet Functions | |||
formula for returning the largest letter in a series of letters | Excel Worksheet Functions | |||
How to replace column letter in refferences with a function using the old column letter? | Links and Linking in Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Excel Worksheet Functions | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) |