Macro Correction

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Macro Correction

Post by zyxw1234 »

Hi Experts,

Code: Select all

Sub STEP11()
Rem 1 Worksheets info
Dim Wb As Workbook
 Set Wb = Workbooks.Open("C:UsersWolfieeeStyleDesktopWolfieeeStyle9.15FilesFundsCheck.xlsb")
Dim Ws1 As Worksheet: Set Ws1 = Wb.Worksheets.Item(1)
Dim Lr As Long
 Let Lr = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
Dim rngIn As Range: Set rngIn = Ws1.Range("A1:S" & Lr & "")
Dim arrIn() As Variant, arrOut() As Variant: Let arrIn() = rngIn.Value2
Dim S10Val As Double: Let S10Val = arrIn(10, 19)
Rem 2 Do it untill we are past 1000
 Let arrOut() = arrIn()
Dim Cnt As Long, SomeTotal As Double
 Let Cnt = 2: Let SomeTotal = arrIn(Cnt, 17)
    Do
     Let arrOut(Cnt, 10) = 1
     Let Cnt = Cnt + 1
     Let SomeTotal = SomeTotal + arrIn(Cnt, 17)
    Loop While SomeTotal < S10Val
Rem 3 Output
 Let rngIn.Value2 = arrOut()
Wb.Save
Wb.Close

End Sub

Line highlighted- : Let S10Val = arrIn(10, 19)
error-run time error 9
subscript out of range


This macro is giving the above error so plz have a look Sir

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

That error would occur if column A of the worksheet has fewer than 10 used rows. Lr would be less than 10, so arrIn(10, 19) would not exist.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Ok so what changes are required sir?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

After calculating Lr, you could insert a check. If Lr is less than 10, display a warning, close the workbook Wb and exit the macro.
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

Plz let me know below which line i have to put this?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro Correction

Post by HansV »

As I mentioned, "After calculating Lr", so below the line Let Lr = ...
Best wishes,
Hans

zyxw1234
Banned
Posts: 253
Joined: 22 Apr 2020, 17:24

Re: Macro Correction

Post by zyxw1234 »

But that doesn't solve the issue
column A of the worksheet may have more than 10 used row Or may be fewer than 10 used rows then what changes are required?

i used this macro
Sub STEP11()
Rem 1 Worksheets info
Dim Wb As Workbook
Set Wb = Workbooks.Open("C:UsersWolfieeeStyleDesktopWolfieeeStyle9.15FilesFundsCheck.xlsb")
Dim Ws1 As Worksheet: Set Ws1 = Wb.Worksheets.Item(1)
Dim Lr As Long
Let Lr = Ws1.Range("A" & Ws1.Rows.Count & "").End(xlUp).Row
If Lr < 10 Then
Wb.Close
Else
Dim rngIn As Range: Set rngIn = Ws1.Range("A1:S" & Lr & "")
Dim arrIn() As Variant, arrOut() As Variant: Let arrIn() = rngIn.Value2
Dim S10Val As Double: Let S10Val = arrIn(10, 19)
Rem 2 Do it untill we are past 1000
Let arrOut() = arrIn()
Dim Cnt As Long, SomeTotal As Double
Let Cnt = 2: Let SomeTotal = arrIn(Cnt, 17)
Do
Let arrOut(Cnt, 10) = 1
Let Cnt = Cnt + 1
Let SomeTotal = SomeTotal + arrIn(Cnt, 17)
Loop While SomeTotal < S10Val
Rem 3 Output
Let rngIn.Value2 = arrOut()
Wb.Save
Wb.Close
End If


End Sub



I will let u know all the details tomorrow Sir
plz give me some time