CODEWITHSUNDEEP
vbaexcel
Zero
Zero

Reputation: 3

How to update existing data from Sheet1 to Sheet2 using Macro?

I just want to ask if someone do have a code for this. I have a saved data in Sheet2 and I want to update it using the new data from Sheet1. In the below example, the code will search for Family "Oh" in Sheet2 and update its details using the updated information from Sheet1 when I click on the Update button. Here are the screenshots of

Sheet1:

Sheet1

and

Sheet2:

Sheet2

Tried this code but I can't get it to work correctly

Sub FindValues()

Dim lookUpSheet As Worksheet, updateSheet As Worksheet
Dim valueToSearch As String
Dim i As Integer, t As Integer

Set lookUpSheet = Worksheets("sheet1")
Set updateSheet = Worksheets("sheet2")

'get the number of the last row with data in sheet1 and in sheet2
lastRowLookup = lookUpSheet.Cells(Rows.Count, "A").End(xlUp).Row
lastRowUpdate = updateSheet.Cells(Rows.Count, "A").End(xlUp).Row

'for every value in column A of sheet2
For i = 1 To lastRowUpdate
 valueToSearch = updateSheet.Cells(i, 1)
 'look the value in column A of sheet1
 For t = 1 To lastRowLookup
    'if found a match, copy column B value to sheet1 and proceed to the next value
    If lookUpSheet.Cells(t, 1) = valueToSearch Then
        updateSheet.Cells(i, 2) = lookUpSheet.Cells(t, 2)
        Exit For
    End If
 Next t
 Next i

End Sub

Thank you in advance for your help

Upvotes: 0

Views: 1379

Answers (2)

Xabier
Xabier

Reputation: 7735

The following should do what you expect, I've commented the code so you may understand what it is doing:

Sub FindValues()
    Dim lookUpSheet As Worksheet, updateSheet As Worksheet
    Dim valueToSearch As String
    Dim i As Long, t As Long

    Set lookUpSheet = Worksheets("Sheet1")
    Set updateSheet = Worksheets("Sheet2")

    lastRowLookup = lookUpSheet.Cells(Rows.Count, "A").End(xlUp).Row
    lastRowUpdate = updateSheet.Cells(Rows.Count, "A").End(xlUp).Row
    'get the number of the last row with data in sheet1 and in sheet2

    For i = 2 To lastRowLookup 'i = 2 to last to omit the first row as that row is for headers

        valueFamily = lookUpSheet.Cells(i, 1) 'Family, 1 = Column A
        valueDOB = lookUpSheet.Cells(i, 2) 'DOB, 2 = Column  B
        valueName = lookUpSheet.Cells(i, 3) 'Name, 3 = Column C
        valueAge = lookUpSheet.Cells(i, 4) 'Age, 4 = Column D
        'above get the values from the four column into variables

        For t = 2 To lastRowUpdate 't = 2 to last to omit the first row as that row is for headers
            If updateSheet.Cells(t, 1) = valueFamily And updateSheet.Cells(t, 2) = valueDOB And updateSheet.Cells(t, 3) = valueName Then
            'if family, dob and name match, then
            updateSheet.Cells(t, 4) = valueAge
            'update age value
            Exit For
            End If
        Next t
    Next i
End Sub

This could be shortened without using the variables and comparing cells instead like below:

Sub FindValues()
    Dim lookUpSheet As Worksheet, updateSheet As Worksheet
    Dim i As Long, t As Long

    Set lookUpSheet = Worksheets("Sheet1")
    Set updateSheet = Worksheets("Sheet2")

    lastRowLookup = lookUpSheet.Cells(Rows.Count, "A").End(xlUp).Row
    lastRowUpdate = updateSheet.Cells(Rows.Count, "A").End(xlUp).Row
    'get the number of the last row with data in sheet1 and in sheet2

    For i = 2 To lastRowLookup 'i = 2 to last to omit the first row as that row is for headers
        For t = 2 To lastRowUpdate 't = 2 to last to omit the first row as that row is for headers
            If updateSheet.Cells(t, 1) = lookUpSheet.Cells(i, 1) And updateSheet.Cells(t, 2) = lookUpSheet.Cells(i, 2) And updateSheet.Cells(t, 3) = lookUpSheet.Cells(i, 3) Then
            'if family, dob and name match, then
            updateSheet.Cells(t, 4) = lookUpSheet.Cells(i, 4)
            'update age value
            Exit For
            End If
        Next t
    Next i
End Sub

The issue you were having is from the fact that you needed to have the IF Statement look at the first 3 cells instead of a single value, so with the AND between conditions you compare all three.

Upvotes: 1

Dy.Lee
Dy.Lee

Reputation: 7567

This is how to update to sql using adodb.

Sub UpdateSQL()

    Dim Cn As Object
    Dim strConn As String, Name As String
    Dim Ws As Worksheet
    Dim strSQL As String
    Dim i As Integer
    Dim vDB

    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.FullName & ";" & _
            "Extended Properties=Excel 12.0;"
    Set Ws = Sheets(1)
    Name = Sheets(2).Name
    With Ws
        vDB = .Range("a2", .Range("d" & Rows.Count).End(xlUp))
    End With

    Set Cn = CreateObject("ADODB.Connection")
    Cn.Open strConn

    For i = 1 To UBound(vDB, 1)
        strSQL = "UPDATE [" & Name & "$] set Age=" & vDB(i, 4) & " where Family = '" & vDB(i, 1) & "' AND DOB =#" & vDB(i, 2) & "# AND Name='" & vDB(i, 3) & "' "
        Cn.Execute strSQL
    Next i

    Cn.Close
    Set Cn = Nothing
End Sub

Upvotes: 0

Related Questions