Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
starting from bottom of range instead of top
I wrote the following code:
Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsSrc = ActiveSheet 'sWhichTop = _ 'InputBox("Please enter 10 or 21 to determine which counties you want extracted", , 10) 'REPLACE with code to determine which table to use Set rCtyLst = wsCtyLst.Range("C2:C11") Workbooks("Mark Top 10.xls").Activate wsCtyLst.Select rCtyLst.Select Workbooks("Top Ten Extract Test.xls").Activate sCtySrcCol = InputBox("Please enter the column where the counties are currently listed", _ , "A") sColMrk10 = InputBox("Please enter the column to mark the Top Ten Counties", "E") 'TEST for valid entries on both variables 'TEST sColMrk10 for existing data ' TEST for county numbers/names or names With wsSrc Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _ .Cells(2, sCtySrcCol).End(xlDown)) End With wsSrc.Select rCtySrc.Select Set rFndCell = Cells.Find(What:=rCtyLst, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) I want it to start searching rCtySrc using the first value in rCtyLst, but it uses the last value. How to I tell it to search from the top down? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
starting from bottom of range instead of top
Do you want to find the top most match?
If yes, you can start at the bottom and find the next one. (If you wanted to find the last occurance, you can start at the top and find the previous one. (.cells(1) instead of .cells(.cells.count) in the following code). You have a few .selects and .activates, so I'm not sure if this is what you want--but it compiled. Note that instead of letting the user type in a column letter or number, I changed it to application.inputbox. This allows the user to point and click--and you don't need to validate that the user actually entered a correct column number/letter. Option Explicit Sub testme01() Dim wsCtyLst As Worksheet Dim wsSrc As Worksheet Dim rFndCell As Range Dim sCtySrcCol As Long Dim sColMrk10 As Long Dim rCtySrc As Range Dim rCtyLst As String Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsSrc = ActiveSheet 'Workbooks("Top Ten Extract Test.xls").Activate sCtySrcCol = 0 sColMrk10 = 0 On Error Resume Next sCtySrcCol = Application.InputBox _ (prompt:="Please enter the column where the " & _ "counties are currently listed", _ Type:=8, Default:="$a$1").Cells(1).Column If sCtySrcCol = 0 Then Exit Sub 'user hit cancel End If sColMrk10 = Application.InputBox _ (prompt:="Please enter the column to mark " & _ "the Top Ten Counties", _ Type:=8, Default:="$e$1").Cells(1).Column If sColMrk10 = 0 Then Exit Sub 'user hit cancel End If On Error GoTo 0 rCtyLst = "something or another" With wsSrc Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _ .Cells(2, sCtySrcCol).End(xlDown)) End With With rCtySrc Set rFndCell = .Cells.Find(What:=rCtyLst, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If rFndCell Is Nothing Then MsgBox "Not found" Else 'do what you want End If End Sub davegb wrote: I wrote the following code: Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsSrc = ActiveSheet 'sWhichTop = _ 'InputBox("Please enter 10 or 21 to determine which counties you want extracted", , 10) 'REPLACE with code to determine which table to use Set rCtyLst = wsCtyLst.Range("C2:C11") Workbooks("Mark Top 10.xls").Activate wsCtyLst.Select rCtyLst.Select Workbooks("Top Ten Extract Test.xls").Activate sCtySrcCol = InputBox("Please enter the column where the counties are currently listed", _ , "A") sColMrk10 = InputBox("Please enter the column to mark the Top Ten Counties", "E") 'TEST for valid entries on both variables 'TEST sColMrk10 for existing data ' TEST for county numbers/names or names With wsSrc Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _ .Cells(2, sCtySrcCol).End(xlDown)) End With wsSrc.Select rCtySrc.Select Set rFndCell = Cells.Find(What:=rCtyLst, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) I want it to start searching rCtySrc using the first value in rCtyLst, but it uses the last value. How to I tell it to search from the top down? Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
starting from bottom of range instead of top
Dave Peterson wrote: Do you want to find the top most match? If yes, you can start at the bottom and find the next one. (If you wanted to find the last occurance, you can start at the top and find the previous one. (.cells(1) instead of .cells(.cells.count) in the following code). Thanks for your reply, Dave. This helps a lot. I'm still having problems with this other part. In the code following the remark, 'TEST for county numbers/names or names, I want to test for valid county name at the top of the list (wsSrc) by comparing it to the first value in the reference list (rCtyLst). If the names match, go ahead with the macro, if they don't, tell the user something is wrong. But for some reason, when I define the range rCtyLst, it selects the last value in the list not the first, and tests for that value. So the rest of the macro shouldn't run. But I don't know how to tell it to start with the first value in the range, since I always thought that when you do a comparison like this, it would automatically start at the top of the range. Any ideas on how to tell it to start with the first value in the range? I guess I could just compare that particular cell instead of using the range. You have a few .selects and .activates, so I'm not sure if this is what you want--but it compiled. Note that instead of letting the user type in a column letter or number, I changed it to application.inputbox. This allows the user to point and click--and you don't need to validate that the user actually entered a correct column number/letter. The .selects and .activates are mostly just to make sure the program is getting the values where I want it to get them. Most of them will be removed when this part of the code is running properly. Of course, I have to be careful that the correct sheet is activated when needed. Thanks for changing it to having the user select the appropriate columns. You are a step ahead of me. I had planned on asking how to do that after I got the macro running properly. Option Explicit Sub testme01() Dim wsCtyLst As Worksheet Dim wsSrc As Worksheet Dim rFndCell As Range Dim sCtySrcCol As Long Dim sColMrk10 As Long Dim rCtySrc As Range Dim rCtyLst As String Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsSrc = ActiveSheet 'Workbooks("Top Ten Extract Test.xls").Activate sCtySrcCol = 0 sColMrk10 = 0 On Error Resume Next sCtySrcCol = Application.InputBox _ (prompt:="Please enter the column where the " & _ "counties are currently listed", _ Type:=8, Default:="$a$1").Cells(1).Column If sCtySrcCol = 0 Then Exit Sub 'user hit cancel End If sColMrk10 = Application.InputBox _ (prompt:="Please enter the column to mark " & _ "the Top Ten Counties", _ Type:=8, Default:="$e$1").Cells(1).Column If sColMrk10 = 0 Then Exit Sub 'user hit cancel End If On Error GoTo 0 rCtyLst = "something or another" With wsSrc Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _ .Cells(2, sCtySrcCol).End(xlDown)) End With With rCtySrc Set rFndCell = .Cells.Find(What:=rCtyLst, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If rFndCell Is Nothing Then MsgBox "Not found" Else 'do what you want End If End Sub davegb wrote: I wrote the following code: Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsSrc = ActiveSheet 'sWhichTop = _ 'InputBox("Please enter 10 or 21 to determine which counties you want extracted", , 10) 'REPLACE with code to determine which table to use Set rCtyLst = wsCtyLst.Range("C2:C11") Workbooks("Mark Top 10.xls").Activate wsCtyLst.Select rCtyLst.Select Workbooks("Top Ten Extract Test.xls").Activate sCtySrcCol = InputBox("Please enter the column where the counties are currently listed", _ , "A") sColMrk10 = InputBox("Please enter the column to mark the Top Ten Counties", "E") 'TEST for valid entries on both variables 'TEST sColMrk10 for existing data ' TEST for county numbers/names or names With wsSrc Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _ .Cells(2, sCtySrcCol).End(xlDown)) End With wsSrc.Select rCtySrc.Select Set rFndCell = Cells.Find(What:=rCtyLst, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) I want it to start searching rCtySrc using the first value in rCtyLst, but it uses the last value. How to I tell it to search from the top down? Thanks! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
starting from bottom of range instead of top
Would rCtyLst.cells(1) give you the top cell in rCtyLst?
Or did you mean something like: With rCtySrc Set rFndCell = .Cells.Find(What:=rCtyLst, _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False) End With This starts at cells(1), but then looks up the list (xlprevious). (I forgot to mention that change in the previous message.) davegb wrote: Dave Peterson wrote: Do you want to find the top most match? If yes, you can start at the bottom and find the next one. (If you wanted to find the last occurance, you can start at the top and find the previous one. (.cells(1) instead of .cells(.cells.count) in the following code). Thanks for your reply, Dave. This helps a lot. I'm still having problems with this other part. In the code following the remark, 'TEST for county numbers/names or names, I want to test for valid county name at the top of the list (wsSrc) by comparing it to the first value in the reference list (rCtyLst). If the names match, go ahead with the macro, if they don't, tell the user something is wrong. But for some reason, when I define the range rCtyLst, it selects the last value in the list not the first, and tests for that value. So the rest of the macro shouldn't run. But I don't know how to tell it to start with the first value in the range, since I always thought that when you do a comparison like this, it would automatically start at the top of the range. Any ideas on how to tell it to start with the first value in the range? I guess I could just compare that particular cell instead of using the range. You have a few .selects and .activates, so I'm not sure if this is what you want--but it compiled. Note that instead of letting the user type in a column letter or number, I changed it to application.inputbox. This allows the user to point and click--and you don't need to validate that the user actually entered a correct column number/letter. The .selects and .activates are mostly just to make sure the program is getting the values where I want it to get them. Most of them will be removed when this part of the code is running properly. Of course, I have to be careful that the correct sheet is activated when needed. Thanks for changing it to having the user select the appropriate columns. You are a step ahead of me. I had planned on asking how to do that after I got the macro running properly. Option Explicit Sub testme01() Dim wsCtyLst As Worksheet Dim wsSrc As Worksheet Dim rFndCell As Range Dim sCtySrcCol As Long Dim sColMrk10 As Long Dim rCtySrc As Range Dim rCtyLst As String Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsSrc = ActiveSheet 'Workbooks("Top Ten Extract Test.xls").Activate sCtySrcCol = 0 sColMrk10 = 0 On Error Resume Next sCtySrcCol = Application.InputBox _ (prompt:="Please enter the column where the " & _ "counties are currently listed", _ Type:=8, Default:="$a$1").Cells(1).Column If sCtySrcCol = 0 Then Exit Sub 'user hit cancel End If sColMrk10 = Application.InputBox _ (prompt:="Please enter the column to mark " & _ "the Top Ten Counties", _ Type:=8, Default:="$e$1").Cells(1).Column If sColMrk10 = 0 Then Exit Sub 'user hit cancel End If On Error GoTo 0 rCtyLst = "something or another" With wsSrc Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _ .Cells(2, sCtySrcCol).End(xlDown)) End With With rCtySrc Set rFndCell = .Cells.Find(What:=rCtyLst, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If rFndCell Is Nothing Then MsgBox "Not found" Else 'do what you want End If End Sub davegb wrote: I wrote the following code: Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsSrc = ActiveSheet 'sWhichTop = _ 'InputBox("Please enter 10 or 21 to determine which counties you want extracted", , 10) 'REPLACE with code to determine which table to use Set rCtyLst = wsCtyLst.Range("C2:C11") Workbooks("Mark Top 10.xls").Activate wsCtyLst.Select rCtyLst.Select Workbooks("Top Ten Extract Test.xls").Activate sCtySrcCol = InputBox("Please enter the column where the counties are currently listed", _ , "A") sColMrk10 = InputBox("Please enter the column to mark the Top Ten Counties", "E") 'TEST for valid entries on both variables 'TEST sColMrk10 for existing data ' TEST for county numbers/names or names With wsSrc Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _ .Cells(2, sCtySrcCol).End(xlDown)) End With wsSrc.Select rCtySrc.Select Set rFndCell = Cells.Find(What:=rCtyLst, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) I want it to start searching rCtySrc using the first value in rCtyLst, but it uses the last value. How to I tell it to search from the top down? Thanks! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
starting from bottom of range instead of top
Dave Peterson wrote: Would rCtyLst.cells(1) give you the top cell in rCtyLst? Or did you mean something like: With rCtySrc Set rFndCell = .Cells.Find(What:=rCtyLst, _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False) End With This starts at cells(1), but then looks up the list (xlprevious). (I forgot to mention that change in the previous message.) I tried your second solution, but it didn't work. I don't know why. I did try .cells(2) and .cells(3) and it made no difference, rFndCell still returned the last item in rCtyLst. However, the solution at the top worked fine. I defined rCtyLstStrt = rCtyLst.cells(1) and it picked up the first item in that list. Thanks for the help. davegb wrote: Dave Peterson wrote: Do you want to find the top most match? If yes, you can start at the bottom and find the next one. (If you wanted to find the last occurance, you can start at the top and find the previous one. (.cells(1) instead of .cells(.cells.count) in the following code). Thanks for your reply, Dave. This helps a lot. I'm still having problems with this other part. In the code following the remark, 'TEST for county numbers/names or names, I want to test for valid county name at the top of the list (wsSrc) by comparing it to the first value in the reference list (rCtyLst). If the names match, go ahead with the macro, if they don't, tell the user something is wrong. But for some reason, when I define the range rCtyLst, it selects the last value in the list not the first, and tests for that value. So the rest of the macro shouldn't run. But I don't know how to tell it to start with the first value in the range, since I always thought that when you do a comparison like this, it would automatically start at the top of the range. Any ideas on how to tell it to start with the first value in the range? I guess I could just compare that particular cell instead of using the range. You have a few .selects and .activates, so I'm not sure if this is what you want--but it compiled. Note that instead of letting the user type in a column letter or number, I changed it to application.inputbox. This allows the user to point and click--and you don't need to validate that the user actually entered a correct column number/letter. The .selects and .activates are mostly just to make sure the program is getting the values where I want it to get them. Most of them will be removed when this part of the code is running properly. Of course, I have to be careful that the correct sheet is activated when needed. Thanks for changing it to having the user select the appropriate columns. You are a step ahead of me. I had planned on asking how to do that after I got the macro running properly. Option Explicit Sub testme01() Dim wsCtyLst As Worksheet Dim wsSrc As Worksheet Dim rFndCell As Range Dim sCtySrcCol As Long Dim sColMrk10 As Long Dim rCtySrc As Range Dim rCtyLst As String Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsSrc = ActiveSheet 'Workbooks("Top Ten Extract Test.xls").Activate sCtySrcCol = 0 sColMrk10 = 0 On Error Resume Next sCtySrcCol = Application.InputBox _ (prompt:="Please enter the column where the " & _ "counties are currently listed", _ Type:=8, Default:="$a$1").Cells(1).Column If sCtySrcCol = 0 Then Exit Sub 'user hit cancel End If sColMrk10 = Application.InputBox _ (prompt:="Please enter the column to mark " & _ "the Top Ten Counties", _ Type:=8, Default:="$e$1").Cells(1).Column If sColMrk10 = 0 Then Exit Sub 'user hit cancel End If On Error GoTo 0 rCtyLst = "something or another" With wsSrc Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _ .Cells(2, sCtySrcCol).End(xlDown)) End With With rCtySrc Set rFndCell = .Cells.Find(What:=rCtyLst, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) End With If rFndCell Is Nothing Then MsgBox "Not found" Else 'do what you want End If End Sub davegb wrote: I wrote the following code: Set wsCtyLst = Workbooks("Mark Top 10.xls").Worksheets("CtyLst") Set wsSrc = ActiveSheet 'sWhichTop = _ 'InputBox("Please enter 10 or 21 to determine which counties you want extracted", , 10) 'REPLACE with code to determine which table to use Set rCtyLst = wsCtyLst.Range("C2:C11") Workbooks("Mark Top 10.xls").Activate wsCtyLst.Select rCtyLst.Select Workbooks("Top Ten Extract Test.xls").Activate sCtySrcCol = InputBox("Please enter the column where the counties are currently listed", _ , "A") sColMrk10 = InputBox("Please enter the column to mark the Top Ten Counties", "E") 'TEST for valid entries on both variables 'TEST sColMrk10 for existing data ' TEST for county numbers/names or names With wsSrc Set rCtySrc = .Range(.Cells(2, sCtySrcCol), _ .Cells(2, sCtySrcCol).End(xlDown)) End With wsSrc.Select rCtySrc.Select Set rFndCell = Cells.Find(What:=rCtyLst, _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) I want it to start searching rCtySrc using the first value in rCtyLst, but it uses the last value. How to I tell it to search from the top down? Thanks! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FORMULA for COUNTING #S STARTING WITH A 4 IN A RANGE 1 TO 100,000 | Excel Worksheet Functions | |||
Dynamic chart that displays a range starting today | Charts and Charting in Excel | |||
Scroll bar starting at bottom of form | Excel Programming | |||
Scrollbars starting at the bottom of form | Excel Discussion (Misc queries) | |||
How to: Add blank row at bottom of range | Excel Programming |