Posts by JonathanVH

The analogue to SUBSTITUTE in VBA is Replace. I assume that the "jumbling" is when it substitutes part of another word (which would be unusual in your example). If you need more sophisticated parsing, so that it could recognize when text is a whole word or part of another, then you could instantiate a (VBScript) RegExp object.

Try this for Completed:

=SUMPRODUCT((D6:D19<>"")*(B6:B19=D6:D19)*(C6:C19="y"))

Okay, you made me download his file. I see that he is looking in column B for the blanks (note the name of my above Sub). I also noticed that he had a typo in the fourth range address.

Sub RemoveBlankBRows()
Dim rngs As Variant, n As Long, r As Long, c As Long
  Application.ScreenUpdating = False
  rngs = Array("A11:J45", "A55:J89", "A99:J133", "A143:J177", "A187:J204", "A215:J232")
  For n = UBound(rngs) To 0 Step -1
    With Range(rngs(n))
      c = .Rows.Count
      For r = c To 1 Step -1
        If Trim$(.Cells(r, 2).Text) = vbNullString Then .Cells(r, 1).EntireRow.Delete
      Next
    End With
  Next
  Application.ScreenUpdating = True
End Sub

But using hard-coded addresses for what are obviously dynamic ranges is not a realistic methodology. Again, consider using tables (ListObjects in VBA) for these orders because then the code can adapt for the number and sizes of orders. Better yet, have just one Excel order range for doing the formatting and fill that from orders in a database.

You never mentioned anything about cell colors. I thought you had recorded a macro to delete the relevant cells. So you now want to clear the contents (but not the formatting?) of cells that are a particular color? That cannot be done with the macro recorder. Your code is missing the For statement to iterate through the cells on each sheet.

So I thought about this, and I would still use SQL. This groups by Branch, Item Number, and Month, and does not report any items that do not go negative.

Sub SummaryRept()
Dim aCn As Object, aRs As Object, d As String, sql As String, f As Long
  Set aCn = CreateObject("ADODB.Connection")
  Set aRs = CreateObject("ADODB.Recordset")
  With ThisWorkbook
    d = .Path & "\" & .Name
  End With
  With aCn
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & d & "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
    .Open
  End With
  sql = "SELECT Branch, [Item Number], MIN(Start) As [Date Negative], '' As [Month], MIN(Available) As [Max Qty Negative] " & _
        "FROM [Sheet1$] " & _
        "WHERE Available < 0 " & _
        "GROUP BY Branch, MONTH(Start), [Item Number];"
  aRs.Open sql, aCn, 3, 1, 1


  With Sheet2.Range("A1")
    For f = 1 To aRs.Fields.Count
      .Cells(1, f) = aRs.Fields(f - 1).Name
    Next
    .Offset(1).Resize(aRs.RecordCount, aRs.Fields.Count).CopyFromRecordset aRs
    .Offset(1, 3).Resize(aRs.RecordCount).FormulaR1C1 = "=TEXT(RC[-1],""mmmm"")"
    .Offset(1, 4).Resize(aRs.RecordCount).NumberFormat = "General"
  End With
  aCn.Close
  Set aCn = Nothing
  Set aRs = Nothing
End Sub

This is hard-coded that the data is at the top left of Sheet1 and the summary report is created on Sheet2.

So the first date in each month that has a negative value? Perhaps you could revise your example of the "data dump" to include multiple branches and months and then show what the result set from that should look like.

As it uses the Text property of the (cell) ranges, it will delete any row that looks as though it has a blank in the first column. Actually, I think I'd add a Trim to that, so change that row to:

If Trim$(.Cells(r, 1).Text) = vbNullString Then .Cells(r, 1).EntireRow.Delete

in case those cells contain spaces and still look blank.

Ah, okay. I thought you were using a Data Connection to a SQL database, in which case one could use something more than the default SELECT *, particularly if the SQL language supported SQL window functions.

