Why my code is producing Error "SUBSCRIPT OUT OF RANGE" While using variant arrays?

Hi Everyone!

I want to Replace negative values with absolute (without Minus sign) in the same column.

While executing the following code, an error pops up saying "Subscript Out of Range".

Please Login or Register  to view this content.

Kindly refer also to the thread http://www.excelforum.com/excel-prog...r-methods.html
and another thread https://www.excelforum.com/excel-pro...ed-thread.html

In the original first thread i wanted to to replace to the whole dates with only day, and, in the aforementioned code, i attempted to convert the negative values to the positive ones in the same sheet in Column "E".

I am writing separate codes for these two tasks. I think this can be solved using Two dimensional dynamic arrays for both the columns in one sub routine, I tried but failed...

I need your valuable help on:
1. Rectification on the error "Subscript out of Range".
2. A Single Procedure for replacing whole dates with only "Day" in Column "D" and Converting negative numbers to Positive Ones in Column "E", specifically using two dimensional dynamic arrays if possible.

P.S: If i need to start separate thread for 2nd point above, please let me know. I would ask the same question in separate thread otherwise any help here would be highly appreciated.

Sample workbook can be downloaded from the links pasted above.

Thanks all

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Thanks for help!

What's the difference between using "LastRow" and "Ubound(MyAmount)"?
Why using "LastRow" produces error while ubound(MyAmount) does not?
Both are producing the same result i.e. Row 129...

LastRow is from the worksheet

Please Login or Register  to view this content.

You load the data into Array

Please Login or Register  to view this content.

Whatever the start cells "E2" in this case, row reference in Array starts from 1, so the size of the array is actually LastRow - 2 + 1, so not LastRow in this case.
If you want to loop all though to the end of the array, UBound function always return the last row index within an array.

Hi,

'cause using LastRow is a logical error according to the array indexes like you can easily check via the VBE Locals windows …

Please Login or Register  to view this content.

Yes, Understood...

In my code Last Row has value of 130, while, the size of Array is 129 as i deducted 1 before loading data into array.
So, either Ubound(MyAmount)or Adjusting LastRow as indicated by Jindon would solve the problem.

Many Thanks to all the contributors here.

Much Appreciated!!!