0

I tried to define multiple (For-Next) loop, but i received the following error.

Overflow error

The code is designed to calculate values in cells (C50:C60), then use this values, to calculate values of cells (D50:D60)

I tried to wrote it in a one code, but i received that error, i tried to wrote it on 2 separates codes, but i still having this error.

Could any one help, Thanks in advance.

The Code is :-

Sub CalculateFs()
For x = 50 To 60
If Cells(x, 2) > 0 Then
    Cells(x, 3) = (Abs(Cells(x, 1)) / (Cells(22, 1) * WorksheetFunction.Pi * Cells(22, 3) ^ 2 * 0.25) / Cells(40, 2)) + (Cells(x, 2) / (Cells(22, 1) * WorksheetFunction.Pi * Cells(22, 3) ^ 2 * 0.25))
Else
    Cells(x, 3) = Abs(Cells(x, 1)) / (Cells(22, 1) * WorksheetFunction.Pi * Cells(22, 3) ^ 2 * 0.25) / Cells(40, 2)
End If
Next x
For S = 50 To 60
    Cells(S, 4) = WorksheetFunction.Min(((95000 / Cells(S, 3)) - (2.5 * Cells(17, 2))), (300 * 252 / Cells(S, 3)), (300), ((94600 / Cells(S, 3)) - (2.5 * Cells(17, 2))))
Next S
End Sub

Note:- The first part of the code (X-Values) is good and has no errors message, but the errors appears always with the second part (S-Values)

JvdV
  • 70,606
  • 8
  • 39
  • 70
Amr Gaber
  • 11
  • 2
  • 4
    Have you researched what the [Overflow](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/overflow-error-6) error might mean? =). – JvdV Dec 19 '19 at 12:37
  • Did you press the "debug" button, verified which variable is giving an overflow and gone from there? – Dominique Dec 19 '19 at 14:16
  • yes i did, i mentioned in my inquiry, that, the error related to the second part of the code (S-Values), this was by using debug button. any way, Sir/(mx5_craig) answer is totally helpful, i recommend this answer for you and me, thanks for your concern – Amr Gaber Dec 21 '19 at 11:38

1 Answers1

0

As JvdV points out the error is likely due to the following statement

"You attempt to use a number in a calculation, and that number is coerced into an integer, but the result is larger than an integer"

replace this line:

Cells(S, 4) = WorksheetFunction.Min(((95000 / Cells(S, 3)) - (2.5 * Cells(17, 2))), (300 * 252 / Cells(S, 3)), (300), ((94600 / Cells(S, 3)) - (2.5 * Cells(17, 2))))

with this:

Cells(S, 4) = WorksheetFunction.Min(((95000 / Cells(S, 3)) - (2.5 * Cells(17, 2))), (300& * 252& / Cells(S, 3)), (300), ((94600 / Cells(S, 3)) - (2.5 * Cells(17, 2))))

Effectively casting these numbers to a long (rather than an integer, Use of symbol # (hash) in VBA Macro)

mx5_craig
  • 108
  • 10
  • Thanks a lot, it was very helpful. Could you please explain to me the limits of integer,Long, and the double, in plain language, when i use the integer, long, and the double. Thanks again – Amr Gaber Dec 21 '19 at 11:29
  • Hi, glad I could help. A VBA integer can only represent a maximum value of 32767. 300 x 256 exceeds that maximum (despite the fact that those numbers in isolation don't). A long can represent a much bigger number. VBA will default use int, unless you specifically tell it not too. – mx5_craig Dec 23 '19 at 12:30