Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting multiple rows within a CurrentRegion
Greetings:
I'm working on a scheduling application to automate sending appointments to another user's calendar. I've been successful in limiting the region to just those cells that are used, and I'm stuck in trying to select multiple ranges to grab eventual "subjects" and dates for the e-mails. Here's what I have so far: Sub Email() Dim rng As Range, curCell As Range Dim bytRowNum as Byte, bytColNum as Byte, bytColumnCounter as Byte, bytRowCounter as Byte Worksheets("Assign").Activate Set rng = ActiveCell.CurrentRegion ' Limits area on worksheet that's been used bytRowNum = rng.Rows.Count ' Counts the rows actually used bytColNum = rng.Columns.Count ' Counts the columns actually used For bytColCounter = 4 To bytColNum For bytRowCounter = 2 to bytRowNum Set curCell = ActiveSheet.Cells(bytCounter,4) If curCell.Value = lblEventID.Caption Or Replace(curCell.Value, "@", "") = lblID.Caption Then 'Here I want to get to the column number of the end cell, which will differ in situations and grab the date that's in the end cell End If Next Next Of course, I've tried various methods, but a jumpstart would be appreciated. Thanks, -- Brian J. Matuschak |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting multiple rows within a CurrentRegion
There are some corrections that need to be made before you can proceed...
Change your variable data types to Longs from Bytes Add "Option Explicit" as the first line in your module Correct the misspelled variable names Then maybe this can get you jumpstarted... -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware Sub Email() Dim rng As Range, curCell As Range Dim bytRowNum As Long, bytColNum As Long, bytColCounter As Long, bytRowCounter As Long Dim dteValue As Date Worksheets("Assign").Activate Set rng = ActiveCell.CurrentRegion ' Limits area on worksheet that's been used bytRowNum = rng.Rows.Count ' Counts the rows actually used bytColNum = rng.Columns.Count ' Counts the columns actually used For bytColCounter = 4 To bytColNum For bytRowCounter = 2 To bytRowNum Set curCell = ActiveSheet.Cells(bytRowCounter, 4) If curCell.Value = lblEventID.Caption Or _ Replace(curCell.Value, "@", "") = lblID.Caption Then dteValue = curCell.End(xlToRight).Value2 End If Next Next End Sub '------------------------------- "Brian J. Matuschak" wrote in message Greetings: I'm working on a scheduling application to automate sending appointments to another user's calendar. I've been successful in limiting the region to just those cells that are used, and I'm stuck in trying to select multiple ranges to grab eventual "subjects" and dates for the e-mails. Here's what I have so far: Sub Email() Dim rng As Range, curCell As Range Dim bytRowNum as Byte, bytColNum as Byte, bytColumnCounter as Byte, bytRowCounter as Byte Worksheets("Assign").Activate Set rng = ActiveCell.CurrentRegion ' Limits area on worksheet that's been used bytRowNum = rng.Rows.Count ' Counts the rows actually used bytColNum = rng.Columns.Count ' Counts the columns actually used For bytColCounter = 4 To bytColNum For bytRowCounter = 2 to bytRowNum Set curCell = ActiveSheet.Cells(bytCounter,4) If curCell.Value = lblEventID.Caption Or Replace(curCell.Value, "@", "") = lblID.Caption Then 'Here I want to get to the column number of the end cell, which will differ in situations and grab the date that's in the end cell End If Next Next Of course, I've tried various methods, but a jumpstart would be appreciated. Thanks, Brian J. Matuschak |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting multiple rows | Excel Discussion (Misc queries) | |||
CurrentRegion Selecting | Excel Programming | |||
selecting multiple rows | Excel Programming | |||
selecting multiple rows within a Macro | Excel Programming | |||
selecting multiple rows within a Macro | Excel Programming |