Looking for a code i have tried but could not make. Looking forward to your response.
When code will run
It will take Sheet("Data") Col"B" first value i.e, 00202_0000 and match that in Sheet("Data") Col"E".
If such value exists then its rows will be copied that Rows are 7, 8 and 9 into Sheet Sheet("PasteData") as i pasted in attached Book1.
and after that whatever value is appear in Sheet("Calc").Range("AE10:AG10") that will be copied and paste into Sheet("Final").Range("E2:G2").
To go for 2nd number Code will erase the previous data available in Sheet("PasteData")
Now code will repeat same function for 2nd Number It will take Sheet("Data") Col"B" 2nd value i.e., 00203_0000 and match that in Sheet("Data") Col"E".
If such value is exists then its rows will be copied that Rows are 13, 14 and 15 into Sheet("PasteData") as i pasted in attached Book2.
and after that whatever value is appear in Sheet("Calc").Range("AE10:AG10") that will be copied and paste into Sheet("Final").Range("E3:G3").
To go for 3rd number Code will erase the previous data available in Sheet("PasteData")
Now code will repeat same function for 3rd Number It will take Sheet("Data") Col"B" 3rd value i.e., 00204_0000 and match that in Sheet("Data") Col"E".
If such value is exists then its rows will be copied that rows are 20, 21 and 22 into Sheet Sheet("PasteData") as i pasted in attached Book3 and
after that whatever value is appear in Sheet("Calc").Range("AE10:AG10") that will be copied and paste into Sheet("Final").Range("E4:G4").
I just need the result that is available in Book3.Sheet("Final") for mentioned three numbers.
Three workbooks have been created to understand the scenario code will RUN in single workbook to give the accurate result as available in Workbook3
Your help will be highly appreciated.
Sub Mu_code()
Dim SearchRange As Range
Set SearchRange = ActiveWorkbook.Worksheets("PasteData").Range("C10:C1500")
Dim SearchInRange As Range
Set SearchInRange = ActiveWorkbook.Worksheets("Data").Range("H6:CL6")
Dim Cell As Range
Dim Search As String
'1st
For Each Cell In SearchRange
If Cell.Value = 2020 Then
Search = "Q" & Application.WorksheetFunction.RoundUp((Cell.Offset(0, 1).Value / 3), 0) & " " & Cell.Value
Cell.Offset(0, 4).Value = Application.WorksheetFunction.SumIf(SearchInRange, Search, SearchInRange.Offset(1))
Else
Search = "Y" & Cell.Value
Cell.Offset(0, 4).Value = Application.WorksheetFunction.SumIf(SearchInRange, Search, SearchInRange.Offset(1))
End If
Next Cell
'2nd
For Each Cell In SearchRange
If Cell.Value = 2020 Then
Search = "Q" & Application.WorksheetFunction.RoundUp((Cell.Offset(0, 1).Value / 3), 0) & " " & Cell.Value
Cell.Offset(0, 5).Value = Application.WorksheetFunction.SumIf(SearchInRange, Search, SearchInRange.Offset(2))
Else
Search = "Y" & Cell.Value
Cell.Offset(0, 5).Value = Application.WorksheetFunction.SumIf(SearchInRange, Search, SearchInRange.Offset(2))
End If
Next Cell
'3rd
For Each Cell In SearchRange
If Cell.Value = 2020 Then
Search = "Q" & Application.WorksheetFunction.RoundUp((Cell.Offset(0, 1).Value / 3), 0) & " " & Cell.Value
Cell.Offset(0, 6).Value = Application.WorksheetFunction.SumIf(SearchInRange, Search, SearchInRange.Offset(3))
Else
Search = "Y" & Cell.Value
Cell.Offset(0, 6).Value = Application.WorksheetFunction.SumIf(SearchInRange, Search, SearchInRange.Offset(3))
End If
Next Cell
[/CODE]End Sub