0

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.

Expected output image

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

Mahesh Thorat
  • 1
  • 4
  • 11
  • 22
Apoorva G
  • 1
  • 1

0 Answers0