Please kindly help me regarding this VBA code issue : )

[JasonLim]

Hi, I cannot understand what is the issue with this code? Can any expert be able to explain and advise me how to solve this problem and your help will be very much appreciated. Thank you.

Below is the code, My understanding is, this will find exact the what you want to find, like example, I would like to find = "Mistral Disney USB Fan MRF500PH", and I very sure that, there is no this product name is the search range, But somehow it return me SkipProductName "Mistral Disney USB Fan MRF500PHB" which is not correct. SkipProductName should return me as "Nothing".

Set SkipProductName = Worksheets("sheet1").Range(Cells(2, 2), Cells(Sheet1XTotalProductName, 2)).Find(ProductName1)

This is correct example A, SkipProductName return as nothing which is correct.

20220318-113707

Image 20220318-113707 hosted in ImgBB

ibb.co

20220318-113718

Image 20220318-113718 hosted in ImgBB

ibb.co

This is the problem, SkipProductName returns as "Mistral Disney USB Fan MRF500PHB" but my search ProductName1 = "Mistral Disney USB Fan MRF500PH". SkipProductName returns nothing like the example A above.

20220318-113747

Image 20220318-113747 hosted in ImgBB

ibb.co

20220318-113801

Image 20220318-113801 hosted in ImgBB

ibb.co

 

[dmt32]

Hi,
its more helpful to forum if you post all your code using code tags from menu bar - Press VBA & insert the code

Your Code Here

Dave

 

[JasonLim]

Hi,
its more helpful to forum if you post all your code using code tags from menu bar - Press VBA & insert the code

Sub Macro1()
'
' TestingOutCode
'

'


Sheet6XTotalProductName = Worksheets("sheet6").Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
       
For RunSkipProductName = 1 To Sheet6XTotalProductName

If RunSkipProductName = 147 Then

Cells(1, 1) = 1

End If


ProductName1 = Worksheets("sheet6").Cells(RunSkipProductName, 1)

Sheet1XTotalProductName = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Set SkipProductName = Worksheets("sheet1").Range(Cells(2, 2), Cells(Sheet1XTotalProductName, 2)).Find(ProductName1)


If SkipProductName Is Nothing Then

Sheet1XTotalProductNameRecord = Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

If RunSkipProductName = 1 Then

ProductName1 = Worksheets("sheet6").Cells(RunSkipProductName, 1)

Product_ID = Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 1) + 1
Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 1) = Product_ID
Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 2) = ProductName1

Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 3) = Product_ID
Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 4) = Worksheets("sheet6").Cells(RunSkipProductName, 2)
Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 5) = Worksheets("sheet6").Cells(RunSkipProductName, 3)
Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 6) = Worksheets("sheet6").Cells(RunSkipProductName, 4)
Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 7) = Worksheets("sheet6").Cells(RunSkipProductName, 5)
Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 8) = Worksheets("sheet6").Cells(RunSkipProductName, 6)
Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord, 9) = Worksheets("sheet6").Cells(RunSkipProductName, 7)
Worksheets("sheet1").Cells((Sheet1XTotalProductNameRecord), 10) = Worksheets("sheet6").Cells(RunSkipProductName, 8)


End If

If RunSkipProductName > 1 Then

Product_ID = Worksheets("sheet1").Cells(Sheet1XTotalProductNameRecord - 1, 1) + 1
Worksheets("sheet1").Cells((Sheet1XTotalProductNameRecord), 1) = Product_ID
Worksheets("sheet1").Cells((Sheet1XTotalProductNameRecord), 2) = ProductName1

Sheet1XTotalRetailPriceRecord = Worksheets("sheet1").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row

Worksheets("sheet1").Cells(Sheet1XTotalRetailPriceRecord, 3) = Product_ID
Worksheets("sheet1").Cells(Sheet1XTotalRetailPriceRecord, 4) = Worksheets("sheet6").Cells(RunSkipProductName, 2)
Worksheets("sheet1").Cells(Sheet1XTotalRetailPriceRecord, 5) = Worksheets("sheet6").Cells(RunSkipProductName, 3)
Worksheets("sheet1").Cells(Sheet1XTotalRetailPriceRecord, 6) = Worksheets("sheet6").Cells(RunSkipProductName, 4)
Worksheets("sheet1").Cells(Sheet1XTotalRetailPriceRecord, 7) = Worksheets("sheet6").Cells(RunSkipProductName, 5)
Worksheets("sheet1").Cells(Sheet1XTotalRetailPriceRecord, 8) = Worksheets("sheet6").Cells(RunSkipProductName, 6)
Worksheets("sheet1").Cells(Sheet1XTotalRetailPriceRecord, 9) = Worksheets("sheet6").Cells(RunSkipProductName, 7)
Worksheets("sheet1").Cells((Sheet1XTotalRetailPriceRecord), 10) = Worksheets("sheet6").Cells(RunSkipProductName, 8)


End If



ElseIf SkipProductName = ProductName Then

Sheet1XTotalRetailPriceRecord = Worksheets("sheet1").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row

SkipNumberBetNumberRow = SkipProductName.Row

Price_ID = Worksheets("sheet1").Cells(SkipNumberBetNumberRow, 1)

Worksheets("sheet1").Cells((Sheet1XTotalRetailPriceRecord), 3) = Price_ID
Worksheets("sheet1").Cells((Sheet1XTotalRetailPriceRecord), 4) = Worksheets("sheet6").Cells(RunSkipProductName, 2)
Worksheets("sheet1").Cells((Sheet1XTotalRetailPriceRecord), 5) = Worksheets("sheet6").Cells(RunSkipProductName, 3)
Worksheets("sheet1").Cells((Sheet1XTotalRetailPriceRecord), 6) = Worksheets("sheet6").Cells(RunSkipProductName, 4)
Worksheets("sheet1").Cells((Sheet1XTotalRetailPriceRecord), 7) = Worksheets("sheet6").Cells(RunSkipProductName, 5)
Worksheets("sheet1").Cells((Sheet1XTotalRetailPriceRecord), 8) = Worksheets("sheet6").Cells(RunSkipProductName, 6)
Worksheets("sheet1").Cells((Sheet1XTotalRetailPriceRecord), 9) = Worksheets("sheet6").Cells(RunSkipProductName, 7)
Worksheets("sheet1").Cells((Sheet1XTotalRetailPriceRecord), 10) = Worksheets("sheet6").Cells(RunSkipProductName, 8)



End If

'If Sheet1XTotalRetailPriceRecord <> Sheet1XTotalProductNameRecord Then

'MsgBox "Problem"

'Cells(1, 1) = 1

'End If


Next RunSkipProductName





End Sub
[/QUOTE]
[QUOTE="dmt32, post: 5853868, member: 219730"]

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Anyone can kindly help me regarding VBA code issue : )

Hi, I cannot understand what is the issue with this code? Can any expert be able to explain and advise me how to solve this problem and your help will be very much appreciated. Thank you. Below is the code, My understanding is, this will find exact the what you want to find, like example, I...

www.excelforum.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.