Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is there a Better Way? (VBA/MS Excel/97)

    I have some code that puts a formula into a worksheet that can reference another worksheet that may not exist until the user elects to have the additional worksheets.

    An example of a worksheet formula created by code is below. It goes in cell C133 in a worksheet called 'Certificate'.

    ActiveCell.FormulaR1C1 = _
    "=IF(Details!R9C2<2,"""",IF('Results Sheet (2)'!R41C6=""NO"",'Results Sheet (2)'!R44C5=""ERROR"","""",'Work Sheet (2)'!R11C3))"

    Note the worksheet names (eg Results Sheet (2)) as these cannot be AutoFilled to subsequent cells. Therefore, the problem I face is that each line of code has to be manually edited for each formula. This is because of the references to worksheets that may not exist. (Users can not simply cancel the Update Links dialog because other cells need to be updated automatically.)

    What I would like is a pointer to how to write the code so that code for sheets that might not yet exist could be created with a Loop type of operation although I haven't been able to come up with anything yet.

    At present, I would have up to 50 lines of referencing formulae (and either 3 or 4 columns on 2-3 worksheets in up to 30 workbooks.) Quite a lot of manually edited code!

    Any suggestions? TIA

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a Better Way? (VBA/MS Excel/97)

    I am not clear on exactly what you are asking. Your formula looks like it is referencing another worksheet in the same workbook. However, you talk about getting the Update Links dialog which you should only get if you have links to worksheets in a different workbook. What exactly do you have?

    I am also not sure is you are looking for a way to loop through all of the worksheets in a workbook, or if you are looking for a way to determine if a particular worksheet exists in the workbook. To loop through all of the worksheets in a workbook, you can use code like this:

    <pre>Dim oWS as Worksheet
    For Each oWS In Worksheets
    MsgBox oWS.Name
    Next oWS
    </pre>


    You can determine if a particular worksheet exists like this:

    <pre>Dim oWS as Worksheet
    On Error Resume Next
    Set oWS = WorkSheets("Results Sheet (2)")
    On Error GoTo 0
    If Not oWS is Nothing Then
    MsgBox "Worksheet Results Sheet (2) exists."
    End If
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a Better Way? (VBA/MS Excel/97)

    Thanks for your reply Legare.

    The workbook does need reference other workbooks (but not in the formula I posted). I am looking for a way to 'write' the code so that I don't get the following example but something that adds the reference to additional worksheets (if they are added by the user). The Update Links dialog will appear if the formulae are placed directly in the worksheets and the user will not know how to resolve a formula that references an non-existent worksheet (but still needs the update to happen for those formulae referencing external workbooks.)

    Clear as mud??

    Range("C134").Select

    ActiveCell.FormulaR1C1 = _
    "=IF(Details!R9C2<2,"""",IF('Results Sheet (2)'!R41C6=""ERROR"","""",'Work Sheet (2)'!R11C7))"
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    ActiveCell.FormulaR1C1 = _
    "=IF(Details!R9C2<3,"""",IF('Results Sheet (3)'!R41C6=""ERROR"","""",'Work Sheet (3)'!R11C7))"
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    ActiveCell.FormulaR1C1 = _
    "=IF(Details!R9C2<4,"""",IF('Results Sheet (4)'!R41C6=""ERROR"","""",'Work Sheet (4)'!R11C7))"
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    ActiveCell.FormulaR1C1 = _
    "=IF(Details!R9C2<5,"""",IF('Results Sheet (5)'!R41C6=""ERROR"","""",'Work Sheet (5)'!R11C7))"
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    ActiveCell.FormulaR1C1 = _
    "=IF(Details!R9C2<6,"""",IF('Results Sheet (6)'!R41C6=""ERROR"","""",'Work Sheet (6)'!R11C7))"
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    ActiveCell.FormulaR1C1 = _
    "=IF(Details!R9C2<7,"""",IF('Results Sheet (7)'!R41C6=""ERROR"","""",'Work Sheet (7)'!R11C7))"
    ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
    ActiveCell.FormulaR1C1 = _
    "=IF(Details!R9C2<8,"""",IF('Results Sheet (8)'!R41C6=""ERROR"","""",'Work Sheet (8)'!R11C7))"

    Only the reference to the worksheet name is changing and that requires a lot of manual editing.

    Is there a better way?

    Thanks, Leigh

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a Better Way? (VBA/MS Excel/97)

    I am still not sure I understand what you are asking, but see if this code will help you get started on what you want to do:

    <pre>Public Sub Test()
    Dim I As Long
    Dim oS1 As Worksheet, oS2 As Worksheet, oS3 As Worksheet
    For I = 0 To 7
    With Worksheets("Sheet1").Range("C134")
    On Error Resume Next
    Set oS1 = Nothing
    Set oS2 = Nothing
    Set oS3 = Nothing
    Set oS1 = Worksheets("Details")
    Set oS2 = Worksheets("Results Sheet (" & (I + 2) & ")")
    Set oS3 = Worksheets("Work Sheet (" & (I + 2) & ")")
    On Error GoTo 0
    If (Not oS1 Is Nothing) And (Not oS2 Is Nothing) And (Not oS3 Is Nothing) Then
    .Offset(I, 0).FormulaR1C1 = _
    "=IF(Details!R9C2<2,"""",IF('Results Sheet (" & (I + 2) & _
    ")'!R41C6=""ERROR"","""",'Work Sheet (" & (I + 2) & ")'!R11C7))"
    End If
    End With
    Next I
    End Sub
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is there a Better Way? (VBA/MS Excel/97)

    Thanks Legare!

    That is exactly what was needed...

    Now I will edit just 2 or 3 of the 21 lines of code that can be cut and pasted and replace 25 (sometimes 50) lines that had to be manually edited for each and every line before.

    I will now go back and convert the 30 odd templates with this easier code.

    Thanks again.

Posting Permissions

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