Free Microsoft Excel 2013 Quick Reference

.SpecialCells(xlLastCell).Select does not work in vbscript

Hi,
vbscript does not seem to understang anything with 'xl' Does anyone knowthe corresponding number for xlLastCell.
e.g.
xlNone = -4142
Thankyou


hallo everybody,
i need help because my vba code works in English Excel but does not work in German Excel or French Excel. i have a error message in this line :
ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C4"
error message after Translation to the german : " Application defined or object defined error"
please help me.

	VB:
	
 Workbook_Open() 
    Dim num As Range 
    Dim num1 As Range 
    Dim pNom As String 
    Dim pId As String 
    Dim pIdSup As String 
    Dim pSupName As String 
    Dim pPers As String 
    Dim pMaxPer As String 
    Dim pPlant As String 
    Dim valPlant As Range 
    pNom = ActiveWorkbook.Name 
    With Application 
        .DecimalSeparator = "." 
        .ThousandsSeparator = " " 
        .UseSystemSeparators = False 
    End With 
     'Application.DisplayAlerts = False
     
    Application.ScreenUpdating = False 
     
    If Sheets("Properties").Cells(1, 256).Text  "OK" Then 
        Sheets("Properties").Cells(1, 256).Value = "OK" 
        Sheets("MasterData").Select 
         
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ 
            "ODBC;DBQ=C:DataABIS;DefaultDir=C:DataABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _ 
            ), Array( _ 
           
"=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit"
_ 
            ), Array("Sync=Yes;")), Destination:=Range("A1")) 
            .CommandText = Array( _ 
            "SELECT DISTINCT TXTODBC.ID_SUPPLIER" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC WHERE
TXTODBC.ID_SUPPLIER'null'") 
            .Name = "ABIS_1" 
            .FieldNames = True 
            .RowNumbers = False 
            .FillAdjacentFormulas = False 
            .PreserveFormatting = True 
            .RefreshOnFileOpen = False 
            .BackgroundQuery = True 
            .RefreshStyle = xlInsertDeleteCells 
            .SavePassword = False 
            .SaveData = True 
            .AdjustColumnWidth = True 
            .RefreshPeriod = 0 
            .PreserveColumnInfo = True 
            .SourceConnectionFile = "C:DataABISABIS.dsn" 
            .Refresh BackgroundQuery:=False 
        End With 
        nb = 2 
        While Sheets("MasterData").Cells(nb, 1).Text  "" 
            If pId = "" Then 
                pId = Sheets("MasterData").Cells(nb, 1).Text 
            Else 
                pId = pId + ";" + Sheets("MasterData").Cells(nb, 1).Text 
            End If 
            nb = nb + 1 
        Wend 
        Sheets("Properties").Select 
        Sheets("Properties").Cells(8, 3).Value = pId 
        Sheets("MasterData").Select 
        pIdSup = pId 
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ 
            "ODBC;DBQ=C:DataABIS;DefaultDir=C:DataABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _ 
            ), Array( _ 
           
"=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit"
_ 
            ), Array("Sync=Yes;")), Destination:=Range("B1")) 
            .CommandText = Array( _ 
            "SELECT DISTINCT TXTODBC.YEAR_MONTH" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" &
Chr(10) & "WHERE (TXTODBC.YEAR_MONTH'6 month  Rolling')" _ 
) 
            .Name = "ABIS_2" 
            .FieldNames = True 
            .RowNumbers = False 
            .FillAdjacentFormulas = False 
            .PreserveFormatting = True 
            .RefreshOnFileOpen = False 
            .BackgroundQuery = True 
            .RefreshStyle = xlInsertDeleteCells 
            .SavePassword = False 
            .SaveData = True 
            .AdjustColumnWidth = True 
            .RefreshPeriod = 0 
            .PreserveColumnInfo = True 
            .SourceConnectionFile = "C:DataABISABIS.dsn" 
            .Refresh BackgroundQuery:=False 
        End With 
         
        nb = 2 
        While Sheets("MasterData").Cells(nb, 2).Text  "" 
            If pPers = "" Then 
                pPers = Sheets("MasterData").Cells(nb, 2).Text 
            Else 
                pPers = pPers + ";" + Sheets("MasterData").Cells(nb, 2).Text 
            End If 
            nb = nb + 1 
        Wend 
        Sheets("Properties").Select 
        Sheets("Properties").Cells(10, 3).Value = pPers 
        Sheets("MasterData").Select 
         
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ 
            "ODBC;DBQ=C:DataABIS;DefaultDir=C:DataABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _ 
            ), Array( _ 
           
"=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit"
_ 
            ), Array("Sync=Yes;")), Destination:=Range("C1")) 
            .CommandText = Array( _ 
            "SELECT MAX(TXTODBC.YEAR_MONTH)" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" & Chr(10) &
"WHERE (TXTODBC.YEAR_MONTH'6 month  Rolling')" _ 
) 
            .Name = "ABIS_3" 
            .FieldNames = True 
            .RowNumbers = False 
            .FillAdjacentFormulas = False 
            .PreserveFormatting = True 
            .RefreshOnFileOpen = False 
            .BackgroundQuery = True 
            .RefreshStyle = xlInsertDeleteCells 
            .SavePassword = False 
            .SaveData = True 
            .AdjustColumnWidth = True 
            .RefreshPeriod = 0 
            .PreserveColumnInfo = True 
            .SourceConnectionFile = "C:DataABISABIS.dsn" 
            .Refresh BackgroundQuery:=False 
        End With 
         
        nb = 2 
        While Sheets("MasterData").Cells(nb, 3).Text  "" 
            If pMaxPer = "" Then 
                pMaxPer = Sheets("MasterData").Cells(nb, 3).Text 
            Else 
                pMaxPer = pMaxPer + ";" + Sheets("MasterData").Cells(nb, 3).Text 
            End If 
            nb = nb + 1 
        Wend 
        Sheets("Properties").Select 
        Sheets("Properties").Cells(12, 3).Value = pMaxPer 
        Sheets("MasterData").Select 
         
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ 
            "ODBC;DBQ=C:DataABIS;DefaultDir=C:DataABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _ 
            ), Array( _ 
           
"=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit"
_ 
            ), Array("Sync=Yes;")), Destination:=Range("D1")) 
            .CommandText = Array( _ 
            "SELECT DISTINCT TXTODBC.PLANT" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC where TXTODBC.PLANT'null'" &
Chr(13) & "" & Chr(10) & "" _ 
) 
            .Name = "ABIS_4" 
            .FieldNames = True 
            .RowNumbers = False 
            .FillAdjacentFormulas = False 
            .PreserveFormatting = True 
            .RefreshOnFileOpen = False 
            .BackgroundQuery = True 
            .RefreshStyle = xlInsertDeleteCells 
            .SavePassword = False 
            .SaveData = True 
            .AdjustColumnWidth = True 
            .RefreshPeriod = 0 
            .PreserveColumnInfo = True 
            .SourceConnectionFile = "C:DataABISABIS.dsn" 
            .Refresh BackgroundQuery:=False 
        End With 
         
        nb = 2 
        While Sheets("MasterData").Cells(nb, 4).Text  "" 
            If pPlant = "" Then 
                pPlant = Sheets("MasterData").Cells(nb, 4).Text 
            Else 
                pPlant = pPlant + ";" + Sheets("MasterData").Cells(nb, 4).Text 
            End If 
            nb = nb + 1 
        Wend 
        Sheets("Properties").Select 
        Sheets("Properties").Cells(11, 3).Value = pPlant 
        Sheets("MasterData").Select 
         
        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ 
            "ODBC;DBQ=C:DataABIS;DefaultDir=C:DataABIS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId" _ 
            ), Array( _ 
           
"=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommit"
_ 
            ), Array("Sync=Yes;")), Destination:=Range("E1")) 
            .CommandText = Array( _ 
            "SELECT DISTINCT TXTODBC.SUPPLIER_NAME" & Chr(13) & "" & Chr(10) & "FROM TXTODBC.TXT TXTODBC" & Chr(13) & "" &
Chr(10) & " WHERE (TXTODBC.SUPPLIER_NAME'null')" _ 
) 
            .Name = "ABIS_5" 
            .FieldNames = True 
            .RowNumbers = False 
            .FillAdjacentFormulas = False 
            .PreserveFormatting = True 
            .RefreshOnFileOpen = False 
            .BackgroundQuery = True 
            .RefreshStyle = xlInsertDeleteCells 
            .SavePassword = False 
            .SaveData = True 
            .AdjustColumnWidth = True 
            .RefreshPeriod = 0 
            .PreserveColumnInfo = True 
            .SourceConnectionFile = "C:DataABISABIS.dsn" 
            .Refresh BackgroundQuery:=False 
        End With 
         
        nb = 2 
        While Sheets("MasterData").Cells(nb, 5).Text  "" 
            If pSupName = "" Then 
                pSupName = Sheets("MasterData").Cells(nb, 5).Text 
            Else 
                pSupName = pSupName + ";" + Sheets("MasterData").Cells(nb, 5).Text 
            End If 
            nb = nb + 1 
        Wend 
        Sheets("Properties").Select 
        Sheets("Properties").Cells(9, 3).Value = pSupName 
        Sheets("MasterData").Select 
         
         'ActiveCell.FormulaR1C1 = "OK"
         
        Sheets("Properties").Select 
        Range("C8:C9").Select 
        Selection.Copy 
         
        Sheets("chart").Select 
        Range("F4").Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
         
        Sheets("Supplier Overview").Select 
        Range("F4").Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
        Sheets("Supplier Overview").Cells(6, 6).Value = Sheets("Properties").Cells(12, 3).Value 
        Sheets("Supplier Overview").Visible = False 
         
        Sheets("Properties").Select 
        Range("C10:C11").Select 
        Selection.Copy 
         
        Range("C117").Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
         
        Range("C11").Activate 
        Selection.Replace What:="%%%*", Replacement:="", LookAt:=xlPart, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
        ReplaceFormat:=False 
         
        Sheets("Properties").Select 
        Range("D19:K32").Select 
        Selection.Copy 
        Sheets("Chart").Select 
        Range("L49").Select 
        ActiveSheet.Paste 
        Sheets("Chart").Cells(6, 6).Value = Sheets("Properties").Cells(12, 3).Value 
         'Sheets("Supplier Overview").Select
         'Range("L49").Select
         'ActiveSheet.Paste
         
        Sheets("Data").Select 
        Range("A1").Select 
        ActiveSheet.PivotTables(1).PivotCache.Refresh 
        Sheets("Data2").Select 
        Range("A1").Select 
        ActiveSheet.PivotTables(1).PivotCache.Refresh 
        Sheets("Data").Select 
         
         'Sheets("Supplier Overview").Visible = False
         
        i = 2 
        Do While Sheets("MasterData").Cells(i, 4).Text  "" 
            plant = Trim(Sheets("MasterData").Cells(i, 4).Text) 
             
            Sheets("chart").Select 
            Sheets.Add 
            Sheets("chart").Select 
            Cells.Select 
            Selection.Copy 
            Sheets((i + 2)).Select 
            ActiveSheet.Paste 
            Application.CutCopyMode = False 
            Sheets((i + 2)).Cells(3, 4).Value = plant 
            Sheets((i + 2)).Select 
            Sheets((i + 2)).Name = plant 
             
            ActiveSheet.ChartObjects("Chart 1").Activate 
            ActiveChart.Axes(xlValue).MajorGridlines.Select 
            ActiveChart.PlotArea.Select 
            ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C4:R62C4" 
             ' line error  " Application defined or object defined error"
            ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C4" 
            ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveWindow.Visible = False 
            ActiveSheet.ChartObjects("Chart 2").Activate 
            ActiveChart.PlotArea.Select 
            ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C5:R62C5" 
            ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C5" 
            ActiveWindow.Visible = False 
             'Windows("624159_2008_05_16_16_49_00.xls").Activate
            ActiveSheet.ChartObjects("Chart 9").Activate 
            ActiveChart.PlotArea.Select 
            ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C6:R62C6" 
            ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C6" 
            ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveWindow.Visible = False 
             'Windows("624159_2008_05_16_16_49_00.xls").Activate
            ActiveSheet.ChartObjects("Chart 8").Activate 
            ActiveChart.PlotArea.Select 
            ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C10:R62C10" 
            ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C10" 
            ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(2).Values = "='" + plant + "'!R50C15:R62C15" 
            ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(3).Values = "='" + plant + "'!R50C16:R62C16" 
            ActiveWindow.Visible = False 
             'Windows("624159_2008_05_16_16_49_00.xls").Activate
            ActiveSheet.ChartObjects("Chart 5").Activate 
            ActiveChart.Axes(xlValue).MajorGridlines.Select 
            ActiveChart.PlotArea.Select 
            ActiveChart.SeriesCollection(1).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(1).Values = "='" + plant + "'!R50C7:R62C7" 
            ActiveChart.SeriesCollection(1).Name = "='" + plant + "'!R49C7" 
            ActiveChart.SeriesCollection(2).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(2).Values = "='" + plant + "'!R50C8:R62C8" 
            ActiveChart.SeriesCollection(2).Name = "='" + plant + "'!R49C8" 
            ActiveChart.SeriesCollection(3).XValues = "='" + plant + "'!R50C1:R62C1" 
            ActiveChart.SeriesCollection(3).Values = "='" + plant + "'!R50C9:R62C9" 
            ActiveChart.SeriesCollection(3).Name = "='" + plant + "'!R49C9" 
            ActiveWindow.Visible = False 
             'Windows("624159_2008_05_16_16_49_00.xls").Activate
            Range("A1").Select 
             
            i = i + 1 
            If Environ("REPORT_SERVER_CLOSE") = "YES" Then 
                ActiveSheet.PageSetup.PrintArea = "$A$1:$K$63" 
                With ActiveSheet.PageSetup 
                    .LeftHeader = "" 
                    .CenterHeader = "" 
                    .RightHeader = "" 
                    .LeftFooter = "" 
                    .CenterFooter = "" 
                    .RightFooter = "" 
                    .LeftMargin = Application.InchesToPoints(0.787401575) 
                    .RightMargin = Application.InchesToPoints(0.787401575) 
                    .TopMargin = Application.InchesToPoints(0.984251969) 
                    .BottomMargin = Application.InchesToPoints(0.984251969) 
                    .HeaderMargin = Application.InchesToPoints(0.4921259845) 
                    .FooterMargin = Application.InchesToPoints(0.4921259845) 
                    .PrintHeadings = False 
                    .PrintGridlines = False 
                    .PrintComments = xlPrintNoComments 
                    .PrintQuality = 300 
                    .CenterHorizontally = False 
                    .CenterVertically = False 
                    .Orientation = xlPortrait 
                    .Draft = False 
                    .PaperSize = xlPaperA4 
                    .FirstPageNumber = xlAutomatic 
                    .Order = xlDownThenOver 
                    .BlackAndWhite = False 
                    .Zoom = False 
                    .FitToPagesWide = 1 
                    .FitToPagesTall = 1 
                    .PrintErrors = xlPrintErrorsDisplayed 
                End With 
            End If 
             '        Sheets("chart").Select
             '       Sheets("chart").Copy After:=Sheets((i + 1))
             '        Sheets("chart (2)").Select
             '        Sheets("chart (2)").Name = plant
             '        Range("A1").Select
             '        Sheets(plant).Select
             '        Sheets(plant).Cells(3, 4).Value = plant
             
        Loop 
        Sheets("chart").Visible = False 
        Sheets("Data").Visible = False 
        Sheets("Data2").Visible = False 
        Sheets("MasterData").Visible = False 
        Application.CutCopyMode = False 
        Application.ScreenUpdating = True 
        Sheets("Supplier  Overview").Select 
        ActiveWorkbook.Save 
         
        If Environ("REPORT_SERVER_CLOSE") = "YES" Then 
            Sheets("Properties").Select 
            ActiveWindow.SelectedSheets.Visible = False 
            Sheets("Supplier  Overview").Select 
            Sheets("Supplier  Overview").Move Before:=Sheets(4) 
            ActiveWorkbook.PrintOut Copies:=1, Collate:=True 
            Set fs = CreateObject("Scripting.FileSystemObject") 
            If fs.FileExists("C:5_PANEL_PDF" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf") Then 
                fs.deleteFile "C:5_PANEL_PDF" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") + ".pdf" 
            End If 
            If fs.FileExists("C:5_PANEL_PDF" + pIdSup + "_" + Format(DateAdd("m", -2, Date), "mmmyyyy") + ".pdf") Then 
                fs.deleteFile "C:5_PANEL_PDF" + pIdSup + "_" + Format(DateAdd("m", -2, Date), "mmmyyyy") + ".pdf" 
            End If 
            If fs.FileExists("C:5_PANEL_PDF" + pIdSup + "_" + Format(DateAdd("m", -3, Date), "mmmyyyy") + ".pdf") Then 
                fs.deleteFile "C:5_PANEL_PDF" + pIdSup + "_" + Format(DateAdd("m", -3, Date), "mmmyyyy") + ".pdf" 
            End If 
             
             
             
            fs.movefile "C:5_PANEL_PDFTemp.pdf", "C:5_PANEL_PDF" + pIdSup + "_" + Format(DateAdd("m", -1, Date), "mmmyyyy") +
".pdf" 
            ActiveWorkbook.Close (False) 
            ActiveWorkbook.Close 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I have this code in the Worksheet/SheetCalculate event of my excel sheet which was created in excel 2000. It works very well. However when the same worksheet in opened in excel 2002 and a cell value changed . It does not execute the function as the unprotect worksheet code does not work in excel 2002.

Can anyone help me with this. This is my code which works well in excel 2000.

ActiveSheet.Unprotect
On Error GoTo ERRMSG
ActiveSheet.Shapes("SudhirP").Select
Selection.Cut
Range("d22:d36").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Range("d37").Select
ERRMSG:
Range("d22:d36").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True
Range("a1").Select

thanks
Neeta

I have this formula which works in word 2003 but does not work in word 2007. I made the template in word 2003.

=IF(AND(B8="",C8="",F8=""),"",IF(ISERROR(VLOOKUP(F8,'Stock Calc'!I$5:J$1000,2,FALSE)),"",VLOOKUP(F8,'Stock Calc'!I$5:J$1000,2,FALSE)))

Stoc Calc is a worksheet which has pivot tables and the formula is referring to the values in the pivot tables.

Please advise.

I have recently switched to Excel 2007. I have written numerous macros in VB for the previous version of macro. In Excel 2007, I find that the relative cell referencing method I used (i.e. RC) does not work in Excel 2007. For example, I used RC6 to refer to a cell at the 6th column on the same row. In the previous Excel, if this is written for say Cell A2, the reference would refer to cell F2. In Excel 2007, it refers to nothing. I have numerous codes written in this way and it would be a real pain to correct them all to fit in to Excel 2007 (and would probably not run in older versions of Excel). Does anyone faced similar problems? How did you solve it?

Thanks.

I have the following code in an excel 2000 spreadsheet:

Private Sub CommandButton1_Click()

For Each cell In Range("c5:H32")
cell.Interior.ColorIndex = 0
Next cell

End Sub

But it does not work in Excel 97.

Any ideas why? I get run time error 1004

Ian,

After i save the file my hyperlink does not work in excel 2003.
I have tried right click as well as the insert hyperlink methods. It work
the first time, but if I save the file or close it and open it again, it does
not work.
Please suggest.

Chinesh

Hello,

I'm a beginner with Excel programming. I inherited a report developed in Excel 2003. The report has a few charts. The axes on the chart change based on a VBA code. The scale changes in 2003 but does not work in 2007. So the graph appears to cut of the values falling out of range. Here is the code. Any help is appreciated.

Private Sub ComboBox1_Change()
On Error GoTo 1
With Worksheets("CPC, % Supply-NPSR").ChartObjects(2).Chart.Axes(xlValue)
.MaximumScale = Range("AF2").Value
.MinimumScale = Range("AF3").Value
End With

With Worksheets("CPC, % Supply-NPSR").ChartObjects(1).Chart.Axes(xlValue)
.MaximumScale = Range("AE2").Value
.MinimumScale = Range("AE3").Value
End With

It does not work in case cell with decimles. Is there ant formuale to resolve this issue?????

I have a spreadsheet we send to numerous vendors that has all the formula
cells protected. We just received a file back from a vendor with incorrect
formulas in it that the vendor should not have been able to change. I'm
reasonably sure the file went to the vendor protected, and I'm trying to
figure out what happened. The file was created in Excel 2002 and works fine
in Excel 2000. The vendor has Excel 2003 which I don't have access to for
testing, and I'm wondering if the protection does not work in the newer Excel
version. I've included the code below. Thanks for any help on this.

Sheets("Period 1").Select
ActiveSheet.Unprotect Password:="vendorprotect"
Cells.Select
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Locked = True
'Unprotect Weeks Performance and Plan Units
Range("T7:T120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("V7:V120").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("A1").Select
ActiveSheet.Protect Password:="vendorprotect", DrawingObjects:=True,
Contents:=True, Scenarios:=True

The following Macro does not work in Excel 2010 - works fine in Excel 2003. I understand that the FileSearch function is gone since 2007. My VBA programing is limited. When I run the macro, it stops at the Application.FileSearch line. Is there a simple fix to this issue? Again my vba knowledge is limited. Thank you for your help

Private Sub CommandButton1_Click()

ActiveSheet.Unprotect Password:="ADJ"

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim ws As Worksheet
Dim rCount As Integer

rCount = 10

Application.ScreenUpdating = False
Application.DisplayAlerts = True
Application.EnableEvents = True

'On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "K:EC-AdjustmentsLog & Summary SheetsEmployer Accounts"
.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then 'Workbooks in folder

For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

'create a new row
rCount = rCount + 1
'rCount = lCount + 5
'Toggle below sections alternately to comments to get employer numbers and KOs

'Copy and Pastes
'Employer number:
'Copy
wbResults.Sheets("Account Summary").Range("B7").Copy
'Paste
Windows("Log Reports.xls").Activate
ActiveSheet.Cells(rCount, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues

'Employer name:
wbResults.Sheets("Account Summary").Range("B6").Copy
Windows("Log Reports.xls").Activate
ActiveSheet.Cells(rCount, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues

'Log Account Balance:
wbResults.Sheets("Account Summary").Range("B19:G19").Copy
Windows("Log Reports.xls").Activate
ActiveSheet.Range(Cells(rCount, 3), Cells(rCount, 8)).Select
Selection.PasteSpecial Paste:=xlPasteValues

'Adjustments Summary Balance:
wbResults.Sheets("Account Summary").Range("B26:F26").Copy
Windows("Log Reports.xls").Activate
ActiveSheet.Range(Cells(rCount, 9), Cells(rCount, 13)).Select
Selection.PasteSpecial Paste:=xlPasteValues

'KickOuts:
wbResults.Sheets("Account Summary").Range("C30").Copy
Windows("Log Reports.xls").Activate
ActiveSheet.Cells(rCount, 14).Select
Selection.PasteSpecial Paste:=xlPasteValues
wbResults.Sheets("Account Summary").Range("C31").Copy
Windows("Log Reports.xls").Activate
ActiveSheet.Cells(rCount, 15).Select
Selection.PasteSpecial Paste:=xlPasteValues

'Last Notice Date:
wbResults.Sheets("Account Summary").Range("f22").Copy
Windows("Log Reports.xls").Activate
ActiveSheet.Cells(rCount, 16).Select
Selection.PasteSpecial Paste:=xlPasteValues

wbResults.Close SaveChanges:=False
Next lCount
End If
End With
'Date Stamp Last Run
ActiveSheet.Range("P7").Copy
ActiveSheet.Cells(7, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

ActiveSheet.Protect Password:="ADJ"

End Sub

I have the following code I made using Excel 2010...

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets(1).Range("C2:D10000")
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScale = 0
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(DataValue).Name = "Data " & DataValue
    ActiveChart.Parent.Name = "Chart"
I am trying to implement it in Excel 2003 and it does not work. I tried replacing "ActiveSheet.Shapes.AddChart.Select" with "Charts.Add". It would then make a chart in its own sheet instead of in the sheet I want it in. Any help in correcting what excel 2003 does not like about the above code would be appreciated.

I'm having trouble with Excel sorting in a VBScript. I have a number of
cells containing data, starting at row 4. I'm trying to sort each column
from row 4 through to the end using the following code:

Set objRange = objExcel.Range(Chr(64+column)+"4", Chr(64+column)&row ).Select
Set objRange2 = objExcel.Range(Chr(64+column)+"4")
objRange.Sort objRange2,,,,,,,1

objRange is my range, objRange2 is the first cell of the column I wish to
sort.

I also tried:

objRange.Sort objRange,,,,,,,1

The sort method does not work in VBScript, nor do a large number of
variations of it! I know from a lot of reading that some Excel functionality
isn't exposed too well in VBScript...

However, the following works for the entire column, using a header row:

Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Sort objRange,,,,,,,1

Any suggestions would be much appreciated.

I have a file that works on one user login but not in another one. I cant understand. Both have admin rights. IT is not a windows issue. Is it something in the VB code I have? Why does it work in one user account and not another?? I am stumped. HELP GUYS!

http://www.box.net/shared/t999283714
http://www.box.net/shared/t999283714

The calculated item I am using for a percentage change works in all cells but
does not compute in the "Grand Total" Column. The formula is "$ diff 07 to
06/2006" where the numerator is also a calculated item.
Putting the cursor in the Grand Total cells and then going to "formulas"
does not give me the option to enter a new formula.
Any help would be greatly appreciated!

The right 'Alt' key does not work in Excel 2003. The key plus the <enter> key
provides a paragraph break in a cell of text. When I discovered the problem
at work, I had to have MS Office re-installed (a few times!), but is not my
preferred option at home.

See enclosed file.

I dont understand why my code does not work. In this example i am trying to find the contents of cell A1. But for some reason the formatting of this cell prevents me from finding a match the text string.....any ideas how to get around this because in my main program i need to be able to find these header strings.

Hi,

I got a question about adding a custom button to the Excel toolbar. I know it is simple, but something does not work in my code below. Could please anyone help!


	VB:
	
 Workbook_Open() 
    Dim ReportButton As MsoControlType 
     
    If MsgBox("Would you like to run a report?", vbYesNo, "Confirmation") = vbYes Then 
        Run "MenuMacro" 
    Else 
        Application.CommandBars("Visual Basic").Controls.Add Type:=msoControlButton, ID:=2950, Before:=7 
        Set ReportButton: = msoControlButton 
        With ReportButton 
            ReportButton.Caption = "Run Report" 
            ReportButton.OnAction = "MenuMacro" 
        End With 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I am trying to achieve with this code is if user does not want to run the report when prompted and clicks "No", a custom button is created, and user can run report at a later time by clicking on this button.

Thanks for your help!

Nat

Hi,

I am using this statement in vba

Code:
but i can see the contents of those columns are not auto-fitted.. if i double click the column heading manually the column is
correctly autofitted.. any ideas why this does not work in VBA ?

thanks
andy

Why does this formula work in a Worksheet formula
Code:
This works fine in a sheet.  Returns the number I expect.

But does not work in VBA

Code:
Dim X As Long
X = WorksheetFunction.Match(Date, Workbooks("bookname.xls").Sheets("Sheet Name").Range("A:A"), 0)
Error is "Unable to get the Match Property of the Worksheet Function Class

I have tried using A1:A100 instead of A:A
I've tried leaving the Match Type Blank, 0, 1, -1.

All error.
Column A is valid dates (Considering the Match Formula in a Cell works).
There are some blanks in column A, but I don't think that matters.
They are Values, not Formulas.

Any ideas?

Thanks

Need some help on below code. This code is work fine in MS Office XP, but it does not work in MS Office 2000.

Code:
Sub sortSoldBought()
Application.ScreenUpdating = False
For Each ws In Worksheets
    If Not (ws.Name = "INTRODUCTION" Or ws.Name = "Working") Then
        lastRow = Sheets(ws.Name).Cells(3, 1).Value
        If lastRow > 6 Then
        'Sort Sold
        Sheets(ws.Name).Range("A6:I" & lastRow - 1).Sort Key1:=Sheets(ws.Name).Range("A6"), Order1:=xlAscending,
Key2:=Sheets(ws.Name).Range("F6") _
        , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
            xlSortNormal
        'Sort Bought
        Sheets(ws.Name).Range("A6:E" & lastRow).Sort Key1:=Sheets(ws.Name).Range("A6"), Order1:=xlAscending,
Key2:=Sheets(ws.Name).Range("B6") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:= _
            xlSortNormal
        End If
    End If
Next ws
Application.ScreenUpdating = True
End Sub
Thanks in advance.
~Dylan

Dear all,

I have an excel file with 30+ columns, and auto filter in use. Many users
may open the file and use autofilter to look for information or update
information in the file. I have also protect the worksheet as some columns
contain formular which I don't want the general users to modify.

As there are so many columns in the file, it is difficult to see clearly
which columns has been filtered in the previous save action. It seems that
the "Show All" button does not work in a protected worksheet. I use excel
2002, and the "Show All" button is dim even the autofilter has applied.

Is there any workaround?? Perhaps a macro which do the "Show All"?

Many thanks,
Marisa

Hi, I have a workbook that is years old. At the time I created it, I
included code so numbers (time) could be entered without the use of a colon.
The code was given to me by an MVP and worked flawlessly for years.

I just emailed this file to another person using the same version as I,
however the code does not work... In other words, when you don't use the
colon, all numbers entered show as zeros.

When I detach the file from my sent folder, everything works fine...

Need help???

Hi,

I have a vb which combines all files in 1 folder into 1 workbook. It works just fine using Excel 97-2003 but it does not work in Excel 2007. The codes are :

Private Sub Workbook_Open()
    If Worksheets(1).Cells(3, 3) <> 1 Then
        Worksheets(1).Cells(3, 3) = 1
        Call categories
    End If
End Sub
Sub categories()
Dim subBook As Workbook
Dim mainBook As Workbook
Dim filePath As String
Dim workSheetCount As Integer

Set mainBook = ThisWorkbook
filePath = mainBook.Path
workSheetCount = 0
    With Application.FileSearch
        .NewSearch
        .LookIn = filePath
        .FileType = msoFileTypeExcelWorkbooks
        If .Execute() > 0 Then
'            MsgBox ("Found " & .FoundFiles.Count & " files")
            For v = 1 To .FoundFiles.Count
                If mainBook.FullName <> .FoundFiles(v) Then
                    Set subBook = Workbooks.Open(.FoundFiles(v))
                    For i = 1 To subBook.Worksheets.Count
                        If subBook.Worksheets(i).Visible = True Then
                            workSheetCount = workSheetCount + 1
                            subBook.Worksheets(i).Copy after:= _
                            mainBook.Sheets(mainBook.Sheets.Count)
                            mainBook.Worksheets(mainBook.Sheets.Count).Name = mainBook.Worksheets(mainBook.Sheets.Count).Name
& " pg " & workSheetCount
                        End If
                    Next i
                    subBook.Close (0)
                End If
            Next v
        End If
    End With
    If mainBook.Sheets.Count > 1 Then
        mainBook.Worksheets(1).Visible = False
    End If

End Sub


Dear all,

I have an excel file with 30+ columns, and auto filter in use. Many users
may open the file and use autofilter to look for information or update
information in the file. I have also protect the worksheet as some columns
contain formular which I don't want the general users to modify.

As there are so many columns in the file, it is difficult to see clearly
which columns has been filtered in the previous save action. It seems that
the "Show All" button does not work in a protected worksheet. I use excel
2002, and the "Show All" button is dim even the autofilter has applied.

Is there any workaround?? Perhaps a macro which do the "Show All"?

Many thanks,
Marisa