Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help ! Emergency ! (Excel 2000)

    I've distributed a budget workbook (fairly complex with lots of cells to fill) to many offices. However, I discovered a formular error. I cannot ask all offices to refill the forms (over 70 sheets, same format though in each workbook). Is it possible to write a VBA in a separate workbook that opens the budget workbook and change the formula in a particular cell of every sheet named 1,2,3,4,5, ... , without changing anything on sheets which have other names (like rates ...).

    Any help will be greatly appreciated.

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help ! Emergency ! (Excel 2000)

    Yes - a Select Case statement will identify each of the sheets.

    The code would probably run quicker if you could set it up to change the files without opening them. In any event, you are likely to have "version control" issues with the file being called different things and being stored in different locations. You may have to set it up so that - for the purposes of the change - each location has the file open and that the file has the same name.

    Can you give us some more details about the change?
    Gre

  3. #3
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help ! Emergency ! (Excel 2000)

    Thanks Unkamunka.

    I can ask each user to rename the file and put it under c: . Each sheet that needs to be changed is named 0,1,2,3,4 .... but some workbook has more sheets than others depending on how many projects they have. I put something below, but how it go through sheets from 0 .... N (and change those particular cells) ? Can you help me with the code ? Many thanks.

    Sub Fix()
    Workbooks.Open Filename:="C:04Budget.xls"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "AAA"
    ActiveCell.FormulaR1C2 = "BBB"
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help ! Emergency ! (Excel 2000)

    What you need is something like:<pre> For I = 1 To Sheets.Count
    Select Case Sheets(I).Name
    Case "0", "1", "2", "3", "4" 'NOTE names are case-sensitive
    CorrectFormulasProcedureGoesHere
    Case Else
    End Select
    Next I</pre>

    Gre

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Help ! Emergency ! (Excel 2000)

    How about something like this, it will work on any worksheets names as an integer and affect no other sheets. It changes cell A1 and B1 (which is what I think you meant in your code. There is no formular1c2 property.

    Steve
    <pre>Option Explicit
    Sub FixCode()
    Dim x As Integer
    Dim wks As Worksheet
    Workbooks.Open FileName:="C:04Budget.xls"
    For x = 0 To Worksheets.Count
    On Error Resume Next
    Set wks = Worksheets(CStr(x))
    On Error goto 0
    If Err = 0 Then
    wks.Range("A1").FormulaR1C1 = "AAA"
    wks.Range("B1").FormulaR1C1 = "BBB"
    End If
    Err.clear
    Next
    ActiveWorkbook.Close (True)
    End Sub</pre>


  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help ! Emergency ! (Excel 2000)

    We are running Norton AntiVirus Corporate Edition with the Heuristic Scanning Option to enable Bloodhound virus detetion selected. I was working with some code in one workbook that would modify code in an different existing workbook. NAV considered what I was doing to be "suspicious" and would quarantine the workbook.

    If you are running NAV you may want to deselect this option if in fact it is enabled.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help ! Emergency ! (Excel 2000)

    Many thanks. Both work well !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •