Hi All,
I have a VBA code that applies filters the required cells from Sheet "Peg_3" . Then locates/finds the header "35001" (which referenced in Dashboard sheet) and update the price and its currency in next column (35001 +1) . However the code updates header names (35001) instead of price and currency. (Kindly refer image "Incorrect Output")
Please tell me what I should update in the code to reflect the required output.
Your thoughts are highly appreciated.
Attached the code, required output and sample workbook for reference.
Code
Sub Add()
Dim op As String
Dim dp As String
Dim d_type As String
Dim dataRange As Range
Dim colRange As Range
Dim FirstFilteredRow As Range
Dim x As Long, y As Long
Dim j As Long
Dim last As Long
Worksheets("Dashboard").Select
op = Range("C5")
dp = Range("C6")
cargo_type = Range("C7")
Worksheets("PEG_3").Select
Worksheets("PEG_3").Range("A1").Select
If Worksheets("PEG_3").Range("A1").Offset(1, 0) <> "" Then
Worksheets("PEG_3").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = op
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = dp
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = d_type
Worksheets("Dashboard").Select
Worksheets("Dashboard").Range("C4").Select
last = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Sheet2
.Range("A1:TO" & last).AutoFilter Field:=1, Criteria1:=Sheet1.Range("C5").Value, Criteria2:=Sheet1.Range("C6").Value
' After Filter, determine the First and Last Filtered Row Numbers x and y
Set dataRange = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
Set FirstFilteredRow = dataRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Areas(1).Rows(1)
x = FirstFilteredRow.Row
y = .Range("A100000").End(xlUp).Row
' Locate Charge Number Sheet2.Range("C7").Value in Sheet1 - Row 1 to find Column
Set colRange = Sheet2.Rows("1:1").Find(Sheet1.Range("C8").Value)
j = colRange.Column
' Copy New Values to Visible Cells Only
.Range(.Cells(x, j), .Cells(y, j)) = Sheet1.Range("C9").Value
.Range(.Cells(x, j + 1), .Cells(y, j + 1)) = Sheet1.Range("C10").Value
' Remove AutoFilter
.Range("A1:TO" & last).AutoFilter
End With
Application.ScreenUpdating = True
MsgBox " Price has been Updated... !!! "
End Sub
Display More