Still Quite new to VBA - Trying to Build a Macro to Clean Up Order Forms

[Andrew_UK]

Hi All,

First up I love these forums, I'm a bit of an excel geek and I love to learn. I'm still quite new to VBA and am really enjoying learning - but I wouldn't claim to fully know what I'm doing with it yet :P

I'm currently trying to build a code to clean up orders which come in for our company and put them into a format which people can upload. Sometimes they come back in a nice neat format but sometimes customers have played around with the sheets to the point that they need to be uploaded manually. I've got bits of this code built but there's bits I'm stuck with. For ease of reading I've highlighted in red the bits which I'm still stuck on. Also caps used selectively only to indicate that this is unfinished code.

Step1: Check that the Macro hasn't already been run, rename the sheet, remove Filters and unhide columns

Sub Tidy_Order_Form()'
' Tidy_Order_Form Macro
'
[COLOR=#333333]Dim wsSheet As Worksheet[/COLOR]
[COLOR=#333333]On Error Resume Next[/COLOR]
[COLOR=#333333]Set wsSheet = Sheets("Upload Sheet")[/COLOR]
[COLOR=#333333]On Error GoTo 0[/COLOR]
[COLOR=#333333]If Not wsSheet Is Nothing Then[/COLOR]
[COLOR=#333333]MsgBox ("Macro Already Run")[/COLOR]
[COLOR=#333333]Else[/COLOR]
[COLOR=#333333]End If[/COLOR][COLOR=#333333]
ActiveSheet.Name = "Raw Order Form"
[/COLOR]If ActiveSheet.AutoFilterMode Then
Cells.AutoFilter
End If
Cells.Select
Selection.EntireColumn.Hidden = False

Step2: If the document contains any errors then display a box asking them to contact their supervisor

I can write it with a Find exact match for #N/A, #DIV/0!, #Name?, #REF!, #VALUE!, #NUM! - but there must be a neater way of handling this?

If *Above conditions are met, then
[COLOR=#333333]MsgBox ("Please Contact Supervisor")
End Sub
[/COLOR]endif

Step3: Scan document to see if we can find a row where "Code", "Description" and "Quantity" all exist - I'm guessing this can be done using a countrows formula? Except that the column headers could be in different places on different sheets...

If this row doesn't exist (or if it exists more than once return)

[COLOR=#333333]MsgBox ("[/COLOR]Column headers not found[COLOR=#333333]")
[/COLOR]End Sub
Endif

Step4: check the rest of the document for these phrases ("Code", "Description" and "Quantity") - if they appear anywhere else (other than in one nice neat row next to each other then

[COLOR=#333333]MsgBox ("[/COLOR]Headers found in multiple places[COLOR=#333333]")
[/COLOR]End Sub
Endif

Now if we've got as far as this point we're ready to start processing. So I need to grab everything from the cell below these column headers to the bottom of the document. I like the lastrow formula as it could be that some data in certain cells is missing...

Step5: Order Processing Bit

lastrow = Columns("A:AZ").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Then take all that data and dump it into a new worksheet using the advanced filter

Sheets.Add.Name = "Upload Sheet"
 
    Worksheets("Upload Sheet").Range("A1").Value = "Code"
    Worksheets("Upload Sheet").Range("B1").Value = "Description"
    Worksheets("Upload Sheet").Range("C1").Value = "'Quantity"
    Worksheets("Upload Sheet").Range("A2").Value = "<>"*""
    Worksheets("Upload Sheet").Range("B2").Value = "<>"*""
    Worksheets("Upload Sheet").Range("C2").Value = ">0"
   
    Sheets("Raw Order Form").Range([COLOR=#ff0000]"REFER TO THE STUFF BELOW CODE/DESCRIPTION/QUANTITY"[/COLOR]).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Upload Sheet").Range("A1:C2"), _
    CopyToRange:=Sheets("Chilled 100815").Range("A3:C3"), _
    Unique:=False

Sheets("Upload Sheet").Select
Rows("1:2").Select
Selection.Delete Shift:=xlUp

ActiveWorkbook.SaveAs Filename:= _
[COLOR=#ff0000]        "ACTIVE USER\My Documents\Book1.csv", FileFormat:= _[/COLOR]
[COLOR=#ff0000]        xlCSV, CreateBackup:=False[/COLOR]
End Sub

How do I get the file to save to the "my documents" of whomever is running the Macro?

Thanks in advance for your help!!

 

[Andrew_UK]

I'm 80% done now with this. It's only really the Order Processing section I'm still stuck with.

The code cell is recorded in my VBA as being row number a and column number d. How can I select this cell using range?

I've tried Range("d"&"a").Select but of course it's expecting letter, number

I've tried selecting cell A1 and then doing

Range(R[a]C[d]).Select but it seems to not like that either.

Any ideas?

 

[V_Malkoti]

With row number a and column number d (e.g. a=5, d=10), you can select that cell as

Sheets("sheetname").Cells(a, d).Select

To get path of My Documents of currently logged in user:

strPath = CreateObject("Wscript.Shell").SpecialFolders("MyDocuments")

 

[Andrew_UK]

Thank you!! Very helpful I'll put it in to practice in the morning.

The first bit selecting the cell looks like it will work for selecting 1 cell but what it really needs to do is select a range of cells from cell(a,d) to cell(lastrow,d)

 

[V_Malkoti]

For that

Range(cells(a,d), cells(lastrow, d)).select

And you shouldn't really need to select cells to perform operation on them. You can directly call the range operation on them. For example

Range(cells(a,d), cells(lastrow, d)).select
Selection.EntireRow.Delete

and

Range(cells(a,d), cells(lastrow, d)).EntireRow.Delete

do the same thing.