CODEWITHSUNDEEP
vbaexcel
RobK
RobK

Reputation: 207

VBA Excel: Dialog window opens when referring to cell in different worksheet

I'm breaking my head over this one and I hope someone can help. I have a procedure that adds a new worksheet into an Excel workbook and adds basic information of this worksheet into an overview in another worksheet (same workbook). It all works fine as it should, but unfortunately with one exception. There is one cell that should have the value of a cell in the newly created Worksheet. I've used this line for it:

c.Offset(0, 27).Value = "=" & Left(AccName.Value, 20) & "!N16"

Here the "Left(AccName.value,20)" equals the worksheet name. Unfortunately here the code opens a dialog window where I can open a file. I have no idea why and thus no idea how I can fix this. Does anybody here have any idea?

Edit: Here's the entire sub:

Sub FillBestandsübersicht()

Dim c As Range
Dim i As Integer
i = 3

'Find next empty row
Set c = Sheets("Bestandsübersicht").Range("A3")
    Do Until c.Value = ""
        Set c = c.Offset(1, 0)
        i = i + 1
    Loop

'Fill Bestandsübersicht
   c.Value = AccName.Value
    c.Offset(0, 1).Value = ProgRef.Value
    c.Offset(0, 2).Value = QuoteNr.Value
    c.Offset(0, 3).Value = PolicyNr.Value
    If LdrY.Value = True Or LocY.Value = False Then c.Offset(0, 4).Value = "n.a."
    c.Offset(0, 5).Value = ddUnderwriters.Value
    c.Offset(0, 6).Value = IncDate.Value
    c.Offset(0, 7).Value = ExpDate.Value
        If LdrY.Value = True Then
        c.Offset(0, 8).Value = "Lead"
        Else
        c.Offset(0, 8).Value = "Follow"
        End If
    c.Offset(0, 10).Value = PMNPL.Value
        If LdrY.Value = True And LocY.Value = True Then
        c.Offset(0, 11).Value = AmountLoc.Value
        Else
        c.Offset(0, 11).Value = 0
        End If
        If CoiY.Value = True Then
        c.Offset(0, 12).Value = AmountCOI.Value
        Else
        c.Offset(0, 12).Value = 0
        End If
    c.Offset(0, 14).Value = "n"
    c.Offset(0, 15).Value = "n"
    If DocY.Value = False Then c.Offset(0, 16).Value = "x" Else c.Offset(0, 16).Value = "n"
    If LdrY.Value = False Or LocY.Value = False Or CoiY.Value = False Then _
        c.Offset(0, 17).Value = "x" Else c.Offset(0, 17).Value = "n"
    If FacY.Value = False Then c.Offset(0, 18).Value = "x" Else c.Offset(0, 18).Value = "n"
    If LdrY.Value = True Or LocY.Value = False Then c.Offset(0, 19).Value = "x" Else c.Offset(0, 19).Value = "n"
    If LdrY.Value = False Or LocY.Value = False Then c.Offset(0, 20).Value = "x" Else c.Offset(0, 20).Value = "n"
    c.Offset(0, 21).Value = "n"
    c.Offset(0, 26).Value = Left(AccName.Value, 20)
    c.Offset(0, 27).Value= "=" & Left(AccName.Value, 20) & "!N16"

'Sort Bestandsübersicht
    Range("A3:AB10000").Sort key1:=Range("A3:A10000"), order1:=xlAscending, Header:=xlNo

'AutoFit rows
    Sheets("Bestandsübersicht").Rows("3:" & i).EntireRow.autofit

End Sub

Upvotes: 0

Views: 172

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

I think there is no sheet within your workbook which name equals to result of this function/calculation: Left(AccName.Value, 20)

Upvotes: 1

Related Questions