Creating a record of data in excel

Question

Currently I have an excel sheet that works out certain prices which change everyday. I would like to have a vba button that records everyday's prices for reference, saving the last price of the data everyday (I update the price frequently during the day) I wrote the code below but seem to be getting the error:

Compile error: Invalid Qualifier

[on the first LastRow.Offset() line]. I am quite new to vba and so any help would be appreciated

Private Sub CommandButton1_Click()

'Selecting the data to copy
Range("C23:O23").Select
Selection.Copy

'Find the last used row in Column B
Dim LastRow As Long
With ActiveSheet
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

'if still today, replace data, if not record the data on the next line
If LastRow = Date Then
    LastRow.Offset(0, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, 
Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Else
    LastRow.Offset(1, 0) = Date
    LastRow.offset(1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, 
Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
End If


End Sub

UPDATE: So, Ive changed a bit of my code where I define the LastRow variable differnetly:

Dim LastRow As Range
Set LastRow = Range("B31").End(xlDown)

This seemed to lead to a different error, "1004", in the line just after the else statement

LastRow.Offset(1, 0).Value = "=today"

Any advice will be appreciated

Scott Holtzman · Accepted Answer · 2018-06-26T13:57:51.133

2

LastRow is defined as Long

Dim LastRow As Long

And then you are trying to use an Offset method, which is only available on Range objects.

Make the following changes and you should be good to go.

Dim LastRow As Range
With ActiveSheet
    Set LastRow = .Cells(.Rows.Count, "B").End(xlUp)
End With

Reading this post on how and why to avoid select will take you far too. The code above can be optimized to work much smarter.

edited Jun 26 '18 at 13:57

answered Jun 25 '18 at 14:33

Scott Holtzman

27,099
5
37
72

Dang, you beat me to it. I was double checking my work. – Lux Claridge Jun 25 '18 at 14:35
Doing that leads to a run-time error '91'...tried with and without '.row' after the 'end(xlUp)' – user1234 Jun 25 '18 at 14:39
@user1234 - read the link I provided and get rid of `select` and `activesheet`. Fully qualify your objects and work directly with them. I strongly suspect the error will go away if you adjust for my answer as well. – Scott Holtzman Jun 25 '18 at 14:41
Add set before defining lastrow fixes everything – user1234 Jun 26 '18 at 10:46
@user1234 - great point. I often forget that simple yet important syntax in my own coding :) – Scott Holtzman Jun 26 '18 at 13:58

score 0 · Answer 2 · edited Jun 25 '18 at 14:21

0

Looks like you have a typo in your else statement:

LastRow.oofset(1, 1).Select

edited Jun 25 '18 at 14:21

dpel

1,954
1
21
31

answered Jun 25 '18 at 14:18

Lux Claridge

175
3
18

Thanks, but that doesnt seem to be it – user1234 Jun 25 '18 at 14:22
Oops, just now saw you said the _first_ Lastrow.Offset line. – Lux Claridge Jun 25 '18 at 14:24

Creating a record of data in excel

2 Answers2