VBA to find data from cells and update the old data

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)

我正在尝试获取替换数据的代码。这是我的情景。在sheet2中,我有数据A2到底部是月(1,2,3)和B2在它的年份(2012)和在c2,d2,e2是数字数据(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.

现在,在sheet1中,我有一个用于下拉列表的表A22来选择月/季。 B22一年。 C22:E22用于编辑选定的月份和年份数值数据。

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.

在Sheet1中如果我选择A22-April B22-2012并在C22:E22中输入新值并单击编辑按钮。我希望看到先前输入值的工作表2表中的反映更改。

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!

任何帮助,将不胜感激。谢谢!

1 个解决方案

#1

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