VBA code help

[markster]

Hello - I wondered if anyone could help me with some macro report to convert a couple of aspects of a report into something I can work with. I’ve been trying to achieve what I want with formulas but it’s just not practicable due to the ever increasing lines of data that appears the report 60,000 plus (takes ages to open and formula calculation causes it to lag. As such it would be great if someone could provide me some macro code that I can run on the report to do the following:

Date & Time

There is a date & time In COLUMN B (B7 onwards) displayed like this 2021.05.10 01:00:01.

I need the macro to look at this column and put the date only in the DD-MMM-YY format into Column Q (from Q7 onwards) and then add the time into COLUMN R (R7 onwards) the thing with the time is that I want it to enter the time rounded down to the nearest hour - 2 hours (time difference) i.e. 13:07, 13:25, 13:45, 13:55 would show as 11:00 (actual time rounded down -2). 10:07, 10:45, 10:57 would be displayed as 08:00 (actual time rounded down-2)

Status

If COLUMN I (from I7) is populated I want it to add CLOSED to COLUMN P (from P7 onwards)
If COLUMN I (from I7) is blank I want it to add OPEN/ORDER to COLUMN P (from P7 onwards)

Thanks in advance and let me know if any clarification is needed.

Thanks
Mark

 

[diddi]

hi its a little bit clunky, but it will work

Sub ParseDates()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Row = 7 To Cells(Rows.Count, "B").End(xlUp).Row
        temp = Replace(Cells(Row, 2), ".", "/")
        Cells(Row, 18) = Val(Mid(Cells(Row, 2), 12, 2)) - 2
        Cells(Row, 17) = Format(Left(temp, 10), "DD-MMM-YY")
        If Cells(Row, 18) < 0 Then
            Cells(Row, 18) = 23
            Cells(Row, 17) = Cells(Row, 17) - 1
        End If
        If Cells(Row, 8) <> "" Then
            Cells(Row, 16) = "Closed"
        Else
            Cells(Row, 16) = "Open/Order"
        End If
    Next Row
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationManual
End Sub

 

[markster]

Mate it works a treat - nothing wrong with clunky when it saves a heap of time for me. Can I be cheeky and ask if you can help me one additional bit of code which will then mean all of my manual processing is cut out?

Basically the customer number is contained in COLUMN K (from K7 onwards) on a Tab labelled Sheet 1. I have a list of customer numbers on a separate tab (tab name is Excluded) The customer numbers are in COLUMN A from A2 onwards. Basically, what I would want it to do would be to look at the two lists and for any Excluded customer numbers on the EXCLUDED TAB to delete that row from Sheet 1

For example customer number C123456 appears in COLUMN A on excluded tab. Customer C123456 is likely to have several entries on my Sheet 1 in COLUMN K so for any customer numbers that appear in the excluded list it deletes all row entries for that customer on Sheet 1. So in this example customer C123456 appears in 36 rows of data through the report so those 32 lines would need to be deleted as would any entries for any other customer numbers on the excluded list. No probs if you don't have time but thought I'd ask anyway.

Cheers
Mark

 

[diddi]

i will take a look for you. that requires a different approach to run with any speed, so i will check later today when i have a moment spare

 

[markster]

Thanks mate - much appreciated.