Procedure too large - Error

satya12

Board Regular
Joined
Oct 19, 2021
Messages
70
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

My team members getting error procedure too large. I am using 64 bit version Excel.
I created 2 macros in one excel file. in the 2nd macro they are getting error. here is the full code. please help me.
VBA Code:
Sub HP_ENT()
    Dim ENTPrice As Workbook
    Dim ENTPricews As Worksheet
    Dim ENTCheat As Workbook
    Dim ENTCheatws As Worksheet
    Dim ENTMacro As Workbook
    Dim ENTMacrows As Worksheet
    Dim ENTMacrohome As Worksheet
    Dim HR As String
    Dim HR1 As String
    Dim Weight As String
    Dim Weight1 As Double
    Dim ShortWeight As String
    Dim PLfilter As String
    Dim rngcell As Range
    Dim Condition As Range
    Dim i As Long
    Dim PN As String, PN1 As String, PN2 As String, PN3 As String, PN4 As String, PN5 As String, PN6 As String, PN7 As String, PN8 As String, PN9 As String
    Dim ENTStrpath As String
    Dim ENTStrFile As String
    Dim ENTStrpath1 As String
    Dim ENTStrFile1 As String
    Dim ENTStrpath2 As String
    Dim ENTStrFile2 As String
    Dim ENTStrpath3 As String
    Dim ENTStrFile3 As String

    
    Set ENTMacro = Workbooks("Material Master Output.xlsm")
    Set ENTMacrows = ENTMacro.Sheets("HP_ENT")
    Set ENTMacrohome = ENTMacro.Sheets("HOME")
    Set ENTPrice = Workbooks.Open(Filename:=(ENTMacrohome.Range("D14").Value))
    Set ENTPricews = ENTPrice.Sheets("DailyPurchasePrice")
    Set ENTCheat = Workbooks.Open(Filename:=(ENTMacrohome.Range("D15").Value))
    Set ENTCheatws = ENTCheat.Sheets("NEW MATRIX")
    Application.ScreenUpdating = False
    ENTMacrows.Rows("2:" & Rows.Count).Delete
    a = ENTPricews.Range("F" & Rows.Count).End(xlUp).Row
    For i = 2 To a
        ENTMacrows.Range("B" & i).Value = ENTPricews.Range("F" & i).Value
        ENTMacrows.Range("O" & i).Value = "HP-" & ENTPricews.Range("G" & i).Value
        ENTMacrows.Range("C" & i).Value = ENTPricews.Range("J" & i).Value
        ENTMacrows.Range("AB" & i).Value = ENTPricews.Range("O" & i).Value
        ENTMacrows.Range("AC" & i).Value = ENTPricews.Range("L" & i).Value
        ENTMacrows.Range("W" & i).NumberFormat = "@"
        ENTMacrows.Range("W" & i).Value = ENTPricews.Range("AQ" & i).Value
        HR = ENTPricews.Range("AS" & i).Value
        HR1 = Left(HR, 3)
        ENTMacrows.Range("N" & i).Value = HR1
        ENTMacrows.Range("A" & i).Value = "HP ENT"
        ENTMacrows.Range("D" & i).Value = ENTPricews.Range("I" & i).Value
        'Weight Procedure
        ShortWeight = ENTPricews.Range("J" & i).Value
    'Hardware - Weight , No weight
        If ENTPricews.Range("W" & i).Value = "Hardware" Then
            If ENTPricews.Range("AJ" & i).Value = "" Then
                ENTMacrows.Range("P" & i).Value = "1"
            Else
                Weight1 = ENTPricews.Range("AJ" & i).Value
                ENTMacrows.Range("P" & i).Value = Weight1 * 2.20462262
            End If
    'Software - Weight+LTU, No Weight+LTU, NoWeight, Weight
        ElseIf ENTPricews.Range("W" & i).Value = "Software" Then
            If ENTPricews.Range("AJ" & i).Value = "" And InStr(ShortWeight, "LTU") > 0 Then
                ENTMacrows.Range("P" & i).Value = "1"
            ElseIf InStr(ShortWeight, "LTU") > 0 And ENTPricews.Range("AJ" & i).Value <> "" Then
                Weight1 = ENTPricews.Range("AJ" & i).Value
                ENTMacrows.Range("P" & i).Value = Weight1 * 2.20462262
            ElseIf ENTPricews.Range("AJ" & i).Value = "" Then
                ENTMacrows.Range("P" & i).Value = "0.01"
            ElseIf ENTPricews.Range("AJ" & i).Value <> "" Then
                Weight1 = ENTPricews.Range("AJ" & i).Value
                ENTMacrows.Range("P" & i).Value = Weight1 * 2.20462262
            End If
    'Remaining all- Weight, No Weight
        ElseIf ENTPricews.Range("AJ" & i).Value = "" Then
            ENTMacrows.Range("P" & i).Value = "0.01"
        ElseIf ENTPricews.Range("AJ" & i).Value <> "" Then
            Weight1 = ENTPricews.Range("AJ" & i).Value
            ENTMacrows.Range("P" & i).Value = Weight1 * 2.20462262
        End If
        If ENTPricews.Range("G" & i).Value = "H5" Or ENTPricews.Range("G" & i).Value = "N3" Then
            ENTMacrows.Range("O" & i).Interior.ColorIndex = 6
        Else
            Call Autofill(i)
        End If
           
    Next i
    Application.ScreenUpdating = True
    ThisWorkbook.Save
    ENTPrice.Close savechanges:=False
    ENTCheat.Close savechanges:=False
