8 Replies - 6359 Views - Last Post: 01 August 2011 - 09:48 AM Rate Topic: -----

#1 ana004  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 24-July 11

do while and for loop together in vb6?

Posted 24 July 2011 - 08:40 PM

helo,
is it possible to combine for loop and do while loop together? if yes, can you advice me how? in FOR LOOP, if i will be dealing with lots of data it will take time for the FOR LOOP to get what i need, so is it possible to have for loop and do while together in vb6? thanks a lot :)

  For row1 = 2 To lastrow1
  xlsheet1.Cells(row1, 2).Select

        For row2 = 1 To lastrow2
               xlsheet2.Cells(row2, 3).Select

                If xlsheet1.Cells(row1, 2).Value = xlsheet2.Cells(row2, 3).Value Then
                              
                    Select Case xlsheet2.Cells(row2, 2).Value
                    
                           Case 1
                                Cells(row1, 4).Value = "Accepted"
                           Case 2
                                Cells(row1, 4).Value = "Processed"
                           Case 3
                                Cells(row1, 4).Value = "Collected / Delivered"
                           Case 4
                                Cells(row1, 4).Value = "Mandatory Record Error"
                           Case 5
                                Cells(row1, 4).Value = "Mandatory Field Error"
                           Case 6
                                Cells(row1, 4).Value = "PO Number Missing"
                            Case 7
                                Cells(row1, 4).Value = "Invalid Buyer ID"
                            Case 8
                                Cells(row1, 4).Value = "Not on Buyer Positive List"
                            Case 9
                                Cells(row1, 4).Value = "Present on Buyer Negative List"
                            Case 10
                                Cells(row1, 4).Value = "Duplicate Invoice Number"
                            Case 11
                                Cells(row1, 4).Value = "Invalid Invoice Number"
                            Case 12
                                Cells(row1, 4).Value = "Client Profile Error"
                            Case 13
                                Cells(row1, 4).Value = "Invalid PO Number"
                            Case 14
                                Cells(row1, 4).Value = "Unspecified Error"
                            Case 15
                                Cells(row1, 4).Value = "Invoice Ignored"
                            Case 16
                                Cells(row1, 4).Value = "Missing Augmentation Rule"
                            Case 17
                                Cells(row1, 4).Value = "Value Cross-Check Error"
                            Case 570
                                Cells(row1, 4).Value = "Invalid PO Number at Header/Line Item"
                    
                    End Select
                End If
            Next row2
  Next row1


Is This A Good Question/Topic? 0
  • +

Replies To: do while and for loop together in vb6?

#2 chuckjessup  Icon User is offline

  • D.I.C Regular

Reputation: 33
  • View blog
  • Posts: 380
  • Joined: 26-October 09

Re: do while and for loop together in vb6?

Posted 26 July 2011 - 09:52 AM

View Postana004, on 24 July 2011 - 08:40 PM, said:

helo,
is it possible to combine for loop and do while loop together? if yes, can you advice me how? in FOR LOOP, if i will be dealing with lots of data it will take time for the FOR LOOP to get what i need, so is it possible to have for loop and do while together in vb6? thanks a lot :)


As far as i know, this is VBA, so Yes in VB6 it will allow you to do for...next loops with in a do...loop loop. The problem i have found in do that is that each time the do...loop is processed, it has to go through each for...next condition before it can continue... so processing huge data sheets, may take a longer time to process... and as a side note, de-bugging a do...loop with one or more for...next loops is a bear to sit through it specially if the issue happens at the end of one of the for... next or do...loop.

So in conclusion: Yes, it is possible... But as for recommended that is determined by what is trying to be done.

Hope this helps.
Jesse Fender
Was This Post Helpful? 0
  • +
  • -

#3 ana004  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 24-July 11

Re: do while and for loop together in vb6?

Posted 28 July 2011 - 03:01 AM

hi jesse, i managed to combine for and do loop together, thanks for your assistance, but my problem now is inserting if-else statement inside the loop, if a certain data in xlsheet1 cannot see in xlsheet2, it should proceed to the next row1, i should use if else statement ryt? i do it like this

if xlsheet1.cells(row1,2).value<>xlsheet2.cells(row2,3).value then
next row1



kindly advice where to put these code or is there a better way to do it? thanks.
Was This Post Helpful? 0
  • +
  • -

#4 chuckjessup  Icon User is offline

  • D.I.C Regular

Reputation: 33
  • View blog
  • Posts: 380
  • Joined: 26-October 09

Re: do while and for loop together in vb6?

Posted 28 July 2011 - 06:19 AM

View Postana004, on 28 July 2011 - 03:01 AM, said:

hi jesse, i managed to combine for and do loop together, thanks for your assistance, but my problem now is inserting if-else statement inside the loop, if a certain data in xlsheet1 cannot see in xlsheet2, it should proceed to the next row1, i should use if else statement ryt? i do it like this

if xlsheet1.cells(row1,2).value<>xlsheet2.cells(row2,3).value then
next row1



kindly advice where to put these code or is there a better way to do it? thanks.



I need a little more code, it was in the for...next loop, but i dont know, please post more code...

