Finding Ranges

[JLong]

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

 

[Guest]

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?

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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.

 

[Tom Ogilvy]

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

 

[Jim Thomlinson]

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...

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thank you very much for your explanation.