SOLVED

Col “B” values should be match with Three months

%3CLINGO-SUB%20id%3D%22lingo-sub-1980820%22%20slang%3D%22en-US%22%3ECol%20%E2%80%9CB%E2%80%9D%20values%20should%20be%20match%20with%20Three%20months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1980820%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20using%20this%20code%20which%20is%20matching%20the%20values%20of%20Col%E2%80%9DB%E2%80%9D%20by%20three%20months%20such%20as%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAug%2C%20Sep%2C%20Oct%20then%20give%20the%20result%20in%20sheet%20%E2%80%9COct%E2%80%9D%20Col%20%22R%22%20where%20at%20Col%20%22Q%22%20%2C%20%22D%22%20is%20available%3C%2FP%3E%3CP%3Esame%20for%20next%20three%20months%3C%2FP%3E%3CP%3ESep%2C%20Oct%2C%20Nov%20code%20will%20give%20the%20result%20in%20sheet%20%E2%80%9CNov%E2%80%9D%20Col%20%22R%22%20where%20at%20Col%20%22Q%22%20%2C%20%22D%22%20is%20available%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20i%20have%20made%20the%20calculation's%20manually%20by%20using%20Vlookup%20i%20found%20a%20wrong%20answer%20that%20the%20code%20is%20pasting%20when%20I%20run%20the%20code.%20It%20paste%20the%20%E2%80%9Cremove%E2%80%9D%20when%20value%20is%20not%20available%20in%20any%20of%20sheet%20rather%20than%20%E2%80%9Cempty%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESolution%20If%20three%20months%20has%20same%20sku%20then%20it%20will%20paste%20the%20%22Remove%22%20in%203rd%20month%20sheet%20%E2%80%9COct%E2%80%9D%20and%20%E2%80%9CNov%E2%80%9D%20Col%20%E2%80%9CR%E2%80%9D%20and%20if%20sku%20is%20not%20available%20even%20in%20one%20month%20it%20will%20be%20%22empty%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eany%20help%20would%20be%20highly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Matching()%0A%0ADim%20row1%2C%20month3%2C%20month2%2C%20month1%2C%20Rating3%2C%20Rating2%2C%20Rating1%2C%20sku%20As%20String%0ADim%20ws%20As%20Worksheet%0ADim%20xa%2C%20xb%2C%20xc%20As%20Integer%0A%0Axa%20%3D%201%0Axc%20%3D%20Worksheets(%22Main%22).Cells(1%2C%205)%0ASheets(%22Main%22).Range(%22A%3AA%22).Clear%0A%20%0AFor%20Each%20ws%20In%20Worksheets%0A%20Sheets(%22Main%22).Cells(xa%2C%201)%20%3D%20ws.Name%0A%20xa%20%3D%20xa%20%2B%201%0ANext%20ws%0ASheets(%22Main%22).Range(%22A2%3AC8%22).Sort%20Key1%3A%3DSheets(%22Main%22).Range(%22B2%22)%2C%20Order1%3A%3DxlAscending%2C%20Header%3A%3DxlNo%0A%0AFor%20xb%20%3D%204%20To%20xc%0Amonth3%20%3D%20Worksheets(%22Main%22).Cells(xb%2C%203)%0Amonth2%20%3D%20Worksheets(%22Main%22).Cells(xb%20-%201%2C%203)%0Amonth1%20%3D%20Worksheets(%22Main%22).Cells(xb%20-%202%2C%203)%0A%0AFor%20x%20%3D%202%20To%20800%0AIf%20Worksheets(month3).Cells(x%2C%2017)%20%3D%20%22D%22%20Then%0Asku%20%3D%20Worksheets(month3).Cells(x%2C%202).Text%0A%0A%0AWith%20Sheets(month3)%0A%20Set%20Findrow%20%3D%20.Range(%22B%3AB%22).Find(What%3A%3Dsku%2C%20LookIn%3A%3DxlValues)%0A%20%0AIf%20Findrow%20Is%20Nothing%20Then%0ARating3%20%3D%200%0AElse%0Arow1%20%3D%20Findrow.Row%0A%0AIf%20Worksheets(month3).Cells(Findrow.Row%2C%2017).Text%20%3D%20%22D%22%20Then%0ARating3%20%3D%201%0AElse%0AEnd%20If%0AEnd%20If%0AEnd%20With%0A%0A%0AWith%20Sheets(month2)%0A%20Set%20Findrow%20%3D%20.Range(%22B%3AB%22).Find(What%3A%3Dsku%2C%20LookIn%3A%3DxlValues)%0A%20If%20Findrow%20Is%20Nothing%20Then%0ARating3%20%3D%200%0AElse%0AIf%20Worksheets(month2).Cells(Findrow.Row%2C%2017).Text%20%3D%20%22D%22%20Then%0ARating2%20%3D%201%0AElse%0AEnd%20If%0AEnd%20If%0AEnd%20With%0A%0A%0AWith%20Sheets(month1)%0A%20Set%20Findrow%20%3D%20.Range(%22B%3AB%22).Find(What%3A%3Dsku%2C%20LookIn%3A%3DxlValues)%0AIf%20Findrow%20Is%20Nothing%20Then%0ARating1%20%3D%200%0AElse%0AIf%20Worksheets(month1).Cells(Findrow.Row%2C%2017).Text%20%3D%20%22D%22%20Then%0ARating1%20%3D%201%0AElse%0ARating1%20%3D%200%0AEnd%20If%0AEnd%20If%0AEnd%20With%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1980820%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1981416%22%20slang%3D%22en-US%22%3ERe%3A%20Col%20%E2%80%9CB%E2%80%9D%20values%20should%20be%20match%20with%20Three%20months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981416%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893407%22%20target%3D%22_blank%22%3E%40Improving%3C%2FA%3E%26nbsp%3BThere%20are%20a%20number%20of%20things%20I%20don't%20get%20why%20you%20are%20doing%20it%20this%20way%20and%20think%20this%20whole%20thing%20could%20be%20much%20more%20efficient%20but%20I%20also%20really%20don't%20know%20what%20you%20are%20expecting%2Fneeding%20on%20the%20output.%20Although%20there%20a%20number%20of%20things%20I%20would%20love%20to%20ask%2Fchallenge%20I%20will%20keep%20it%20only%20to%20where%3CSPAN%3E%26nbsp%3BI%20THINK%20your%20problem%20might%20be%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ea)%26nbsp%3B%20In%20this%20segment%20of%20code%20I%20believe%20you%20have%20a%20typo%20on%20your%20variable%20name%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20With%20Sheets(month2)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Set%20Findrow%20%3D%20.Range(%22B%3AB%22).Find(What%3A%3Dsku%2C%20LookIn%3A%3DxlValues)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20If%20Findrow%20Is%20Nothing%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Rating%3CU%3E%3CSTRONG%3E3%3C%2FSTRONG%3E%3C%2FU%3E%20%3D%200%3C%2FP%3E%3CP%3EI%20think%20you%20meant%20to%20have%20Rating%3CU%3E%3CSTRONG%3E2%3C%2FSTRONG%3E%3C%2FU%3E%20%3D%200%20on%20this%20line%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eb)%3CSPAN%3E%26nbsp%3B%20%26nbsp%3BIn%20ALL%20cases%20you%20don't%20reinitialize%20the%20RATING%20variable%20and%20rely%20on%20it%20falling%20into%201%20of%20the%20conditions%20where%20it%20is%20set.%26nbsp%3B%3CBR%20%2F%3EFor%20Rating3%20I%20think%20it%20will%20always%20be%201%20because%20I%20can't%20see%20how%20it%20will%20not%20find%20itself%20and%20even%20if%20it%20is%20on%20the%20page%202x%20(i.e.%20you%20find%20it%20on%20a%20row%20that%20isn't%20%22D%22)%20you%20don't%20have%20a%20condition%2Fset%20to%20change%20the%20value%20of%20Rating3%20and%20as%20I%20mentioned%20you%20don't%20re-initialize%20it%20either.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20Rating2%20i%20believe%20you%20are%20%3CU%3Emissing%3C%2FU%3E%20a%20condition%2Fset.%26nbsp%3B%20In%20the%20Month1%20%26amp%3B%202%20sections%20you%20have%20this%20segment%20of%20code%20EXCEPT%20the%20bold%20section%20is%20missing%20from%20Month2%20code%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BIf%20Worksheets(month1).Cells(Findrow.Row%2C%2017).Text%20%3D%20%22D%22%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Rating1%20%3D%201%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BElse%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3CU%3E%3CSTRONG%3ERating1%20%3D%200%3C%2FSTRONG%3E%3C%2FU%3E%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BEnd%20If%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1981486%22%20slang%3D%22en-US%22%3ERe%3A%20Col%20%E2%80%9CB%E2%80%9D%20values%20should%20be%20match%20with%20Three%20months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981486%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893407%22%20target%3D%22_blank%22%3E%40Improving%3C%2FA%3E%26nbsp%3BI%20still%20don't%20understand%20some%20of%20what%20you%20are%20doing%20and%20why%20so%20still%20hesitant%20to%20change%20too%20much%20but%20here%20is%20a%20simplified%20version%20of%20the%20macro%20that%20I%20believe%20gives%20correct%20results.%26nbsp%3B%20NOTE%20that%202%20cases%20appear%20to%20be%20wrong%20based%20on%20your%20VLOOKUP%20but%20your%20VLOOKUP%20doesn't%20extend%20to%20the%20bottom%20on%20the%20Oct%202020%20page.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Matching2()%0ADim%20row1%2C%20month3%2C%20month2%2C%20month1%2C%20Rating3%2C%20Rating2%2C%20Rating1%2C%20sku%20As%20String%0ADim%20ws%20As%20Worksheet%0ADim%20xa%2C%20xb%2C%20xc%20As%20Integer%0Axa%20%3D%201%0Axc%20%3D%20Worksheets(%22Main%22).Cells(1%2C%205)%0ASheets(%22Main%22).Range(%22A%3AA%22).Clear%0A%20%0AFor%20Each%20ws%20In%20Worksheets%0A%20%20%20%20%20Sheets(%22Main%22).Cells(xa%2C%201)%20%3D%20ws.Name%0A%20%20%20%20%20xa%20%3D%20xa%20%2B%201%0ANext%20ws%0ASheets(%22Main%22).Range(%22A2%3AC8%22).Sort%20Key1%3A%3DSheets(%22Main%22).Range(%22B2%22)%2C%20Order1%3A%3DxlAscending%2C%20Header%3A%3DxlNo%0AFor%20xb%20%3D%204%20To%20xc%0A%0A%20%20%20%20month3%20%3D%20Worksheets(%22Main%22).Cells(xb%2C%203)%0A%20%20%20%20month2%20%3D%20Worksheets(%22Main%22).Cells(xb%20-%201%2C%203)%0A%20%20%20%20month1%20%3D%20Worksheets(%22Main%22).Cells(xb%20-%202%2C%203)%0A%20%20%20%20%0A%20%20%20%20For%20x%20%3D%202%20To%20800%0A%20%20%20%20%20%20%20%20If%20Worksheets(month3).Cells(x%2C%2017)%20%3D%20%22D%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20sku%20%3D%20Worksheets(month3).Cells(x%2C%202).Text%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20MatchFound(month2%2C%20sku%2C%20%22D%22)%20And%20MatchFound(month1%2C%20sku%2C%20%22D%22)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Worksheets(month3).Cells(x%2C%2018)%20%3D%20%22Remove%22%0A%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20x%0ANext%20xb%0AMsgBox%20(%22Worksheets%20Updated%22)%0AEnd%20Sub%0A%0AFunction%20MatchFound(shtname%2C%20sku%2C%20val)%20As%20Boolean%0A%20%20%20MatchFound%20%3D%20(Worksheets(shtname).Range(%22b%3Ab%22).Find(What%3A%3Dsku%2C%20LookIn%3A%3DxlValues).Offset(0%2C%2015)%20%3D%20val)%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1981950%22%20slang%3D%22en-US%22%3ERe%3A%20Col%20%E2%80%9CB%E2%80%9D%20values%20should%20be%20match%20with%20Three%20months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981950%22%20slang%3D%22en-US%22%3EI%20am%20really%20thankful%20that%20you%20give%20me%20that%20huge%20help%20after%20understanding%20it%20completely.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20so%20much.%3CBR%20%2F%3EThank%20you%20once%20again%20Sir.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1981952%22%20slang%3D%22en-US%22%3ERe%3A%20Col%20%E2%80%9CB%E2%80%9D%20values%20should%20be%20match%20with%20Three%20months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1981952%22%20slang%3D%22en-US%22%3EExactly%20there%20is%20a%20problem%20which%20stops%20the%20code%20working%20completely%20and%20giving%20the%20correct%20answer.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20so%20much%20Sir%20you%20are%20really%20great.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1985001%22%20slang%3D%22en-US%22%3ERe%3A%20Col%20%E2%80%9CB%E2%80%9D%20values%20should%20be%20match%20with%20Three%20months%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1985001%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BHi%2C%20sir%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20using%20your%20code%20where%20an%20error%20is%20occurred%20when%20it%20is%20applied%20to%20other%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EError%20is%26nbsp%3BObject%20variable%20or%20With%20block%20variable%20not%20set%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I was using this code which is matching the values of Col”B” by three months such as

 

