Macro to validate data and summarize results.

[mohan08]

Hi,

Can you help with Macro for the Attached Spread sheet.

For Sheet Trans1 and Trans 2 on Column S i have IF condition for 7 different cells and values
which returns results after checking all the 7 conditions

with Sheet Main i need the results of Trans1 and Trans2 against Gnum i have used Match and index to return the results.

 

[Marc L]

Hi, a VBA demonstration for starters to copy to the Sheet1 (Main) worksheet module :​

Sub Demo1()
    Dim L&, V, R&, W
        UsedRange.Offset(1).Clear
        Application.ScreenUpdating = False
    With [Trans1!A1].CurrentRegion
        L = .Rows.Count
       .Range("B2:B" & L & ",S2:S" & L).Copy [A2]
    End With
    With [Trans2!A1].CurrentRegion
        V = Application.Index(.Value2, Evaluate("ROW(2:" & .Rows.Count & ")"), [{2,19}])
    End With
    For R = 1 To UBound(V)
        W = Application.Match(V(R, 1), UsedRange.Columns(1), 0)
        If IsError(W) Then L = L + 1: Rows(L).Range("A1:C1").Value2 = Array(V(R, 1), , V(R, 2)) Else Cells(W, 3).Value2 = V(R, 2)
    Next
        Application.ScreenUpdating = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​