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):

=((M5-M2)/3)+M2

M4 (which shows £331,000):

=((M5-M2)/3)+M3

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

Thanks!

Also posted here: https://www.mrexcel.com/forum/…mmy-data.html#post4744080

Re: VBA IIf statement error since update to Office 2016

Quote

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.

Thanks!

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)"
Else
     strStaff = Trim(WorksheetFunction.Proper(DataRange(intRow, staffCol)))
End If

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

Also posted here: http://www.mrexcel.com/forum/e…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: http://www.mrexcel.com/forum/e…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: http://www.mrexcel.com/forum/e…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

HI..

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

Easily, yeah, sure.

Quote

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

Code

Sub Treat1()
Dim WkAr
Dim WkR
Dim DelRg  As Range
   WkAr = Array(4, 6, 8, 10)
   Set DelRg = Cells(Rows.Count, 1).End(3).Offset(1, 0)
   
   For Each WkR In WkAr
       Set DelRg = Union(DelRg, Cells(WkR, 1))
   Next
   DelRg.EntireRow.Delete


End Sub

Display More

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?

Thanks

I have an array loaded with row numbers that I'd like to delete.

I have an array with the numbers stored as strings, and another array where they're stored as integers.

Is there a way of quickly deleting all these rows from my sheet?

Thanks!

Also posted here: http://www.mrexcel.com/forum/e…ed-array.html#post4592807

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:

Sub GetNextEWN()
    Dim lastRowA As Integer
    Dim lastRowB As Integer
    
    lastRowA = ActiveSheet.Cells(1048576, 1).End(xlUp).Row + 1
    lastRowB = ActiveSheet.Cells(1048576, 2).End(xlUp).Row + 1
    
    If lastRowA >= lastRowB Then
        ActiveSheet.Range("A" & lastRowA).Value = WorksheetFunction.Max(Range("A12:A" & lastRowA)) + 1
    Else
        ActiveSheet.Range("A" & lastRowB).Value = WorksheetFunction.Max(Range("A12:A" & lastRowA)) + 1
    End If
    
End Sub
Sub GetNextCEN()
    Dim lastRowA As Integer
    Dim lastRowB As Integer
    
    lastRowA = ActiveSheet.Cells(1048576, 1).End(xlUp).Row + 1
    lastRowB = ActiveSheet.Cells(1048576, 2).End(xlUp).Row + 1
    
    If lastRowB >= lastRowA Then
        ActiveSheet.Range("B" & lastRowB).Value = WorksheetFunction.Max(Range("B12:B" & lastRowB)) + 1
    Else
        ActiveSheet.Range("B" & lastRowA).Value = WorksheetFunction.Max(Range("B12:B" & lastRowB)) + 1
    End If
    
End Sub

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.

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

I want to be able to find the first blank row in column A and put a formula in there, but ONLY if column B is also blank.

So effectively I think I'm looking to find the first row where both columns A & B are blank.

Thanks

Also posted here: http://www.mrexcel.com/forum/e…-columns.html#post4581074

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?

Thanks

Also posted here: http://www.mrexcel.com/forum/e…omething.html#post4576931

Is there any advantage to this code:

With Worksheets("Sheet1")
  .Range("A:B").NumberFormat = "0.00"
End With

vs

With Worksheets("Sheet1")
  .Columns("A:B").NumberFormat = "0.00"
End With

or vice versa?

Thanks!

Also posted here: http://www.mrexcel.com/forum/e…olumns-b.html#post4575923

Ok, firstly, here's the function:

Private Function UniqueValues(InputRange As Range)
    Dim cell As Range
    Dim tempList As Variant
    
    tempList = vbNullString
    
    For Each cell In InputRange
        If cell.Value <> vbNullString Then
            If InStr(1, tempList, cell.Value) = 0 Then
                If tempList = vbNullString Then tempList = Trim(CStr(cell.Value)) Else tempList = tempList & "|" & Trim(CStr(cell.Value))
            End If
    Next cell
    
    UniqueValues = Split(tempList, "|")
    
End 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:

FGHCAD
DES PMO
Admin Support
PMO
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?

Thanks

Also posted here: http://www.mrexcel.com/forum/e…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

Thanks!

Also posted here:

http://www.mrexcel.com/forum/e…-columns.html#post4553823

Re: Application.ScreenUpdating = False not working properly

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

Workbook attached.

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