Search Excel for a Value

[Anonymous]

I have a ton of this sort of worked out but I need to figure out how to solve this!

I want my app to have a user select a piece of text in autocad and it scane the first column in an excel file and when it finds the value of the textstring a msgbox says what the value in column 2 is...can anybody help me?


With oSheet
If LTrim(.Cells(2, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(2, 2))
End If
If LTrim(.Cells(3, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(3, 2))
End If
If LTrim(.Cells(4, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(4, 2))
End If
If LTrim(.Cells(5, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(5, 2))
End If
End With

[Anonymous]

Sub stub1()
Dim oRange As Range
For Each oRange In ActiveSheet.UsedRange
If oRange.Value = "test" And _
oRange.Column = 1 Then
MsgBox oRange.Offset(0, 1).Value
End If
Next oRange
End Sub

--
gl - Paul
wrote in message news:5053143@discussion.autodesk.com...
I have a ton of this sort of worked out but I need to figure out how to
solve this!

I want my app to have a user select a piece of text in autocad and it scane
the first column in an excel file and when it finds the value of the
textstring a msgbox says what the value in column 2 is...can anybody help
me?


With oSheet
If LTrim(.Cells(2, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(2, 2))
End If
If LTrim(.Cells(3, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(3, 2))
End If
If LTrim(.Cells(4, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(4, 2))
End If
If LTrim(.Cells(5, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(5, 2))
End If
End With

[fxcastil]

A simple for/next loop will work

dim indx as integer

with OSheet
for Indx= 1 to 10

If LTrim(.Cells(indx, 1)) = oSpaceNo.Value Then
'Row then column)
MsgBox (.Cells(indx, 2))
end if

next

end with

Fred Castillo

[Anonymous]

Both of these options still keep reading everything in each row...would a case statement work better? Like this

With oSheet
Select Case Value
Case oSpaceNo.Value = LTrim(.Cells(2, 1))
MsgBox (.Cells(2, 2))
Case oSpaceNo.Value = LTrim(.Cells(3, 1))
MsgBox (.Cells(3, 2))
End Select
End With Message was edited by: stck2mlon

[fxcastil]

Add an "exit for" to exit the loop when value is found this
will stop searching when value is found


for Indx= 1 to 10

If LTrim(.Cells(indx, 1)) = oSpaceNo.Value Then
'Row then column)
MsgBox (.Cells(indx, 2))
exit for ' the value was found exit the loop
end if

next


Fred Castillo

[Anonymous]

I am not sure how to fix this...it just returns the same value regardless of the index. Also, will the index mean I have to know how many rows are in my worksheet, because I have 106 workbooks with 2 worksheets in each one 😞

Private Sub cmdSelect_Click()

Me.Hide

'--------Get The Space Number--------
'Declare Variables
Dim returnobj As Object
Dim entbasepnt As Variant

'For Handle Storage
Dim entHandle As String
Dim entry As AcadEntity
'For Handle Storage

On Error Resume Next
ThisDrawing.Utility.GetEntity returnobj, entbasepnt, "Select Text: "

'For Handle Storage
For Each entry In returnobj
entHandle = returnobj.ObjectID
Next

'--------Start the Excel Query--------
Dim oExcel As Excel.Application
Dim oWBK As WorkBook
Dim oSheet As WorkSheet
Dim oSpaceNo
Dim indx As Integer

oSpaceNo = Trim(returnobj.TextString)

On Error Resume Next
UserForm1.Hide
Err.Clear
Set oExcel = GetObject(, "Excel.Application")

oExcel.visible = False
Set oWBK = Workbooks.Open(FileName:="C:\Data\Consulting\WHC\test.xls")
Set oSheet = oExcel.Worksheets(1)

With oSheet
For indx = 1 To 10

If LTrim(.Cells(indx, 1)) = oSpaceNo.Value Then
'Row then column)
MsgBox (.Cells(indx, 2))
Exit For ' the value was found exit the loop
End If

Next

End With

WorkBook.Close True, "C:\Data\Consulting\WHC\test.xls"

'Me.Show
End Sub

[Anonymous]

Have you considered using the find method?
Either that or use the worksheet function vlookup.

wrote in message news:5053143@discussion.autodesk.com...
I have a ton of this sort of worked out but I need to figure out how to solve this!

I want my app to have a user select a piece of text in autocad and it scane the first column in an excel file and when it finds the
value of the textstring a msgbox says what the value in column 2 is...can anybody help me?


With oSheet
If LTrim(.Cells(2, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(2, 2))
End If
If LTrim(.Cells(3, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(3, 2))
End If
If LTrim(.Cells(4, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(4, 2))
End If
If LTrim(.Cells(5, 1)) = oSpaceNo.Value Then 'Row then column)
MsgBox (.Cells(5, 2))
End If
End With

[fxcastil]

You can use the "usedrange" property of Excel
to get the row count

IntRows=oSheet.Usedrange.Rows.count

see this link for other methods and samples.
http://discussion.autodesk.com/thread.jspa?messageID=5043090



I am not sure what you mean by
" it just returns the same value regardless of the index"
but the routine look fine to me.
Test the routine like below and see what happens

With oSheet
For indx = 1 To IntRows

Msgbox LTrim(.Cells(indx, 1))
Next


Fred Castillo








http://discussion.autodesk.com/thread.jspa?messageID=5043090

[Anonymous]

I am not sure how the find method applies here. The user has selected a piece if text...open excel and find the value in a specific row and return all of the values for that recordset.

[Anonymous]

These kinds of conversations work better if you don't wait 4 days to reply.

Ranges have find methods. The find method can return the first cell that
matches your search criteria. If you want the address,then access the
address property of the cell returned. If you want the row, then access
the row property of the cell. Use the offset methods to get adjacent cells
if you want. It seems like you are trying to write code to essentially
replace what is done automatically for you via the find method.

Perhaps I'm wrong.








wrote in message news:5057216@discussion.autodesk.com...
I am not sure how the find method applies here. The user has selected a piece if text...open excel and find the value in a specific
row and return all of the values for that recordset.