Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

Close Window (X)   
Excel VBA Course
[80% Discount] Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Today!)

Vba Code For A Combination Chart



Hi! I currently have the below is an excerpt of code that works to give me a bar graph for one set of data. I would be interested to know if any one on here knows how to take my code below and alter it so it gives an output of a combination graph (Bar and line Graph). The bar graph would be the same set of data it is pulling now from a pivot table. The line graph data would be on a different worksheet in a table. Please let me know if you need any more info. thanks!

[code]
Dim shp As Shape
Set shp = ActiveSheet.Shapes.AddChart(xlColumnStacked)
shp.Chart.SetSourceData Source:=ActiveSheet.PivotTables(1).TableRange1, _
PlotBy:=xlColumns
Dim objChart1 As ChartObject
Set objChart1 = Sheets("Upcoming Quarter Requirements").ChartObjects(1)

With Range("B13:C22")
shp.Left = .Left
shp.Top = .Top
shp.Width = .Width
shp.Height = .Height
End With
With shp.Chart.PivotLayout.PivotTable
.PivotFields("Resource Assigned").Orientation = xlRowField

End With
shp.Chart.ChartType = xlColumnStacked
[\code]

Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

(80% Discount Ends Soon!)

View Course




Similar Topics







I have a graph with various information in it. One is a line graph that tracks hours used in that department per month. The problem is that when it gets to the last month, and there is not yet data for future months, the line goes down to Zero (leavin this big diagonal line that makes the data look funny). I need this line to stop on the last month there is data for. (I do not manually enter the data, it is a formula that I do not wish to delete.) I know I can manually move the data that the line is pulling to make it stop on the last month, but I have a graph for 36 different departments and that's a pain to have to manually adjust 36 graphs each month. Is there a way to tell the line to stop if there is no data? Thanks!!


Hey everyone,

I'm really hoping someone can help me with this...

I need to plot percentages over time in a line graph in excel. I don't want to have to do a percentage equation in the spreadsheet, I just want excel to take two sets of values and display the percentage in the chart. For example, I need B1 as a percentage of B2 for week 1, C1 as a percentage of C2 for week 2, etc....
Can someone please offer a suggestion for how to do this? I would really appreciate it.
Also, would it be possible to link data from other sheets in the workbook into one single chart?


Morning all,

Say I had a line graph for 4 years and I wanted it to be a solid line for the first three and a dashed line for the last one, is that possible?

Many thanks for your help


I have a pivot chart with a bunch of data series, and every time we
include/exclude one of the series to see how the chart changes, all the
colors change and we have to spend time re-identifying which series is
which, using the legend to check the colors.

Is there a way to "lock" the colors, so that the bars stay the same colors
regardless of whether other bars are included/excluded?

Thanks!
Keith





Hello,

As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).

Code:

Sub AddImage()
Dim strPath As String
Dim strFile As String
Dim Picture As Object
Dim Response As String
Dim C As Range
strPath = "C:\\Photos\"
 
For Each C In Range("A1", Range("A65536").End(xlUp))
    If C  0 Then
        With C.Offset(0, 1)
            Set Picture = Nothing
            On Error Resume Next
            Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
            Picture.Top = .Top
            Picture.Left = .Left
            Picture.LockAspectRatio = msoTrue
            Picture.Width = 157
            Picture.Height = 138

        
    End With
    End If
     
NextC:
Next C
End Sub


I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.

Please help

Thanks!


Regarding Charts in Excel:

Is there a way to have the Min and Max values adjusted dynamically for the Scale of the values being displayed ??

I know that I can use named ranges to display various sections of data -- month by month, or quarter by quarter, for example.

But when the value of these ranges vary greatly from section to section I end of having to manually go and adjust the Min and Max values of the chart scale.

For example, if I was looking at a graph of the S&P 500's prices last November, a Min and Max range of 750 to 1200 would be fine. But if I had a dynamic range established and scrolled over to view the S&P 500's prices for this past February, I would need a Min and Max range of around 650 to 850.

Is there a way to have these Min & Max values adjust automatically depending on the values being displayed ??

Thanks

StanSz


I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.




I have a given length of steel bar...Example(550 feet)
I have various cut lengths that I can pick from...
14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)

I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....

Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable

Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
Any and all advice/help would be appricated.....


Hi everyone,

I found an excellent macro this morning that allows the user to filter a pivot table based on the value found in a specific cell. This cell essentially acts as a search bar, allowing the user to type in what they are looking for rather than select it from a drop-down list.

The macro works perfectly for my purposes except in one regard: I can no longer perform a "show all" filter. If I leave the "search bar" cell blank, the pivot table shows nothing. I'm sure that there is an easy fix for this but I'm still learning the basics of how to write and use macros. How can I change the code so that when I leave the cell blank, the pivot table shows all? The search bar cell is D2.

Thanks for your time!


This may be less of an Excel question than a general data display question, but I'll try here.

