Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Excel Loop VBA

Posted on 2007-09-28
Medium Priority
Last Modified: 2012-06-27
How would I make a loop in Excel Vba.
The data is
        Col              A           B                  C                D
 Row15             4.5       100          10000000      
 Row 16
 Row 17           5.5        100         100000000

Then to say if C16 is not null then
D16 = CommitNumber.TextBox1.Value + 1 but if
D16 is null then it would check
D17 = CommitNumber.TextBox1.Value + 1

Any help would greatly be apprecatied>

Private Sub CommandButton1_Click()

If Not IsNull(ActiveSheet.Range("C15")) Then
         ActiveSheet.Range("D15") = CommitNumber.TextBox1.Value
End If

End Sub
Question by:ca1358
  • 4
  • 2
LVL 29

Expert Comment

ID: 19981419
If Range("C16") <> Null Then
    If Range("D16") <> Null Then
        Range("D16") = CommitNumber.TextBox1.Value + 1
        Range("D17") = CommitNumber.TextBox1.Value + 1
    End If
End If
LVL 45

Expert Comment

ID: 19981424
What is CommitNumber?

Author Comment

ID: 19981545
CommitNumber is this module which is the source for the text box on the User Form.

Is there not a way to loop this?  This will have to go throught Column C about 30 rows checking to see if it is not null add 1 to CommitNumber.  The example of the commitNumber would be 5000 then the next one would be 5001 if Column C is not null.

Thank you both for looking.
Public Conn As ADODB.Connection
Public rsCommit As ADODB.Recordset
Public rs2NewLastCommitNumber As ADODB.Recordset

Public Sub OpenADOCommitNumber()
    Dim dbpath As String
    'With Password
   dbpath = "Data Source=\\Volume.mdb;Jet OLEDB:Database Password=mandatory;"

    Set Conn = New ADODB.Connection
    With Conn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open dbpath
    End With
        Set rsCommit = New ADODB.Recordset
    With rsCommit
        .Open UCase(fOSUserName) & "Commit", Conn, adOpenDynamic, adLockOptimistic, adCmdTable
        CommitNumber.TextBox1.Value = .Fields(0).Value + 1
    End With
    Set rs2NewLastCommitNumber = New ADODB.Recordset
    With rs2NewLastCommitNumber
        .Open UCase(fOSUserName) & "NewLastCommitNumber", Conn, adOpenDynamic, adLockOptimistic, adCmdTable
        CommitNumber.TextBox2.Value = .Fields(0).Value

    End With
End Sub
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 29

Expert Comment

ID: 19981588
What does the ADO code shown above have to do with the loop you are wanting? Are we to loop through the rows in the Recordset and update the spreadsheet?

I need a bit more info in order to proceed, sorry.

Author Comment

ID: 19981678
The commitNumber is only populated once.  Then I need the code to just add one to that numbe(5000)r if Column C is not null(5001), then go to the next, if Column C is not null add 1(5002) but if not skip that number and go to the next.  

     Col              A           B                  C                D
 Row15             4.5       100          10000000       5000
 Row 16
 Row 17           5.5        100         100000000     5001
 Row 18           5.5        100         100000000     5002
 Row 19
 Row 20           5.5        100         100000000     5003

LVL 29

Accepted Solution

Badotz earned 2000 total points
ID: 19981734
Perhaps this will work (untested, sorry)

Dim col As Integer
Dim row As Long

col = 3 ' Column C
row = 15 ' Row 15

While Cells(row, col) Is Not Null
    If Cells(row, col + 1) Is Not Null Then
        Cells(row, col) = CommitNumber.TextBox1.Value + 1
        Cells(row, col + 1) = CommitNumber.TextBox1.Value + 1
    End If
    row = row + 1
LVL 29

Expert Comment

ID: 19981858
No worries - glad to help.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

575 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question