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