Loop functional - randomly fails 3rd time through

[JulieSB]

Hi - I am going crazy trying to figure out why my code is not working. For context, this code is copying in a row - finding a value in that row ("NeedVal"), and then dividing that value by an increment (e.g. $1.5B / $500M = 3), takes that multiple (3 "MultiVal"), and pastes that increment "MultiVal" times. Will paste 2 times for $1B, 3 times for $1.5B etc.

This works without error 2 times through - but on the third time, the code just stops (no error message, just macro over) at the underlined row below.

I am fairly certain that the "activecell" is in the place I intend, because I color that cell pink - which is the last functional line of code. The following line does not execute (where I try to get the Long "NeedVal" to set as a value

For what its worth, all my values are negative numbers, i do not think it is a sign issue.

Any help greatly appreciated!

Julie

Dim x As Integer
Dim intRowNum As Integer, intColNum As Integer
Dim strCopyRange, firstIntercompanyCopy
intRowNum = Range("StartingIntercompanyRow").Value
intColNum = Range("StartingIntercompanyColumn").Value
intIncrement = Range("IntercompanyIncrement").Value
Dim NeedVal As Long, MultiVal As Long

For x = 1 To Range("MonthCount2") 'This is a named range in the excel workbook
Sheets("Balance Sheet Calculations").Select
Cells(intRowNum, intColNum).Select
Range(ActiveCell, ActiveCell.Offset(0, 8)).Select

'columns being copied
Selection.Copy


Sheets("Funding").Select
Range("StartIntercompanyPaste").Select

'start of the short term funding sheet
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Interior.Color = RGB(0, 255, 0)
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Calculate
ActiveCell.Interior.Color = RGB(255, 0, 150)
NeedVal = ActiveCell.Offset(0, 7).Value
MsgBox NeedVal
ActiveCell.Offset(1, 0).Select

If NeedVal * -1 > intIncrement Then
MultiVal = NeedVal / intIncrement * -1
ActiveCell.Offset(-1, 7) = intIncrement * -1
Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 8)).Select
Selection.Copy
For y = 1 To MultiVal
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(1, 0).Select
Next y
End If

Call New_Cash_Optimization

intRowNum = intRowNum + 1

Next x

 

[JulieSB]

I have figured out the issue - but need help resolving.

I cant store "NeedVal" as a value greater than teh absolute value of about ~2.1B - any work around for this???

Thanks!

 

[JulieSB]

Found solution - change as long to as double.