As a general rule if...then...else statements work with in loops... i have used them myself many times.

Jesse Fender
Was This Post Helpful? 0
  • +
  • -

#5 ana004  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 24-July 11

Re: do while and for loop together in vb6?

Posted 28 July 2011 - 05:23 PM

hi jesse,
here is the complete process:

   lastrow1 = xlsheet1.UsedRange.Rows(xlsheet1.UsedRange.Rows.Count).Row
   lastrow2 = xlsheet2.UsedRange.Rows(xlsheet2.UsedRange.Rows.Count).Row
   

 Do While found = False
 For row1 = 2 To lastrow1
    
  xlsheet1.Cells(row1, 2).Select
  
    Do
        For row2 = 1 To lastrow2
        
               xlsheet2.Cells(row2, 3).Select
                
          
                
                If xlsheet1.Cells(row1, 2).Value = xlsheet2.Cells(row2, 3).Value Then
  
                        found = True
                        
                    Select Case xlsheet2.Cells(row2, 2).Value

                           Case 1
                                Cells(row1, 4).Value = "Accepted"
                           Case 2
                                Cells(row1, 4).Value = "Processed"
                           Case 3
                                Cells(row1, 4).Value = "Collected / Delivered"
                                Cells(row1, 4).Interior.Color = RGB(0, 255, 0)
                           Case 4
                                Cells(row1, 4).Value = "Mandatory Record Error"
                           Case 5
                                Cells(row1, 4).Value = "Mandatory Field Error"
                           Case 6
                                Cells(row1, 4).Value = "PO Number Missing"
                            Case 7
                                Cells(row1, 4).Value = "Invalid Buyer ID"
                            Case 8
                                Cells(row1, 4).Value = "Not on Buyer Positive List"
                            Case 9
                                Cells(row1, 4).Value = "Present on Buyer Negative List"
                            Case 10
                                Cells(row1, 4).Value = "Duplicate Invoice Number"
                            Case 11
                                Cells(row1, 4).Value = "Invalid Invoice Number"
                            Case 12
                                Cells(row1, 4).Value = "Client Profile Error"
                            Case 13
                                Cells(row1, 4).Value = "Invalid PO Number"
                            Case 14
                                Cells(row1, 4).Value = "Unspecified Error"
                            Case 15
                                Cells(row1, 4).Value = "Invoice Ignored"
                            Case 16
                                Cells(row1, 4).Value = "Missing Augmentation Rule"
                            Case 17
                                Cells(row1, 4).Value = "Value Cross-Check Error"
                            Case 570
                                Cells(row1, 4).Value = "Invalid PO Number at Header/Line Item"
                         
                    End Select
                
    Exit Do
           
              
              End If
              
                             
        Next row2
        
            Loop Until found = False
            
   Next row1
   
Loop


Was This Post Helpful? 0
  • +
  • -

#6 ana004  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 24-July 11

Re: do while and for loop together in vb6?

Posted 28 July 2011 - 06:53 PM

for clarification jesse, i will insert if-else statement inside the loop so, every time the value in xlsheet1 cannot found in xlsheet2 it will proceed to the next value (xlsheet1) to process and it will display "not in the report" as the status. if my explanation is not clear, please let me know, more thanks.
Was This Post Helpful? 0
  • +
  • -

#7 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: do while and for loop together in vb6?

Posted 29 July 2011 - 05:08 PM

You can nest any combination of do and for loops. The difference is quite simple: in a for loop, you know exactly how many iterations you are going to do going in, and in a do loop, you don't. Jesse makes a good point, in that loops imply overhead. So, you need to work to minimize the number of iterations that you are doing.

I will also amplify Jesse's statement: If then else statements do work (not just as a general rule) inside either do or for loops (of course, so long as they aren't buggy).
Was This Post Helpful? 0
  • +
  • -

#8 chuckjessup  Icon User is offline

  • D.I.C Regular

Reputation: 33
  • View blog
  • Posts: 380
  • Joined: 26-October 09

Re: do while and for loop together in vb6?

Posted 30 July 2011 - 08:13 PM

View PostBobRodes, on 29 July 2011 - 05:08 PM, said:

You can nest any combination of do and for loops. The difference is quite simple: in a for loop, you know exactly how many iterations you are going to do going in, and in a do loop, you don't. Jesse makes a good point, in that loops imply overhead. So, you need to work to minimize the number of iterations that you are doing.

I will also amplify Jesse's statement: If then else statements do work (not just as a general rule) inside either do or for loops (of course, so long as they aren't buggy).


Lol i just remeber having to sit through about 100 for...next lops before the code would progress to the next...
As far as the statement about the general rule i was making it as i havent dealt with the VBA and Excel... so i wasnt so sure that they would allow that... but i am glad that it can be done..

:)

Jesse Fender
Was This Post Helpful? 0
  • +
  • -

#9 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: do while and for loop together in vb6?

Posted 01 August 2011 - 09:48 AM

VBA is exactly the same as VB where for and do loops are concerned. :) VBA really only differs with respect to what objects are available automatically at runtime. If you want to dig into this, have a look at the Object Browser in VB6 and in Excel.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1