Posts by BG1983

  • Receiving error codes with using user form to rearrange and remove columns

    Re: Receiving error codes with using user form to rearrange and remove columns


    just thought about it and I guess it would actually be 4 tiers... first tier would be a list of all of the columns (not sure if I mentioned this but it needs to be dynamic
    in the sense the number of columns will vary.. and the date fields, etc. would also vary from column to column depending on the data in the spreadsheet...


    Tier1 - All Columns (Individual Selections to choose how to filter)



    Add / Remove Box Add/Remove Add/Remove
    Tier2 - Select which filter items (duplicates) you want (Checkbox) / Date1 (Text Field) Date2 (Text Field) / Blank or Not Blank (Select which)




    Tier 3 Add Add Add
     Table would be filtered in the background at this point and the selections would be added here




    Tier 4 Choose the order of the columns (Move up, down?)
    Remove any columns you do not want




    Send to Spreadsheet Select "Go" and create new worksheet with the
    then clear original table filtered / arrange / visible cells data
    filters

  • Receiving error codes with using user form to rearrange and remove columns

    Re: Receiving error codes with using user form to rearrange and remove columns


    Ideally yes, I would want an option to filter the data but if no filters are selected, then it would move everything.


    The filters would be
    1.) - Duplicate text one column (so, just the same if you were to filter a table) --- i was thinking this would be a check box field
    2.) - date range for the columns that only contain dates (there are never any columns that have a mix of dates and text) --- was thinking this would be 2 fields for the date range
    3.) - Blank or not Blank


    So, my original plan was to have


    1.) list of all fields (tier 1) --- you would select which ones you wanted to move and then in "tier 2" of the report you would have 3 separate choices of where to add the item from tier 1. Those choices would be the 3 filtering options I listed above
    -
    2.) 3 separate areas to add those fields to, with 3 separate add/remove areas because of the 3 different filter options (this would be what i am referencing as tier 2)
    - from there you would filter accordingly and again select "add" which would move the data to the final area prior to selecting "Go" or something where it creates the new sheet. (I would imagine all of the filtering could be done in the background as the user is making their choices.. then the "Go" button would just be moving all visible cells...


    3.) tier 3 would be the final selection... which is the option to remove columns or rearrange the column order



    I am not even sure if this is doable but it made sense to me because of like I said, the filtering as you go in the background..

  • Receiving error codes with using user form to rearrange and remove columns

    Re: Receiving error codes with using user form to rearrange and remove columns


    Well, my original thought was to also have the functionality to both filter out criteria as well as rearrange/remove columns (then export to sheet2) but I couldnt figure out how to do both at once... so i guess my question is do you know how to do that? And if not, what do I need to change to just rearrange/move only the selected columns? Below is the full code for that userform... (minus the trigger in the module..)


  • Receiving error codes with using user form to rearrange and remove columns

    Re: Receiving error codes with using user form to rearrange and remove columns


    its not that its an error - its that it is not copying the entire column.. its copying row 1 only... also, there is an 'error on resume next' line in there because for some reason autofilter doesn't like tables... it ends up converting it back to a range on its own so I just added a line to remove the table at the beginning.. and the on error resume next in case its not a table yet.

  • Receiving error codes with using user form to rearrange and remove columns

    Re: Receiving error codes with using user form to rearrange and remove columns


    I sent the workbook - if you press control d to open the userform, then click on "open column editing tool", then move some columns, then press filter


    ***Note - the only tab that can be on the workbook is sheet1 and then results will go into sheet 2, which is created at the beginning of this particular sub

  • Receiving error codes with using user form to rearrange and remove columns

    I am just a dynamic userform... so, want it to be able to
    remove columns/rearrange them
    filter by criteria with checkboxes, then have the option to filter by date ranges for those columns that have dates, etc.
    Option to build your own pivot table with the area for them to choose which filters to add where.


    I have a lot of it well under way.. but ive hit a road block at the column deleting/sorting. The form I am having trouble with the code is the one titled "removereorder"
    It is bringing over the correct columns but it is only bringing over the header...


    any help or suggestions would be very much appreciate!



    forum.ozgrid.com/index.php?attachment/68994/

  • Conditional Formatting Non Blank Cells n Pivot Table

    Re: Conditional Formatting Non Blank Cells n Pivot Table


    So I Used this... Just need to get it to not apply to the first column , last row, or first row. Can anyone tell me how to make the fill of these ranges white?


    Code
    Sub Fill()    With ActiveSheet.PivotTables("PivotTable2")
        For Each rCell In Range("B3").CurrentRegion
            If rCell.Value <> "" Then
                rCell.Interior.Color = vbRed
            Else: rCell.Interior.Color = vbWhite
            End If
        Next rCell
    End With
