calculation to

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
764
Office Version
  1. 365
Platform
  1. Windows
Good afternoon,

I have created the code below with thanks to people on this group, but I am looking at slimming the data down in the current file so I am hoping to look at instead of a formula being entered in column "K" as per the below.
As I have the data for the vlookup already in my destination file is there anyway I can just have the value showing instead of the fornula

Code:
Dim x As Workbook, y As Workbook
Dim r As Range
Dim ts As Date

Application.ScreenUpdating = False
Sheet4.Visible = True 'Adherence
Sheets("Adherence").Range("A2:K5000").ClearContents

If Not bFileOpen("Adherence and Conformance Report.xlsx") Then Workbooks.Open ("Adherence and Conformance Report.xlsx")
If Not bFileOpen("Stats for Agents.xlsm") Then Workbooks.Open ("Adherence and Conformance Report.xlsx")

Set x = Workbooks("Adherence and Conformance Report.xlsx")
Set y = Workbooks("Stats for Agents.xlsm")

y.Activate
x.Sheets("RawData").[A2:J3000].Copy [A2]

Set r = [E2:H3000]
r = Evaluate("IF(" & r.Address & "="""",""""," & r.Address & "/86400)")
r.NumberFormat = "hh:mm:ss"
x.Close

' formula to a value based here '
With Range("K2:K" & Cells(Rows.Count, "A").End(3).Row)
    Application.DisplayAlerts = False
    .Formula = "=IF(A2="""","""",IFERROR(VLOOKUP(B2,source!B:N,13,0),""Leaver""))"
    .Value = .Value
End With

Sheets("Welcome").Select
Range("E13").Select
Range("E13").Value = Format(Now(), "mm/dd/yyyy hh:mm")
Sheet4.Visible = xlVeryHidden

End Sub

Function bFileOpen(wbname As String) As Boolean
On Error Resume Next
bFileOpen = Len(Workbooks(wbname).Name)
On Error GoTo 0
End Function

Thanks in advance
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
You're code already converts the formula to a value, so not sure what you're asking
 
Upvote 0
thank you @Fluff, my fault I didnt realise as I had originally had an issue and removed this line.
I have re put it in and it does.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
sorry @Fluff can I ask you one more question?
If I have a line of code I am writting and it stretches across the screen how do I enter down a line to keep it in the window without the code erroring?

example
Code:
.Formula = "=IF(A2="""","""",=IF(F2=""CS Billing"",""Comms CS"",IF(F2=""BB Tier 1"",""Diagnostics"",IF(F2=""Tel T2"",""Tier 2 Telecoms"",IF(F2=""BB Tier 2"",""Tier 2 BB"",IF(F2=""Utilities"",""Utility CS"",IF(F2=""IR Tier 1"",""Intial Responder"",IF(F2=""T3"",""Tier 2 Telecoms"",""Check"")))))))))"
    .Value = .Value

and I want it to show as
.Formula = "=IF(A2="""","""",=IF(F2=""CS Billing"",""Comms CS"",IF(F2=""BB Tier 1"",""Diagnostics"",IF(F2=""Tel T2"",""Tier 2 Telecoms"",
IF(F2=""BB Tier 2"",""Tier 2 BB"",IF(F2=""Utilities"",""Utility CS"",IF(F2=""IR Tier 1"",""Intial Responder"",
IF(F2=""T3"",""Tier 2 Telecoms"",""Check"")))))))))"
    .Value = .Value

just so its easier to read and the code to work as I am pulling it from my current sheet and want to add it in so it updates automatically instead of in a formula
thanks
 
Upvote 0
That formula is wrong, I suspect that you have the wrong number of ) at the end.
 
Upvote 0
Is it one ) for each ( one I take it like a normal formula?
Also do I move down a line by _ this at the end of the line and starting a new like
Code:
.Formula = "=IF(A2="""","""",=IF(F2=""CS Billing"",""Comms CS"",_
IF(F2=""BB Tier 1"",""Diagnostics"",IF(F2=""Tel T2"",""Tier 2 Telecoms"", etc.....
thanks
 
Upvote 0
Is it one ) for each ( one I take it like a normal formula?
Yup :)
To spilt it you need to do
VBA Code:
Formula = "=IF(A2="""","""",IF(F2=""CS Billing"",""Comms CS""," & _
                  "IF(F2=""BB Tier 1"",""Diagnostics"",IF(F2=""Tel T2"",""Tier 2 Telecoms""," & _
                  "IF(F2=""BB Tier 2"",""Tier 2 BB"",IF(F2=""Utilities"",""Utility CS""," & _
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top