filling in a missing value in a cell

Posted on 2012-08-30
Last Modified: 2012-09-28
Hello. I have a sheet where sometimes the first cell in the last row is missing or contains a space (?). The first column of this sheet contains dates. I want to fill in that last cell with the date of the cell just above it plus one day in either of these cases. I tried the code here but I am getting an error for

ActiveSheet.Range(LastRowA & "1").Formula = ActiveSheet.Range(LastRowA & "1").Value + 1

The error says Run-time error '1004'
Application-defined or object-defined error.

 Sub fillInTodayDateInFinalTestSheet()
LastRowB = Range("B" & Rows.Count).End(xlUp).Row

LastRowA = Range("A" & Rows.Count).End(xlUp).Row
'MsgBox (Cells(LastRowA, 1).Value)
If LastRowA = LastRowB - 1 Or Cells(LastRowA, 1).Text = " " Then ActiveSheet.Range(LastRowA & "1").Formula = ActiveSheet.Range(LastRowA & "1").Value + 1

 If LastRowA < LastRowB - 1 Then MsgBox ("problem in filling in last date in sheet for svet")

End Sub

Open in new window

Question by:willie108
    LVL 22

    Expert Comment

    See if this code works for you:

     Sub fillInTodayDateInFinalTestSheet()
    LastRowA = Range("A65536").End(xlUp).Row
    LastRowB = Range("B65536").End(xlUp).Row
    LastRowA1 = Range("A65536").End(xlUp).Row + 1
    'MsgBox (Cells(LastRowA, 1).Value)
      If LastRowA = LastRowB - 1 Or Cells(LastRowA, 1).Text = " " Then
        Range("A" & LastRowA1).Value = Range("A" & LastRowA).Value + 1
      End If
     If LastRowA < LastRowB - 1 Then
        MsgBox ("problem in filling in last date in sheet for svet")
     End If
    End Sub

    Open in new window

    LVL 24

    Accepted Solution

    on a side note...
    I am guessing that you are using the line below to check the value (debugging)
    'MsgBox (Cells(LastRowA, 1).Value)

    Open in new window

    it can be better to use the immediate window and:
    Debug.Print Cells(LastRowA, 1).Value
    Debug.Print Cells(LastRowA, 1).address

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now