Excel 2007 VBA tutorial-loop to update data


VBA loop: update data

In this example, We use VBA do while loop statement to move to the row in which data are to be updated. To update data for the user, the program prompts the user to enter the ID of book that its Title will be updated. If the ID is matched with the one in the list, the update process is occurred.

To run this example VBA code, you will need a Form and two Labels, two TextBox, and one CommandButton. You may copy and paste the sample data in to your current worksheet.

BookID Title
B001 VBA Programming for Excel 2007
B002 C++ Programming for Beginners
B003 Learn Java in 60 hours
B004 C# Programming for Java Developer
B005 C++ Graphics and Game Programming


VBA for Excel: VBA code example of using VBA do while loop to move to update point in the Excel sheet

VBA code for this example:


Private Sub CommandButton1_Click()
    If TextBox1.Text <> "" Then
        updateBook TextBox1.Text, TextBox2.Text
    Else: MsgBox "Enter Book ID"
    End If
End Sub
 
Sub updateBook(ID As String, Title As String)
    Dim row As Integer
    row = 1
    Do While Cells(row, 1) <> ""
        If StrComp(ID, Cells(row, 1)) = 0 Then
            'update data
            Cells(row, 2) = Title
        End If
        row = row + 1 'move to the next row
    Loop
   
End Sub


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.