VBA-Excel: Array Functions – LBound and UBound()
Description:
LBound and UBound() Functions returns the starting index ( Lower Bound) and Ending index (Upper Bound) of an array.
Format:
LBound(arrArrayName [, dimension])
UBound(arrArrayName [, dimension])
Arguments:
- arrArrayName
- Mandatory
- Type: Array
- Array whose lower or upper bound needs to found
- dimension
- Optional
- Type: Numeric
- If an array is one dimensional then no need to provide this argument but if it is Two or Multi dimensional array then you need to provide the dimension as well for which you want lower or upper bound. (Same way you can find the LBound and UBound of Single dimensional Dynamic array or Two dimensional Dynamic array)
Example:
Function FnLowerUpperBound() Dim arrOneDArray(1 To 5) Dim arrTwoDArray(1 To 5, -2 To 10) Dim arrMultiDArray(0 To 4, -2 To 4, 2 To 7) Dim strString strString = "LBound of arrOneDArray is: " & LBound(arrOneDArray) & vbCrLf strString = strString & "LBound of 2nd dimension of arrTwoDArray is: " & LBound(arrTwoDArray, 2) & vbCrLf strString = strString & "LBound of 3rd dimension of arrMultiDArray is: " & LBound(arrMultiDArray, 3) & vbCrLf MsgBox strString strString = "UBound of arrOneDArray is: " & UBound(arrOneDArray) & vbCrLf strString = strString & "UBound of 2nd dimension of arrTwoDArray is: " & UBound(arrTwoDArray, 2) & vbCrLf strString = strString & "UBound of 3rd dimension of arrMultiDArray is: " & UBound(arrMultiDArray, 3) & vbCrLf MsgBox strString End Function
Array functions-LBound
Array functions-UBound()
I need help, about array()
I have three columns in a spreadsheet (example):
what I need is to break to a new line each time it encounters the word “lot”, noting that there are products that do not have lots as in the delta example item.
before
ID Description observations
123 product alpha (Lot: 3911924, Qty: 70, Dt Val: 30/10 / 2016Lote: 3901749, Qty: 20, Dt Val: 30/10 / 2016Lote: 4201107, Qty: 410, Val Deuteronomy: 30/07 / 2017Lote : 4249269, Qty: 4110, Dt Val: 30/08 / 2017Lote: 4258339, Qty: 390, Dt Val: 30/08/2017)
after
123 alpha product Lot: 3911924, Qty: 70, Dt Val: 10/30/2016
123 alpha product Lot: 3901749, Qty: 20, Dt Val: 10/30/2016
123 alpha product Lot: 4201107, Qty: 410, Dt Val: 07/30/2017
123 alpha product Lot: 4249269, Qty: 4110, Dt Val: 30/08/2017
123 alpha product Lot: 4258339, Qty: 390, Dt Val: 30/08/2017
As I did not find solution and consider a good example, I appreciate the help
You said you have 3 columns in spreadsheet, in your example can you tell me whats value those 3 columns contains, it looks to me a one single string,
It can be done shouldnt take much time once i know what is there in 3 columns separately.