Hi, I have a problem with a Do Until loop, it doesn't stop unless I
Ctrl Break out of it, here's the loop:

Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")
i = 4
Do Until i = 131
If Range("A" & i).Value = productRow Then
foundRowNum = i
MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation
Exit Do
End If
Loop

Am I missing something here or is it ok? I want the user to be able
to input the product number and the msgbox to display the product
number and it's corresponding row...is my syntax off somewhere, are my
dimensions wrong? I'm clueless...please advise...thanks...

-Lee

Above the line:

productRow = InputBox(...)

I have:

discountRow = Range("D4")
unitCostRow = Range("B4")

How can I incorporate the whole column of data that I have in the
Range D4:D131 and B4:B131? There are different percentages in the
range D4:D131 and different product costs in range B4:B131 but I only
have the ranges as seen here thinking that excel will realize that it
isn't a static $b$4, catch my drift? So I need VBA to read the whole
column of data and not just that first cell, or B4 and D4...thanks.



On May 19, 2:12 am, " wrote:
Hi, I have a problem with a Do Until loop, it doesn't stop unless I
Ctrl Break out of it, here's the loop:

Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")
i = 4
Do Until i = 131
If Range("A" & i).Value = productRow Then
foundRowNum = i
MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation
Exit Do
End If
Loop

Am I missing something here or is it ok? I want the user to be able
to input the product number and the msgbox to display the product
number and it's corresponding row...is my syntax off somewhere, are my
dimensions wrong? I'm clueless...please advise...thanks...

-Lee

In the do loop the value of i never increases from 4 so if prodrow isn't true
in A4 then the loop will be endless. Try this instead:-

Sub NNN()

Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")

For i = 4 To 131

If Range("A" & i).Value = productRow Then
foundRowNum = i

MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation

End If

Next


End Sub

" wrote:

Hi, I have a problem with a Do Until loop, it doesn't stop unless I
Ctrl Break out of it, here's the loop:

Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")
i = 4
Do Until i = 131
If Range("A" & i).Value = productRow Then
foundRowNum = i
MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation
Exit Do
End If
Loop

Am I missing something here or is it ok? I want the user to be able
to input the product number and the msgbox to display the product
number and it's corresponding row...is my syntax off somewhere, are my
dimensions wrong? I'm clueless...please advise...thanks...

-Lee

For your loop problem, you can either do as Mike H has suggested, or take the
loop you have now and add this line between the End If and Loop statements
near the end:
i = i + 1
that will increment i and let you out of the loop eventually.

What you will also probably want to do is to check for after you exit either
loop is whether or not you actually found a match (perhaps they mis-typed the
product code). Mike H's For...Next loop is actually better for that because
if no match is found, i will be 132 when it falls out of the loop, where as
with the Do Until loop, it will be 131 and you might have actually found the
match as the last entry at row 131.

----
As for your discountRow and unitCostRow values, I'm not certain what you're
trying to place into those variables, if it is the row number, then you
missed it - you're actually picking up the value in those two cells and
placing that into the variables.

I presume that you want to pick up the values. Remember that in your loop
above, when you get a match on the product number, you have the row number of
that match in i. Now if you want the actual discount percentage and unit
cost, you can do this with your code:

Dim discount as Single ' so we can handle floating point
Dim unitCost as Currency ' I presume it's monetary amount
Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")
i = 4
discount = 0 ' initialize/reset
unitCost = 0 ' initialize/reset
Do Until i = 131
If Range("A" & i).Value = productRow Then
foundRowNum = i
MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation
unitCost = Range("B" & i).Value
discount = Range("D" & i).Value
Exit Do
End If
i = i + 1
Loop
If unitCost = 0 Then
'did not find a matching product number
Else
MsgBox "Unit Cost is: " & Format(unitCost, "Currency") _
& Discount Percent is: " & Format(discount, "Percent")
End If


" wrote:

Hi, I have a problem with a Do Until loop, it doesn't stop unless I
Ctrl Break out of it, here's the loop:

Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")
i = 4
Do Until i = 131
If Range("A" & i).Value = productRow Then
foundRowNum = i
MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation
Exit Do
End If
Loop

Am I missing something here or is it ok? I want the user to be able
to input the product number and the msgbox to display the product
number and it's corresponding row...is my syntax off somewhere, are my
dimensions wrong? I'm clueless...please advise...thanks...

-Lee

Ok, I'm going to try some of the suggestions, thanks guys, I will get
back to you later, I actually have to work now and don't have the time
to implement this in the program just yet...though I have tried and
I'm already running late...ah, they love it! Just kidding...