End Sub

Public Sub Autofill(i As Long)
    Set ENTMacro = Workbooks("Material Master Output.xlsm")
    Set ENTMacrows = ENTMacro.Sheets("HP_ENT")
    Set ENTMacrohome = ENTMacro.Sheets("HOME")
    ENTStrpath = ENTMacrohome.Range("D14").Value
    ENTStrFile = Right(ENTStrpath, Len(ENTStrpath) - InStrRev(ENTStrpath, "\"))
    Set ENTPrice = Workbooks(ENTStrFile)
    Set ENTPricews = ENTPrice.Sheets("DailyPurchasePrice")
    ENTStrpath1 = ENTMacrohome.Range("D15").Value
    ENTStrFile1 = Right(ENTStrpath1, Len(ENTStrpath1) - InStrRev(ENTStrpath1, "\"))
    Set ENTCheat = Workbooks(ENTStrFile1)
    Set ENTCheatws = ENTCheat.Sheets("NEW MATRIX")
    
    PLfilter = ENTPricews.Range("G" & i).Value
    ENTCheatws.Range("$A$1 : $BA$1000000").AutoFilter Field:=1, Criteria1:=PLfilter
    
    With ENTCheatws
        For Each Condition In .Range("D2", .Cells(.Rows.Count, "D").End(xlUp)).SpecialCells(xlCellTypeVisible)
            If Condition.Value = "All descriptions must have FIO and Description must have the word POINTNEXT" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "FIO") > 0 And InStr(ENTMacrows.Range("C" & i).Value, "POINTNEXT") > 0 Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "All skus in this PL will end in PE and do not have a # suffix. Also, part Number should start with H or U and it mustn't have any suffix. (E.g HN834PE or U9333PE ) if so, CAREPACK must be at the beginning of the description." Then
                PN = ENTPricews.Range("F" & i).Value
                PN1 = Left(PN, 1)
                PN3 = Right(PN, 2)
                If PN3 = "PE" And InStr(ENTPricews.Range("F" & i).Value, "#") <= 0 And PN1 = "H" Or PN1 = "U" Then
                    ENTMacrows.Range("C" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("C" & i).Value
                    ENTMacrows.Range("D" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("D" & i).Value
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "All skus in this PL will end in E. Also, part Number should start with H or U and it mustn't have any suffix. (E.g HN834E or U9333E ) if so, CAREPACK must be at the beginning of the description." Then
                PN = ENTPricews.Range("F" & i).Value
                PN1 = Left(PN, 1)
                PN2 = Right(PN, 1)
                PN5 = Left(Right(PN, 2), 1)
                If PN2 = "E" And PN5 <> "P" And InStr(ENTPricews.Range("F" & i).Value, "#") <= 0 And PN1 = "H" Or PN1 = "U" Then
                    ENTMacrows.Range("C" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("C" & i).Value
                    ENTMacrows.Range("D" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("D" & i).Value
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "All skus in this PL will end in PE or E . Also, part Number should start with H or U and it mustn't have any suffix. (E.g HN834PE or U9333PE ) if so, CAREPACK must be at the beginning of the description." Then
                PN = ENTPricews.Range("F" & i).Value
                PN1 = Left(PN, 1)
                PN2 = Right(PN, 1)
                PN3 = Right(PN, 2)
                If PN3 = "PE" Or PN2 = "E" And PN1 = "H" Or PN1 = "U" And InStr(ENTPricews.Range("F" & i).Value, "#") <= 0 Then
                    ENTMacrows.Range("C" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("C" & i).Value
                    ENTMacrows.Range("D" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("D" & i).Value
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "CTO or FIO must be in the description" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") > 0 Then
                   ENTMacrows.Range("U" & i).NumberFormat = "@"
                   ENTMacrows.Range("K" & i).NumberFormat = "@"
                   ENTMacrows.Range("AO" & i).NumberFormat = "@"
                   ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                   ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                   ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                   ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                   ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                   ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                   ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                   ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                   ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                   ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                   ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                   ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                   ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                   ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                   ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                   ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                   ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                   Exit For
                End If
            ElseIf Condition.Value = "CTO or FIO must be in the description. Item has weight (0.1 pound or more) We must convert Kgs into Lbs" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") > 0 And ENTPricews.Range("AJ" & i).Value <> "" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                End If
            ElseIf Condition.Value = "CTO or FIO must be in the description. Product has NO weight in the pricebook. (We must add 0.01 pounds)" Then
                 If InStr(ENTMacrows.Range("C" & i).Value, "CTO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") > 0 And ENTPricews.Range("AJ" & i).Value = "" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "CTO, FIO or Factory Express must in be the description. Item has weight (0.1 pound or more) We must convert Kgs into Lbs. Also, all those parts ending in #8ZN" Then
                PN = ENTPricews.Range("F" & i).Value
                PN4 = Right(PN, 4)
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "Factory Express") > 0 And ENTPricews.Range("AJ" & i).Value <> "" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                End If
            ElseIf Condition.Value = "CTO, FIO or Factory Express must in be the description. Product has NO weight in the pricebook. (We must add 0.01 pounds) Also, all those parts ending in #8ZN" Then
                PN = ENTPricews.Range("F" & i).Value
                PN4 = Right(PN, 4)
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") Or InStr(ENTMacrows.Range("C" & i).Value, "Factory Express") > 0 And ENTPricews.Range("AJ" & i).Value = "" Then
                    If PN4 = "#8ZN" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                    End If
                End If
            ElseIf Condition.Value = "CTO, FIO or Moonshot must be in the description. Item has weight (0.1 pound or more) We must convert Kgs into Lbs" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "Moonshot") > 0 And ENTPricews.Range("AJ" & i).Value <> "" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                End If
            ElseIf Condition.Value = "CTO, FIO or Moonshot must be in the description. Product has NO weight in the pricebook. (We must add 0.01 pounds)" Then
                 If InStr(ENTMacrows.Range("C" & i).Value, "CTO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "Moonshot") > 0 And ENTPricews.Range("AJ" & i).Value = "" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "E-LTU might be the description OR Product without weight in the pricebook (We must add 0.01 pounds)" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "E-LTU") <= 1 Or ENTPricews.Range("AJ" & i).Value = "" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "If no CTO, FIO in the description " Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") <= 0 Then
                   ENTMacrows.Range("U" & i).NumberFormat = "@"
                   ENTMacrows.Range("K" & i).NumberFormat = "@"
                   ENTMacrows.Range("AO" & i).NumberFormat = "@"
                   ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                   ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                   ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                   ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                   ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                   ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                   ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                   ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                   ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                   ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                   ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                   ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                   ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                   ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                   ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                   ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                   ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                   Exit For
                End If
            ElseIf Condition.Value = "If no CTO, FIO in the description. Item has weight (0.1 pound or more) We must convert Kgs into Lbs" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") <= 0 And ENTPricews.Range("AJ" & i).Value <> "" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                End If
            ElseIf Condition.Value = "If no CTO, FIO in the description. Product has NO weight in the pricebook. (We must add 0.01 pounds) Also, E-LTU might be the description." Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "E-LTU") > 0 And ENTPricews.Range("AJ" & i).Value = "" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "If no CTO, FIO or Moonshot in the description. Item has weight (0.1 pound or more) We must convert Kgs into Lbs" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "Moonshot") <= 0 And ENTPricews.Range("AJ" & i).Value <> "" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                End If
            ElseIf Condition.Value = "If no CTO, FIO or Moonshot in the description. Product has NO weight in the pricebook. (We must add 0.01 pounds) Also, E-LTU might be the description." Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "Moonshot") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "E-LTU") > 0 And ENTPricews.Range("AJ" & i).Value = "" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "If the description has NOT Software or SVC or SERVICE" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "Software") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "SVC") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "SERVICE") <= 0 Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "If the description of the sku says Software or SVC or SERVICE" Then
                    If InStr(ENTMacrows.Range("C" & i).Value, "Software") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "SVC") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "SERVICE") > 0 Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                    End If
            ElseIf Condition.Value = "Item with some weight (0.1 pound or more) We must convert Kg into lbs" Then
                     If ENTPricews.Range("AJ" & i).Value <> "" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                    End If
            ElseIf Condition.Value = "Item with some weight (0.1 pound or more) We must convert Kg into lbs. FIO or CTO is in description. " Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CTO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") > 0 And ENTPricews.Range("AJ" & i).Value <> "" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                End If
            ElseIf Condition.Value = "Item with some weight (0.1 pound or more) We must convert Kg into lbs. FIO or CTO is NOT in the description." Then
                    If InStr(ENTMacrows.Range("C" & i).Value, "CTO") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "FIO") <= 0 And ENTPricews.Range("AJ" & i).Value <> "" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                    End If
            ElseIf Condition.Value = "Item with some weight (1 pound or more) We must convert Kg into lbs" Then
                If ENTPricews.Range("AJ" & i).Value >= 1 Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Part Number should start with H and it must have a suffix. (E.g H7J32A3#Y1Q ) if so, FIO CAREPACK must be at the beginning of the description." Then
                PN = ENTPricews.Range("F" & i).Value
                PN1 = Left(PN, 1)
                If PN1 = "H" And InStr(ENTPricews.Range("F" & i).Value, "#") > 0 Then
                    ENTMacrows.Range("C" & i).Value = "FIO CAREPACK" & " " & ENTMacrows.Range("C" & i).Value
                    ENTMacrows.Range("D" & i).Value = "FIO CAREPACK" & " " & ENTMacrows.Range("D" & i).Value
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Part Number should start with H and it mustn't have any suffix. (E.g H9XM9E ) if so, CAREPACK must be at the beginning of the description." Then
                PN = ENTPricews.Range("F" & i).Value
                PN1 = Left(PN, 1)
                If PN1 = "H" And InStr(ENTPricews.Range("F" & i).Value, "#") <= 0 Then
                    ENTMacrows.Range("C" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("C" & i).Value
                    ENTMacrows.Range("D" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("D" & i).Value
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Part Number should start with H or U and it mustn't have any suffix. (E.g H7JR6E or U7T87E ) if so, CAREPACK must be at the beginning of the description." Then
                PN = ENTPricews.Range("F" & i).Value
                PN1 = Left(PN, 1)
                If PN1 = "H" Or PN1 = "U" And InStr(ENTPricews.Range("F" & i).Value, "#") <= 0 Then
                    ENTMacrows.Range("C" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("C" & i).Value
                    ENTMacrows.Range("D" & i).Value = "CAREPACK" & " " & ENTMacrows.Range("D" & i).Value
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Pricing condition - Purchase Cost must be <100" Then
                If ENTPricews.Range("O" & i).Value < 100 Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Pricing condition - Purchase Cost must be =>100" Then
                If ENTPricews.Range("O" & i).Value >= 100 Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Product without weight in the pricebook. (We must add 0.01 pounds)" Then
                If ENTPricews.Range("AJ" & i).Value = "" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Product without weight in the pricebook. (We must add 0.01 pounds) Also, E-LTU might be the description." Then
                If InStr(ENTMacrows.Range("C" & i).Value, "E-LTU") > 0 Or ENTPricews.Range("AJ" & i).Value = "" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Product without weight in the pricebook. (We must add 0.01 pounds). FIO or CTO is in description. " Then
                If InStr(ENTMacrows.Range("C" & i).Value, "FIO") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "CTO") > 0 And ENTPricews.Range("AJ" & i).Value = "" Then
                        ENTMacrows.Range("U" & i).NumberFormat = "@"
                        ENTMacrows.Range("K" & i).NumberFormat = "@"
                        ENTMacrows.Range("AO" & i).NumberFormat = "@"
                        ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                        ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                        ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                        ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                        ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                        ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                        ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                        ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                        ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                        ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                        ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                        ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                        ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                        ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                        ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                        ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                        ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                        Exit For
                End If
            ElseIf Condition.Value = "CRAY should be in the description" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "CRAY") > 0 Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Networking app should be in the description" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "Networking app") > 0 Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Visual Remote Guidan" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
            ElseIf Condition.Value = "Unique p-line, part number should NOT begin with SLA" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
            ElseIf Condition.Value = "Unique p-line; please use set up information in this row. P-line is the only trigger. This is a HARDWARE product" Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
            ElseIf Condition.Value = "Unique p-line; please use set up information in this row. P-line is the only trigger. This is VIRTUAL product." Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
            ElseIf Condition.Value = "Warranty or WTY is in the description" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "Warranty") > 0 Or InStr(ENTMacrows.Range("C" & i).Value, "WTY") > 0 Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Warranty or WTY is NOT in the description" Then
                If InStr(ENTMacrows.Range("C" & i).Value, "Warranty") <= 0 Or InStr(ENTMacrows.Range("C" & i).Value, "WTY") <= 0 Then
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                End If
            ElseIf Condition.Value = "Unique p-line; part number should start with H and it must have a suffix. (E.g H7J32A3#Y1Q ) if so, FIO CAREPACK must be at the beginning of the description." Then
                    PN6 = ENTPricews.Range("F" & i).Value
                    PN7 = Left(PN6, 1)
                    If PN7 = "H" And InStr(PN6, "#") > 0 Then
                    ENTMacrows.Range("C" & i).Value = "FIO CAREPACK" & " " & ENTMacrows.Range("C" & i).Value
                    ENTMacrows.Range("D" & i).Value = "FIO CAREPACK" & " " & ENTMacrows.Range("D" & i).Value
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                    End If
            ElseIf Condition.Value = "Unique p-line; part Number should start with H and it must have a suffix. (E.g H7J32A3#Y1Q ) if so, FIO CAREPACK must be at the beginning of the description." Then
                    PN8 = ENTPricews.Range("F" & i).Value
                    PN9 = Left(PN8, 1)
                    If PN9 = "H" And InStr(PN8, "#") > 0 Then
                    ENTMacrows.Range("C" & i).Value = "FIO CAREPACK" & " " & ENTMacrows.Range("C" & i).Value
                    ENTMacrows.Range("D" & i).Value = "FIO CAREPACK" & " " & ENTMacrows.Range("D" & i).Value
                    ENTMacrows.Range("U" & i).NumberFormat = "@"
                    ENTMacrows.Range("K" & i).NumberFormat = "@"
                    ENTMacrows.Range("AO" & i).NumberFormat = "@"
                    ENTMacrows.Range("J" & i).Value = Condition.Offset(0, 9).Value
                    ENTMacrows.Range("K" & i).Value = Condition.Offset(0, 10).Value
                    ENTMacrows.Range("L" & i).Value = Condition.Offset(0, 11).Value
                    ENTMacrows.Range("T" & i).Value = Condition.Offset(0, 18).Value
                    ENTMacrows.Range("U" & i).Value = Condition.Offset(0, 19).Value
                    ENTMacrows.Range("V" & i).Value = Condition.Offset(0, 20).Value
                    ENTMacrows.Range("X" & i).Value = Condition.Offset(0, 22).Value
                    ENTMacrows.Range("Y" & i).Value = Condition.Offset(0, 23).Value
                    ENTMacrows.Range("Z" & i).Value = Condition.Offset(0, 24).Value
                    ENTMacrows.Range("AF" & i).Value = Condition.Offset(0, 30).Value
                    ENTMacrows.Range("AI" & i).Value = Condition.Offset(0, 33).Value
                    ENTMacrows.Range("AL" & i).Value = Condition.Offset(0, 36).Value
                    ENTMacrows.Range("AM" & i).Value = Condition.Offset(0, 37).Value
                    ENTMacrows.Range("AN" & i).Value = Condition.Offset(0, 38).Value
                    ENTMacrows.Range("AO" & i).Value = Condition.Offset(0, 39).Value
                    ENTMacrows.Range("AP" & i).Value = Condition.Offset(0, 40).Value
                    ENTMacrows.Range("AQ" & i).Value = Condition.Offset(0, 41).Value
                    Exit For
                    End If
            End If
        Next
    End With
End Sub
 
I Changed the code, again i am getting same error.

If you do not need to pass your arguments ByRef then try passing them ByVal & see if this resolves your issue

Rich (BB code):
Private Sub Assign(ByVal ENTMacrows As Worksheet, ByVal i As Long, ByVal Condition As Range)

'code

End Sub

Dave
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I will try to reproduce the code in a file and see if I get the same error.
 
Upvote 0
I am not getting the By Ref error but it looks to me that you need to pass another parameter.

Calling the new Sub Assign
VBA Code:
Assign NTMacrows:=ENTMacrows, i:=2, Condition:=Condition

The Sub first line
VBA Code:
Private Sub Assign(ENTMacrows As Worksheet, i As Long, Condition As Range)
Thank for you that, I missed it.

Also I recommend that Condition and all other variables be declared in Sub Autofill (or passed in as parameters).
 
Upvote 0
I am not getting the By Ref error but it looks to me that you need to pass another parameter.
I copied the code but there is a lot of setup involved to be able to run it. Did you actually create a file with all these sheets in it, with the right names?
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top