Aug, Sep, Oct then give the result in sheet “Oct” Col "R" where at Col "Q" , "D" is available

same for next three months

Sep, Oct, Nov code will give the result in sheet “Nov” Col "R" where at Col "Q" , "D" is available

 

Now i have made the calculation's manually by using Vlookup i found a wrong answer that the code is pasting when I run the code. It paste the “remove” when value is not available in any of sheet rather than “empty”

 

Solution If three months has same sku then it will paste the "Remove" in 3rd month sheet “Oct” and “Nov” Col “R” and if sku is not available even in one month it will be "empty".

 

any help would be highly appreciated.

 

 

Sub Matching()

Dim row1, month3, month2, month1, Rating3, Rating2, Rating1, sku As String
Dim ws As Worksheet
Dim xa, xb, xc As Integer

xa = 1
xc = Worksheets("Main").Cells(1, 5)
Sheets("Main").Range("A:A").Clear
 
For Each ws In Worksheets
 Sheets("Main").Cells(xa, 1) = ws.Name
 xa = xa + 1
Next ws
Sheets("Main").Range("A2:C8").Sort Key1:=Sheets("Main").Range("B2"), Order1:=xlAscending, Header:=xlNo

For xb = 4 To xc
month3 = Worksheets("Main").Cells(xb, 3)
month2 = Worksheets("Main").Cells(xb - 1, 3)
month1 = Worksheets("Main").Cells(xb - 2, 3)

