Posts by Royzer

    First of all, thank you for your time.


    This is an end of year payroll report. I have to adjust employees total base earnings by subtracting amounts for any pay above "base", with the result in the cell adjacent to the beginning Total amount. The adjusting amounts above base will already be in place. I just need to sum the adjustments for each person and net them from the original total amount. I'm dealing with hundreds of employees, so I really need to find a way to do this with a macro. Thanks!



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

    Re: Problem with Index/Match with Countif


    Quote from NBVC;700046

    This number: 25-003 is repeated in 2 different groups of stations. Is it supposed to appear in the unique list once for each group or just once overall?


    That is an error on my part when setting up the test data. CBR numbers can occur multiple times for one station, but the same CBR number should never show up in two separate stations.

    I am really stumped. Several projects ("CBR's") may require that dollars be spent in multiple months to get to completion.
    Each month that has payments will have the CBR number for the project, with the description, amount spent, etc.


    The "Project Balances" sheet should list each CBR number only ONCE and accumulate costs in total based on the spending associated with that CBR number throughout the "2014 Actual" sheet.


    The array formula only works some of the time. I would really appreciate it someone would take a look at my example file to see if you can help me solve this. (The file will open with two windows to show both sheets). Thank you



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

    Re: Need to determine if each value in a list is paired with every other value in a l


    I adapted the formula to the real spreadsheet but I've only giving me a couple of matches. I've attached the real spreadsheet with the formula and conditional formatting in it. Would you please take a look at it? forum.ozgrid.com/index.php?attachment/58496/

    I have two columns with values in them. The values in column A are considered to be "paired" with the ones in the adjacent column B cell. There are 1040 rows with these pairs of values.


    I need a way to determine if every value in a list has been paired with each of the other values in the list.


    Hopefully the attached worksheet explains it better than I have here.


    Thanks for any help you can give me.forum.ozgrid.com/index.php?attachment/58495/

    I've spent a couple of hours (at least) looking for answers to this problem to no avail, so I would really appreciate any help you could give me.


    The "Accrued Other" sheet will always have the current month in A9 as text.


    Sheet2 will have a variable number of rows and I would like to concatonate "RCD" with the date from Accrued OtherA9 and "Expenses" in the bottom cell in column C.


    The code will already select and paste a string in the correct cell, but I cannot get it to include the text from Accrued Other A9.


    Code
    Sheets("Sheet2").Activate
                           
        lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
        Range("B" & lastrow).Select
        Selection.PasteSpecial Paste:=xlPasteValues
        Range("A" & lastrow).Value = "061-000-000-20200-0000"
        Range("C" & lastrow).Value = [B]?[/B]


    Thanks.

    Hi. I am getting the #N/A error with this formula and I've tried adding IFERROR to it to blank it, and also IFISNA but I cannot get the formula right. Would someone please show me how to revise this formula to show a blank instead of #N/A? Thanks!



    =IF(OR(AS7="",AR7=0),"",IF(AQ8="NEXT",((VLOOKUP(N7,KEEP!$D$5:$E$92,2,FALSE)&O7&"000-11400-0000"))))

    This loop is deleting the rows I want to delete but errors out after stopping on the cell containing LastWord and highlighting part of the loop.


    The loop:



    Then it yellows this section, even though the active cell is the one with LastWord.


    Code
    Cells.Find(VeryLastWord, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False).Activate


    I'd really appreciate some help with this.


    Thanks!

    Because of circumstances with file A I have to use file B to open it and put in some temporary code into A each time it runs, as we are prohibited from saving A. I originally had B set up with a button to open A and add the code, then close B.


    I wanted to eliminate the "push the button" step in the process, so I added code to "push the button" on auto-open, then auto close (file B) leaving A as the only open file.


    The problem is that I realized that I should have turned off screen updating of B, but I cannot stop the code to add it because the time from file open to file close is so short.


    Is there a keyboard shortcut or something I can use to BREAK the vba in the short time B is open?


    Thanks!

    Re: Conditionally format cell if invalid date is entered


    Data Validation does an even better job than I would have gotten from conditional formatting. That's just what I needed. Thanks !!






    I am pulling contract data from 31 files with into one spreadsheet using VBA . The spreadsheet has formulas that look at the contract start and end dates and spreads the contract amount evenly over the number of months determined from those two dates. I just ran into a problem with one of the dates entered by a user. The dates should have been:
    [TABLE="width: 194"]

    [tr]


    [TD="class: xl66, width: 95"]9/1/2015[/TD]
    [TD="class: xl66, width: 99"]8/01/16[/TD]

    [/tr]


    [/TABLE]


    But the end date was keyed as this, which causes an issue in the spreadsheet.
    [TABLE="width: 194"]

    [tr]


    [TD="class: xl66, width: 95"]9/1/2015[/TD]
    [TD="class: xl66, width: 99"]098/01/16[/TD]

    [/tr]


    [/TABLE]


    The cell she type it in is formatted as Date, but Excel let her key it like that anyway. Finally to my question: Is there a way to conditionally format all date columns to highlight cells that have invalid dates?


    I'm using Excel 2010.


    Thanks!

    Hi. We have many files that are used on a monthly basis and at the end of the month the user performs a SAVE AS to set up the next month's file. Normally this means manually changing the dates on each tab to match the new month in the mm-dd format. The code below will skip the first sheet of each file (the Summary sheet) and rename the next 31 tabs. This does exactly what I need it to do except one thing: I'd like to use an input box for the user to specify the month number and have that number used in the formula where the number 9 is at the moment. Any help would be greatly appreciated. Thanks!


    Code
    Sub renametabs()
    
    
    For i = 1 To 31: Worksheets(i + 1).Name = "9-" & i: Next
    
    
    End Sub