Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 573
Default range, cells won't work

The following code hangs at the marked line:

Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst")

Set wsSrc = ActiveSheet
Set rCtyLst = wsCtyLst.Range("C2:C11")
'wsCtyLst.Activate
'rCtyLst.Select

sCtyCol = InputBox("Please enter the column where the counties are
currently listed", _
, "A")
sColMrk10 = InputBox("Please enter the column to mark the Top Ten
Counties")
'sCtyLstCol = "C"


'TEST for valid entries on both variables
'TEST sColMrk10 for existing data


' TEST for county numbers/names or names
Set rCty = wsSrc.Cells(2, sCtyCol).End(xlDown) '<---ERROR
wsSrc.Activate
rCty.Select
Set rFndCell = Cells.Find(What:=rCty, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

Select Case rFndCell
Case "ADAMS"

lRow = wsCtyLst.Cells(2, sCtyLstCol).End(xlDown)

Set rCtyLst = wsCtyLst.Range(.Cells(2, sCtyLstCol), _
.Cells(lRow, sCtyLstCol)) <--ERROR

' wsCtyLst.Activate
' rCtyLst.Select

Case "01-ADAMS"
MsgBox "oops!"
End Select


For Each rCell In rCtyLst

wsSrc.Activate
Set rFndCell = Cells.Find(What:=rCell, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rFndCell Is Nothing Then
Set rCtyMrkr = wsSrc.Cells(rFndCell.Row, sColMrk10)

rCtyMrkr = "y"
End If

Next
Application.ScreenUpdating = True
End Sub

Defining a range by cells defined by variable names never seems to
work, no matter how many ways I try to get it or how many lines of code
I look at in other examples. Any ideas?

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 573
Default range, cells won't work

Got it! Tried again, finally got it to work

With wsCtyLst
Set rCtyLst = .Range(.Cells(2, sCtyLstCol), .Cells(lRow,
sCtyLstCol))
End With

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cidtional Formatting doesn't work across all cells in range Twishlist Excel Worksheet Functions 6 September 26th 07 04:34 PM
CTRL A doesn't work on a range of cells DianeG Excel Discussion (Misc queries) 2 June 4th 07 08:44 PM
=EVALUATE("{ to work in a range of cells Fin Fang Foom Excel Worksheet Functions 8 May 9th 07 11:33 PM
Calculating the maximum value in a range of cells in a closed work Barb Reinhardt Excel Worksheet Functions 1 October 11th 05 03:03 PM
Formatting a range of cells doesn't work Stefi Excel Discussion (Misc queries) 6 August 15th 05 12:21 PM


All times are GMT +1. The time now is 06:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"