VBA to remove formulas with values causing wrong calculations

[ItalianPlatinum]

For the longest time I have been unable to find a way to remove the formulas from my sheet so the user just sees the values. I tried many different variations. the most recent below also doesn't work. Every time I use VBA to attempt to remove the formulas to values the data is wrong. if I comment out the line the formulas calculate correctly. What am I doing wrong or should be doing differently? Copy and paste is having the same issue. Is it my data? I don't get it. Any help is appreciated.

.Range("L13:AC" & lr2).Value = .Range("AC13:U" & lr2).Value

'Fomulas for Review (NAS Compare)
With wsComp
lr2 = .Cells(rows.count, "D").End(xlUp).row
    .Range("L13:L" & lr2).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,'NAS'!$A$2:$Y$10000,2,0),""YYYYMMDD"")),"""")"
    .Range("M13:M" & lr2).Formula = "=IFERROR(VALUE(VLOOKUP(D13,'NAS'!$A$2:$Y$10000,3,0)),"""")"
    .Range("N13:N" & lr2).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,'NAS'!$A$2:$Y$10000,19,0),""YYYYMMDD"")),"""")"
    .Range("O13:O" & lr2).Formula = "=IFERROR(VLOOKUP(D13,'NAS'!$A$2:$Y$10000,22,0),"""")"
    .Range("P13:P" & lr2).Formula = "=IF(ISERROR(VLOOKUP(D13,'NAS'!$A$2:$Y$10000,23,0)),,VLOOKUP(D13,'NAS'!$A$2:$Y$10000,23,0))"
    .Range("Q13:Q" & lr2).Formula = "=0"
    .Range("R13:R" & lr2).Formula = "=0"
    .Range("S13:S" & lr2).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,'NAS'!$A$2:$Y$10000,17,0),""YYYYMMDD"")),"""")"
    .Range("T13:T" & lr2).Formula = "=IFERROR(VALUE(TEXT(VLOOKUP(D13,'NAS'!$A$2:$Y$10000,18,0),""YYYYMMDD"")),"""")"
    .Range("U13:U" & lr2).NumberFormat = "General"
    .Range("U13:U" & lr2).Formula = "=IFERROR(VLOOKUP(D13,'NAS'!$A$2:$Y$10000,8,0)&"""","""")"
    .Range("U13:U" & lr2).NumberFormat = "@"
    .Range("V13:V" & lr2).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(E13=N13,""OK"",""CHECK""))"
    .Range("W13:W" & lr2).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(CONCATENATE(F13,O13)=""IND"",""OK"",""CHECK""))"
    .Range("X13:X" & lr2).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",(G13-P13))"
    .Range("Y13:Y" & lr2).Formula = "=0"
    .Range("Z13:Z" & lr2).Formula = "=0"
    .Range("AA13:AA" & lr2).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(H13=S13,""OK"",""CHECK""))"
    .Range("AB13:AB" & lr2).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(I13=T13,""OK"",""CHECK""))"
    .Range("AC13:AC" & lr2).Formula = "=IF(OR(B13=0,B13="""",C13=98),"""",IF(K13=U13,""OK"",""CHECK""))"
    .Range("L13:AC" & lr2).Value = .Range("AC13:U" & lr2).Value
    .Range("L12:AC12") = Array("Date", "Time", "Date", "Type", "Rate", "Rate", "Rate", "Pay", "Record", "C", "Date", "Type", "Rate", "Rate", "Rate", "Pay", "Record", "C")
End With

 

[Jeffrey Mahoney]

I like to create a hidden row above my table that stores the formulas for all the table rows. I can use a macro to simply value all the cells. When I'm ready to refresh the numbers I can either copy the formulas down through the rows or use a macro. This method allows me to edit formulas in that top row and not rely on VBA to recreate formulas which have to be edited in VBA (what a pain).

 

[ItalianPlatinum]

It has become cumbersome, but creating an new hidden row in this sheet would shift everything

I honestly used to do that, but then felt like I wasn't being very efficient. Probably not the case but not sure. So I try to house it in the VBA so the user cant touch it. then PW protect the VBA

 

[Peter_SSs]

find a way to remove the formulas from my sheet so the user just sees the values.

In your code you have this line near the end

.Range("L13:AC" & lr2).Value = .Range("AC13:U" & lr2).Value

Shouldn't those two coloured ranges be the same as each other?

.Range("L13:AC" & lr2).Value = .Range("L13:AC" & lr2).Value

 

[ItalianPlatinum]

In your code you have this line near the end

.Range("L13:AC" & lr2).Value = .Range("AC13:U" & lr2).Value

Shouldn't those two coloured ranges be the same as each other?

.Range("L13:AC" & lr2).Value = .Range("L13:AC" & lr2).Value

Yes they are. that was just me mid testing to see what formulas weren't computing. I kept removing columns to see which columns were impacted. essentially they all were. My bad I didnt send the actual original

 

[Peter_SSs]

With the two ranges the same, the formulas get replaced with their values for me. So are you all sorted now?

 

[ItalianPlatinum]

With the two ranges the same, the formulas get replaced with their values for me. So are you all sorted now?

No it doesn’t work I’ll send you screenshots of with formulas then post the code to show the different/incorrect results

 

[Peter_SSs]

I’ll send you screenshots of with formulas

XL2BB would be better.

 

[ItalianPlatinum]

Unfortunately i cannot as it has client information is there a better / easier way without the file?

This is with the formulas

.Range("L13:AC" & lr2).Value = .Range("L13:AC" & lr2).Value

And this is with the one line of code active. Is it the type of formulas I use or something?

 

[Peter_SSs]

Unfortunately i cannot as it has client information is there a better / easier way without the file?

Surely you can just alter/hide/delete any confidential information just as you have in the images?
XL2BB isn't the whole file, it is just a small section.
Otherwise that is a lot of typing to set that up to test.

BTW, can you also post the whole of your code rather than just the part given in post #1?