Should this be this grouped by only Item Number or is it also grouped by Branch and/or Month?

I would create the that result set using SQL, not Excel. What variant of SQL (e.g., Oracle, SQL Server, MySQL)?

You're not using the right tools for the job. Sure, use Excel to format and display your data, but use a database, not Excel, to store your data.

Rather than the interesting code above, I suggest something less abstruse.

Sub RemoveBlankARows()
Dim rngs As Variant, n As Long, r As Long, c As Long
  Application.ScreenUpdating = False
  rngs = Array("A11:J45", "A55:J89", "A99:J133", "A143:J147", "A187:J204", "A215:J232")
  For n = UBound(rngs) To 0 Step -1
    With Range(rngs(n))
      c = .Rows.Count
      For r = c To 1 Step - 1
        If .Cells(r, 1).Text = vbNullString Then .Cells(r, 1).EntireRow.Delete
      Next
    End With
  Next
  Application.ScreenUpdating = True
End Sub

What's a "data dump?" Is that a fixed-width file output from some other system?

Cell A11 has a List validation using range A3:A7.
Cell A12 has this formula: =VLOOKUP($A$11,$A$3:$B$7,2,FALSE)
Cell B11 has: =INDEX($A$3:$A$7,MATCH(B$12,$B$3:$B$7,0))
Cell C11 has: =INDEX($A$3:$A$7,MATCH(C$12,$B$3:$B$7,0)) so it can just be copied from B11
Cell B12 has: =INDEX($B$3:$B$7,MATCH(SMALL(ABS($B$3:$B$7-$A$12),2),ABS($B$3:$B$7-$A$12),0)) entered as an array formula so it displays as {=INDEX($B$3:$B$7,MATCH(SMALL(ABS($B$3:$B$7-$A$12),2),ABS($B$3:$B$7-$A$12),0))}
Cell C12 has: =INDEX($B$3:$B$7,MATCH(SMALL(ABS($B$3:$B$7-$A$12),3),ABS($B$3:$B$7-$A$12),0)) entered as an array formula
Cell A14, used as the chart title, has: ="Cities Closest in Population to "&$A$11
The chart uses range A11:C12. To extend the chart to show more cities, copy C11:C12 to the right and then edit the copied formulas in row 12 to increment the number used with the SMALL function. Save as arrays after editing.

[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"CitiesPop.JPG","data-attachmentid":1207430}[/ATTACH]

If you can run your macro by clicking run from that View Macros menu, then it should also run from a button. Use a Forms Controls button, not an ActiveX Controls Command Button.

That error sounds as though you attempted to edit what the macro recorder created. The macro recorder does not "record keystrokes." It converts any relevant actions you do and creates VBA code to get to the same result. E.g., if you click on a cell, it does not record your moving the mouse and clicking; it instead writes the VBA code for that action: something like "Range("C5").Select."

That should be

Array("RED", "GREEN", "BLUE")

of course.:0ops:

Macros and Visual Basic for Applications (VBA) are two ways of saying the same thing. Using the Macro Recorder writes a program in VBA. The VBA code generated by the Macro Recorder is inelegant but it should work for this. You can then use Design Mode to add a button to a sheet and assign your generated macro to that button.

Not an array; an array formula. Click the link in my reply.

It is possible to create an Excel array formula to find the city with the closest, next closest, etc. population from a range like I have in A3:B7. It is quite an advanced technique, though, similar to this.

Yours should work correctly in cell E2 only, because you need to specify an absolute reference (those dollar signs) for the table_array range argument.

Well, if the ranges can contain actual empty cells, then you will need to either explicitly define each separate range (i.e., the code will not dynamically adjust) or have some guaranteed way to identify header rows (and be okay with deleting all blank rows between rows.

I just opened your file... Why not just use tables for those ranges? Then it would be simple to iterate through the sheet's Tables collection (and it would also be easier to hide or delete blank rows).