Autofilter and return cell values using VBA

Hanes109 · May 22nd 2021

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

gijsmo · May 23rd 2021

If I've interpreted your requirements correctly, try the attached version.

Dashboard TEST #2.xlsb

**KjBox** · May 23rd 2021

An alternative method not using Autofilter

Try this

Code

Sub Add2()
    Dim sOP As String, sDP As String, sDType As String
    Dim lNum As Long, dPrice As Double, sCurr As String, lTrans As Long, dtExp As Date, dtEff As Date
    Dim x, i As Long, ii As Long
    
    With Sheet1
        sOP = .[c5]: sDP = .[c6]: sDType = .[c7]
        lNum = .[c8]: dPrice = .[c9]: sCurr = .[c10]: lTrans = .[c11]: dtExp = .[c12]: dtEff = .[c13]
    End With
    With Sheet2.Cells(1).CurrentRegion
        x = .Value
        ii = Application.Match(lNum, .Rows(1), 0)
        For i = 2 To UBound(x, 1)
            If x(i, 1) = sOP And x(i, 2) = sDP And x(i, 4) = sDType Then
                x(i, 5) = lTrans: x(i, ii) = dPrice: x(i, ii + 1) = sCurr
                x(i, 532) = dtExp: x(i, 533) = dtEff
            End If
        Next
        .Value = x
    End With
    MsgBox " Price has been Updated... !!! "
        
End Sub

Display More

Hanes109 · May 23rd 2021

gijsmo and KjBox Both macros are working perfect, This made my day. Thank you so much!!!

gijsmo - I would like to know how it works without using autofilter? Just for understanding purpose.

Also,

Just wondering whether it is possible to reference a sheet name in C4?? In which sheet the macro should work

Lets say I have PEG_3, PEG_5,_PEG_7 sheets are available. In C4, sheet name has to be selected.

If I select PEG_7 in C4, The details should be updated only on PEG_7 sheet.

Thanks again!!

**KjBox** · May 23rd 2021

I think you meant to ask me how the macro works without autofilter!

It loads all the data on the "PEG" sheet into an array (Named x). The code then loops through that array and for rows where there is a match for the OP, DP & d_type entered in the dashboard it first locates the column for the entered Number then adds the values for Price, Currency, Transit Time, Expiry and Effective Date to the array in the correct positions.

When the loop completes all iterations the values in it are written back to the PEG sheet updating it.

It is possible to amend the code to work on a particular sheet where the sheet name is entered in Dashboard Sheet Cell C4.

I will post the amended code shortly, and include some code comments to help you see what the code is doing where.

Hanes109 · May 23rd 2021

Wow, That's excellent.

Attached the sample sheet for selecting sheet name..

gijsmo · May 23rd 2021

Yeh, it's a simple change to allow the PEG sheet to be selected from the Dashboard.

Updated version is attached.

My version is using autofilter which is relatively quick and efficient (and relatively easy to follow the code too I think).

There are lots of comments in the code so hopefully this is relatively clear.

Dashboard TEST #3.xlsb

**KjBox** · May 23rd 2021

In the most recent file you posted, I see that the column headers are not consistent. So you need the code to first identify the correct column for OP, DP, d_type, Expiry & Effective date (as well as the Number Column)?

Also, you have entered sheet PEG_7 and NUMBER 35037. There is no column with "35037" as the column header, what do you want the code to do in cases such as that? A message to say that the NUMBER is invalid?

Hanes109 · May 23rd 2021

gijsmo - Thank you so much for your efforts..! Works perfect!!

@kjBox- Yes the formats change for each sheet,

"Also, you have entered sheet PEG_7 and NUMBER 35037. There is no column with "35037" as the column header, what do you want the code to do in cases such as that? A message to say that the NUMBER is invalid?"

-Please consider available header number from the sheet.

For

PEG_7 - number- 20007

PEG_5 - number - 40141

Please let me know what should I update if the format changes.

Thanks a lot.

Hanes109 · May 23rd 2021

I'm so sorry for not making it clear gijsmo. As the formats change in the updated file, Price is not getting updated on sheet PEG_5 and PEG_7. Sorry again!!

gijsmo · May 23rd 2021

Just remember for this to work on anything other than the PEG_3 sheet, the other PEG sheets need to have the columns called OP, DP, d_type, transit_time, expiry and effective_date

The macro won't do any updating if these columns do not all exist

Hanes109 · May 23rd 2021

gijsmo - oh its my miss then, sorry!!! but transit column not available on other two sheets.(PEG_5 and PEG_7) Is there anything we can do?

Hanes109 · May 23rd 2021

gijsmo and KjBox - I'm facing another scenario where I couldn't edit values. For eg, we have already entered price as 93 AUD and I have to edit it as 157 CAD.

gijsmo · May 23rd 2021

Best to specify as many requirements in one go in order to minimise re-coding.

The assumption based on the original sample was that the macro had to :

1. Autofilter PEG sheet on OP, DP, d_type

2. Find the number to be matched column (eg, 35037)

3. Update the number to be matched column with price & the adjacent column with the currency

4. Update the transit_time, expiry & effective_date columns with data from the Dashboard sheet

All the columns were located using a Range.Find method, if one or more of the columns on the PEG sheet were missing, nothing would get updated

So :

1. Is Transit Time the only column that may or may not exist on the PEG sheets ?

2. Will the column names on the other PEG sheets be the same ? eg OP, DP, d_type, etc ?

gijsmo · May 23rd 2021

Quote from Hanes109

gijsmo and KjBox - I'm facing another scenario where I couldn't edit values. For eg, we have already entered price as 93 AUD and I have to edit it as 157 CAD.

If I read this right, you just need to enter 157 as Price and CAD as Currency on the Dashboard sheet and then click on the Add button again to change the values.

Hanes109 · May 23rd 2021

1. Is Transit Time the only column that may or may not exist on the PEG sheets ? - Yes

2. Will the column names on the other PEG sheets be the same ? eg OP, DP, d_type, etc ? - Yes

Hanes109 · May 23rd 2021

If I read this right, you just need to enter 157 as Price and CAD as Currency on the Dashboard sheet and then click on the Add button again to change the values. - Yes, Exactly.

Hanes109 · May 23rd 2021

Else Can we add clear contents code in specific filtered range? So that new values will be updated.

gijsmo · May 23rd 2021

If you want to eg change 93 AUD to 157 CAD (for the same OP, DP & d_type values) then just enter 157 CAD and click on the Add button - this will overwrite the 93 AUD with 157 CAD.

If you want to clear those values then one way is to clear the Price and Currency values on the Dashboard sheet and then click the Add button. The only small issue here is that it will need a small tweak to the code to actually clear the price - the current code will set the values to 0 (zero). This tweak has been made in the version attached below.

As for the other change, the attached version will ignore Transit Time column if it is not found.

Dashboard TEST #4.xlsb

**KjBox** · May 23rd 2021

Your Sheet PEG_5 has "DP" in Column A, "d_type" in Column B, no "Transit Time" (that can be ignored if not present) then "OP" in Column KO but there are no values in that column. There are values in Column KP which has "service_type" as a header, those values look as if they should be the OP values and should be in Column KO! Is that correct?

As for the NUMBERS, PEG_3 all start with a "3". PEG_5 with a "4" and PEG_7 with a "2", is that correct?

Autofilter and return cell values using VBA

Participate now!

Share

Tags