VBA Paste Special with stored constant

[pal777]

Using Excel 2000, WinXP.

I want to write a macro that does a Paste Special Add or Subtract of a stored constant (1462) to a selected range of cells with dates in them. This is to convert between 1900 and 1904 date systems (i.e. Win Vs. Mac). Here's my simple effort (below). It doesn't work though - it won't compile past the "Deltat_date.Copy" line.

I know this is a really basic syntax question, but I program about once a year, and am having trouble finding a quick explanation in my references. Any guidance would be much appreciated!

Thank you
Peter L.
=============================================
Sub date_1904()
' date_1904 Macro

Private Const Delta_date As Integer = 1462

Delta_date.Copy
Selection.PasteSpecial Paste:=xlAll, Operation:=xlSubtract, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "mm/dd/yy"
End Sub

 

[Greg Truby]

1. Use Code Tags so we can read you code more easily.

2. Your problem is that you cannot just copy a constant to the clipboard. You have to copy a Range Object if you want to then use Paste Special.

The following code finds the first empty cell and drops your constant into it and then does the operation. I just used the selected cells for a example

Sub Paste1462()
    Const c_intDelta As Integer = 1462
 
    Dim rngTarg As Excel.Range, rngEmptyCell As Excel.Range
 
    Set rngTarg = Selection
 
    Set rngEmptyCell = ActiveSheet.Cells.SpecialCells(XlCellType.xlCellTypeBlanks).Cells(1, 1)
 
    rngEmptyCell = c_intDelta
    rngEmptyCell.Copy
    rngTarg.PasteSpecial Operation:=xlPasteSpecialOperationSubtract
    rngEmptyCell.Clear
End Sub

 

[Greg Truby]

To anyone else that might have thought if there was a way to keep from polluting an actual cell, short of creating a new sheet or a new workbook, I couldn't find a way of getting it to work. Both dataobjects and web component range objects appear to fail:

Sub Paste1462owc()
    Const c_intDelta As Integer = 1462
    Dim wcWS As OWC11.Spreadsheet, wcCell As OWC11.Range
 
    Set rngTarg = Selection
    Set wcWS = New OWC11.Spreadsheet
    Set wcCell = wcWS.Range("A1")
    Let wcCell.Value = c_intDelta
    wcCell.Copy
    rngTarg.PasteSpecial Operation:=xlPasteSpecialOperationSubtract
End Sub
 
Sub Paste1462do()
 
    Const c_intDelta As Integer = 1462
    Dim doClip As MSForms.DataObject, rngTarg As Range, r As Range
 
    Set rngTarg = Selection
    Set r = rngTarg.Offset(, 1)
    Set doClip = New MSForms.DataObject
    doClip.SetText c_intDelta
    doClip.PutInClipboard
    rngTarg.PasteSpecial Operation:=xlPasteSpecialOperationSubtract
End Sub

 

[Legacy 68668]

or maybe

Sub date_1904()
' date_1904 Macro

Const Delta_date As Integer = 1462

With Selection
    .Value = Evaluate(.Address & "-" & Delta_date)
    .NumberForamt = "mm/dd/yy"
End With
End Sub

 

[pal777]

Not so obvious! Many thanks, Greg, Seiya.

Regards,

Peter L.

 

[pal777]

Hi Seiya:

This works perfectly (after correcting the ".NumberFormat" typo).

Thank you,

Peter

===============================================

or maybe

Sub date_1904()
' date_1904 Macro

Const Delta_date As Integer = 1462

With Selection
    .Value = Evaluate(.Address & "-" & Delta_date)
    .NumberForamt = "mm/dd/yy"
End With
End Sub