0

I've got a workbook in which I'm hoping to automatically calculate the expiry date on initialization of the file.

My current logic in mind is as follows, but it's giving me an "Ambiguous Name" error message:

Private Sub Workbook_Open()

If Worksheet(1).Range("G30") Is Nothing Then
Range("G30").Value = Now + 120
On Error GoTo 0

End Sub

The workbook is designed to be updated as required, thus it will need to check whether a date stamp has already been marked on.

Would anyone have any suggestions in this case? Many thanks in advance!

kitarika
  • 175
  • 1
  • 12

1 Answers1

1

that would be:

Private Sub Workbook_Open()
    With Worksheets("General Profiling")
        If IsEmpty(.Range("G30")) Then .Range("G30").Value = Now + 120
    End With
End Sub

since:

  • Worksheet isn't a valid object reference

  • someRange Is Nothing

    works for checking whether a Range typed variable someRange has been assigned or not

    while Worksheets(1).Range("G30") simply defaults to the Value property of that Range object and then you have to check it against being Empty or not

  • your 2nd range reference (Range("G30").Value) isn't fully qualified

    then it'd reference Range("G30") in the currently active worksheet, which could not be the one you want.

    using the With Worksheets(1) - End With block and dots (.) before allRange calls will make sure they reference the same (and wanted) worksheet

user3598756
  • 28,893
  • 4
  • 18
  • 28
  • user3598756@ Many thanks for your detailed explanation, it's very helpful! Unfortunately when trying out the script on my workbook, it didn't quite do what I was hoping for. Initially I thought it was due to a referencing formula I had in that cell which gave me a 0 value, however after deleting the formula, the date was still not populating on opening or changing of the workbook. Would I have been missing anything else? – kitarika Oct 17 '16 at 06:16
  • may be the worksheet you want is not the first in the `Worksheets` collection so that `Worksheets(1)` isn't pointing at the right one: what's the name of the worksheet you need to check cell "G30" content of? – user3598756 Oct 17 '16 at 06:36
  • Might also be that the cell appears empty, but isn't. If the worksheet reference is correct, try a string comparison instead by changing the line to `If .Range("G30") = "" Then .Range("G30").Value = Now + 120` – Vegard Oct 17 '16 at 06:39
  • @Vegard, thanks for your contribution. That always stumbled me: how can a cell _look_ not `Empty` if its `Value` is `""`? – user3598756 Oct 17 '16 at 06:44
  • @Vegard @user3598756 Thanks guys for your contributions. I have tried Vegard's replacement line but unfortunately it didn't work as well. The worksheet is called "General Profiling" and is definitely the first worksheet. The only other factor I can think of is that the cell is a merged one with `G30 & G31` but I don't think this would be an issue at all. – kitarika Oct 17 '16 at 06:56
  • see edited answer. Where I both used the _actual_ worksheet name and had a typo of mine corrected (`Workbook_Open()` instead of `Workbook_Open2()`...) – user3598756 Oct 17 '16 at 07:01
  • @user3598756 I am not sure. `IsEmpty` can fail even if the cell looks empty, but I don't know specifically if the string comparison will necessarily work. It would depend on the content, of course, but I don't know the precise scenario(s) where it may be the case. See [this question](http://stackoverflow.com/questions/19105452/cell-is-empty-but-isempty-is-not-working) where it seems to be the case, but they haven't defined *why*. – Vegard Oct 17 '16 at 07:03