Convert VBA Code to Formulas in Excel

dannie

New Member
Joined
Dec 29, 2014
Messages
34
Basic need is I need my macro turned into formulas or something that can be on the spreadsheet without macros. Work is getting rid of the ability to use macros (disabling all), so I need my codes to work without being codes. I have tried different formulas (find, if, vlookup, etc) and I can't find something that works out completely or easily and user friendly. We are used to having it "easy" with macros, but soon will not have that luxury. I am trying to avoid just copying and pasting and sorting individually (human error is a thing when the spreadsheet is used by a dozen people throughout the day).

What I Want: If first cell in row is value "PD", then cut/paste to sheet named "PD", and then sort by date column/time column.

Any help is appreciated. Thank you in advance!

Example of code below:

If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

If Target.Value2 = "PD" Then
Lastrow = Sheets("PD").Cells(Rows.Count, "A").End(xlUp).Row + 1
Rows(Target.Row).Copy Destination:=Sheets("PD").Rows(Lastrow)
Application.EnableEvents = False
Rows(Target.Row).Delete
Application.EnableEvents = True
End If
End If

Dim ws As Worksheet

For Each ws In Sheets
If ws.Name <> "OS" Then
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("I4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ws.Sort.SortFields.Add Key:=Range("J4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A4:Y42")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
Next ws
For Each ws In Sheets
If ws.Name = "OS" Then
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=Range("K4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ws.Sort.SortFields.Add Key:=Range("L4"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange Range("A4:Y42")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
Next ws
End Sub

-dannie
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,659
Office Version
  1. 2016
  2. 2003 or older
Platform
  1. Windows
Hi,
SORT functions are now available in Microsoft 365. (I don't have that version with me, though.) That function makes it easy to sort the data without using working cells. Better provide us with your information on what version of Excel you are using at work.

Information about SORT function.

 

dannie

New Member
Joined
Dec 29, 2014
Messages
34
Currently on Excel 2016, will be moving to Microsoft 365 (where macros will not be supported).

We have sheet1 as a template, and fill in information from Column A to Column Y. Whatever word is put into Column A determines which sheet it goes to for processing and then sorting on determined sheet.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,704
Office Version
  1. 365
Platform
  1. Windows
Currently on Excel 2016
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

will be moving to Microsoft 365 (where macros will not be supported).
365 supports VBA if you will be using the desktop app.
 

dannie

New Member
Joined
Dec 29, 2014
Messages
34
Thanks for the help. I do understand that macros work with the new Microsoft, but my job going forward is not allowing macros for security reasons.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,704
Office Version
  1. 365
Platform
  1. Windows
As you are deleting the PD rows from the sheet, there is no way of using a formula.
 

Forum statistics

Threads
1,176,096
Messages
5,901,373
Members
434,887
Latest member
zoath

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