Do Until loop keeps looping

[chris316]

Hey guys, new to the forum. I'm having troubles with a script I have. What I'm trying to do is search an excel sheet for a particular string in the first column. I want to find the row number and display the corresponding column info. Below is the test one I've been using to figure out my issue. It seems that the loop doesn't like my strWord variable for some reason, although putting "9873" directly into the loop seems to work fine. Is there something I have to do for my defined variable to work? Thanks.



Dim intRow, strWord, strExcel, strPositionID, strLocation, strIncomingCalls, strOutgoingCalls

strWord = "9873"

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("C:\Query\test.xls")


intRow = 1


Do Until objExcel.Cells(intRow,1).Value = strWord
intRow = intRow + 1
Loop


strPositionID = objExcel.Cells(intRow, 2).Value
strLocation = objExcel.Cells(intRow, 3).Value
strIncomingCalls = objExcel.Cells(intRow, 2).Value
strOutgoingCalls = objExcel.Cells(intRow, 3).Value


objExcel.Workbooks(1).Close
objExcel.Quit


Msg = msgBox ("User ID: " & strUserName & vbNewLine & "Computer Name: " & strCharacters & vbNewLine & "Position ID:" & strPositionID & vbNewLine & "Location:" & strLocation & vbNewLine & "Incoming Calls:" & strIncomingCalls & vbNewLine & "Outgoing Calls:" & strOutgoingCalls)

 

[SkipVought]

hi,

The value in the cell, 9872 is a NUMBER not a STRING.

Test with a numeric variable, not a string variable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[chris316]

Hey, figured out the issue. Thanks Skip for the help...in case someone wanted to know. If fixed the issue by using Option Explicit at the top of my script...this completely slipped my mind when creating it. Here is my modified code.


Option Explicit

Dim intRow, strWord, strExcel, strPositionID, strLocation, strIncomingCalls, strOutgoingCalls
Dim objExcel, objWorkbook, Msg

strWord=9873

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("C:\Query\test.xls")


intRow = 1


Do Until objExcel.Cells(intRow,1).Value = strWord
intRow = intRow + 1
Loop


strPositionID = objExcel.Cells(intRow, 2).Value
strLocation = objExcel.Cells(intRow, 3).Value
strIncomingCalls = objExcel.Cells(intRow, 2).Value
strOutgoingCalls = objExcel.Cells(intRow, 3).Value


objExcel.Workbooks(1).Close
objExcel.Quit



Keep in mind that the number at the top does not include quotes as it is not a string. Thanks again Skip for pointing me in the right direction.

 

[SkipVought]

Your str prefixed variables ought to be DEFINED as STRING variables, or prefix not used at all.

It is misleading, IMHO!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cmartin75]

Try to use the msgbox function to debug the script:

Option Explicit

Dim intRow, strWord, strExcel, strPositionID, strLocation, strIncomingCalls, strOutgoingCalls
Dim objExcel, objWorkbook, Msg

strWord=9873

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open ("C:\Query\test.xls")

intRow = n ' the row with the value of 9873 you're searching

msgbox objWorkBook.cells(intRow, 1)

What's the pop-up message?

 

[chris316]

Hey Skip thanks for the input...I was just testing a bunch of things out and didn't bother renaming anything. Glad you reminded me though. Thanks. I did run into a slight problem I can't seem to figure out. When doing my loop through the excel sheet I would like it to ignore case. As it is right now if I try looping with a string of lowercase letters and the values within the excel sheet are uppercase I get an error indicating the value could not be found. I tried:

Const IgnoreCase = TRUE

at the top of my script but case still has an effect. Any help would be awesome. Thanks.

 

[PHV]

What about replacing this:
Do Until objExcel.Cells(intRow,1).Value = strWord
with this ?
Do Until UCase(objExcel.Cells(intRow,1).Value) = UCase(strWord)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chris316]

Thank you PHV, I didn't know you could encapsulate different commands like that. Thanks for all the help.