Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Update data - Instead of update with new data, new line created

0

When a record is found and amendment done, instead of update with new data a new line of data is created.

Can't find any error in the following macro

Password is 'skb5153'

Sub Store_update_Data()

' OriginalClubAccount

' Takes data from one worksheet and stores in the next empty row on another worksheet.

'or, if the record exists already, it will be updated

Dim sourceSheet As Worksheet

Dim dataSheet As Worksheet

Dim searchValue As Variant

Dim dataVoucherNo As Range

Dim recordRow As Integer

Dim Find As Range

Dim C As Long, i As Long

'Make some sheet variables so we can use those instead of hard-coding sheet references in the code

Set sourceSheet = Sheets("Form")

Set dataSheet = Sheets("TB")

'Column that contains the value for which we will search.

Set dataVoucherNo = dataSheet.Range("E7:E65000")

'Value to search for.

searchValue = sourceSheet.Range("F13").Value

'Check if the user input a value and hit the OK button.

If searchValue <> vbNullString Then

    'Value input, so search for it.

    'Search

    'More specific info:https://docs.microsoft.com/en-us/office/vba/api/excel.range.find

    Set Fnd = dataVoucherNo.Find(what:=searchValue, _

                                     LookIn:=xlValues, _

                                     LookAt:=xlWhole, _

                                     SearchOrder:=xlByRows, _

                                     MatchCase:=False)

    'Check if a record was found.

        If Not Fnd Is Nothing Then

            'Record Found!

            'Get the row of the record.

            recordRow = Fnd.Row

           recordAddress = Fnd.Address

            'Put the records information back into the form.

            For C = 5 To 19 Step 2

            i = i + 1

            sourceSheet.Range(Format(C, "F" & 0)).Value = dataSheet.Cells(recordRow, i).Value

            Next C

        Else

        'Input new record!

        'Get the next empty row from the Data Sheet

        nextRow = dataSheet.Range("E" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

        End If

        'Input the form values into the Data Sheet.

        dataSheet.Cells(nextRow, 1).Value = sourceSheet.Range("F5").Value

        dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("F7").Value

        dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F9").Value

        dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("F11").Value

        dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F13").Value

        dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("F15").Value

        dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F17").Value

        dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("F19").Value

        'Clear Data

        sourceSheet.Range("F5").Value = ""

        sourceSheet.Range("F7").Value = ""

        sourceSheet.Range("F9").Value = ""

        sourceSheet.Range("F11").Value = ""

        sourceSheet.Range("F13").Value = ""

        sourceSheet.Range("F15").Value = ""

        sourceSheet.Range("F17").Value = ""

        sourceSheet.Range("F19").Value = ""

        End If

End Sub

Answer
Discuss

Answers

0
Selected Answer

I find your code hard to read and difficult to copy. Please use code tags in future.

When your code requires the same action repetitively the best solution is to create sub routines or function which you can call at different points in your code. I have taken this one step further by giving the routines multiple functionalities which is determined by the arguments with which they are called. All code is in module TeachExcel.

You can now click Find Record to call up an existing record, make changes to it and press Submit to either update an existing record or append a new one.

Note that your circular references are on the TB Summary tab, rows 103 and 110, perhaps more. When you open that tab you will see the first cell with a circular reference specified in the Status Bar at the bottom left of your screen.

Discuss
0

Hi there, next time, please surround the macro code with CODE tags, which you can do by selecting your code and clicking the CODE button in the editor.

As for the macro in question, it is supposed to add the data to a new row; in order to get it to overwrite the data that it found, try this....

I can't test it right now, but try removing these lines:

For C = 5 To 19 Step 2
            i = i + 1
            sourceSheet.Range(Format(C, "F" & 0)).Value = dataSheet.Cells(recordRow, i).Value
            Next C

and replace them with:

nextRow = recordRow

This should do what you're saying but, also, there should be a feature added in a later tutorial that is strictly for updating a value. Are you taking the premium course for forms?

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login