VBA code help

markster

Well-known Member
Joined
May 23, 2002
Messages
561
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
hi its a little bit clunky, but it will work :)
VBA Code:
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

Well-known Member
Joined
May 23, 2002
Messages
561
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
May 23, 2002
Messages
561
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Thanks mate - much appreciated.
 

Forum statistics

Threads
1,141,818
Messages
5,708,766
Members
421,588
Latest member
Wawie

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
Top