String with Cell Reference

[Guest]

I have a procedure that has to deal with a sequence of columns that grows
from week to week; i.e., this week I'm working with columns A thru AJ, next
week it'll be A thru AK. At one point in the code I need a string
representation of the first cell in each column, e.g., "A1", "B1", etc. I
dealt with that initially by hardcoding an array to hold all the strings, but
I'm sure there must be a property I can iterate through to get the first-cell
references for whatever column I'm dealing with -- on the first pass give me
"A1", on the second give me "B1", etc. Can someone please help me with that
property name and/or bit of code?

 

[Bernie Deitrick]

Larry,

Let's say that you have a ColumnCount variable

Sub TryNow()
Dim ColumnCount As Integer
Dim i As Integer

ColumnCount = 2

For i = 1 To ColumnCount
MsgBox Cells(1, i).Address(False, False)
Next i
End Sub


But there isn't any reason I can think of that would reguire a cell address in your code.... post
your code for further assistance ....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Here's a chunk from the middle of my code to illustrate what I'm doing.
First, to explain the general context:

MSAccess spits out an Excel file. Its first four columns are fixed, five
through xxxxx contain historic numbers for weeks going back to 11/2006, and
the columns after xxxxx are also fixed, containing data for the most recent
seven weeks. The challenge is that the piece in the middle grows from week
to week, which affects what columns all the subsequent stuff resides in.

To make it even more fun, the first thing my macro does is insert an
ADDITIONAL column after xxxxx, where the historical numbers are totalled so
the individual historic columns can be hidden (that keeps the width of the
visible columns manageable).

Once the new column is set up, my code figures out and stores the column
NUMBER of various other columns I need to do formatting things to. Using
those numbers, I can tap the corresponding array element to get the cell
references I actually need. "Why?", you say?

As you'll see from my code excerpt, several of the columns have names like
'last visible week', 'first hidden week', 'cum totals column', etc. I have
string variables corresponding to each of those, where I need to store the
cell reference of the top cell, e.g., "J1." The reason I need to do THAT is
so my code can subsequently do formatting things to a range of cells, e.g.,
"select cells A1 thru strLastHiddenColumn and make the contents bold italic
with a blue background."

When I first wrote this I expected it to be used for a couple months at
most, so I just set up an array to hold the cell references I needed (the
first couple lines of the excerpt show the tail end of that array). As so
often happens, though, the app doesn't show any sign of going away, so rather
than an array that handles a finite number of weeks/columns, I need something
dynamic that will deal with however many columns Access spews out.

So if instead of all the array mumbo-jumbo there's a simple Excel property
that will return "oh, last visible week is in column 19 this week? Then you
want to use S1 when you're formatting that cell!", I'll be happy as a clam.

***************************************************
arrColNames(63) = "BK1"
arrColNames(64) = "BL1"


'First set up additional column for prior WW totals
intUsedColumns = ActiveSheet.UsedRange.Columns.Count
intUsedRows = ActiveSheet.UsedRange.Rows.Count
intCumColumn = intUsedColumns - 16
Range("A1").Select
ActiveCell.Offset(0, intCumColumn - 1).Select
Selection.EntireColumn.Insert
Range(arrColNames(intCumColumn)).Select
ActiveCell.Value = "Cum Prev Weeks"

'Then load all numeric and string values for significant columns
intFirstWW = 5
intLastHiddenWW = intCumColumn - 1
intFirstVisWW = intCumColumn + 1
intLastVisWW = intCumColumn + 6
intFirstWDay = intCumColumn + 11
intLastWDay = intCumColumn + 17
intUsedColumns = intLastWDay

strFirstWWColName = arrColNames(intFirstWW)
strLastHiddenWWColName = arrColNames(intLastHiddenWW)
strCumColName = arrColNames(intCumColumn)
strFirstVisWWColName = arrColNames(intFirstVisWW)
strLastVisWWColName = arrColNames(intLastVisWW)
strFirstWDayColName = arrColNames(intFirstWDay)
strLastWDayColName = arrColNames(intLastWDay)

Range(strCumColName).Select
Selection.EntireColumn.Select
For Each cel In Selection.Range("A2:A" & intUsedRows)
cel.FormulaR1C1 = "=SUM(RC[-8]:RC[-1])"
Next cel

Range(strLastWDayColName).Select
Selection.Value = "Fri"
ActiveCell.Offset(0, -1).Select
Selection.Value = "Thu"
ActiveCell.Offset(0, -1).Select
Selection.Value = "Wed"
ActiveCell.Offset(0, -1).Select
Selection.Value = "Tue"
ActiveCell.Offset(0, -1).Select
Selection.Value = "Mon"
ActiveCell.Offset(0, -1).Select
Selection.Value = "Sun"
ActiveCell.Offset(0, -1).Select
Selection.Value = "Sat"
Range("A1:" & strLastWDayColName).Select 'First make all column
headings blue...
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Range("A11").Select 'Then make the first four light gray...
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range(strFirstWWColName & ":" & strLastVisWWColName).Select 'Then make
WW columns light yellow…
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

****************************************

 

[Guest]

You can stand down, Bernie; the .ADDRESS reference you provided works fine,
got rid of the array and several string variables.