Posts by EXCELn00b18

I have a power query table with a column for each month. It currently starts with January and goes to December. I need to sort them backwards from the current month.

EX. Today it is July so they would sort as: Jul, Jun, May, Apr, Mar, Feb, Jan, Dec, Nov, Oct, Sep, Aug.

Thanks for any help

Basically I have 8 route and 8 drivers. Every week the route a driver has changes on a rotating schedule. The table below is an example of how it works. I creating a 1 page print out as an overview for the week to give to our dispatch and on this sheet i need to have who is on what route. I am trying to find a way to only display 1 week at a time.

My idea was to use WEEKNUM() to find the week number and then use some formula to convert that to only 1-8. For example weeks week 9 would instead be week 1 and so on. There is 2 issues with that, 1) I cant figure out the math to get that working and 2) when the year starts over you would go from 4 (week 52) back to 1 which messes up the rotation.

I am not opposed to using VBA, I just started out with formulas because i understand them a little bit better and was hoping to figure this out on my own.

Any help is much appreciated.

I am trying to make a formula that will see if a column contains either "R", "B", or "D". If it does contain on of those then return a "Y" otherwise return a "N".

=IF((ISNUMBER(FIND({"R","B","D"},[A.B.C.D.]))),"Y","N")

This formula almost works but will only return a "Y" if the cell contains an "R" or whichever one I first.

EX.

[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 72"]

99992BY

9309 RY

0513 BYL

8018 Y

W0319 RN

0312 BN

9708 BY

0412 BY

0412 BY

[/TABLE]
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 48"]

[TD="width: 64"] [/TD]

[/TABLE]

How to insert row including formula to the end of a table using VBA? I have a formula to generate the next number in my table, but as of right now the only way i can find to add another row is to tab all the way to the end of the table. Is there a way to automatically add a row and insert that formula to the end of the table?

I can't seem to get any of the usual functions I use (Vlookup, Match, Error) to work for what I need, I am wanting to See if The value in column A in the table is included in Column L. The Reason I need this is because I only need to show the Row if Cell A of the row is in column L.

Column K is the column in which i was using to notate weather the value was in column L

I have a Column of cells that I get from another program. In the column some cells are blank, some cells have 1 number, and then some cells have several numbers separated by 3 spaces EX.(1 1 5 4). Is there a way that I can add these values together in one cell so in my example i would add them in to one cell valuing 11?

Yes, this is what i ended up doing in the end, thank you for you input.

With ActiveSheet.PageSetup
     .PrintTitleRows = "$1:$1"
End With

I have read that with VBA you can manipulate websites in IExplorer. I currently use a web site that I have to regularly check and if there is a link i have to click on it. It is pretty much a race between all of the other locations in my company to confirm it first. Is there a way to automate this process with VBA or do i need to look into something else?

Thank you for all your help. You are awesome.

I have gotten my code working almost perfectly thanks to help from dangelor. The only issue i have now is when it prints the file, it prints one page with just the top row because that is where the first page break is. I have attached my new code if anyone could please help me.

Sub CYCLE()
    ' Keyboard Shortcut: Ctrl+q
    Dim I As Long, J As Long


    With ActiveSheet
        J = .Cells(.Rows.Count, 3).End(xlUp).Row
        .Range("L1") = "TAG"
        .Range("N1") = "COUNT"
        .Range("M1") = "PAC"
        .Range("O1") = "NOTES"
        .Range("P1:Q1").Merge
        .Rows(1).Style = "Heading 2"
        .Columns(1).HorizontalAlignment = xlRight


        .Range("L2:L" & J).FormulaR1C1 = _
        "=IF(RC[-2]=0,(IF(RC[-4]="""","""",(IF(RC[-3]=2,"""",""PULL"")))),"""")"
        .Range("N2:N" & J) = "________"
        .Range("M2:M" & J).FormulaR1C1 = _
        "=(IF(RC[-7]>1,""Y"",""""))"


        For I = J To 2 Step -1
            If .Cells(I, 3).Value <> .Cells(I - 1, 3).Value Then
                .HPageBreaks.Add Before:=Cells(I, 3)
            End If
       Next I
    End With


'   PRINT
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .PrintArea = ""
        .Orientation = xlPortrait
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut
End Sub

I have a report that I run several times a day and I need to format it in excel. I have got my VBA macro working almost right but I have a few things I cant figure out

I have pasted my macro with notes in the places I need help. I need to copy row one and paste it after every page break so that it ends up at the top of each page. I also need to fill K2 and L2 down to the last row after inserting the formula in K2

Sub CYCLE()
'
' CYCLE Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False


    Application.PrintCommunication = True
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "TAG"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "COUNT"
    Range("M1:O1").Select
    Selection.Merge
    ActiveCell.FormulaR1C1 = "NOTES"
    Rows("1:1").Select
    Selection.Style = "Heading 2"
    Selection.Copy
    Columns("A:A").Select
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlRight
    End With
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "________"
'FILL DOWN
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]=0,(IF(RC[-4]="""","""",(IF(RC[-3]=2,"""",""pull"")))),"""")"
    Range("K2").Select
'FILL DOWN
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Dim I As Long, J As Long
    J = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
    For I = J To 2 Step -1
        If Range("C" & I).Value <> Range("C" & I - 1).Value Then
            ActiveSheet.HPageBreaks.Add Before:=Range("C" & I)
        End If
    Next I


'NEED TO COPY ROW 1 AFTER EVERY BREAK


    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

Thank you for any and all help