VBA Search for match, if found, clear content in match rows

Engalpengal

New Member
Joined
May 10, 2023
Messages
43
Office Version
  1. 365
Platform
  1. Windows
Hello.
I am working on a project, where i want to track products in our factory.
The process start by me loading orders in a workbook called "Ordre"
It is a list of products with information spread out in rows C:P - See Pic 1
When a process is done, a date is filled in Q:AF depending on witch prosess that is completed (VBA program). These columns is hidden. - See Pic 2
In column AH:AV I have excel formulas that shows If the product should go throug the process (No=x, Yes=Process desc ex. PR), or if the process is done(Done=V). - See Pic 3

Ex of formulas:
=IF(C22<1;"";IF(AE22>1;"V";XLOOKUP(F22;BOM!A:A;BOM!AC:AC;"x";0)))

With a click of a button all Product lines With "V" in column AV (the last process) is copied into av workbook called History.

Now i need to cleare (ClearContents) the same lines that was copied into History, from workbook "Ordre".

Under follows my attempt.
The program does not give me an Error, but nothing happens either, other than the workbook is thinking.
So what am i doing wrong?

VBA Code:
Sub Fjern_ferdig()

unprotect
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim wbo As Workbook
Dim wsSrc As Worksheet
Dim i As Integer

Set wbo = ThisWorkbook
Set wsSrc = wbo.Worksheets("Ordre") '______________________________________________________Sheet for search area
Set srcKey = wsSrc.Range("AV8") '___________________________________________________________Search key (The cell contains the letter V)

finalrow = wsSrc.Range("AV1000").End(xlUp).Row '__________________________________________Setting search area (These cells contains excel formulas)

For i = 48 To finalrow
    If Cells(i, 48) = srcKey Then
        wsSrc.Range(Cells(i, 3), Cells(i, 32)).ClearContents
    End If
Next i

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
protect
ActiveWorkbook.Save

End Sub

Sub unprotect()
ActiveSheet.unprotect "1234"

End Sub

Sub protect()
ActiveSheet.protect "1234"

End Sub
 

Attachments

  • Pic 1.jpg
    Pic 1.jpg
    148.3 KB · Views: 30
  • Pic 2.jpg
    Pic 2.jpg
    174.9 KB · Views: 28
  • Pic 3.jpg
    Pic 3.jpg
    64.8 KB · Views: 25
I understand Kevin. Its all on me.
Im the one thats not providing with correct information. Sorry about that 😓
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,224,822
Messages
6,181,164
Members
453,021
Latest member
Justyna P

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