Debug IF Then within For Next

[excelcraze]

Hello,

I have been trying to debug following code since yesterday, but am failed. Can somebody help me please?
I actually don't know For... Next loop. I was just trying to create my own for the project I have.
I use F8 and see what'a happening but even when the value matches it doesn't execute comments what's after 'Then'

Private Sub CommandButton1_Click()

Me.Hide
Application.ScreenUpdating = False
Sheets("Add").Select
lr = Application.WorksheetFunction.Count(Range("A:A"))
For i = 1 To lr
If Range("A" & i).Value = frmPBill.cbSelectCustID.Value Then
        
frmViewPBills.lblPBillID.Caption = frmPBill.cbSelectCustID.Value
frmViewPBills.Show
Else


End If


MsgBox "Customer (" & frmPBill.cbSelectCustID.Value & ") does not exist"
Next i
End Sub

 

[GTO]

Hi there,

I would most likely suggest running up from the bottom of the sheet to find the last cell with data, rather than COUNT(). Disregarding that, as we cannot see your workbook, I would look at this:

Range("A" & i).Value = frmPBill.cbSelectCustID.Value

If the range's value is numeric, and the values in your (presumably) combobox are as well, then try wrapping CDbl() around the combobox's .Value. If that works, it means that VBA was reading a string (it always does AFAIK) from the combobox, and failed to coerce this to a number to compare to the cell's value. See, TextBoxes, ComboBoxes and such, all store strings, so sometimes you need to coerce the control's return value. Does that help?

Mark

 

[excelcraze]

try wrapping CDbl() around the combobox's .Value.

An expert only know that.

Thank you very much indeed.

I would most likely suggest running up from the bottom of the sheet to find the last cell with data, rather than COUNT().

Can you tell me how to do that? I did this way as I don't know how to do that.

Thank you once again.

 

[dispelthemyth]

Find last row with

<Code>

LR = Range("A65536").End(xlUp).Row

</Code>

 

[hiker95]

excelcraze,


Try changing the next two code lines:

lr = Application.WorksheetFunction.Count(Range("A:A"))

For i = 1 To lr

To this:

lr = Cells(Rows.Count, "A").End(xlUp).Row

For i = lr To 1 Step -1

 

[hiker95]

dispelthemyth,

I would suggest that you use code tags.


When posting VBA code, please use Code Tags - like this:

[code]

Paste your code here.

[/code]

 

[dispelthemyth]

Ive been using the below as code tags
<code>
<noparse><code><</code>> not []


Is that not a valid tag?

I'll use [] going forward, i just thought the difference was formatting

dispelthemyth,

I would suggest that you use code tags.


When posting VBA code, please use Code Tags - like this:

[code]

Paste your code here.

[/code]

</noparse></code>

 

[hiker95]

dispelthemyth,

You could go here:
Test Here

And, start a new thread.

Then use your present style for displaying some macro code.

Then use what I have suggested for the same macro code.

Save the thread.

Then open the thread again, and, you should see the difference.

 

[excelcraze]

Thank you hiker95.

 

[excelcraze]

Please tell me how to code to deal with value of a textbox.
When I wrap textbox.value with CDbl() it did what exactly I want. But if I enter any letters into it I get error, how can I solve this issue. How to avoid this error and how to show a message box if letters are input?

Thanks.