Hi All,
Here i am having 3 workbook. we have to taku values from excel1 , filter it in the Excel 2 copy values from excel 2 then paste it into excel3.
Here i wrote the code for the condition , but after run the code i found some errors like if any value not found in the excel it is taking visible cell (i.e header) and paste he header values into the output. can you please help me.
After filtering if excel 2 "C" Columns is blank don't go to another process. start the next iteration this what i want.
Here i am having 3 workbook. we have to taku values from excel1 , filter it in the Excel 2 copy values from excel 2 then paste it into excel3.
Here i wrote the code for the condition , but after run the code i found some errors like if any value not found in the excel it is taking visible cell (i.e header) and paste he header values into the output. can you please help me.
After filtering if excel 2 "C" Columns is blank don't go to another process. start the next iteration this what i want.
VBA Code:
Sub Macro()
Dim Price As Workbook
Dim Pricews As Worksheet
Dim Cheat As Workbook
Dim Cheatws As Worksheet
Dim Macro As Workbook
Dim Macrows As Worksheet
Dim HP As String
Dim CTO As String
Dim CTO5 As String
Dim PLfilter As String
Dim PNfilter As String
Dim PNfilter1 As String
Dim PNfilter2 As String
Dim PNfilter3 As String
Dim AAE As String
Dim AAE1 As String
Dim ATE As String
Dim ATE1 As String
Dim B21 As String
Dim B211 As String
Dim rngcell As Range
Dim PL As Range
Set Price = Workbooks("Price book.xlsx")
Set Pricews = Price.Sheets("Sheet1")
Set Cheat = Workbooks("Copy of Cheat Sheet.xlsm")
Set Cheatws = Cheat.Sheets("Template")
Set Macro = Workbooks("INC.xlsm")
Set Macrows = Macro.Sheets("Sheet1")
Macrows.Rows("2:" & Rows.Count).ClearContents
a = Pricews.Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To a
'Product Number
Macrows.Range("A" & i).Value = Pricews.Range("D" & i).Value
'PL
HP = "HP-"
Macrows.Range("B" & i).Value = HP & Pricews.Range("E" & i).Value
'Short Description
Macrows.Range("C" & i).Value = Pricews.Range("G" & i).Value
'Purchase Price
Macrows.Range("E" & i).Value = Pricews.Range("S" & i).Value
'HP PL filtering
PLfilter = Pricews.Range("E" & i).Value
Cheatws.Range("$A$1 : $BA$1000000").AutoFilter Field:=2, Criteria1:=PLfilter
'Suffix w/out option filtering
AAE = Pricews.Range("D" & i).Value
AAE1 = Right(AAE, 3)
ATE = Pricews.Range("D" & i).Value
ATE1 = Right(ATE, 3)
B21 = Pricews.Range("D" & i).Value
B211 = Right(B21, 4)
If InStr(Range("A" & i).Value, "#") > 0 Then
PNfilter = Left(Pricews.Range("D" & i), (Application.WorksheetFunction.Find("#", Pricews.Range("D" & i), 1) - 1))
PNfilter1 = Right(PNfilter, 2)
Cheatws.Range("$A$1 : $BA$1000000").AutoFilter Field:=3, Criteria1:=PNfilter1
ElseIf AAE1 = "AAE" Then
Cheatws.Range("$A$1 : $BA$1000000").AutoFilter Field:=3, Criteria1:=AAE1
ElseIf ATE1 = "ATE" Then
Cheatws.Range("$A$1 : $BA$1000000").AutoFilter Field:=3, Criteria1:=ATE1
ElseIf B211 = "B-21" Then
Cheatws.Range("$A$1 : $BA$1000000").AutoFilter Field:=3, Criteria1:=B211
Else
PNfilter2 = Pricews.Range("D" & i).Value
PNfilter3 = Right(PNfilter2, 2)
Cheatws.Range("$A$1 : $BA$1000000").AutoFilter Field:=3, Criteria1:=PNfilter3
End If
For Each PL In Range("C2", Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeVisible)
If InStr(PL.Value, " ") > 0 Then
'Autofill
With Cheatws
For Each rngcell In .Range("G2", .Cells(.Rows.Count, "G").End(xlUp)).SpecialCells(xlCellTypeVisible)
If InStr(rngcell.Value, "<100") > 0 Then
If Macrows.Range("E" & i).Value < 100 Then
Macrows.Range("H" & i).NumberFormat = "@"
Macrows.Range("I" & i).NumberFormat = "@"
Macrows.Range("K" & i).NumberFormat = "@"
Macrows.Range("H" & i).Value = rngcell.Offset(0, -1).Value
Macrows.Range("J" & i).Value = rngcell.Offset(0, 3).Value
Macrows.Range("L" & i).Value = rngcell.Offset(0, 10).Value
Macrows.Range("K" & i).Value = rngcell.Offset(0, 11).Value
Macrows.Range("I" & i).Value = rngcell.Offset(0, 15).Value
Macrows.Range("M" & i).Value = rngcell.Offset(0, 17).Value
Macrows.Range("N" & i).Value = rngcell.Offset(0, 19).Value
Macrows.Range("O" & i).Value = rngcell.Offset(0, 23).Value
Macrows.Range("P" & i).Value = rngcell.Offset(0, 24).Value
Macrows.Range("Q" & i).Value = rngcell.Offset(0, 25).Value
End If
ElseIf Macrows.Range("E" & i).Value > 100 Then
Macrows.Range("H" & i).NumberFormat = "@"
Macrows.Range("I" & i).NumberFormat = "@"
Macrows.Range("K" & i).NumberFormat = "@"
Macrows.Range("H" & i).Value = rngcell.Offset(0, -1).Value
Macrows.Range("J" & i).Value = rngcell.Offset(0, 3).Value
Macrows.Range("L" & i).Value = rngcell.Offset(0, 10).Value
Macrows.Range("K" & i).Value = rngcell.Offset(0, 11).Value
Macrows.Range("I" & i).Value = rngcell.Offset(0, 15).Value
Macrows.Range("M" & i).Value = rngcell.Offset(0, 17).Value
Macrows.Range("N" & i).Value = rngcell.Offset(0, 19).Value
Macrows.Range("O" & i).Value = rngcell.Offset(0, 23).Value
Macrows.Range("P" & i).Value = rngcell.Offset(0, 24).Value
Macrows.Range("Q" & i).Value = rngcell.Offset(0, 25).Value
End If
Next
End With
Else
Exit For
End If
Next
Next i
End Sub