VBA to find data from cells and update the old data

Question

I am trying to get code for replacing data. Here is my scenario. In sheet2 I have data A2 to bottom is Month(1,2,3) and in B2 its year (2012) and in c2, d2, e2 is numerical data (200, 150,50)

Now, In sheet1 I have a table A22 for dropdown list to select month/ quarter. B22 for year. C22:E22 are to edit the selected month and year numerical data.

In Sheet1 If I select A22-April B22-2012 and enter new values in C22:E22 and click edit button. I would like to see the reflected changes in the sheet 2 table for the previous entered values.

Sub edit() 

Dim s1 As Worksheet, s2 As Worksheet 
Set s1 = Sheets("Sheet1") 
Set s2 = Sheets("Sheet2") 
v1 = s1.Range("A22") 
v2 = s1.Range("B22") 
v3 = s1.Range("C22") 
v4 = s1.Range("D22") 
v5 = s1.Range("E22") 
s2.Activate 
For Each r In Intersect(ActiveSheet.UsedRange, Range("A:E")) 
If r.Value = v1 Then 
r.Offset(0, 1).Value = v2 
r.Offset(0, 2).Value = v3 
End If 
Next 

Worksheets("Sheet1").Range("A22:E22").ClearContents 

End Sub

Any help would be appreciated. Thanks!

What does your code look like? Are you getting errors, if so what are they? — Tim Williams
@TimWilliams I am Novice and trying to put it together code Sub edit() Dim s1 As Worksheet, s2 As Worksheet Set s1 = Sheets("Sheet1") Set s2 = Sheets("Sheet2") v1 = s1.Range("A22") v2 = s1.Range("B22") v3 = s1.Range("C22") v4 = s1.Range("D22") v5 = s1.Range("E22") s2.Activate For Each r In Intersect(ActiveSheet.UsedRange, Range("A:E")) If r.Value = v1 Then r.Offset(0, 1).Value = v2 r.Offset(0, 2).Value = v3 End If Next Worksheets("Sheet1").Range("A22:E22").ClearContents End Sub code — user3190729
Thanks for the edit. I was in midway editing and you finished it. — user3190729

Tim Williams Tim Williams · Accepted Answer · 2014-01-13T18:30:50

Something like this:

Sub SaveEdit() 

Dim s1 As Worksheet, s2 As Worksheet
Dim bFound as boolean, r as Range 

Set s1 = Sheets("Sheet1") 
Set s2 = Sheets("Sheet2") 

v1 = s1.Range("A22") 
v2 = s1.Range("B22") 
v3 = s1.Range("C22") 
v4 = s1.Range("D22") 
v5 = s1.Range("E22") 

If v1<>"" and v2<>"" Then
    For Each r In Intersect(s2.UsedRange, s2.Range("A:E")).Rows 
      If r.cells(1).Value = v1 and r.cells(2).Value=v2 Then 
        r.cells(3).Value = v3 
        r.cells(4).Value = v4
        r.cells(5).Value = v5
        bFound = True
        s1.Range("A22:E22").ClearContents
      End If 
    Next
    If Not bFound then msgbox "No match found for this record!"
Else
    msgbox "both Year and Month are required!"
End If


End Sub

VBA to find data from cells and update the old data

1 Answers