For x = 2 To 800
If Worksheets(month3).Cells(x, 17) = "D" Then
sku = Worksheets(month3).Cells(x, 2).Text


With Sheets(month3)
 Set Findrow = .Range("B:B").Find(What:=sku, LookIn:=xlValues)
 
If Findrow Is Nothing Then
Rating3 = 0
Else
row1 = Findrow.Row

If Worksheets(month3).Cells(Findrow.Row, 17).Text = "D" Then
Rating3 = 1
Else
End If
End If
End With


With Sheets(month2)
 Set Findrow = .Range("B:B").Find(What:=sku, LookIn:=xlValues)
 If Findrow Is Nothing Then
Rating3 = 0
Else
If Worksheets(month2).Cells(Findrow.Row, 17).Text = "D" Then
Rating2 = 1
Else
End If
End If
End With


With Sheets(month1)
 Set Findrow = .Range("B:B").Find(What:=sku, LookIn:=xlValues)
If Findrow Is Nothing Then
Rating1 = 0
Else
If Worksheets(month1).Cells(Findrow.Row, 17).Text = "D" Then
Rating1 = 1
Else
Rating1 = 0
End If
End If
End With

 

9 Replies

@Improving There are a number of things I don't get why you are doing it this way and think this whole thing could be much more efficient but I also really don't know what you are expecting/needing on the output. Although there a number of things I would love to ask/challenge I will keep it only to where I THINK your problem might be:

