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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

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