Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Ranges
Hi, I having a problem finding the appropiate way of
selecting ranges. I have a group of workbooks with a table on a sheet. These tables varie in size (rows & column numbers). I need to open each workbook and find a value on the first column and then retrieve the corresponding value on the last column. I am trying to create a VBA macro to do this. Here is whare I am stuck. Dim Wsheet As Worksheet Dim Rag As Range Dim Urag As Range Dim str As Variant Dim StC As String Dim I As Integer Dim UpL As String Dim MyRag As String Dim ID as String ID = "AK-0252" Set Wsheet = ThisWorkbook.ActiveSheet Set Urag = Wsheet.UsedRange str = Split(Urag.Address, "$") For I = 0 To UBound(str) If str(I) < "" Then StC = StC & str(I) End If Next I UpL = Mid(StC, InStr(1, StC, ":") + 2, Len(StC)) MyRag = "A1:A" & UpL Wsheet.Range(MyRag).Select Selection.Find(What:=ID, After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _ :=False).Activate Set Rag = Application.ActiveCell Rag.Select Up to here I am doing what I want, but I am not sure this is the most efficient way. Can someone give me an idea of how to do this better? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Ranges
Thanks Bernie, but how can I then get the correspoing
value from the last column? My problem is how to get the last cell on the row of the found value. An offset would work but, how can I get the offset value? I was using the UsedRange to get that. Any hints? -----Original Message----- J, Assuming you only have the one table on the sheet, here's a shorter version: Dim myValue As Variant myValue = Cells(Range("A:A").Find(What:="AK-0252", _ LookIn:=xlValues, _ LookAt:=xlWhole).Row, 256).End(xlToLeft).Value MsgBox myValue HTH, Bernie MS Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Ranges
Dim Wsheet As Worksheet
Dim Rag As Range Dim Rng as Range Dim ID as String Dim LastCol as Long Dim RagLast as Range ID = "AK-0252" Set Wsheet = ThisWorkbook.ActiveSheet LastCol = Wsheet.UsedRange.Columns( _ WSheet.UserRange.Columns.Count).Column Set rng = Intersect(Wsheet.Columns(1), WSheet.UsedRange) set Rag = rng.Find(What:=ID, After:=rng(rng.count), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False If Rag is Nothing then msgbox "Id: " & ID & " was not found" exit sub End if ' no to get the last column value for the found row set RagLast = wSheet.Cells(Rag.Row,LastCol) msgbox RagLast.Address(0,0) & " has value of " & RagLast.Value You can use arguments to the address command to adjust how it is returned: ? Range("A1:F20").Address $A$1:$F$20 ? Range("A1:F20").Address(0,0) A1:F20 ? Range("A1:F20").Address(1,0) A$1:F$20 But you don't even need to do that as I showed you. -- Regards, Tom Ogilvy "JLong" wrote in message ... Hi, I having a problem finding the appropiate way of selecting ranges. I have a group of workbooks with a table on a sheet. These tables varie in size (rows & column numbers). I need to open each workbook and find a value on the first column and then retrieve the corresponding value on the last column. I am trying to create a VBA macro to do this. Here is whare I am stuck. Dim Wsheet As Worksheet Dim Rag As Range Dim Urag As Range Dim str As Variant Dim StC As String Dim I As Integer Dim UpL As String Dim MyRag As String Dim ID as String ID = "AK-0252" Set Wsheet = ThisWorkbook.ActiveSheet Set Urag = Wsheet.UsedRange str = Split(Urag.Address, "$") For I = 0 To UBound(str) If str(I) < "" Then StC = StC & str(I) End If Next I UpL = Mid(StC, InStr(1, StC, ":") + 2, Len(StC)) MyRag = "A1:A" & UpL Wsheet.Range(MyRag).Select Selection.Find(What:=ID, After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _ :=False).Activate Set Rag = Application.ActiveCell Rag.Select Up to here I am doing what I want, but I am not sure this is the most efficient way. Can someone give me an idea of how to do this better? TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Ranges
His code already does that.
The only caution I would make is that it assumes the ID value will be found and that there is a value in the last column for that found row. A failed first assumption gives you an error (91) and a failed second assumption gives you the wrong value. -- Regards, Tom Ogilvy wrote in message ... Thanks Bernie, but how can I then get the correspoing value from the last column? My problem is how to get the last cell on the row of the found value. An offset would work but, how can I get the offset value? I was using the UsedRange to get that. Any hints? -----Original Message----- J, Assuming you only have the one table on the sheet, here's a shorter version: Dim myValue As Variant myValue = Cells(Range("A:A").Find(What:="AK-0252", _ LookIn:=xlValues, _ LookAt:=xlWhole).Row, 256).End(xlToLeft).Value MsgBox myValue HTH, Bernie MS Excel MVP |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Ranges
A short coming in what I suggest would be the assumption that
LastCol = Wsheet.UsedRange.Columns( _ WSheet.UserRange.Columns.Count).Column UsedRange is not always accurate. This could be replaced with Dim rngUsed as Range set rngUsed = Wsheet.Range("A1").CurrentRegion LastCol = rngUsed.Columns(rngUsed.columns.count).column -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Dim Wsheet As Worksheet Dim Rag As Range Dim Rng as Range Dim ID as String Dim LastCol as Long Dim RagLast as Range ID = "AK-0252" Set Wsheet = ThisWorkbook.ActiveSheet LastCol = Wsheet.UsedRange.Columns( _ WSheet.UserRange.Columns.Count).Column Set rng = Intersect(Wsheet.Columns(1), WSheet.UsedRange) set Rag = rng.Find(What:=ID, After:=rng(rng.count), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False If Rag is Nothing then msgbox "Id: " & ID & " was not found" exit sub End if ' no to get the last column value for the found row set RagLast = wSheet.Cells(Rag.Row,LastCol) msgbox RagLast.Address(0,0) & " has value of " & RagLast.Value You can use arguments to the address command to adjust how it is returned: ? Range("A1:F20").Address $A$1:$F$20 ? Range("A1:F20").Address(0,0) A1:F20 ? Range("A1:F20").Address(1,0) A$1:F$20 But you don't even need to do that as I showed you. -- Regards, Tom Ogilvy "JLong" wrote in message ... Hi, I having a problem finding the appropiate way of selecting ranges. I have a group of workbooks with a table on a sheet. These tables varie in size (rows & column numbers). I need to open each workbook and find a value on the first column and then retrieve the corresponding value on the last column. I am trying to create a VBA macro to do this. Here is whare I am stuck. Dim Wsheet As Worksheet Dim Rag As Range Dim Urag As Range Dim str As Variant Dim StC As String Dim I As Integer Dim UpL As String Dim MyRag As String Dim ID as String ID = "AK-0252" Set Wsheet = ThisWorkbook.ActiveSheet Set Urag = Wsheet.UsedRange str = Split(Urag.Address, "$") For I = 0 To UBound(str) If str(I) < "" Then StC = StC & str(I) End If Next I UpL = Mid(StC, InStr(1, StC, ":") + 2, Len(StC)) MyRag = "A1:A" & UpL Wsheet.Range(MyRag).Select Selection.Find(What:=ID, After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _ :=False).Activate Set Rag = Application.ActiveCell Rag.Select Up to here I am doing what I want, but I am not sure this is the most efficient way. Can someone give me an idea of how to do this better? TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Ranges
I agree with you Tom. In my code my assumption was that he wanted the right
most column whether it contained data or not, which is more than just a little dangerous. But since he just wanted his code tightend up; I tighened it up. That new line would make a good second argument for my intersect if that is what is intended. Just change the Column to Entirecolumn as the final parameter... "Tom Ogilvy" wrote: A short coming in what I suggest would be the assumption that LastCol = Wsheet.UsedRange.Columns( _ WSheet.UserRange.Columns.Count).Column UsedRange is not always accurate. This could be replaced with Dim rngUsed as Range set rngUsed = Wsheet.Range("A1").CurrentRegion LastCol = rngUsed.Columns(rngUsed.columns.count).column -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Dim Wsheet As Worksheet Dim Rag As Range Dim Rng as Range Dim ID as String Dim LastCol as Long Dim RagLast as Range ID = "AK-0252" Set Wsheet = ThisWorkbook.ActiveSheet LastCol = Wsheet.UsedRange.Columns( _ WSheet.UserRange.Columns.Count).Column Set rng = Intersect(Wsheet.Columns(1), WSheet.UsedRange) set Rag = rng.Find(What:=ID, After:=rng(rng.count), _ LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False If Rag is Nothing then msgbox "Id: " & ID & " was not found" exit sub End if ' no to get the last column value for the found row set RagLast = wSheet.Cells(Rag.Row,LastCol) msgbox RagLast.Address(0,0) & " has value of " & RagLast.Value You can use arguments to the address command to adjust how it is returned: ? Range("A1:F20").Address $A$1:$F$20 ? Range("A1:F20").Address(0,0) A1:F20 ? Range("A1:F20").Address(1,0) A$1:F$20 But you don't even need to do that as I showed you. -- Regards, Tom Ogilvy "JLong" wrote in message ... Hi, I having a problem finding the appropiate way of selecting ranges. I have a group of workbooks with a table on a sheet. These tables varie in size (rows & column numbers). I need to open each workbook and find a value on the first column and then retrieve the corresponding value on the last column. I am trying to create a VBA macro to do this. Here is whare I am stuck. Dim Wsheet As Worksheet Dim Rag As Range Dim Urag As Range Dim str As Variant Dim StC As String Dim I As Integer Dim UpL As String Dim MyRag As String Dim ID as String ID = "AK-0252" Set Wsheet = ThisWorkbook.ActiveSheet Set Urag = Wsheet.UsedRange str = Split(Urag.Address, "$") For I = 0 To UBound(str) If str(I) < "" Then StC = StC & str(I) End If Next I UpL = Mid(StC, InStr(1, StC, ":") + 2, Len(StC)) MyRag = "A1:A" & UpL Wsheet.Range(MyRag).Select Selection.Find(What:=ID, After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _ :=False).Activate Set Rag = Application.ActiveCell Rag.Select Up to here I am doing what I want, but I am not sure this is the most efficient way. Can someone give me an idea of how to do this better? TIA |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Ranges
Thanks to all, I like all your suggestions. Great stuff
guys. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Ranges
Tom, why do you write this
LastCol = rngUsed.Columns(rngUsed.columns.count).column Isn't the (rngUsed.columns.count) the total number of columns in the range? Why the last .column? Is it something I am missing or am I just to green? TIA |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Ranges
Thank you very much for your explanation.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding specific text in ranges | Excel Discussion (Misc queries) | |||
Finding max from different ranges of data | Excel Discussion (Misc queries) | |||
Finding maximum of various ranges of data | Excel Discussion (Misc queries) | |||
Finding all Similarly Named Ranges? | Excel Programming | |||
Finding ranges in an array, cut and paste also. | Excel Programming |