Changing Formula to a VBA script

[randy2012]

I have a formula that reads:

=IFERROR(IF(A6<>"",TEXT(A6-DATE(YEAR(A6),1,0),"000")&"-"&TEXT(COUNTIF(A$2:A6,A6),"000"),""),0)

If i multiply this formula in row b all the way down to the last row, the excel sheet will slow down.


Is there a way to make this formula a VBA script to negate that issue? Thanks!

[mjr veverka]

To how many rows do you copy this formula ?

Try: Run macro, select cell or range and then choose the appropriate option:

Please Login or Register  to view this content.

[scottiex]

can you sort the list by date? or is it already sorted? (seems natural that it might be). If so maybe something like

Please Login or Register  to view this content.

sorting then running it then unsorting might also be fast, but hopefully not necessary.

[jindon]

Your formula refers to A6, but I assume the formula is from row2.

Change [b2] accordingly.

Please Login or Register  to view this content.

[Maudibe]

Or you can try:

Please Login or Register  to view this content.

HTH,
Maud

dates.png

[randy2012]

The last one is really good. Thanks guys!

I will test this to see if it lags and will get back to you soon...

My testing is 4000 entries within the spreadsheet consisting of that code.

Im getting a run mismatch with Maudibe's code.
Run mismatch with Jindon's code.

porucha's code works but is missing the - sign between the numbers.


Sub Createcode()
lastrowx = Cells(Rows.Count, "A").End(xlUp).Row
a = Range("a1:a" & lastrowx).Value
For i = 6 To lastrowx
TYear = DateValue("1/1/" & Year(a(i, 1)))
a(i, 1) = Format(a(i, 1) + 1 - TYear, "000")
If a(i, 1) = a(i - 1, 1) Then
j = j + 1
Else
j = 1
End If
a(i, 1) = a(i, 1) & "-" & Format(j, "000")
Next i
Range("b1:b" & lastrowx).Value = a
End Sub

[mjr veverka]

... code works but is missing the - sign between the numbers ...

Formula is yours, not mine ... ... Your formula "likes me" ...

[randy2012]

It does work! Nice.

Sadly the only thing i find difficult to do with Formula based algorithm is to refresh the sheet every year while transferring the old historical data (last year's entries) to another tab.

Note to others: If you can fix your formula here and make it work better with less lag... then its a deal! I'll add to your reputation.

[jindon]

Please Login or Register  to view this content.

[Maudibe]

Here u go,

LOG_Rev.xlsm

HTH,
Maud