macro: excel 2003(run) to excel 2016(problem)

[brianjj]

i have this macro that don't run on new excel.
when i activate the macro the clipboard is cleared. where is the problem?. thanks all

Sub expirydate()
Range("AB12").Select
    Selection.Copy
    Range("AB6:AB11").Select
    Range("AB11").Activate
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Range("AB6:AB11").Select
    Range("AB11").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
       Range("AA6:AB11").Select
    Range("AB11").Activate
    Selection.Sort Key1:=Range("AB12"), Order1:=xlAscending, Header:=xlGuess _
        , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("Z10").Select
End sub

[xlnitwit]

Hi,

Please use code tags when posting code as required by forum rules.

You should not be able to name a routine date() as that is a VBA keyword. Perhaps this variation of your code will suffice

Sub rundate()
    With Range("AB6:AB11")
        .FormulaR1C1 = Range("AB12").FormulaR1C1
        .Value2 = .Value2
    End With
    Range("AA6:AB11").Sort Key1:=Range("AB12"), Order1:=xlAscending, Header:=xlGuess _
                         , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("Z10").Select
End Sub

[brianjj]

Hi,

Please use code tags when posting code as required by forum rules.

done

You should not be able to name a routine date() as that is a VBA keyword. Perhaps this variation of your code will suffice

Sub rundate()
    With Range("AB6:AB11")
        .FormulaR1C1 = Range("AB12").FormulaR1C1
        .Value2 = .Value2
    End With
    Range("AA6:AB11").Sort Key1:=Range("AB12"), Order1:=xlAscending, Header:=xlGuess _
                         , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("Z10").Select
End Sub

it doesn't work yet.

[xlnitwit]

it doesn't work yet.

Could you be slightly more specific?

[brianjj]

clipboard clear.
in AB6:AB11 there must be a date. but nothing appears. if i put mouse on the cell appear 00/01/1900

ex:
1 20 gennaio 17
2 17 febbraio 17
3 17 marzo 17
4 21 aprile 17
6 16 giugno 17
9 15 settembre 17

[xlnitwit]

The code I posted doesn't use the clipboard at all. What exactly is in AB12?

[brianjj]

AB12:

=SE(VAL.ERRORE(CERCA.VERT(AA12;'gestione portafoglio'!$CW$10:$CX$21;2));0;(CERCA.VERT(AA12;'gestione portafoglio'!$CW$10:$CX$21;2)))

[xlnitwit]

Then I would assume that your lookup values in column AA6:AA1 are not being found for some reason, hence your formula returns 0. The clipboard is not a factor.

[brianjj]

AA6:AA11 are numbers that i put manually indicate month

in my ex before 1 is for january.. 2 for february, 9 to september... and so on

always third friday of the month. it is for expiry date in stock exchange

[xlnitwit]

Unless you can post a workbook that shows differently I still believe that your data is the problem.

Change the formula in AB12 to this
=CERCA.VERT(AA12;'gestione portafoglio'!$CW$10:$CX$21;2)

and re-run the code and tell me what is in A6:A11 at the end, please.

[brianjj]

in AB12 appears: #N/D
and in AB6:AB11 : #NOME?

[xlnitwit]

So the lookup is definitely failing for AB12.

If you change the code to this and run it- with the new formula- what is in AB6:AB11 at the end

Sub rundate()
    With Range("AB6:AB11")
        .FormulaR1C1 = Range("AB12").FormulaR1C1
 '       .Value2 = .Value2
    End With
    Range("AA6:AB11").Sort Key1:=Range("AB12"), Order1:=xlAscending, Header:=xlGuess _
                         , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("Z10").Select
End Sub

[brianjj]

Ab6:ab11 : #nome?

[xlnitwit]

There should be a formula in those cells after running that.

[brianjj]

changing only the vba code: cells are clear but in there is the original formula.
changing also the new formula: appears #NOME? and under there is the new formula

[xlnitwit]

So you get all formulas and they result in #N/D in AB12 but #NOME? in AB6:AB11?

[brianjj]

changing only the vba code: AB6:AB11 cells are clear but in there is the original formula. AB12 is 0

changing also with the yours "new formula": in AB6:AB11 appears #NOME? and under there is the new formula. AB12 is #N/D

[xlnitwit]

I don't really understand how you can have the same formula working in AB12 but not even calculating in AB6:AB11. Can you try this code with the new formula

Sub rundate()
        Range("AB12").Copy 
       Range("AB6:AB11").PasteSpecial xlPasteFormulas
    Range("AA6:AB11").Sort Key1:=Range("AB12"), Order1:=xlAscending, Header:=xlGuess _
                         , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("Z10").Select
End Sub

and tell me what appears in each cell?

[brianjj]

AB6:AB11 cells are clear, nothing appears.
if i go on and click one of these cells appear the formula that we have in AB12 (changed AA11 or AA9 ...exc.)

[xlnitwit]

What is the result of the formulas?

[brianjj]

with AA6: "1" must be 20 january 2017
AA7: "2" must be 17 february 2017

but, nothing appears, no results. seem that copy only the formula on AB12

[xlnitwit]

What is the formula in AB6 now?

Perhaps you could post a sample workbook?

[brianjj]

AB6 became: =SE(VAL.ERRORE(CERCA.VERT(AA6;'gestione portafoglio'!$CW$10:$CX$21;2));0;(CERCA.VERT(AA6;'gestione portafoglio'!$CW$10:$CX$21;2)))

but no result

[brianjj]

AB6 became: =SE(VAL.ERRORE(CERCA.VERT(AA6;'gestione portafoglio'!$CW$10:$CX$21;2));0;(CERCA.VERT(AA6;'gestione portafoglio'!$CW$10:$CX$21;2)))

but no result

[brianjj]

this is what i have.
i'm not able to insert the macro.
when a manually type numbers 1 to 12 e push the buttom of the macro in the cell appear the expity date of that month (third fryday)

[brianjj]

why it run on excel 2003 and not now on the new one?
probably there is a code that the new excel don't recognize

[xlnitwit]

Then I can only presume that your formula is not finding a match for the lookup value and therefore returning 0, and that you have set Excel to suppress the display of 0 values.

Your sample workbook doesn't actually help since it does not contain the source sheet with the lookup table.

[xlnitwit]

There are no differences between those formulas in 2003 and 2016. As you have not provided the lookup table I can't say why it does not work for you.

[brianjj]

in another macro i add a string and it works. i think also in this one maybe is the same but i don't know where and what.
for now, thank you for your time.

[brianjj]

in another macro i add a string and it works. i think also in this one maybe is the same but i don't know where and what.
for now, thank you for your time.