cell references in vba

[NDBC]

I am trying to select a cell I know it is a certain amount of columns to the
right of column "i" (variable called laps) and a set number of rows down
(variable called done).

Effectively i want is .Range("i"+laps & done). What is the correct code for
this.

Thanks

 

[FSt1]

hi,
post all of your code.

Regards
FSt1

 

[Jacob Skaria]

Use OFFSET; For example

Range("A1").Offset(5,2).Select will select 5 the cells 5 rows down and 2
columns right.

If this post helps click Yes

 

[NDBC]

Private Sub Sort_Click()

Dim MaxLap As Integer
Dim Rdone As Integer
Dim Onlap As Integer
Dim tl As Integer
Dim br As Integer



'A Grade
Worksheets("A Grade").Range("G5:IV104").Sort Key1:=Worksheets("A
Grade").Range("I5"), Order1:=xlDescending, _
DataOption1:=xlSortNormal

MaxLap = Worksheets("A Grade").Range("I5")
Rdone = 0
If MaxLap > 0 Then
For l = MaxLap To 1
Onlap = WorksheetFunction.CountIf(Worksheets("A Grade").Range("I5:I104"), l)
If Onlap > 0 Then
tl = 5 + Rdone
br = 4 + Onlap + Rdone
Worksheets("A Grade").Range("G" & tl & ":IV" & br).Sort Key1:=Worksheets("A
Grade").Range(("i" + Onlap) & tl), Order1:=xlascending,
DataOption1:=xlSortNormal
Rdone = Rdone + Onlap
End If
Next l
End If


A very brief explanation of what is happening. There is a list of riders
with lap times adjacent. The riders can all have varying amounts of laps.
Column i is the number of laps completed. First i sort by number of laps
completed in descending order. Then I am trying to sort the sub groups of
riders who have finished the same amount of laps by the time they finished
their last lap (furthest used column to the right of column I) in ascending
order. Hope this helps a bit.

 

[Jacob Skaria]

I hit 'Post' before intended.

Use OFFSET; For example

Range("A1").Offset(5,2).Select will select the cell 5 rows down and 2
columns right ie; C6

In your case it would be

Range("I1").Offset(done,laps).Select
OR may be
Range("I1").Offset(done-1,laps-1).Select


If this post helps click Yes

 

[NDBC]

It's obvious when you put it in front of me. What annoys me is I even have
that in some of my other code. Thank you.

 

[Jacek]

Range(Cells(x,y),Cells(x+5,y+5)).Select