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

Topic

New Reply

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

Reply | Quote

Replies

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:

Dim oWS as Worksheet
    For Each oWS In Worksheets
        MsgBox oWS.Name
    Next oWS

You can determine if a particular worksheet exists like this:

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

Reply | Quote

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

Reply | Quote

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:

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

Reply | Quote

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.

Reply | Quote