a)  In this segment of code I believe you have a typo on your variable name:

        With Sheets(month2)
              Set Findrow = .Range("B:B").Find(What:=sku, LookIn:=xlValues)
              If Findrow Is Nothing Then
                  Rating3 = 0

I think you meant to have Rating2 = 0 on this line

 

b)   In ALL cases you don't reinitialize the RATING variable and rely on it falling into 1 of the conditions where it is set. 
For Rating3 I think it will always be 1 because I can't see how it will not find itself and even if it is on the page 2x (i.e. you find it on a row that isn't "D") you don't have a condition/set to change the value of Rating3 and as I mentioned you don't re-initialize it either.

For Rating2 i believe you are missing a condition/set.  In the Month1 & 2 sections you have this segment of code EXCEPT the bold section is missing from Month2 code:

     If Worksheets(month1).Cells(Findrow.Row, 17).Text = "D" Then
          Rating1 = 1
     Else

          Rating1 = 0
     End If

 

best response confirmed by Improving (Occasional Contributor)
Solution

@Improving I still don't understand some of what you are doing and why so still hesitant to change too much but here is a simplified version of the macro that I believe gives correct results.  NOTE that 2 cases appear to be wrong based on your VLOOKUP but your VLOOKUP doesn't extend to the bottom on the Oct 2020 page.

