11 Replies - 592 Views - Last Post: 28 June 2012 - 05:54 AM Rate Topic: -----

#1 gobyg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-June 12

How to halt a loop code

Posted 20 June 2012 - 07:42 AM

Hello,
I have a VB code that searches the excel record. I want the code to halt when it finds a result, and to proceed once I hit "Enter"
Please help.
THank you.
Is This A Good Question/Topic? 0
  • +

Replies To: How to halt a loop code

#2 tlhIn`toq  Icon User is online

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,357
  • Joined: 02-June 10

Re: How to halt a loop code

Posted 20 June 2012 - 08:02 AM

VB6 is pretty much dead for new projects and used mostly for legacy stuff until it is replaced. Did you mean for this to be in VB6 or VB.NET?

Show us your code and maybe we can help. Otherwise we have nothing to help with.
Was This Post Helpful? 0
  • +
  • -

#3 gobyg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-June 12

Re: How to halt a loop code

Posted 20 June 2012 - 08:36 AM

Sorry, I meant VB.NET
Below is my code
Sheet1 has many names, more or less the same, but when it finds the 1st one, it stops and not doing any more search.
I want the code to halt when it finds a matching name and when I hit "Enter" it should proceed searching further along the rows.Everytime, when it finds a result, I should enter, till it reaches the bottom of the row.

Private Sub cmdok_Click()
Dim uRange As Range
Dim FindTitle As Range
Dim tRow As Long
Dim tCol As Integer

Set uRange = ActiveSheet.UsedRange
Set FindTitle = uRange.Find(Me.txtname.Value)

    If FindTitle Is Nothing Then
        MsgBox ("Sorry! No keyword title is found")
    Else
        tRow = FindTitle.Row
        tCol = FindTitle.Column
      
        lblname2.Caption = Cells(tRow, tCol).Value
        tCol = tCol + 1
        Lblamount2.Caption = Cells(tRow, tCol).Value
                
    End If
    
    
    
End Sub

Was This Post Helpful? 0
  • +
  • -

#4 Neku  Icon User is offline

  • D.I.C Regular

Reputation: 20
  • View blog
  • Posts: 268
  • Joined: 21-May 09

Re: How to halt a loop code

Posted 20 June 2012 - 10:29 AM

thats actually VB6 code

to exit a loop you can put a condition that once become true will exit the loop.
for continuing it when you press Enter you'll need a control (usually textbox) that have events like Key_Down and put a condition to recognize what key you pressed (work only if the control is focused) and if the Ascii code is from the button Enter then it will re enter the loop.
Was This Post Helpful? 0
  • +
  • -

#5 tlhIn`toq  Icon User is online

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,357
  • Joined: 02-June 10

Re: How to halt a loop code

Posted 20 June 2012 - 10:41 AM

To be clear you (the OP) don't really want to exit the loop - What you've described is PAUSING execution within the loop then continuing after the user hits the [ENTER] key.
Was This Post Helpful? 0
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: How to halt a loop code

Posted 20 June 2012 - 01:42 PM

A simple way to do this is to use the MsgBox function. Put relevant result data in the MsgBox, and then when the user hits Ok, the loop will continue.
Was This Post Helpful? 0
  • +
  • -

#7 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 179
  • Posts: 1,599
  • Joined: 17-April 07

Re: How to halt a loop code

Posted 21 June 2012 - 07:58 AM

i think actually the loop is not needed there just a range variable with find method is enough hoe it will solve this problem
Was This Post Helpful? 0
  • +
  • -

#8 tlhIn`toq  Icon User is online

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5316
  • View blog
  • Posts: 11,357
  • Joined: 02-June 10

Re: How to halt a loop code

Posted 21 June 2012 - 08:03 AM

View Postthava, on 21 June 2012 - 08:58 AM, said:

i think actually the loop is not needed there just a range variable with find method is enough hoe it will solve this problem


Ok... You have my interest. I love seeing new lines of logic to accomplish a goal. How would you do this without a loop and still maintain the OP's need to have a dialog pop up and be dismissed for each matching item found?
Was This Post Helpful? 0
  • +
  • -

#9 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: How to halt a loop code

Posted 21 June 2012 - 03:33 PM

<Ok... You have my interest.

Mine too. :)
Was This Post Helpful? 0
  • +
  • -

#10 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 179
  • Posts: 1,599
  • Joined: 17-April 07

Re: How to halt a loop code

Posted 22 June 2012 - 12:22 AM

i think Actually this is what he try to do
Dim uRange As Range
Dim Sh As Worksheet
Dim cCell As Object
Private Sub cmdok_Click()
    Dim FindTitle As Range
    Dim tRow As Long
    Dim tCol As Integer
    Set FindTitle = Sh.Cells(1, 1)
    If cCell Is Nothing Then
        Set FindTitle = uRange.Find(Me.TxtName.Text)
    Else
        Set FindTitle = uRange.Find(Me.TxtName.Text, cCell)
    End If
    If FindTitle Is Nothing Then
        MsgBox ("Sorry! No keyword title is found")
    Else
        tRow = FindTitle.Row
        tCol = FindTitle.Column + 1
        Set cCell = FindTitle.Cells(1, 1)
        lblname2.Caption = Cells(tRow, tCol).Value
    End If
End Sub


Private Sub UserForm_Initialize()
Set Sh = ActiveSheet
Set uRange = Sh.UsedRange
End Sub


since it's a Form its not closed until we close it
when he hit enter every time it search for the data in the remaining cells in that sheet

This post has been edited by thava: 22 June 2012 - 12:38 AM

Was This Post Helpful? 0
  • +
  • -

#11 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,979
  • Joined: 19-May 09

Re: How to halt a loop code

Posted 22 June 2012 - 12:48 PM

I have to say that thava's interpretation of the requirements is as good as any. :)
Was This Post Helpful? 0
  • +
  • -

#12 gobyg  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 19-June 12

Re: How to halt a loop code

Posted 28 June 2012 - 05:54 AM

Below is the code that I ended up using:

Public Sub OK3_Click()

MySearch = Array(Me.txtname.Value)
With Sheets("Sheet1").Range("A1:A300")

For i = LBound(MySearch) To UBound(MySearch)

'If you want to find a part of the rng.value then use xlPart
'if you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to MySearch(I)
Set rng = .Find(What:=MySearch(i), _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
FirstAddress = rng.address
Do
frm1st.Visible = True
lblname2.Caption = rng.Value
Lblamount2.Caption = rng.Offset(0, 1)
lblceleb2.Caption = rng.Offset(0, 3)
frm1st.Caption = "Given to " & rng.Offset(o, 5)
lblgivenvalue.Caption = rng.Offset(o, 4)
If rng.Offset(0, 7) <> "" Then
response = MsgBox("Do you want to update this relative", vbYesNo, "Update Need")
If response = 6 Then
formupdate.Show
End If
Else

End If

Set rng = .FindNext(rng)

Loop While Not rng Is Nothing And rng.address <> FirstAddress
MsgBox ("No More Matching Key word found")
End If
Next i
End With
End Sub


THank you for you help guys
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1