PDA

View Full Version : From a form - write todays date and a variable value in the next available row



instanceofti
07-21-2016, 05:03 PM
My ultimate goal: from my form - Take today's date and put it in the next available cell in column A and the value of the variable "Howmany" next to it in column B

OMG this should be so simple but I can't get it to write on the proper worksheet. Here is my code for writing the variable. (figured I could do "offset" and "DATE" somehow for the date part.)

This gets runtime error 1004 no cells found:

Dim r As Range
Sheets("Numbers").Activate
Set r = Intersect(ActiveSheet.UsedRange, Range("A:A")).Cells.SpecialCells(xlCellTypeBlanks)
r(1) = Howmany
(yada yada ... put date in column A somehow)

instanceofti
07-21-2016, 05:26 PM
it worked as coded (the passing the variable part) but it wrote in the active sheet at the time. I want to make the sheet "Numbers" active and write it in there instead

Paul_Hossler
07-21-2016, 05:48 PM
Something simple maybe?

Usually there's no need to select or activate a sheet just to get or put data



Option Explicit
Sub test()
Dim howmany As Long, nextrow As Long

howmany = 12345

nextrow = Worksheets("Numbers").Cells(Worksheets("Numbers").Rows.Count, 1).End(xlUp).Row + 1
Worksheets("Numbers").Cells(nextrow, 1).Value = Date
Worksheets("Numbers").Cells(nextrow, 2).Value = howmany
End Sub

instanceofti
07-21-2016, 06:01 PM
OMG that's perfect. Thanks for the quick response.