Thursday, September 08, 2005

[VBScript and Excel] Relative/Absolute references

Again at Automate Excel In Vbscript... ( Vbscript ), Pentium10 wrote
I have a script which fills cells with exce.range.value function, but I have columns more than A-Z, how can I handle AA,AB, AC... columns???

Here is the script what I have:
excel.Range(chr(65+i) + str(excelrow), chr(65+i) + _
 str(excelrow)).value = lstOrder.cell(i,1)
And I wrote:

Re: I have a script which fills cells with ...

That does me, but for you I'd suggest adding a few more items to the Split(). After Z put in ",AA,AB,AC," etc until you have what you need. Then code your references in terms of numeric column and a row and let the function generate a meaningful address.

For example:
(iCount in this case is the number of rows in an array I was committing to the spreadsheet.)

2 comments:

Anonymous said...

Function getCellName(aRow, aCol)
Dim cellName, reminder, quosant
quosant = (aCol \ 26)
reminder = (aCol Mod 26)

If reminder = 0 Then
reminder = 26
quosant = quosant - 1
End If

If quosant > 0 Then
cellName = cellName & Chr(64 + quosant)
End If
cellName = cellName & Chr(64 + reminder)

cellName = cellName & aRow
getCellName = cellName

End Function

Bruce M. Axtens said...

I will have to check this one out Monday, when I get back to a Windows box. On first reading it looks great.
--Bruce.