On May 19, 5:15 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
For your loop problem, you can either do as Mike H has suggested, or take the
loop you have now and add this line between the End If and Loop statements
near the end:
i = i + 1
that will increment i and let you out of the loop eventually.

What you will also probably want to do is to check for after you exit either
loop is whether or not you actually found a match (perhaps they mis-typed the
product code). Mike H's For...Next loop is actually better for that because
if no match is found, i will be 132 when it falls out of the loop, where as
with the Do Until loop, it will be 131 and you might have actually found the
match as the last entry at row 131.

----
As for your discountRow and unitCostRow values, I'm not certain what you're
trying to place into those variables, if it is the row number, then you
missed it - you're actually picking up the value in those two cells and
placing that into the variables.

I presume that you want to pick up the values. Remember that in your loop
above, when you get a match on the product number, you have the row number of
that match in i. Now if you want the actual discount percentage and unit
cost, you can do this with your code:

Dim discount as Single ' so we can handle floating point
Dim unitCost as Currency ' I presume it's monetary amount
Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")
i = 4
discount = 0 ' initialize/reset
unitCost = 0 ' initialize/reset
Do Until i = 131
If Range("A" & i).Value = productRow Then
foundRowNum = i
MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation
unitCost = Range("B" & i).Value
discount = Range("D" & i).Value
Exit Do
End If
i = i + 1
Loop
If unitCost = 0 Then
'did not find a matching product number
Else
MsgBox "Unit Cost is: " & Format(unitCost, "Currency") _
& Discount Percent is: " & Format(discount, "Percent")
End If

" wrote:
Hi, I have a problem with a Do Until loop, it doesn't stop unless I
Ctrl Break out of it, here's the loop:

Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")
i = 4
Do Until i = 131
If Range("A" & i).Value = productRow Then
foundRowNum = i
MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation
Exit Do
End If
Loop

Am I missing something here or is it ok? I want the user to be able
to input the product number and the msgbox to display the product
number and it's corresponding row...is my syntax off somewhere, are my
dimensions wrong? I'm clueless...please advise...thanks...

-Lee

Hey thanks, it worked out great...that was exactly what I was looking
for my man...I feel so much better now that I don't have to pull my
hair out anymore!!!

On May 19, 5:15 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
For your loop problem, you can either do as Mike H has suggested, or take the
loop you have now and add this line between the End If and Loop statements
near the end:
i = i + 1
that will increment i and let you out of the loop eventually.

What you will also probably want to do is to check for after you exit either
loop is whether or not you actually found a match (perhaps they mis-typed the
product code). Mike H's For...Next loop is actually better for that because
if no match is found, i will be 132 when it falls out of the loop, where as
with the Do Until loop, it will be 131 and you might have actually found the
match as the last entry at row 131.

----
As for your discountRow and unitCostRow values, I'm not certain what you're
trying to place into those variables, if it is the row number, then you
missed it - you're actually picking up the value in those two cells and
placing that into the variables.

I presume that you want to pick up the values. Remember that in your loop
above, when you get a match on the product number, you have the row number of
that match in i. Now if you want the actual discount percentage and unit
cost, you can do this with your code:

Dim discount as Single ' so we can handle floating point
Dim unitCost as Currency ' I presume it's monetary amount
Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")
i = 4
discount = 0 ' initialize/reset
unitCost = 0 ' initialize/reset
Do Until i = 131
If Range("A" & i).Value = productRow Then
foundRowNum = i
MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation
unitCost = Range("B" & i).Value
discount = Range("D" & i).Value
Exit Do
End If
i = i + 1
Loop
If unitCost = 0 Then
'did not find a matching product number
Else
MsgBox "Unit Cost is: " & Format(unitCost, "Currency") _
& Discount Percent is: " & Format(discount, "Percent")
End If

" wrote:
Hi, I have a problem with a Do Until loop, it doesn't stop unless I
Ctrl Break out of it, here's the loop:

Dim productRow As String
Dim foundRowNum As Integer
Dim i As Integer

productRow = InputBox("Enter the product code")
i = 4
Do Until i = 131
If Range("A" & i).Value = productRow Then
foundRowNum = i
MsgBox "The row for product " & productRow & " is " &
foundRowNum & ".", _
vbInformation
Exit Do
End If
Loop

Am I missing something here or is it ok? I want the user to be able
to input the product number and the msgbox to display the product
number and it's corresponding row...is my syntax off somewhere, are my
dimensions wrong? I'm clueless...please advise...thanks...

-Lee