Need VBA to find Demerits code from different code segment
Dear experts
I have raw data which is need to validate as two category Demerits and Non Demerits. from different depends upon value of count.
i have support sheet with set of segment if set of segment match with rawdata Merit code col J:J, result will be taken from same segment of result
for e.g
if tier is BSE first match sheet BSE if product ID is repeat twice and merit code SAP and SMB , check if these criteria match same segment in BSE sheet under Individual get result of segment result. Demertis and Non Demerits,
if not match then result not found.
i want result in B and L column
B has to fill down how many times product repeat in A:A
L result from sheets if Tier,Capcitivy,Merits Code.
Re: Need VBA to find Demerits code from different code segment
try:
Outcome is in sheet "Result"
PHP Code:
Option Explicit Sub test() Dim lr&, i&, lookupRng As Range, cell As Range, count&, f If Evaluate("=ISREF(Result!A1)") Then Sheets("Result").Delete Sheets("Rawdata").Copy after:=Sheets("Rawdata") ActiveSheet.Name = "Result" Columns(2).Insert lr = Cells(Rows.count, "A").End(xlUp).Row For Each cell In Range("A2:A" & lr) count = WorksheetFunction.CountIf(Range("A2", cell), cell) cell.Offset(, 1).Value = count Set lookupRng = Sheets(cell.Offset(, 8).Value).Columns(IIf(cell.Offset(, 9).Value = "Individual", 2, 6)) Set f = lookupRng.Find(cell.Offset(, 10).Value) If Not f Is Nothing Then cell.Offset(, 11).Value = f.Offset(, 1).Value Next End Sub
Re: Need VBA to find Demerits code from different code segment
Thanks alot sir for valuble time and quickly solved, i try to re-assign column nos in code with actual rawdata ,it give some error, i reform the column can u help me same code run on actual data
Re: Need VBA to find Demerits code from different code segment
Try again:
PHP Code:
Option Explicit Sub test() Dim lr&, i&, lookupRng As Range, cell As Range, count&, f If Evaluate("=ISREF(Result!A1)") Then Sheets("Result").Delete Sheets("Rawdata").Copy after:=Sheets("Rawdata") ActiveSheet.Name = "Result" Columns(3).Insert lr = Cells(Rows.count, "A").End(xlUp).Row For Each cell In Range("B2:B" & lr) count = WorksheetFunction.CountIf(Range("B2", cell), cell) cell.Offset(, 1).Value = count Set lookupRng = Sheets(cell.Offset(, 10).Value).Columns(IIf(cell.Offset(, 18).Value = "Individual", 2, 6)) Set f = lookupRng.Find(cell.Offset(, 8).Value) If Not f Is Nothing Then cell.Offset(, 42).Value = f.Offset(, 1).Value Next End Sub
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks