Problem with Range.Cells.Offset and Range.Cells( row + offset, column)

[erazmus]

Hi Guys,

Hoping someone can shed some light on where I am going wrong here.

I am trying to automate parts of a break rostering system.

The sub is supposed to check through a range which holds each persons
availability, assigned breaks, and other information which would not be
made available to everyone.

When it finds a cell formatted to represent a break it will look up the
person's name and the break time.

It will then search through a second range which is the simple version
of the roster which is sent out to each person.

It searches for the relevant time in this second range to get the cell
address. It then should cycle down each row underneath the target time
until it finds an empty cell where it can then put the person's name.

The part where I am slipping up is "cycle down each row underneath the
target time until it finds an empty cell"

Can someone please look at the following code and advise if there is an
obvious error?

I am quite noob(ish) when it comes to this so feel free to laugh and
point (and provide advice) if there is an easier way of doing this

TIA

Deon.

Private Sub Generate_Roster()
Dim rngTodaysResources, rngTodaysRoster As Range
Dim index, rowCounter, colBreakTime, rowBreakTime, rowBreakName,
colBreakName, colRosterTime, rowRosterTime As Integer

Set rngTodaysResources = Range("A1:AT45")
Set rngTodaysRoster = Range("AV24:BB62")

rowCounter = 0

For Each Cell In rngTodaysResources
If Cell.Interior.Color = vbBlue And Cell.Value = "" Then

colBreakTime = Cell.Column
rowBreakTime = 1
colBreakName = 1
rowBreakName = Cell.Row
targetBreakTime = Cells(rowBreakTime, colBreakTime)
targetName = Cells(rowBreakName, colBreakName)

'' The following returns expected values which means that the
last block of assignments worked
MsgBox targetName & " at " & Format(targetBreakTime, "hh:mm AMPM")


'' Cycle through today's roster to find where the matching time is
in the roster
For index = 1 To rngTodaysRoster.Cells.Count
If rngTodaysRoster.Cells(index).Value = targetBreakTime Then
colRosterTime = rngTodaysRoster.Cells(index).Column
rowRosterTime = rngTodaysRoster.Cells(index).Row

'' This next MsgBox returns expected values, the one after that
does not however.
MsgBox "Match found at " &
rngTodaysRoster.Cells(index).Address & ", Should match row " &
rowRosterTime & " and column " & colRosterTime

'' This next MsgBox returns the right values for rowCounter,
but not for the address,
'' doesn't matter if I use -
'' rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0)
'' or
'' rngTodaysRoster.Cells((rowRosterTime + rowCounter),
colRosterTime)
'' It always states an address which is not anywhere near
correct, usually CQ58 or so.

MsgBox "RowCounter is " & rowCounter & " entering
search at " & rngTodaysRoster.Cells((rowRosterTime + rowCounter),
colRosterTime).Address

'' Never enters this loop. Address issue above.
While rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0).Value <> ""
rowCounter = rowCounter + 1
MsgBox rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0).Address
MsgBox "Value of cell is :" &
rngTodaysRoster.Cells(rowRosterTime, colRosterTime).Offset(rowCounter,
0).Value & ":"
MsgBox "No match found at row " & (rowRosterTime +
rowCounter) & " and column " & colRosterTime
Wend

rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0) = targetName

MsgBox "RowCounter is " & rowCounter & " Exit search at
" & rngTodaysRoster.Cells(rowRosterTime,
colRosterTime).Offset(rowCounter, 0).Address
End If
Next
End If
Next

End Sub

 

[Rowan]

Hi Deon

I think that the problem lies in the statement:

rngTodaysRoster.Cells((rowRosterTime + rowCounter), colRosterTime).Address

This offsets from the first cell in the range rngTodaysRoster ie AV24,
rather than the first cell in the sheet ie A1. colRosterTime and
rowRosterTime are set based on the number of rows and columns in a sheet and
not in the range. So you might want to change this portion of the statement
to:

activesheet.Cells((rowRosterTime + rowCounter), colRosterTime).Address
or state the relevant sheet name if it may not be the activesheet.

One other observation: If you declare your variables:
Dim Variable1, Variable2, Variable3 as Integer
then only Variable3 will be an integer. The other two will be variants so
you should use:
Dim Variable1 as Integer, Variable2 as Integer, Variable3 As Integer
I normally declare each variable on its own line but that's my personal
preference to make it more readable.

Hope this helps
Rowan

 

[erazmus]

Hi Rowan,

Thanks for the info regarding the cell offset, I just assumed that
'range.Cells( row, column)' was an absolute reference to the worksheet,
never stopped to think that it was working relative to the range, seems
so obvious now considering what was happening

Regarding the Dim As part, will take that on board.

Thanks again

Deon.