CODEWITHSUNDEEP
vbaexcelrecord
user1234
user1234

Reputation: 121

Creating a record of data in excel

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

Upvotes: 0

Views: 97

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27259

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.

Upvotes: 2

Lux Claridge
Lux Claridge

Reputation: 205

Looks like you have a typo in your else statement:

LastRow.oofset(1, 1).Select

Upvotes: 0

Related Questions