CurrentRegion.Select
End S

  • Conditional Formatting Non Blank Cells n Pivot Table

    Hi, I am trying to figure the VB code to highlight or Fill all Non-Blank Cells within a pivot table range (range is different each time I run the macro but the pivot table name will always be "PivotTable1" and the worksheet its on will always be named "Who's On 1st".


    So, the columns that this will not int he pivot table that this will not need to apply to is the first column, the first row, and the last row (Column Label, Row Label, and Grand Total)


    Color to highlight/Fill = 204


    Anyone know how to do this??

  • Copy formula across row with dynamic range

    Hi, I have a table (Table1) starting at row 17 and ends at different #s depending on the workload for that day. Columns will always be from A:AT. In row 16, just above the table, I am trying to figure out the best VBA formula to count the number of non blank cells in each column and display that total for each column.. so, in A16,B16,C16, etc...
    I was using the formula =SUBTOTAL function but am running into problems... Any help on that would greatly be appreciated!


    Another random question: I made a custom slicer (colors) and then applied it to my 6 slicers in the worksheet but noticed that the VBA code for doing that (with record) is ridiculously long... is there a more efficient way to do that? I was duplicating SlicerStyleDark1 , then modifying it (Now SlicerStyleDark2 after the duplication) to have the "Whole Slicer" background black with a font of light blue, and then changing the header font to light blue. Obviously I can just leave the slicer as is, but any suggestions there would be great... just dont know and havent seen how to do that before.


    Thanks!

  • Getting Compile Error - Expected Range or Function right after updating to Excel 2016

    Hi All, so I have been working on this macro for the past day and I happened to also update to Office 2016 today. Now, I am getting a compile error = Expected Variable or Function for every one of my Range or Selection code lines... does anyone know if something changed... or what is happening here? here it the code... first error occurs at line "Range(Selection, Selection.End(xlDown)).Select" right after the Range("Q24").Select line


    Code
    [/COLOR][COLOR=#333333]Option Explicit[/COLOR]Sub Hearings()'    Application.ScreenUpdating = False    Application.StatusBar = False    Application.DisplayAlerts = False    Application.EnableEvents = False    Application.Calculation = xlCalculationManual'    Sheets("FLHearingsMaster").Select    Range("Q24").Select    Range(Selection, Selection.End(xlDown)).Select    Range(Selection, Selection.End(xlToRight)).Select    Selection.ClearContents'    Dim a, i As Long, myMin As String, AMPM As String, m As Object    With Range("J24", Range("J" & Rows.Count).End(xlUp))        a = .Value        With CreateObject("VBScript.RegExp")            .IgnoreCase = True            .Pattern = "(\d{1,2})( *([ap]m)|:(\d{2}) *([ap]m)?|(\d{2}) *([ap]m))"            For i = 1 To UBound(a, 1)                If TypeName(a(i, 1)) = "Double" Then a(i, 1) = _                Format$(a(i, 1), "hh:mm am/pm")                If .test(a(i, 1)) Then                    Set m = .Execute(a(i, 1))(0).submatches                    myMin = m(3) & m(5)                    If myMin = "" Then myMin = "00"                    AMPM = m(2) & m(4) & m(6)                    If Trim$(AMPM) = "" Then                        Select Case Val(m(0))                        Case 8 To 11: AMPM = " AM"                        Case Else: AMPM = " PM"                        End Select                    End If                    If Trim$(AMPM) = "" Then AMPM = " AM"                    a(i, 1) = m(0) & ":" & myMin & " " & AMPM                Else                    a(i, 1) = ""                End If            Next        End With        With .Columns(-4)            .Value = a: .NumberFormat = "h:mm AM/PM"        End With    End With    Range("M1:N1").Value = Now'    Range("I24").Select    Range(Selection, Selection.End(xlDown)).Select    Selection.Copy    Range("Q24").Select    ActiveSheet.Paste    Range("R24").Select    ActiveSheet.Paste    Range("S24").Select    ActiveSheet.Paste    Range("T24").Select    ActiveSheet.Paste    Range("U24").Select    ActiveSheet.Paste'    ActiveSheet.ListObjects("Table_FLHearingsMaster").Range.AutoFilter Field:=2, _    Criteria1:=Array("Broward", "Miami-Dade", "Palm Beach"), Operator:=xlFilterValues    Range("Table_FLHearingsMaster[[#Headers],[Office Coverage]]").Select    ActiveCell.FormulaR1C1 = "FTL"    Range("Table_FLHearingsMaster[[#Headers],[FTL]]").Select    Selection.Copy    Range(Selection, Selection.End(xlDown)).Select    ActiveSheet.Paste    Application.CutCopyMode = False    ActiveSheet.ShowAllData'    ActiveSheet.ListObjects("Table_FLHearingsMaster").Range.AutoFilter Field:=2, _    Criteria1:=Array("Hillsborough", "Pasco", "Pinellas"), Operator:=xlFilterValues    Range("Table_FLHearingsMaster[[#Headers],[FTL]]").Select    ActiveCell.FormulaR1C1 = "TPA"    Range("Table_FLHearingsMaster[[#Headers],[TPA]]").Select    Selection.Copy    Range(Selection, Selection.End(xlDown)).Select    ActiveSheet.Paste    Application.CutCopyMode = False    ActiveSheet.ShowAllData'    ActiveSheet.ListObjects("Table_FLHearingsMaster").Range.AutoFilter Field:=20 _    , Criteria1:="<>FTL", Operator:=xlAnd, Criteria2:="<>TPA"    Range("Table_FLHearingsMaster[[#Headers],[TPA]]").Select    ActiveCell.FormulaR1C1 = "Other"    Range("Table_FLHearingsMaster[[#Headers],[Other]]").Select    Selection.Copy    Range(Selection, Selection.End(xlDown)).Select    ActiveSheet.Paste    Range("Table_FLHearingsMaster[[#Headers],[Other]]").Select    ActiveCell.FormulaR1C1 = "Office Coverage"    Application.CutCopyMode = False    ActiveSheet.ShowAllData'    ActiveSheet.ListObjects("Table_FLHearingsMaster").Range.AutoFilter Field:=4 _    , Criteria1:=""    Range("Table_FLHearingsMaster[[#Headers],[Hearing Time]]").Select    ActiveCell.FormulaR1C1 = "Missing"    Range("Table_FLHearingsMaster[[#Headers],[Missing]]").Select    Selection.Copy    Range(Selection, Selection.End(xlDown)).Select    ActiveSheet.Paste    Application.CutCopyMode = False    Range("Table_FLHearingsMaster[[#Headers],[Missing]]").Select    ActiveCell.FormulaR1C1 = "Hearing Time"    ActiveSheet.ShowAllData'    ActiveSheet.ListObjects("Table_FLHearingsMaster").Range.AutoFilter Field:=7, _    Criteria1:="="    Range("Table_FLHearingsMaster[[#Headers],[Attorney Attending Hearing]]").Select    ActiveCell.FormulaR1C1 = "Missing"    Range("Table_FLHearingsMaster[[#Headers],[Missing]]").Select    Selection.Copy    Range(Selection, Selection.End(xlDown)).Select    ActiveSheet.Paste    Range("Table_FLHearingsMaster[[#Headers],[Missing]]").Select    Application.CutCopyMode = False    ActiveCell.FormulaR1C1 = "Attorney Attending Hearing"    ActiveSheet.ShowAllData'    Application.Calculation = xlCalculationAutomatic    Range("$R$24").Select    ActiveCell.FormulaR1C1 = "=IF((RC[-10]=""Missing""), ""Yes"", ""No"")"    Range("$R$24").Select    Range("$R$24").Copy    Selection.AutoFill Destination:=Range( _    "Table_FLHearingsMaster[Missing Hearing Attorney]")    Columns("R:R").Copy    Columns("R:R").Select    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _    :=False, Transpose:=False    Application.CutCopyMode = False'    Range("$Q$24").Select    ActiveCell.FormulaR1C1 = "=TEXT(RC[-13],""mmm-yyyy "")"    Range("$Q$24").Select    Range("$Q$24").Copy    Selection.AutoFill Destination:=Range( _    "Table_FLHearingsMaster[Hearing Month  Year]")    Columns("Q:Q").Copy    Columns("Q:Q").Select    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _    :=False, Transpose:=False    Application.CutCopyMode = False'    Range("S24").Select    ActiveCell.FormulaR1C1 = "=IF((RC[-14]=""Missing""), ""Yes"", ""No"")"    Range("S24").Select    Selection.Copy    Application.CutCopyMode = False    Selection.AutoFill Destination:=Range( _    "Table_FLHearingsMaster[Missing Hearing Time]")'    Range("$T$24").Select    ActiveCell.FormulaR1C1 = "=IF((RC[-5]>=TODAY()),""Yes"", ""No"")"    Range("$T$24").Select    Range("$T$24").Copy    Selection.AutoFill Destination:=Range( _    "Table_FLHearingsMaster[Sale Date Set]")    Columns("T:T").Copy    Columns("T:T").Select    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _    :=False, Transpose:=False    Application.CutCopyMode = False    Application.Calculation = xlCalculationManual'    Rows("22:22").Select    Selection.NumberFormat = "0"    Range("Table_FLHearingsMaster").Select    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    Selection.Borders(xlEdgeLeft).LineStyle = xlNone    Selection.Borders(xlEdgeTop).LineStyle = xlNone    Selection.Borders(xlEdgeBottom).LineStyle = xlNone    Selection.Borders(xlEdgeRight).LineStyle = xlNone    Selection.Borders(xlInsideVertical).LineStyle = xlNone    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone'    Range("Table_FLHearingsMaster[#All]").Select    ActiveWorkbook.Worksheets("FLHearingsMaster").ListObjects( _        "Table_FLHearingsMaster").Sort.SortFields.Clear    ActiveWorkbook.Worksheets("FLHearingsMaster").ListObjects( _        "Table_FLHearingsMaster").Sort.SortFields.Add Key:=Range( _            "Table_FLHearingsMaster[New Hearing Date]"), SortOn:=xlSortOnValues, Order _                 :=xlAscending, DataOption:=xlSortNormal    ActiveWorkbook.Worksheets("FLHearingsMaster").ListObjects( _        "Table_FLHearingsMaster").Sort.SortFields.Add Key:=Range( _            "Table_FLHearingsMaster[County]"), SortOn:=xlSortOnValues, Order:= _                 xlAscending, DataOption:=xlSortNormal    ActiveWorkbook.Worksheets("FLHearingsMaster").ListObjects( _        "Table_FLHearingsMaster").Sort.SortFields.Add Key:=Range( _            "Table_FLHearingsMaster[Hearing Time]"), SortOn:=xlSortOnValues, Order:= _                xlAscending, DataOption:=xlSortNormal    ActiveWorkbook.Worksheets("FLHearingsMaster").ListObjects( _        "Table_FLHearingsMaster").Sort.SortFields.Add Key:=Range( _            "Table_FLHearingsMaster[Attorney Attending Hearing]"), SortOn:=xlSortOnValues _                , Order:=xlAscending, DataOption:=xlSortNormal    With ActiveWorkbook.Worksheets("FLHearingsMaster").ListObjects( _        "Table_FLHearingsMaster").Sort        .Header = xlYes        .MatchCase = False        .Orientation = xlTopToBottom        .SortMethod = xlPinYin        .Apply    End With'    Application.Calculation = xlCalculationAutomatic    Range("BI24").Select    ActiveCell.FormulaR1C1 = "=CLEAN((TRIM(PROPER(RC[-51]))))"    Range("BI24").Select    Selection.Copy    Range(Selection, Selection.End(xlDown)).Select    ActiveSheet.Paste    Columns("BI:BI").Copy    Columns("BI:BI").Select    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _    :=False, Transpose:=False    Range("BI24").Select    Range("BI24").Copy    Range(Selection, Selection.End(xlDown)).Select    Selection.Copy    Range("J24").Select    ActiveSheet.Paste    Application.CutCopyMode = False    With Selection        .HorizontalAlignment = xlLeft    End With    Application.Calculation = xlCalculationManual'    Columns("BI:BI").Delete Shift:=xlToLeft    Range("Table_FLHearingsMaster[#All]").Select    Selection.Borders(xlDiagonalDown).LineStyle = xlNone    Selection.Borders(xlDiagonalUp).LineStyle = xlNone    With Selection.Borders(xlEdgeLeft)        .LineStyle = xlContinuous        .Weight = xlThin    End With    With Selection.Borders(xlEdgeTop)        .LineStyle = xlContinuous        .Weight = xlThin    End With    With Selection.Borders(xlEdgeBottom)        .LineStyle = xlContinuous        .Weight = xlThin    End With    With Selection.Borders(xlEdgeRight)        .LineStyle = xlContinuous        .Weight = xlThin    End With    With Selection.Borders(xlInsideVertical)        .LineStyle = xlContinuous        .Weight = xlThin    End With    With Selection.Borders(xlInsideHorizontal)        .LineStyle = xlContinuous        .Weight = xlThin    End With    Selection.RowHeight = 13'    ActiveWindow.ScrollColumn = 1    Cells.Select'    Application.ScreenUpdating = True    Application.StatusBar = True    Application.DisplayAlerts = True    Application.EnableEvents = True    Application.Calculation = xlCalculationAutomatic [COLOR=#333333]End Sub[/COLOR][COLOR=#333333]


    [/COLOR]

  • Help Debugging long sorting, labeling and highlighting macro

    Re: Help Debugging long sorting, labeling and highlighting macro


    Okay. Sorry about that. Attached is the document with the code.


    I am getting an error at line 126 - Invalid procedure call or argument


    What I am trying to do at that point is filter out the data and input text into the visible cells in column AJ depending on the filtering..


    so,

    Code
    ActiveWorkbook.Sheets("Sheet2").ListObjects("Table").Range.AutoFilter Field:=23, Criteria1:="<>"    ActiveWorkbook.Sheets("Sheet2").ListObjects("Table").Range("$AJ$").SpecialCells(xlCellTypeVisible).FormulaR1C1 = "Complaint Sent To Court - Not Filed"
    ActiveSheet.ShowAllData
'


    Here, I am filtering on column 23 and if it not blank, I am putting in "Complaint Sent To Court - Not Filed" - for everything that is visible. At the end of all of this i am going to create a pivot using the descriptions from column AJ.


    I have attached a better example doc, with the code.


    Thanks

  • Help Debugging long sorting, labeling and highlighting macro

    Re: Help Debugging long sorting, labeling and highlighting macro


    2nd part of code..


  • Help Debugging long sorting, labeling and highlighting macro

    Hi - I keep trying to fix this macro and feel like I am making things worse. At my wits end.. any help would be appreciated with just looking and pointing out things you know for a fact are going to throw errors...