PDA

View Full Version : VBA not doing Mathmitical calculation when its ran



menor59
04-16-2013, 08:53 AM
Hello all,

Thank you for your time in reading this.
I have the following code that runs and brings data from sheets to a Data Sheet.



Option Explicit
Sub Quote()
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Dim lr As Integer
Dim ar As Variant
Set sh = Sheet14
For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 5) = "Quote" Then
ws.Range("D6:D62").AutoFilter 1, ">0"
lr = ws.Range("C" & Rows.Count).End(xlUp).Row
If lr >= 7 Then
Sheet14.Range("A65536").End(xlUp)(2).Value = ws.Name
ws.Range("A7:G62").Copy Sheet14.Range("C65536").End(xlUp)(2)
sh.Range(sh.Cells(sh.Cells(Rows.Count, 1).End(xlUp).Row, 1), _
sh.Cells(sh.Cells(Rows.Count, 3).End(xlUp).Row, 1)).Value = ws.Name
End If
ws.AutoFilterMode = False
End If
Next ws
End Sub


Heres the problem:
When the Above code is run it isnt doing the calculation from E7:F62. For example E7 has =ROUND(L7/(1-N7),2) and E8 has =E7*D7. When the code is run its putting the Formula there on the DataQuote Sheet, and not the results.

Also Id like to make one Modification:
say I would Like to Exempt a Sheet. On Sheets Quote 1, Quote 2, Quote 3, Quote 4, and quote 5 I have a Checkbox that is Linked to R6 on each sheet so that if its Checked r6 goes true. If R6 on any of those sheets says True, Exempt that sheet or sheets from the DataQuote Sheet which is Sheet14..


Any thoughts is greatly appreciated.

Thank you!

mdmackillop
04-16-2013, 11:32 AM
Could you be creating a circular error? If you still have issues, can you post your workbook?

menor59
04-16-2013, 11:48 AM
if needed the password is net1444 There is a macro that will Unprotect it all

menor59
04-16-2013, 11:53 AM
See Attached The tabs are RED

mdmackillop
04-16-2013, 04:05 PM
I can't replicate your problem. Make sure that Calculation is not set to Manual (maybe left over by some other code?)

menor59
04-16-2013, 04:32 PM
See attachment

Paul_Hossler
04-16-2013, 05:27 PM
When the code is run its putting the Formula there on the DataQuote Sheet, and not the results.


Would it work better if you did a Copy / PasteSpecial values in order to get the values into the output?

Little more complicated because of the filters, etc. but that's the easiest way to get rid of formulas (AFAIK)

Paul

menor59
04-16-2013, 05:28 PM
What ever is easier. I just dont know how to impliment it

snb
04-17-2013, 12:28 AM
crossposted:
http://www.thecodecage.com/forumz/microsoft-excel-forum/213964-visual-basic-applications-not-doing-mathmatical-calculation-when-its-ran.html