Replies

Thanks Hans,

It works.

Thanks Rudi,

Unfortunately the case is not that simple. There are large amount of tables in different pages across 300+ pages. And to make things worse, that last comma is not the only comma in the table cell.

Hi Hans, I think I got it now…should have tried the formula with column B. Thank you.

Thanks Hans,

Am I missing something? The formula you suggested be placed in column C “removed” the repeated Management Company name (only cell C2 and C22 have contents now after the formula is copied down). I need to get rid of repeated Name (column and at the same time maintaining that sorting.

Regards
Ridz

Thanks Hans,

Is it possible to automate the plotting though? I am still using the codes you provided a while back to open each workbook and plot line charts. I am stumbled on plotting this kind of bar charts now.

Regards
Riduwan

Thank you so much…it works wonder.

Thanks Hans,

Below are the lines of codes that were mostly “recorded”. I am still trying to put everything together and make them do: create a new XLS file from each record, transpose the data (column A is now the dates, column B is the NAV data), plot the charts, fix those labels issues and save the files. All with a click of button. Can help?

Sub CopyTranspose()
Sheets(“NAV”).Select
Sheets(“NAV”).Move
Rows(“2:2”).Select
Selection.Delete Shift:=xlUp
Rows(“1:2″).Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Cells.Find(What:=”100”, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

Sub PlotLineChart()
Dim myLineChart As Chart
Dim dBeginDate As Date
Dim dEndDate As Date
Dim intMonths As Integer
Application.ScreenUpdating = False
Const strTargetChartPath = “D:NotProcessed”
Range(“B1”).Select
newFileName = Selection.Cells.Value
newFileName = strTargetChartPath & newFileName
ActiveWorkbook.SaveAs Filename:=newFileName
Application.ScreenUpdating = False
Range(“B1”).Select
FinalPlotRow = Range(“B65536”).End(xlUp).Row
FinalPlotCell = “A1”
CompleteBlock = “B” & FinalPlotRow & “:A1”
Range(CompleteBlock).Name = “AreaToPlot”
MinimumValue = Application.Min(Range(“B:B”))
MinimumValue = Application.RoundDown(MinimumValue, -1)
MaximumValue = Application.Max(Range(“B:B”))
MaximumValue = Application.RoundUp(MaximumValue, -1)
MajorUnitValue = Application.Even(Application.RoundDown((MaximumValue – MinimumValue) / 4, 0))
‘ Beginning date.
Range(“B1”).Select
FinalRow = Range(“B65536”).End(xlUp).Row
FinalCell = “A” & FinalRow
dBeginDate = DateValue(Range(FinalCell))
‘ Ending Date.
dEndDate = DateValue(“31/7/2007”)
‘ Calculate number of months between dates.
intMonths = ((Year(dEndDate) – Year(dBeginDate)) * 12) + _
Month(dEndDate) – Month(dBeginDate)
‘ Display number of months.
NumberOfMonthValue = Application.RoundDown(Str$(intMonths) / 5, 0)
If NumberOfMonthValue <= 0 Then
NumberOfMonthValue = 1
End If
Set SourceData = ActiveSheet.Range("AreaToPlot")
Set myLineChart = Charts.Add
With myLineChart
.ChartType = xlLine
.SetSourceData Source:=SourceData, PlotBy:=xlColumns
.HasLegend = False
.Location Where:=xlLocationAsNewSheet
.HasTitle = False
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = NumberOfMonthValue
.MajorUnitScale = xlMonths
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = False
.ReversePlotOrder = False
End With
With ActiveChart.Axes(xlValue)
.MinimumScale = MinimumValue
.MaximumScaleIsAuto = True
.MajorUnit = MajorUnitValue
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Sheets("Chart1").Select
Sheets("Chart1").Name = "Chart"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Data"
Sheets("Chart").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Thank you in advance.

How on earth do you know that I need to restart the PC?

Thank you Hans.

Thanks Hans,

I did as suggested (declaration and set the Options). But now I bumped into Error 424 at the same line.

Regards
Ridz

1. It was not declared anywhere else.

2. Word is running at all time.

Cheers
Ridz

More than good for me. I just need to replace the first occurance of ”

” after MemberNo.

Thank you once again.

Thanks again Hans,

I probably did not make myself clear. Is it possible to make the combined data to be in Column A only with delimiters “” to represent the original Column and ”

” to represent original Row? I need to pull the data into Word and do mass find and replace.

Thanks.

You are a life saver…

Thank you so much

Thank you thank you….

At least it is pasted on Word now. But I have a new problem…the client has named the file with characters such as “.”, “&”, and I believe it is causing error 5152: Invalid file name at

.ActiveDocument.SaveAs Filename:=newWord

. Any quick fix apart from manually going through 1218 files and change the filenames?

Thank you again Hans

I switched to Word and could not see anything out of ordinary, except the new doc is created.

I tried your suggestion to change the codes – now it gives me run time error 429: ActiveX component can’t create object at

Set appWord = GetObject(, "Word.Application")

Regards
Ridz