newb - use R1C1 reference to select cell

[eoan]

I've tried searching the forum for an answer for this but no luck, so posting it...

All I want to do is start by selecting a cell, say A1, then selecting a cell say three down and five along. I've tried using:

    Range("A1").Select
    Range("R[3]C[5]").Select

and swapping out or round the quotes or brackets in various combinations but I can't get right...

Any help would be very gratefully received!

 

[Dennisk]

try the spreadsheet forum and not the Access database forum

 

[eoan]

Cheers dk.

 

[gemma-the-husky]

but i satruggled to get the sysntax right, and i had to change column letters into numbers so that

cell(D6) became cell(4,6)

and cell AB12 became cell(28,12)

you only need a little function to change a lettered column into a numeric value
chr() and asc() functions reuqired.

 

[eoan]

Awesome! Thanks gth, that's very helpful - should be able to do exactly what i need using that.

Cheers!

 

[Brianwarnock]

Isn't the correct approach to use Offset?

Brian

 

[gemma-the-husky]

might be brian

its just in excel you use =A1 + 2, sort of format

but you cant seem to use cells("A1") to refer to excel cells in access VBA

the way i found was to use cells(row, col) or maybe cells(col, row) (both numeric) and I just stuck with that

--------
at least once you have an aboslute row/col ref, you can use normal arithmetic operations on it

 

[eoan]

Hi Brian - thank you for responding; i've taken a look at offset's help page and unless i'm mistaken it is used within a cell to capture a range elsewhere on the page.

What I am looking for is some access-based vba to do the following:

1. Check a table for the "start position" - this is where the April figure needs to be placed on the report, e.g. D5, with May in E5, June in F5 etc. etc.
2. Check a form/table for the required month number (fiscal ie apr = 1)
3. Select the right start cell based on the required month.

Originally I hoped that selecting the "start position" and substituting the fiscal month number as the 'C' component in an R1C1-style address would work (1= current position, 2= one column across etc.) but couldn't find a way to get excel to accept it using Range(XX).Select

I can use gth's solution as follows:

1. store cell address in two parts (ColNo and RowNo)
2. ColNo = chr(asc(ColNo)+FiscalMonthNo-1)
3. Range(ColNo & RowNo).Select

(I need to deduct one as under this process 1 is not the current position but one column along.)

I would prefer not to use two variables i.e. use StartCell (D5) instead of ColNo (D) & RowNo (5) and just 'bump' the location across from the StartCell, but I'm happy with anything that works!

Do let me know if you have any other ideas - or if I've misunderstood how to use offset

 

[chergh]

to get to the cell 5 across and 3 down from A1 it would be:

range("A1").offset(3,5).select

 

[eoan]

That's absolutely perfect, chergh - thank you!

 

[Brianwarnock]

Hi Brian - thank you for responding; i've taken a look at offset's help page and unless i'm mistaken it is used within a cell to capture a range elsewhere on the page.

From Excel VBA Help

Brian

Offset property as it applies to the Range object.

Returns a Range object that represents a range that’s offset from the specified range. Read-only.

expression.Offset(RowOffset, ColumnOffset)

expression Required. An expression that returns a Range object.

RowOffset Optional Variant. The number of rows (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset downward, and negative values are offset upward. The default value is 0.

ColumnOffset Optional Variant. The number of columns (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left. The default value is 0.

 

[chergh]

might be brian

but you cant seem to use cells("A1") to refer to excel cells in access VBA

the way i found was to use cells(row, col) or maybe cells(col, row) (both numeric) and I just stuck with that

You can use the cells function with column letter but the format is different, it is cells(row,col).

To select cell D5 it would be:

worksheets("SheetName").cells(5, "D").select

It's more intuitive to use:

worksheets("SheetName").range("D5").select

it's also unecessary to use asc and chr function to convert column letter to a column number the following is what I use for this:

Public Function RetColNum(colLet As String) As Long

   RetColNum = thisworkbook.worksheets(1).Range(colLet & "1").Column

End Function

You can use the address property of a range object to return the column letter which can then be passed to the function, you also need to add code for when you column is "AA" or higher.

And to change a column number into a column letter:

Public Function RetColLett(colNum As Long) As String

If colNum < 27 Then
    RetColLett = Left(ThisWorkbook.Worksheets(1).Cells(1, colNum).Address(rowabsolute:=False, columnabsolute:=False), 1)
Else
    RetColLett = Left(ThisWorkbook.Worksheets(1).Cells(1, colNum).Address(rowabsolute:=False, columnabsolute:=False), 2)
End If

End Function