I'm trying to help my team in reconciliation, to identify which rows should be reconciled and which they can ignore. For example I have data of 10 stores, For each store, there is column which has the difference between cashbook and bank statement for a specified date range. (Ref to the image below)
Step 1: If the difference is between -50 to +50, it should be marked as "ignore" (this is pretty straightforward)
Step 2: For each store, one cell has to be compared with every other cell (belonging to the same store) and if the difference is between -50 to 50 it can be ignored , else it should be marked as "to be reconciled". Like wise these steps have to be followed for all the 10 stores.
This is what I have tried so far. In the excel sheet I gave row numbers to each row at a store level and tried looping through that range for each store.
Sub loop_range()
Dim iCell As Range
Dim iCell1 As Range
Dim iRange1 As String
Dim iRange2 As String
Dim rangeName As String
Sheets("Test").Select
Dim lRow As Long
Dim lCol As Long
lRow = Cells.Find(What:="*", After:=Range("H1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Row
lCol = Cells.Find(What:="*", After:=Range("H1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Column
For Each iCell In Range("H1:H" & lRow).Cells
iRange1 = ActiveCell.Row
iRange2 = ActiveCell.Offset(0, 2).Value + iRange1 - 1
rangeName = "H" & iRange1 & ":H" & iRange2
'MsgBox "1st row = " & iRange1 & " last row = " & iRange2 & " range = " & rangeName
For Each iCell1 In Range(rangeName).Cells
ActiveCell.Offset(0, 3).Value = "=RC[-8]"
Next iCell1
Next iCell
End Sub
Kindly advise on how I can get this done in Excel VBA