Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cidtional Formatting doesn't work across all cells in range | Excel Worksheet Functions | |||
CTRL A doesn't work on a range of cells | Excel Discussion (Misc queries) | |||
=EVALUATE("{ to work in a range of cells | Excel Worksheet Functions | |||
Calculating the maximum value in a range of cells in a closed work | Excel Worksheet Functions | |||
Formatting a range of cells doesn't work | Excel Discussion (Misc queries) |