I have 3 variables that I'm charting. I have one on the primary axis and it's values are in the billions. I have one on the secondary axis and it's a percentage. I need to display a 3rd who's value is in the millions. If I put it on the primary axis, you cannot see modest changes.

Is there a tricky way to get all three on one graph? I know I could split on two graphs, so that's my backup plan.

Thanks,

Dave


I run a small business. I want to start keeping track of inventory using a barcode system.

I know Excel "ok", but I don't know programming.'

I'd like to be able to do something very simple (for now)...

For example...if I had PRODUCT / CODE / INV VALUE

A - CODE A - 5
B - CODE B - 11
C - CODE C - 14

I would want to be able to click a button (or scan a code for) : ADD +1 or REMOVE -1

...then scan the barcode on the product (A) and have it add or remove one, respectively. So, if the row for A is:

A - CODE A - 5

I choose "ADD" (with a button or scan code), then scan the barcode, and the spreadsheet automatically finds the code in the sheet, and changes the corresponding inventory value:

A - CODE A - 6

Does that make sense? How might I go about doing that?


Hi,

Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.

Please HELP!!!


Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet.

I am running Excel 2011 for Mac.

I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.

Appreciate any help, let me know if you have any further questions.

Hunter


I am using XL2007 and have a macro that refreshes microsoft query connections. The issue is the refreshes only happen if you step through the macro using the debugger. When you run the macro normally, everything else functions properly, but the data is not refreshed.

Any help is appreciated. Here is an excerpt of the code:


Workbooks.Open Filename:="C:\Profile.xls"
Sheets("SELECTION").Select
Range("F3").Value = SNR

' THIS PART ONLY WORKS IF YOU STEP THROUGH THE MACRO USING THE DEBUGGER...IF YOU RUN MACRO NORMALLY THE CONNECTIONS DO NOT REFRESH
ActiveWorkbook.Connections("Connection").Refresh
ActiveWorkbook.Connections("Connection14").Refresh
ActiveWorkbook.Connections("Query from C_Profile").Refresh
ActiveWorkbook.Connections("Query from C_Profile1").Refresh


I have been using Excel (XP) to make a text chart for several months. Some of
my text entries are rather lengthy. The past two weeks these lengthy entries
are showing up as pound signs (#########) when I click off the cell. I know
the text will fit in the cell, and the problem isn't solved by making the
cell bigger or using a little bit less text. I have the cells formatted as
"text" and "wrap to fit". I have printed the pages and the printed version
also has pound signs. I just want my text to show up!



Is it possible to apply a filter to multiple sheets within the same Excel workbook, using the same filter criteria?

For example, suppose you have tables of data on 3 different tabs, all having the same headings. Suppose one of the headings is "gender". If you apply a filter to the table on Sheet 1, to only show the rows containing the word "female", is it possible to have the tables on Sheets 2 and 3 automatically update to only show the "female" rows? Or would you have to manually update the filter criteria in Sheets 2 and 3?

I hope this makes sense. Thank you in advance for any help.


Seemingly super simple, but I can't figure it out.

When I create a bar chart, the bars are horizontal. I want the chart bars to be vertical. It tried to rotate the chart so that it is vertical, but the "rotate" options are greyed out.

How do I get those bars vertical?


I'm looking for a formula that pulls the text from a cell unti it hits a space.

I'm using the formula below but keep getting #VALUE results


B1: =LEFT(A1,FIND(",",A1,1))


I know it's not that hard but can't figure it out.

thanks for the help

max


I have a requirement to change the panes in excel.
My excel file will contain more than one tab.
I have to free the first two lines in all the tabs except the first one.
I tried the following code.

Code:

xlsobj.Worksheets(1).Rows("1:2").Select
ActiveWindow.FreezePanes = True


But in this I have to change the Active Worksheets in VBA, Which I want to avoid.
Is there any method to achieve this without selecting the cells.
Something like
Code:

xlsobj.Worksheets(1).Rows("1:2").FreezePanes = True





I saw two threads in this forum that asked this question, with no good answer. I am posting this solution for anybody still struggling with this.

The question:
How can you prevent a cell's contents from overflowing into the next cell?

Of course, you can make the column wider or turn on text wrapping, but you might not want to. Each of those solutions can mess up the layout of your sheet. You may just want to truncate the value.

Some people have suggested putting a space in the next cell. This is unnecessary, a pain in the but, and will mess up any ISBLANK type formulas, among other things.

The solution:
Select the cells in question and turn on text wrapping (Format>Cells>Alignment>Wrap Text).
Now select the row(s) in question and manually set the row height, by right clicking the row number and selecting "Row Height". Check the height of an adjacent row for a good value.

Your cells will now not spill over either horizontally or vertically. They will simply truncate anything that doesn't fit.

Be careful now, because parts of your data may be hidden. This can cause its own set of problems if one or two digits are neatly hidden away. Think ahead if other people might be using this sheet, and not be expecting to have some data hidden.

Tested in Excel 2002