Offset Range

[lemo]

it's probably trivial, but my brain is boiling by now..

i am trying to offset the range to the right using the following -

LastInsp = WorksheetFunction.Match(1, Range("EB" & r & ":EY" & r).Offset(0, c), 0)

where 'c' changes from 0 to n as i go through the table.

my problem is that it ADDS n columns to the range, and i'd like it to SHIFT the range n columns to the right.

???
l

 

[HaHoBe]

Hi, lemo,

maybe use the range with Cells and add the offset directly:

LastInsp = WorksheetFunction.Match(1, Range(Cells(r, 132 + c), Cells(r, 155 + c)), 0)

Ciao,
Holger

 

[lemo]

works!
thanks HaHoBe.

unfortunately, i just realized that my problem is more troubling. my goal is to find the position of the LAST cell in one-dimensional range that has a "1". there may be more than one. the 'match' function finds the first.. have no clue how to match right to left..

l

 

[HaHoBe]

Hi, lemo,

why not work the range from high column to low and stop at the first appearence with negative steps?

Ciao,
Holger

 

[lemo]

not sure how to do it, but will look into it.

one problem i foresee though is that it's going to take a long time for all these iterations to happen. i need to fill a relatively large grid (3300x90 or so) with quasi-randomly positioned 1's. even without these column iterations it's a turtle.. oh well - longer lunch break..

thanks,
l

 

[HaHoBe]

Hi, lemo,

maybe consider FIND as an alternative if turning off ScreenUpdating and Calculation still is to slow.

Sub LoopFromRight()
Dim lngCounter As Long
Dim lngCol As Long
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For lngCounter = 3000 To 4 Step -1
    For lngCol = 153 To 132 Step -1                 'Columns need to be adjusted
        If Cells(lngCounter, lngCol).Value = 1 Then
            Debug.Print Cells(lngCounter, lngCol).Address
            Exit For
        End If
    Next lngCol
Next lngCounter
With Application
    .ScreenUpdating = True
    .Calculation = xlAutomatic
End With
End Sub

Ciao,
Holger

 

[lemo]

thanks a bunch once more HaHoBe.

i managed to come up with something conceptually similar to your code, but there are a couple of very helpful lines there (didn't know about 'xlCalculationManual' and also was assigning values to variables instead of just printing them which i assume saves time also).

l