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

 

[Bernie Deitrick]

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

 

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

 

[Guest]

Try this function instead. It returns a variant as I do not know what you
will be storing in the right most column. You may want to change it to
something more appropirate...

Private Function FindValue(ByVal strId As String) As Variant
Dim rngFound As Range
Dim rngReturnValue As Range

Set rngFound = Cells.Find(strId)

If rngFound Is Nothing Then
MsgBox strId & " Not found"
FindValue = Empty
Else
Set rngReturnValue = Intersect(rngFound.EntireRow,
Cells.SpecialCells(xlCellTypeLastCell).EntireColumn)
FindValue = rngReturnValue.Value
End If

End Function

 

[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

 

[Guest]

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

 

[Tom Ogilvy]

rngUsed.columns.count is sufficient if rngUsed starts with column 1. I
used a more general form that doesn't require the rngUsed to start in A1
although in this application, I assumed that it did.

I originally used it with UsedRange which doesn't always start A1 and
maintained the same approach for consistency. (Again, in this particular
application, it is probably safe to assume it starts in A1).

to illustrate from the immediate window for a range that doesn't start in
column 1

set rngUsed = Range("F10:Z40")
? rngUsed.Columns.count
21
? rngUsed.Columns(rngUsed.columns.count).column
26

 

[Guest]

Thank you very much for your explanation.