Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Column = 1 Or Target.Column = 2 Then
'if index don't exists, then pass in the last row index with last column index)
summary_row = Sheet1.Cells(Rows.Count, "a").End(xlUp).row
index_row = Sheet2.Cells(Rows.Count, "a").End(xlUp).row
lastCol = Sheet2.Cells(1, Columns.Count).End(xlToLeft).Column
i = indexCell(Target.row, summary_row, index_row)
MsgBox (i)
MsgBox (summary_row)
If i <> summary_row Then
MsgBox (i)
MsgBox (Target.row)
Call FillCells(i, Target.row, lastCol)
Else
Call FillCells(summary_row + 1, index_row, lastCol)
End If
'if index exists, then pass in pass in the existed row index with last column index)
'Call FillCells(existrow, lastCol)
'End If
End Sub
Public Function indexCell(ByVal selected_row As Integer, ByVal summary_lastrow As Integer, ByVal index_lastrow As Integer) As Integer
Dim j As Integer
j = summary_lastrow
For i = 1 To summary_lastrow
If Sheet1.Cells(i, 1).Value = Sheet2.Cells(selected_row, 1).Value Then
j = i
End If
Next i
indexCell = j
End Function
Sub FillCells(ByVal summary_row As Integer, ByVal index_row As Integer, ByVal col As Integer)
'this portion is to cater to insert new row.
Sheet1.Cells(summary_row, 1).Value = Sheet2.Cells(index_row, 1).Value
If Sheet2.Cells(index_row, 2).Value = "Priority 1" Then
Sheet1.Cells(summary_row, 2).Value = "1"
ElseIf Sheet2.Cells(index_row, 2).Value = "Priority 2" Then
Sheet1.Cells(summary_row, 2).Value = "2"
ElseIf Sheet2.Cells(index_row, 2).Value = "Priority 3" Then
Sheet1.Cells(summary_row, 2).Value = "3"
End If
For i = 1 To col
'MsgBox (Sheet2.Cells(1, i).Value)
If Sheet2.Cells(1, i).Value = "first count" Then
Sheet1.Cells(summary_row, 3).Value = Sheet2.Cells(index_row, i).Value
MsgBox (Sheet2.Cells(index_row, i).Value)
End If
Next i
For i = 1 To col
'MsgBox (Sheet2.Cells(1, i).Value)
If Sheet2.Cells(1, i).Value = "last count" Then
Sheet1.Cells(summary_row, 4).Value = Sheet2.Cells(index_row, i).Value
End If
Next i
If (Sheet1.Cells(summary_row, 4).Value <> "") Or (Sheet1.Cells(summary_row, 3).Value <> "") Then
a = Sheet1.Cells(summary_row, 3).Value
b = Sheet1.Cells(summary_row, 4).Value
'c = (b - a) / a
Sheet1.Cells(summary_row, 6).Value = FormatPercent(((a - b) / a), 2)
End If
'Cell(row, column)
End Sub
