Re: autosize chart
Thanks Andy. It works well.
Re: autosize chart
Thanks Andy. It works well.
Re: autosize chart
Thanks...
It works well.
One more question please.
Before adding newseries to the chart, I want to check for and delete any existing series in the chart. Please help me to implement this with VBA code.
Thanks
Leyo
Re: autosize chart
Hello
I have attached an example worksheet.
In sheet2, when I execute "failedcpk" sub, values less than sheet2.range("aq1") is copied from sheet1. When the cpkchart sub is executed, the barchart "BAR1" is assigned with the source data. The range of sourcedata keeps on varying with the number in range(aq1"). I want the chart also resize with it.
Hello
In my Excel analysis sheet, I have a bar chart "BAR1". The source data of the chart is assigned using the following macro. If the source data is large, the chart is not clear. Is there a way to autosize the chart depending on the source data range so that the chart is always clear?
Sub charting()
If Range("s6") = 0 Then GoTo l1
ActiveSheet.ChartObjects("BAR1").Activate
ActiveChart.ChartArea.Select
If Range("s6") = 1 Then ActiveChart.SetSourceData Source:=Sheets("Summary").Range("W6:x6"), PlotBy:=xlColumns
If Range("s6") > 1 Then ActiveChart.SetSourceData Source:=Sheets("Summary").Range(Worksheets("summary").Range("W6:x6"), Worksheets("summary").Range("w6:x6").End(xlDown)), PlotBy:=xlColumns
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "=Summary!R13C18"
ActiveChart.ChartTitle.Font.Size = 10
ActiveChart.ChartTitle.Font.ColorIndex = 5
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Interior.ColorIndex = xlNone
With ActiveChart.Axes(xlCategory)
.CrossesAt = 1
.TickLabelSpacing = 1
.TickMarkSpacing = 1
.AxisBetweenCategories = False
.ReversePlotOrder = False
End With
ActiveChart.SeriesCollection(1).Interior.ColorIndex = 3
With ActiveChart
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = _
"Measurements failed"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "# PA modules Failed"
ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue
ActiveChart.SeriesCollection(1).DataLabels.Font.Bold = True
End With
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnit = 1
.MajorUnit = 1
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveSheet.Shapes("BAR1").AutoSize
Exit Sub
l1:
ActiveSheet.ChartObjects("BAR1").Activate
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Selection.Delete
End Sub
Display More
Re: advanced filter not working
Thanks Sir
It is working now.
Hello
In the attached file, I have some data in the range "ab5:ab10". Used the advanced filter with unique True to copy the unique data in range "ab5:ab10" to range aa5 with the following code:
Worksheets("summary").Range(Worksheets("summary").Range("ab5"), Worksheets("summary").Range("ab5").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, copytorange:=Worksheets("Summary").Range("aa5"), unique:=True
For some reason, I am getting all the data in range aa5..., not the unique one.
What is the error here?
Appreciate your help.
Thanks
Hello
In the attached excel file, In am looking for cells that are bold in the range"F12:H13" with the following code. It works well:
Sub cc()
Dim st As Range, c As Range, i As Integer
i = 1
Set st = Range("f12:h13")
For Each c In st
If c.Font.Bold = True Then Cells(i, 1) = c.Value: i = i + 1
Next c
End Sub
In the range "F6:h7", I have the same numbers and applied conditional formatting so that cells with value between 13 and 35 will be bold. The above code with "set st = range("f6:h7")" is not working.
Appreciate your help to understand this.
Hello
We have an internal website to get all of reuslts of the testing done in the lab. In the website, certain selections like, product name, measurement name,date etc needs to selected. Once the web site finds the data, there is an option "Run Excel" to select. Once "Run Excel" is selected, a new excel file with all the test results is opened in a new window. Now, I can save the excel file. This was working fine with Windows 2000 and excel 2000.
Now, I migrated to WidowsXp and Excel2003. With "Run Excel" option, the excel file is opened in the internet explorer , not as a separate window. I am not able to save the excel file. IS it due to some settings in Excel/Windows XP?
Appreciate your help.
Re: Bar Chart with percentage
Thanks.
You have asked "Apply data labels to the new series and then link each data label to the cell containing the % value."
I am not able to apply data labels to the new series. When I try to apply data labels with format data series, the first series is selected.
Hello
Please have a look on the attached excel file.I have data which shows gain in each year. It is plotted with Bar chart. Gain in terms % is in column J. Is it possible to show the % also on each bar ?. For example the bar for Year1 should show the text 25 %.
Appreciate your help.
Re: Compare two ranges
This was exactly I was looking for....Thanks
Re: Compare two ranges
Sorry for the mess..
Trying to comapre Column C to Column A and the result should be in Column D.
Hello
This might be pretty easy for you. In the attached sheet, I have some data in "A4:A98" and another set of data in "B4:B97". One data is missing in "B4:B97" than in "A4:A98". Looking for a VBA way to find the missing data. I think we can implement this using find function. There must be an easy way...
Appreciate your help.
Thanks
Leyo
Re: advanced filter on visible cells
I couldn't understand your suggection. Is it something like this:
Worksheets("Raw data").Activate
lngrows = Range("b65536").End(xlUp).Row
Set rngdata = Range("A1:al" & lngrows)
rngdata.AutoFilter field:=10, Criteria1:="FAIL"
rngdata.AdvancedFilter , field = 11, copytorange = Worksheets("summary").Range("b3"), unique = True
Thanks
Hello
I have lots of data in the sheet"Raw data" of the workbook. I am autofiltering on field 10 with criteria "FAIL". In filed 11, I will get all failed units. because of the nature of data, there are duplicate failed units in filed 11. Right now, I am using the following code to get the unique failed units. Basically, I am copying the visible cells to range c4 in the "summary" sheet and applying the advanced filter in range c4.
Is it possible to do adavnced filtering to get the unique values on the specialcells(visible)?.
This is the code I am using (This is a section of my actual code)
Worksheets("Raw data").Activate
lngrows = Range("b65536").End(xlUp).Row
Set rngdata = Range("A1:al" & lngrows)
rngdata.AutoFilter Field:=10, Criteria1:="FAIL"
Set rngtocopy = Nothing
On Error Resume Next
Set rngtocopy = Range(rngdata(2, 11), rngdata(lngrows - 2, 11)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rngtocopy Is Nothing Then
rngtocopy.Copy Destination:=Worksheets("summary").Range("c4")
On Error Resume Next
Worksheets("summary").Range(Worksheets("summary").Range("c4"), Worksheets("summary").Range("c4").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, copytorange:=Worksheets("Summary").Range("b3"), unique:=True
Worksheets("summary").Range(Worksheets("summary").Range("c4"), Worksheets("summary").Range("c4").End(xlDown)).ClearContents
Display More
I have the following code as part of worksheet event change. If any cell on range("n21:N137") is clicked, I want range("a1") = clicked cell text. Based on this , couple of other cells also change. But when I execute the following code, getting the error message:
"The instruction has referred memeory....could not be read. Click OK to teriminate the program" and excel shuts down. Is anything wrong in the code?
Appreciate your help.\
Re: Shortcut for selecting all the files
Thanks
This is the way I am doing it. Thought if there is a shortcut...will be easier.
Hello
In my code I am using the "getopenfilename" with Multiselect true to open a bunch of files from a folder. Is there a shortcut key to select all the displayed files in the folder when the getopenfilename is executed ?
Re: issues with autofiltering
Thanks Andy
This works well and my code is very fast now.
Re: issues with autofiltering
Thanks Andy
That was big mistake from my side...
In the second portion of the code as shwon below, after setting the rngtocopy, I am removing the auto filter before copying to the cell next to the last in the column. If I don't remove the autofilter, The following line gives error:
rngtocopy.Copy Destination:=Cells(6, 84 + (d - 1) * 51 + i + (t - 1) * 17).End(xlDown).Offset(1, 0).
If Autofilter is active, won't the above statement work?.
The code I presented is a part of a large worksheet...so making autofilter ON/OFF makes the code slow.
For i = 0 To 15
rngdata.AutoFilter Field:=2, Criteria1:=Format(5)
rngdata.AutoFilter Field:=3, Criteria1:=Format(i)
Set rngtocopy = Nothing
On Error Resume Next
Set rngtocopy = Range(rngdata(2, 4), rngdata(lngrows - 2, 4)).SpecialCells(xlCellTypeVisible)
rngdata.AutoFilter
On Error Goto 0
If Not rngtocopy Is Nothing Then
rngtocopy.Copy Destination:=Cells(6, 84 + (d - 1) * 51 + i + (t - 1) * 17).End(xlDown).Offset(1, 0)
End If
Next i
Display More