Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    While not... (6.0)

    I use to "scanning" all value in excel column until last cell is blank the instrauction :
    While not my_range =""
    my code
    wend

    how to scanning filed until last value in filed is balnk in vba for Excel?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: While not... (6.0)

    I assume you mean 'field' instead of 'filed'. What kind of field? Where is this?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: While not... (6.0)

    sorry field...
    In access table...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: While not... (6.0)

    I already asked you to post Access questions in the Access forum. I'll move this thread from VBA to Access.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: While not... (6.0)

    An Excel 97-2003 worksheet always has 65536 rows, whether they are filled or not.
    An Access table only has as many records as are needed, so in general, you do not loop until a field is blank, but until the last record.
    Say that rst is a variable of type Recordset (ADO or DAO). You'd use a loop like this:

    Do While Not rst.EOF
    ' your code to process the record here
    ...
    rst.MoveNExt
    Loop

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: While not... (6.0)

    tks for suggestion...

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: While not... (6.0)

    Now...

    Is possible to skip from cicling/loop the dupes... admit have records 000001,000001...ecc.
    use only one...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: While not... (6.0)

    Open a recordset on an SQL statement that groups by the field.

    Dim strSQL As String
    strSQL = "SELECT FieldName FROM TableName GROUP BY FieldName"
    rst.Open strSQL, ...

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: While not... (6.0)

    but not undesrtand how to merge this piece of code with the new...

    Do While Not rst.EOF
    ' your code to process the record here
    ...
    rst.MoveNExt
    Loop

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: While not... (6.0)

    First open the recordset (rst.Open ...), then loop through the records (Do While ... Loop).

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: While not... (6.0)

    Good morning Hans...
    Yes i know and work fine this first part of you code....
    But my qstion is to integrate the last part of your code refered: ...strSql, ecc...

    this:
    Do While Not rst.EOF
    ' your code to process the record here
    ...
    rst.MoveNExt
    Loop

    and this:
    Dim strSQL As String
    strSQL = "SELECT FieldName FROM TableName GROUP BY FieldName"
    rst.Open strSQL, ...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: While not... (6.0)

    See my previous reply. The part where you open the recordset comes first. The part where you loop through the records goes below that.

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: While not... (6.0)

    aaaaaaaaaaah...
    Tks.

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: While not... (6.0)

    Hans sorry me but difficult...
    In the tabel COPE are many dupes and i want to loop only distint record in COPE table...

    Option Explicit
    Global CN1 As ADODB.Connection, RS1 As ADODB.Recordset
    Global Const gPROVADatabasePath2 = "Data Source=GCD01F4500DATIPUBBLICAAPPLICAZIONIDB2.MDB;"
    Sub TEST_RECORD()
    Dim RIGA As String
    Dim strSQL As String
    RIGA = 0
    Set CN1 = New ADODB.Connection
    CN1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
    Set RS1 = New ADODB.Recordset
    RS1.Open "COPE", CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

    strSQL = "SELECT COPE FROM COPE GROUP BY COPE"

    Do While Not RS1.EOF
    RIGA = RIGA + 1
    RS1.MoveNExt
    Loop

    End Sub

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: While not... (6.0)

    What is the problem?

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •