Hello community,
I'm looking for someone who can turn the automated features I created using VBA in desktop excel into the Office Scripts equivalent. I can shoot you $10 via venmo, paypal, zelle, whatever you have beforehand for trust's sake.
So, in my excel doc, I have vba set to automatically perform macros when it is opened. I here you can just schedule automations online. So, it doesn't specifically have to be upon open in sharepoint.
There are two sheets. I have it currently set up to search Sheet 1 (List of UPCs) in Column D (Start Date) for any row that has a date that has passed. I have it set up to do two things. 1st, it transfers the entire row of all whose date has passed to the next available row on Sheet 2 (Historical Data), and 2nd, it clears columns C through E of those same rows on Sheet 1.
I also have a text box set to come up to remind the user to add formula =today() to any blank column Es as my code was contingent upon today's date.
If you can translate my code to the equivalent Office Scripts automation, I can pay $10. If you improve upon my process, I will tip you more!
Please help! Attached is my excel doc, and below is the VBA code I wrote for it before I found out it wasn't supported in the online version.
Private Sub Workbook_Open()
'Declaring variables
Dim ws As Worksheet
Dim wt As Worksheet
Dim i As Long
Dim Lastrow As Long
Dim NewRecordsRow As Long
Application.ScreenUpdating = False
Set ws = Worksheets("List of UPCs")
Set wt = Worksheets("Historical Data")
Lastrow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
NewRecordsRow = wt.Range("B" & wt.Rows.Count).End(xlUp).Row
For i = 2 To Lastrow
If ws.Cells(i, "E").Value < Date Then
NewRecordsRow = NewRecordsRow + 1
ws.Cells(i, "E").EntireRow.Copy Destination:=wt.Cells(NewRecordsRow, 1)
ws.Range(ws.Cells(i, 3), ws.Cells(i, 5)).Clear
End If
Next i
Application.ScreenUpdating = True
MsgBox "Fill in any blank Column E fields with the formula =today(). This ensures certain functions act accordingly."
End Sub
