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

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

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

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

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

Thanks to all, I like all your suggestions. Great stuff
guys.