Posts by ShosMeister

Re: Cell value is a cell reference. How do I display the value in the cell reference?

Look up INDIRECT in the help. Sounds like that's what you are trying to do.

Re: Combining Pillars in the graph

Instead of a Clustered Column type chart, look up the Stacked Column.

Re: Sum Values in One Column based on the last date listed in another column 1 year a

See attached.

Re: Displaying Table based on Dropdown selection

You need to provide a sample of the chart you are trying to get. It's a relatively simple matter to change the data source of the chart based on the value in the pull-down (more specifically when the pull-down is changed).

Re: Running Two Loops Together VBA

As long as your not talking threading then yes they can be done. It all depends on how you implement it. If you have something working and need help fixing it, you need to attach a sample indicating where the failure is and what your expectations are.

Re: Create multiple charts using VBA

Try this:

Sub CreateCharts()
  Dim ws As Worksheet
  Dim ChartName As String
  Dim NumCharts As Integer
  Dim lp As Integer
  Dim ChartRange1 As Range
  Dim ChartRange2 As Range
  Dim tmpRow As String
    
  Set ws = Sheets("Data")
  NumCharts = WorksheetFunction.CountA(Columns(16))
    
    
  For lp = 0 To NumCharts - 1 'Step 2
    tmpRow = (lp * 29) + 3
    ChartName = ws.Cells(tmpRow, 4) '"chrt" & Range(D4) i.e. cost center


    Set ChartRange1 = Worksheets("Data").Range(ws.Cells(tmpRow + 9, 4), ws.Cells(tmpRow + 9, 15))
    Set ChartRange2 = Worksheets("Data").Range(ws.Cells(tmpRow + 18, 4), ws.Cells(tmpRow + 18, 15))


    ws.Cells(tmpRow, 17).Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.Parent.Name = ChartName
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Characters.Text = "Cost Center " & ChartName


    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "Plan"
    ActiveChart.SeriesCollection(1).Values = ChartRange1   '"='Data'!$D$41:$O$41"


    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "Actual"
    ActiveChart.SeriesCollection(2).Values = ChartRange2   '"='Data'!$D$50:$O$50"
    ActiveChart.SeriesCollection(2).XValues = "='Data'!$D$1:$O$1"


    ActiveSheet.Shapes(ChartName).Height = 350   ' resize
    ActiveSheet.Shapes(ChartName).Width = 500    ' resize
    ActiveSheet.Shapes(ChartName).Top = ws.Cells(tmpRow, 17).Top
    ActiveSheet.Shapes(ChartName).Left = ws.Cells(tmpRow, 17).Left
  Next lp


End Sub

The only item you'll have to add in is to clear the old charts before creating new ones if you use existing sheets. From what you said before though that you are creating the data sheet from another function, this shouldn't be necessary.

Re: Create multiple charts using VBA

You're using the Module3:CreateCharts() right? I'm guessing the Module1:AutoChart() was just a test?

Re: Macro Performance Randomly Varying

Is everything running local or over a network connection?

Re: Running Two Loops Together VBA

Sounds like what you want is to thread the Excel VBA procedures? Not as far as I know directly within VBA.

Re: Delete rows based on criteria with a large data set

I don't think it's ridiculous but understand your wanting it to be faster. One thing I can think of would be to use conditional formatting for all the cells (that could be done during import or in advance) and then searching for the format. So if the value in the cell is outside of the min/max values, change cell format to red or something. Then just look for any cell that is red and delete the entire row. Haven't tested, just a thought.

The multiple column min/max you answered even though you didn't understand the question. I was trying to determine if each column was AND or OR with the other columns in that row. You answered by saying if any cell's value was outside of the min/max range for that column, the entire row is deleted. That makes the answer to my question an OR function. Again, the above idea may help speed it up.

One other thing in your code you are comparing <= min but > max. Should they both be <= | => or just < | >?

Re: Delete rows based on criteria with a large data set

First off, 10 seconds isn't that long considering what it would take by hand.

Second, the rule you didn't define is if the multiple column min/max are AND or OR.

Third, if your import will always have a different number of columns, your "table" at the top will extend past row 25 and your code will fail. I'd suggest you create that "table" on a separate worksheet. You can still use the named references as long as they are workbook names and not just worksheet names.

Other than that, looks like it works pretty well. You didn't indicate that it was broken, just slow so my only suggestion would be to sort the data by the column you are comparing and then change your look from looking at every row to a while loop. May make it go a bit faster but again, a lot faster than by hand.

Re: Create multiple charts using VBA

Is is coincidental that each "table" has 7 labels? If it's not and there will always be 7, then you can just create the charts once and have them update when you change the data.

I'm guessing this isn't the case, so... You use "X" in your VBA to track where the data series is coming from. You initialize it at 12 but never increment it.

Re: Dynamic Report

A sample with the master data and an example of the output you expect would be helpful.

Re: Creating Automatic Label creater spreadsheet

You just put in the first one and then click and drag the lower corner of the cell and Excel will automatically increment the number. So entering A-01 into the first cell and click/drag down 29 cells, you'll end up with A-01 through A-30. Works with any combination of letters as Excel will always increment the last number sequence.

If you don't know how, look up "Use the fill handle to fill data" in the help.

Re: Creating Automatic LAbel creater spreadsheet.

Your attachment doesn't seem to demonstrate your description/problem.

Re: Import External Data locking source worksheet

Haven't done a lot with "live" data but have you looked to see if there is a "close"/"disconnect" command available that you can use to unlock (b)?

Re: Office 2007 CreateItem(olMailItem) failing

Sorry for omitting that code. It actually does what you did, just gets the object. Included here for completeness:

'+------------------------------------------------------------------------------------------------+
'| This will be used to determine if an instance of an application is running. For this specific  |
'| workbook (as of Aug 2010), the only application we are concerned with is Outlook.              |
'|                                                                                                |
'| strApp is a string containing the application to search for (i.e. "Outlook.Application")       |
'| bCreate is a boolean used to determine if the object should be created if not found:           |
'|    True - create the object and return it or null if creation failed                           |
'|    False - don't create the object. Return null if an instance does not exist.


Function Get_ApplicationObject(strApp As String, bCreate As Boolean) As Object
  Dim tmpApp As Object
  
  On Error Resume Next
  Set tmpApp = GetObject(, strApp)
  If tmpApp Is Nothing Then
    If bCreate = True Then
      Set tmpApp = CreateObject(strApp)
      If tmpApp Is Nothing Then
        tmpApp = Null
      End If
    Else
      tmpApp = Null
    End If
  End If
  On Error GoTo 0
  Set Get_ApplicationObject = tmpApp
End Function

Thanks for confirming the change of .Recipients to .To. Any ideas why though?

Re: Office 2007 CreateItem(olMailItem) failing

Still trying to resolve this issue - been working around by running on Office 2003 version workstation. I've seen some other e-mail related VBA and instead of using .Recipients.Add, they just use the .To= to set the recipients e-mail addresses. Have not been able to test yet, but, wanted to see if anyone else had any other ideas.

Re: Macro - hide row if cells are empty

Just cut it down by deleting some of the rows/sheets. In the end, you can take whatever we fix here and copy just the code over to your original workbook.

Re: Macro - hide row if cells are empty

Attach your sample please and we'll get this fixed for you.