CODEWITHSUNDEEP
excelvba
Benjamin Alonso Tan
Benjamin Alonso Tan

Reputation: 33

Updating data using macro

I want to update data recorded on a worksheet to another main copy in another worksheet. My question is how do i make it such that i can take the records on the worksheet and update as i want it? As of now, I can only update the last row, even if i change the row number in the code. Any idea why and how do i solve it?

The code as below

Private Sub CommandButton1_Click()


Dim Name As String
Dim Problem As Integer


Worksheets("Sheet1").Select
Name = Range("C4")
Problem = Range("D4")
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("B4").Select
If Worksheets("Sheet2").Range("B4").Offset(1, 0) <> "" Then
Worksheets("Sheet2").Range("B4").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Name
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Problem
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("C4").Select
End Sub

Sheet1: Where I insert the new data and press update. This will send values to Sheet 2

enter image description here

Sheet 2: Data are updated here. I would like to for example, go back to the middle of this list and edit one of them, for example jo's entry.

enter image description here

Upvotes: 0

Views: 6081

Answers (1)

Noldor130884
Noldor130884

Reputation: 994

Now I understand :) 

Private Sub CommandButton1_Click()

Dim Name As String
Dim Problem As Integer
Dim Source As Worksheet, Target As Worksheet
Dim ItsAMatch As Boolean
Dim i As Integer

Set Source = ThisWorkbook.Worksheets("Sheet1")
Set Target = ThisWorkbook.Worksheets("Sheet2")
Name = Source.Range("B5")
Problem = Source.Range("C5")

Do Until IsEmpty(Target.Cells(5+i,2)) ' This will loop down through non empty cells from row 5 of column 2 
    If Target.Cells(5+i,2) = Name Then
        ItsAMatch = True 
        Target.Cells(5+i,3) = Problem ' This will overwrite your "Problem" value if the name was already in the column
        Exit Do
    End If
    i = i+1
Loop

' This will write new records if the name hasn't been already found
If ItsAMatch = False Then
    Target.Cells(5,2).End(xlDown).Offset(1,0) = Name
    Target.Cells(5,2).End(xlDown).Offset(0,1) = Problem
End If

Set Source = Nothing
Set Target = Nothing

End Sub

This was not tested but should work

Upvotes: 1

Related Questions