PDA

View Full Version : Problem about a variable value In excel.



thanhvanchi
12-08-2016, 08:02 PM
Hello everyone!


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)

Thank's a lot!

Paul_Hossler
12-08-2016, 08:12 PM
I tink you were thinking of something like this



Option Explicit
Sub demo()
Dim rNextAvailable As Range

'from the last cell (- .Rows.Count) in col A (= 1) go up to the first non-blank cell (.End(xlUp) )
'then go down one row, but in same column (.Offset(1, 0))
With ActiveSheet
Set rNextAvailable = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

'put the date in the blank cell
rNextAvailable.Value = DateSerial(2016, 12, 25)

'put the number in the cell in the same one, one column over (B)
rNextAvailable.Offset(0, 1).Value = 12345
End Sub

SamT
12-09-2016, 05:56 AM
With ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
.Value =Howmany
.Offset(0, 1) = Date
End With