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: 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
Display More
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
Display More
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.