?
Solved

filling in a missing value in a cell

Posted on 2012-08-30
2
Medium Priority
?
244 Views
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

0
Comment
Question by:willie108
2 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 38353140
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


Flyster
0
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38353276
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

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 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