Excel Cell Functions (1 Viewer)

[cable]

I'm creating a fairly complicated excel sheet in access vba and I'm finding i'm needing to do some manipulation of the references, enough to make me start think i need to spend time writing a set of functions to make this easier (ie add x columsn to ref, x rows, etc)

Before I do that has anybody else already been through the pains of this?

 

[cable]

ok these are bit rough n ready but they might be useful to someone:
I might add more if I find I need them

Public Function rI2C(OrgVal As Integer) As String
'converts a number into an excel style column reference
'28=AB, C=3 etc
Dim FirstNum As Integer, SecondNum As Integer

FirstNum = Int((OrgVal - 1) / 26)
SecondNum = OrgVal - (26 * FirstNum)

If OrgVal <= 26 Then
    rI2C = Chr(OrgVal + 64)
Else
    rI2C = Chr(FirstNum + 64) & Chr(SecondNum + 64)
End If
    
End Function

Public Function rC2I(Column As String) As Integer
'turns a column reference (AB) into a number (28)
Dim FirstChr As String, SecondChr As String
Dim FirstAsc As Integer, SecondAsc As Integer

If Len(Column) = 1 Then
    rC2I = Asc(Column) - 64
ElseIf Len(Column) = 2 Then
    FirstChr = Left(Column, 1)
    SecondChr = Right(Column, 1)
    
    FirstAsc = Asc(FirstChr) - 64
    SecondAsc = Asc(SecondChr) - 64
    
    rC2I = 26 * FirstAsc + SecondAsc
Else
    rC2I = 0
End If
End Function

Public Function r(vCol As Variant, Row As Integer) As String
'returns string excel reference
If IsNumeric(vCol) Then
    r = rI2C(CInt(vCol)) & Row
Else
    r = vCol & Row
End If
End Function

Public Function rColAdd(sReference As String, iCols As Integer) As String
'adds x columns to a string reference
Dim sCol As String, sRow As String      'section start seperated into col, row
Dim I As Integer

'split the section start into col:row
sCol = "": sRow = ""
For I = 1 To Len(sReference)
    'are we numeric?
    If IsNumeric(Mid(sReference, I, 1)) Then
        sRow = sRow + Mid(sReference, I, 1)
    Else
        sCol = sCol + Mid(sReference, I, 1)
    End If
Next I

rColAdd = rI2C(rC2I(sCol) + iCols) & CInt(sRow)
End Function

Public Function rRowAdd(sReference As String, iRows As Integer) As String
'add x rows to a string reference
Dim sCol As String, sRow As String      'section start seperated into col, row
Dim I As Integer

'split the section start into col:row
sCol = "": sRow = ""
For I = 1 To Len(sReference)
    'are we numeric?
    If IsNumeric(Mid(sReference, I, 1)) Then
        sRow = sRow + Mid(sReference, I, 1)
    Else
        sCol = sCol + Mid(sReference, I, 1)
    End If
Next I

rRowAdd = sCol & CInt(sRow) + iRows
End Function

Public Function rAdd(sReference As String, Optional iRows As Variant, Optional iCols As Variant) As String
'add x cols and/or y rows to a string reference
Dim sCol As String, sRow As String      'section start seperated into col, row
Dim I As Integer

If IsMissing(iRows) Then iRows = 0
If IsMissing(iCols) Then iCols = 0

'split the section start into col:row
sCol = "": sRow = ""
For I = 1 To Len(sReference)
    'are we numeric?
    If IsNumeric(Mid(sReference, I, 1)) Then
        sRow = sRow + Mid(sReference, I, 1)
    Else
        sCol = sCol + Mid(sReference, I, 1)
    End If
Next I

rAdd = rI2C(rC2I(sCol) + iCols) & CInt(sRow) + iRows
End Function

this is probly less useful

Public Function rFind(SectionStart As String, SectionColumns As Integer, SectionRows As Integer, Period As Integer) As String
'find reference for the specified period in a section
Dim nRes As Single
Dim iChunk As Integer                   'which chunk is this period in?
Dim iCol As Integer                     'which column in the chunk?
Dim sCol As String, sRow As String      'section start seperated into col, row
Dim I As Integer

nRes = Period / SectionColumns
'returns a single, the fix part is which chunk
'the decimal part is the position in the chunk

iChunk = Fix(nRes)

iCol = (nRes - iChunk) * SectionColumns

'split the section start into col:row
sCol = "": sRow = ""
For I = 1 To Len(SectionStart)
    'are we numeric?
    If IsNumeric(Mid(SectionStart, I, 1)) Then
        sRow = sRow + Mid(SectionStart, I, 1)
    Else
        sCol = sCol + Mid(SectionStart, I, 1)
    End If
Next I

'return new reference
rFind = rI2C(rC2I(sCol) + iCol - 1) & CInt(sRow) + (iChunk * SectionRows)
End Function

Basically I have x columns of data to generate and export, x can be far larger than then number of columns excel can support, and each column has y rows of information. My export wraps the data round a set point (printing new row titles in the process), this last function finds where a specified period is in this style of setup.

ie

Section 1
T1  1 2 3 4 5 6 7 8 9
T2  1 2 3 4 5 6 7 8 9

T1  10 11 12 13 14 15
T2  10 11 12 13 14 15