VBA - If match, fill in Date and time in cell

[Engalpengal]

Hello
I need help from your clever minds....again.
What I want is the following-

Areas:
Search key: Sheets("PL").Range("J7")
- This is a order no that is manualy typed in

Search area: Sheets("Ordre").Range("C13:C2000")
- Ordre->Listed product lines, Column "C" is the first culumn in product row, last column is "P"
- There can be more than one product line with the same order no

Task:
I want the program to look through Sheets("Ordre").Range("C13:C2000") and see if there is a match between "Search key" and the listed products (can be more then one hit).
If there is a match, the program needs to fill inn "Today`s date and time" in the same matched row, column "AK"
When this is done, i also want the program to clear the value in Sheets("PL").Range("J7"). The cell formatting must be intact.

unfortunately, i can not share the content of the excel sheets. The reason is that there is some information that is sensitive.

Hope that the info i have granted is good enough for you to understand the issue and help me out with a solution

 

[Skyybot]

Try this on a copy of your Workbook as it may cause unexpected results.

Sub getMatch()
Dim wb As Workbook, wsPL As Worksheet, wsOrd As Worksheet
Dim srchRange As Range, found As Variant, foundNext As Variant, start As Variant
srchKey As String
Set wb = ThisWorkbook
Set wsPL = wb.Worksheets("PL"): Set wsOrd = wb.Worksheets("Ordre")
srchKey = wsPL.Range("J7")
Set srchRange = wsOrd.Range("C13:C200")
start = srchRange.Find(srchKey).Address
found = start
Do
foundNext = srchRange.FindNext(Range(found)).Address
found = foundNext
foundRow = Right(foundNext, Len(foundNext) - InStr(2, foundNext, "$"))
ws.Cells(foundRow, "AK") = Date
Loop Until start = foundNext
wsPL.Range("J7").ClearContents
End Sub

 

[Peter_SSs]

unfortunately, i can not share the content of the excel sheets. The reason is that there is some information that is sensitive.

If the suggestion above does not do what you want, surely it would not be too hard to create a small dummy data set with any sensitive data replaced by non-sensitive data?

 

[Engalpengal]

Hello again and thanks for the response. The above programming gave some Error. Will it help if i share a print screen? We are not allowed to download any programs thats not been through security check (that can take months).

 

[Engalpengal]

Last page

 

[Engalpengal]

Try this on a copy of your Workbook as it may cause unexpected results.

Sub getMatch()
Dim wb As Workbook, wsPL As Worksheet, wsOrd As Worksheet
Dim srchRange As Range, found As Variant, foundNext As Variant, start As Variant
srchKey As String
Set wb = ThisWorkbook
Set wsPL = wb.Worksheets("PL"): Set wsOrd = wb.Worksheets("Ordre")
srchKey = wsPL.Range("J7")
Set srchRange = wsOrd.Range("C13:C200")
start = srchRange.Find(srchKey).Address
found = start
Do
foundNext = srchRange.FindNext(Range(found)).Address
found = foundNext
foundRow = Right(foundNext, Len(foundNext) - InStr(2, foundNext, "$"))
ws.Cells(foundRow, "AK") = Date
Loop Until start = foundNext
wsPL.Range("J7").ClearContents
End Sub

FIrst i god an error on following line:
srchKey As String --> Compile error: Statement invalide outside Type Block
I typed Dim in front

Then i got an arror at following line:
ws.Cells(foundRow, "AK") = Date --> Run-time error '424' Object required

What do you recommend doing here?

 

[Engalpengal]

I got it. just needed a bit tuning. Thank you so much Skyybot!!