Sub Matching2()
Dim row1, month3, month2, month1, Rating3, Rating2, Rating1, sku As String
Dim ws As Worksheet
Dim xa, xb, xc As Integer
xa = 1
xc = Worksheets("Main").Cells(1, 5)
Sheets("Main").Range("A:A").Clear
 
For Each ws In Worksheets
     Sheets("Main").Cells(xa, 1) = ws.Name
     xa = xa + 1
Next ws
Sheets("Main").Range("A2:C8").Sort Key1:=Sheets("Main").Range("B2"), Order1:=xlAscending, Header:=xlNo
For xb = 4 To xc

    month3 = Worksheets("Main").Cells(xb, 3)
    month2 = Worksheets("Main").Cells(xb - 1, 3)
    month1 = Worksheets("Main").Cells(xb - 2, 3)
    
    For x = 2 To 800
        If Worksheets(month3).Cells(x, 17) = "D" Then
            sku = Worksheets(month3).Cells(x, 2).Text
            If MatchFound(month2, sku, "D") And MatchFound(month1, sku, "D") Then
                Worksheets(month3).Cells(x, 18) = "Remove"
            Else
            End If
        Else
        End If
    Next x
Next xb
MsgBox ("Worksheets Updated")
End Sub

Function MatchFound(shtname, sku, val) As Boolean
   MatchFound = (Worksheets(shtname).Range("b:b").Find(What:=sku, LookIn:=xlValues).Offset(0, 15) = val)
End Function
I am really thankful that you give me that huge help after understanding it completely.

Thank you so much.
Thank you once again Sir.
Exactly there is a problem which stops the code working completely and giving the correct answer.

Thank you so much Sir you are really great.

@mtarler Hi, sir, 

 

I have been using your code where an error is occurred when it is applied to other sheets.

 

Error is Object variable or With block variable not set

@Improvingmaybe you could give a little more detail.  what do you mean by "applied to another sheets"?  When the error occurs can you click debug and say exactly what line?  can you include the workbook?  

@mtarler 

 

error comes from this line that goes yellow with the error mentioned in recent post

 

MatchFound = (Worksheets(shtname).Range("b:b").Find(What:=sku, LookIn:=xlValues).Offset(0, 15) = val)

@Improving I took a shortcut, cutting and pasting the code you had.  I don't know if this will fix it but try adding ActiveWorkbook. in front so it looks like this:

 MatchFound = (ActiveWorkbook.Worksheets(shtname).Range("b:b").Find(What:=sku, LookIn:=xlValues).Offset(0, 15) = val)

 

not sure why it worked initially and then stopped working.  But if it isn't the "Active" workbook (i.e. you are call this macro to run on a different book then that needs to be changed.

@mtarler Its working thank you so much Sir, for helping out again.