Posts by gers1978

    Re: Smooth a chart line without dummy data

    Problem solved on the Mr Excel thread:

    "Since that's a straight line, you can simply get the chart to plot that. Right-click it, choose Select Data, then click the Hidden and Empty Cells button, and choose the 'connect data points with line' option."

    I have inherited a spreadsheet and given the task of trying to get a line on a chart to show a smooth progression between actual values. Presently the user inserted dummy data in blank cells, to give the impression of a smooth line.

    For example M2 contains £219,000. M5 contains £387,000. M3 and M4 are blank, but they have inserted the following formula to give "dummy" data which well then show a smooth line when charted:

    M3 (which shows £275,000):


    M4 (which shows £331,000):


    There must be a better way of doing it than this?


    Also posted here:…mmy-data.html#post4744080

    Re: VBA IIf statement error since update to Office 2016


    I'd guess that your error was actually an accident waiting to happen.

    You've hit the nail on the head. I think what's happened is since I changed a lot of my If Then Else statements to IIf (in an effort to streamline the amount of code, plus give me some practice using IIf as I'd never used it) I've never encountered a situation where there was an error in one of the cells. Obviously I'd tested the code when it used If Then Else statements and it worked fine, then when I switched to IIf, I've never retested, and it's came up by accident in a real world situation now.


    My company just updated to Office 2016.

    I have a spreadsheet with loads of code, created in Excel 2010, that has worked fine for months.

    Since updating to 2016, the following line gives a type mismatch error (Error 2007):

    strStaff = IIf(IsError(DataRange(intRow, staffCol)), "(no staff)", Trim(WorksheetFunction.Proper(DataRange(intRow, staffCol))))

    However if I change it to:

    If (IsError(DataRange(intRow, staffCol))) Then
         strStaff = "(no staff)"
         strStaff = Trim(WorksheetFunction.Proper(DataRange(intRow, staffCol)))
    End If

    it works fine. Has IIf been removed from Office 2016?

    Also posted here:…l-2016-a.html#post4652969

    I’m looking to write some code so that when the workbook opens it loops through all the worksheet names looking for one named with today’s date. If it finds one, do nothing. If it doesn’t, I want to find the one with the most recent date, take a copy of it, and rename the new copy of the sheet to today’s date.

    Any ideas? I'm stuck even knowing where to begin

    Also posted here:…ate-open.html#post4648150

    I have a function where a string and a string-based array are passed in.

    Using the VBE Watch function I can see the string passed in is:

    TD1a/b/ PMO & CT

    The array contains:

    TD1a/b/ PMO & CT
    TD1a/b/ PMO & CT

    The line:

    If staffName = whichArray(intCount) Then

    is resolving to False, in other words it's skipping to the Else part, even when intCount = 0 or 1.

    However if I run it when the string and array contain "TD1" it works fine.

    Seems like either the /, space or & are causing issues?

    Also posted here:…orrectly.html#post4594932

    Re: Delete rows where row numbers are stored in array

    I think I have the below working:

    Set deleteRange = .Range("A" & CInt(deleteArray(LBound(deleteArray))))
            For j = LBound(deleteArray) + 1 To UBound(deleteArray)
                Set deleteRange = Union(deleteRange, .Range("A" & deleteArray(j)))
            Next j

    Re: Delete rows where row numbers are stored in array

    Quote from apo;774840


    Can you change that to an array of row numbers you would like to keep?

    Easily, yeah, sure.


    If so.. you could load all the values from your sheet into a seperate array and use these ' rows you want to keep' array(2d array) in the 2nd argument of the Application.Index method..

    Not sure I follow this bit...

    Re: Delete rows where row numbers are stored in array

    Quote from PCI;774829

    No, it is a good way.
    To speed up and avoid to go from bottom to top, you can use the UNION and delete in shot
    For example

    I'm not sure how I would apply that to my code. Could you break it down please?

    Re: Delete rows where row numbers are stored in array

    Quote from Craggs82;4592874

    Are the numbers in ascending order? if so you could perhaps loop through the array and keep a counter to account for the offset caused by removing rows.

    They're in numerical order, but not sequential.

    I have some code that loops through a variant array (called DataRange, which in itself was populated by reading a sheet into it, as I believe it's quicker to loop through an array than loop through the rows on the sheet).

    One of the bits of code in this loop identifies rows for deletion:

    If booFlag = True Then
                      rowsToDelete = IIf(rowsToDelete = vbNullString, intRow, rowsToDelete & "," & intRow)
     End If

    (booFlag is set earlier in the loop, if the row is identified as one to be deleted.

    So what I have now is a string of row numbers, separated by a comma.

    I then a have a string array, called deleteArray, populated using:

    deleteArray = Split(rowsToDelete, ",", -1, vbBinaryCompare)

    I then write my original DataRange array back to the worksheet, then delete the rows using:

    For j = UBound(deleteArray) To LBound(deleteArray) Step -1
                 .Range("A" & deleteArray(j)).EntireRow.Delete
             Next j

    It SEEMS to work. Can anyone see anything wrong with this approach?


    Re: Find first blank row across 2 columns

    I came up with this myself, it SEEMS to work but I'm not sure if it's flawed:

    Re: Find first blank row across 2 columns

    Ok, thanks, but I think an example of what I'm trying to do would be better. Attached is a sample sheet with dummy data.

    What I'm trying to achieve is a button to add a new EWN. When clicked it should:

    1) Look down column A (starting at A12) for the first blank row
    2) Make sure that column B of the same row is also blank
    3) Work out the maximum number in any of the cells above in column A (so in my attached example this would be 6)
    4) Populate the cell in A with this value

    I also want a button to add a new CEN, which does the same as above but for column B, i.e. find the first blank row in B, but also make sure the equivalent cell in A is blank, before taking the next sequential number from column B's values.

    There's a slight complication in column B in that not all values so far are numerical, but I'll cross that bridge when I come to it.

    I have an array of strings that I want to check a variable, strName, against, but for some of the values in the array I just want to see if strName exists anywhere within the array element. For other elements I want to check if strName is equal to the entirety of the array element.

    Do I need 2 arrays, one for the those elements I need to check using InStr and one for those I need to check using equals? Or is there a way of doing this with a single array?


    Also posted here:…omething.html#post4576931

    Ok, firstly, here's the function:

    The problem comes when one of the unique values is contained *within* another unique value.

    For example, in my column on the worksheet the unique values might be:

    Admin Support
    Tech Assist

    The problem with the function above is it effectively builds a string that (should) looks like:

    FGHCAD|DES PMO|Admin Support|PMO|Tech Assist

    Then Splits the items into an array based on the | character. Whilst this is a very good and clever solution, it has one flaw that I'm not sure how to overcome, namely that because the string "PMO" exists within the string "DES PMO", then item "PMO" never gets added. In other words the temp list ends up as:

    FGHCAD|DES PMO|Admin Support|Tech Assist

    so that when the Split happens, "PMO" doesn't get added.

    Any ideas?


    Also posted here:…e-values.html#post4562746

    I have some code that creates a pivot table based on a worksheet.

    One of the columns is a person's selling rate, another has their name, another has hours worked. The pivot table end up grouping them by name, with their selling rate multiplied by their hours.

    Problem is, sometimes someone's selling rate has changed during the year. My problem is then that the pivot table still sees this as one person, and either sums, averages or takes a max of their selling rate.

    What I would like to do is perhaps append " (old rate)" to their name if there's a change in selling rate so that when the pivot table is created it sees this as two people and doesn't average/sum/max their selling rate.

    As an example, say my source sheet has:

    Smith, John | 10 | £10.00
    Smith, John | 20 | £10.00

    The pivot table would correctly group this to:

    Smith, John | 30 | £300.00

    However if John Smith's selling rate changed to £15 during the year, my source sheet might say:

    Smith, John | 10 | £15.00
    Smith, John | 20 | £10.00

    In which case, currently, my pivot table (incorrectly) end s up with:

    Smith, John | 30 | £350.00

    What I'd like to see is:

    Smith, John | 10 | £150
    Smith, John (old rate) | 20 | £200


    Also posted here:…-columns.html#post4553823

    Re: VBA: Named Argument Not Found error

    Hmm, seem to have solved it with:

    detailsSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

    Could someone explain why this works but:

    detailsSheet.PasteSpecial Paste:=xlPasteValues

    doesn't? Thanks