Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Range.Cells.Offset and Range.Cells( row + offset, column)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Range.Cells.Offset and Range.Cells( row + offset, col
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 " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Range.Cells.Offset and Range.Cells( row + offset, col
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 :P Regarding the Dim As part, will take that on board. Thanks again :) Deon. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro problem: range offset question | Excel Discussion (Misc queries) | |||
Offset function problem-Dynamic range | Excel Discussion (Misc queries) | |||
Sum a range of cells starting from an offset date list from TODAY() | Excel Worksheet Functions | |||
Dynamic Range Offset causing problem with this code | Excel Programming | |||
Dynamic range offset problem! | Excel Programming |