ItalianPlatinum
Well-known Member
- Joined
- Mar 23, 2017
- Messages
- 870
- Office Version
- 365
- 2019
- Platform
- Windows
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.
VBA Code:
.Range("L13:AC" & lr2).Value = .Range("AC13:U" & lr2).Value
VBA Code:
'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