Subscript Out Of Range Then exit

[trprasad78]

Hello experts ,

In below code "mycost" say's "Subscript Out Of Range" then it has to exit from DO loop

Please give your suggestion.

Do
    LEPro_Cost = LEPro_Cost + myCost(1, i)
    LePro_Rev = LePro_Rev + myRev(1, i)

   
    Debug.Print i, Month(DateRng(1, i)), LEPro_Cost, LePro_Rev
   
    i = i + 1
  Loop Until Month(DateRng(1, i)) = 4

 

[YasserKhalil]

Hello
It is better to upload sample of your workbook and the original code to find someone helps you
Regards

 

[Monty]

Mr Prasad..

Thank you for posting interesting questions..I really liked it...But please upload sample files...for quick response.

 

[trprasad78]

please check the sample file,macro also in that file.

if current month is not found in given range , it has to give zero has out put instead out of range.

it has to exit from do loop and follow the next

 

[jindon]

You don't have Sep-19, 2019/9/1 in "Date", so variable j is Empty.
Hence i is also Empty through i = j.

Then you will get that error on
LEPro_Cost = LEPro_Cost + myCost(1, i)

 

[trprasad78]

You don't have Sep-19, 2019/9/1 in "Date", so variable j is Empty.
Hence i is also Empty through i = j.

Then you will get that error on
LEPro_Cost = LEPro_Cost + myCost(1, i)

Your understanding is right , I would like to know if project before the current Month. Output should be zero.
How to fix.

 

[jindon]

1) You should have declared the loop counters and also always better to state "Option Explicit".

2) As is, add the evaluation for if i is empty or not.
If i is empty then let the variables values of 0 and exit sub, else continue.

 

[trprasad78]

1) You should have declared the loop counters and also always better to state "Option Explicit".

2) As is, add the evaluation for if i is empty or not.
If i is empty then let the variables values of 0 and exit sub, else continue.

thank you

i am not good in VBA, Can you make changes in sample file upload back please?

 

[jindon]

No loop

Sub test()
    Dim x As Variant, y As Long
    Dim MTDCost As Double, MTDRev As Double, myDate As Date
    Dim LEPro_Cost As Double, LePro_Rev As Double
    myDate = [d38]
    x = Application.Match(CLng(myDate), Rows(17), 0)
    If Not IsError(x) Then
        MTDCost = Rows(30).Cells(x)
        MTDRev = Rows(31).Cells(x)
        y = 12 - Month(myDate) + 4
        If y > 12 Then y = y - 12
        LEPro_Cost = Application.Sum(Rows(30).Cells(x).Resize(, y))
        LePro_Rev = Application.Sum(Rows(31).Cells(x).Resize(, y))
    End If
    Debug.Print "MTD Cost "; MTDCost
    Debug.Print "MTD Revenue "; MTDRev
    Debug.Print "Le Pro Cost "; LEPro_Cost
    Debug.Print "Le Pro Revenue "; LePro_Rev
End Sub

 

[trprasad78]

No loop

Sub test()
    Dim x As Variant, y As Long
    Dim MTDCost As Double, MTDRev As Double, myDate As Date
    Dim LEPro_Cost As Double, LePro_Rev As Double
    myDate = [d38]
    x = Application.Match(CLng(myDate), Rows(17), 0)
    If Not IsError(x) Then
        MTDCost = Rows(30).Cells(x)
        MTDRev = Rows(31).Cells(x)
        y = 12 - Month(myDate) + 4
        If y > 12 Then y = y - 12
        LEPro_Cost = Application.Sum(Rows(30).Cells(x).Resize(, y))
        LePro_Rev = Application.Sum(Rows(31).Cells(x).Resize(, y))
    End If
    Debug.Print "MTD Cost "; MTDCost
    Debug.Print "MTD Revenue "; MTDRev
    Debug.Print "Le Pro Cost "; LEPro_Cost
    Debug.Print "Le Pro Revenue "; LePro_Rev
End Sub

Thank you so much its working fine

 

[trprasad78]

@jindon i used your code now if the jan-17 project ends LE Value should be zero.

but i am getting some value, i guess it include grand totals ?

please refer attached file also code attached in that file.

 

[trprasad78]

any one can help on this above issue

 

[jindon]

1) Never use VBA reserved word like "path" as a variable name.
I don't even feel like to read the rest.

2) Your question is now completely changed, so better close this thread and open a new thread for the new question.