I have been trying to solve this from quite few hours without any success. The code below is suppose to look in 2 different worksheets and calculate the total sum of a specific value.
In sheet 1 - column A we have unique values which are taken from sheet 2 - column G. Now the code below is suppose to take the unique value from sheet 1 - column A, verifies how many times the specific value is mentioned in sheet 2 - column G and sums up its total value from column N - sheet 2. The total sum will then be pasted in Sheet 1 - column F.
I am getting the run-time error 13 after I declared the variables lastrow1 and lastrow2. However, if I remove those variable from the code, I won't get any error, but the result will be 0 every time I run the code, which is not correct.
Can someone help me in finding a smarter way? Must appreciated!
I have re-changed the code and now it seems to work. My problem is that it puts the same value in all the cells (column F - from the sumif formula) which is not correct. How can I fix that?
Sub sumvalueF()
Dim ws As Worksheet
Dim ws1 As Worksheet
Dim lastRow As Long
Set ws = Sheets("Sheet1")
Set ws1 = Sheets("Sheet2")
lastRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Range("F4:F" & lastrow).value = WorksheetFunction.sumif(ws.Range("g7:g30000"), ws1.Range("A4"), ws.Range("n7:n30000"))
End Sub
Change ws1.Range("F4:F & lastrow").value with ws1.Range("F4:F" & lastrow).value
EDIT
loop version:
For i = 4 To lastRow
ws1.Cells(i, 6).Value = WorksheetFunction.SumIf(ws.Range("g7:g30000"), ws1.Range("A"&i), ws.Range("n7:n30000"))
Next i