VBA Array Subscript error

[nmounir]

Hello Everyone,

I am new to VBA and was trying to write a VBA array code for some reason I keep getting asubscript out of range error
I would like VBA to calculate target sales in column E to be equal to the current SalesAmounts X 2 which in column D.

Option Explicit

Sub Arrytraining()

Dim SalesAmounts() As Variant
Dim i As Long, Dimension1 As Long
Dim SalesTraget() As Variant

SalesAmounts = Range("D2", Range("D2").End(xlDown))
Dimension1 = UBound(SalesAmounts, 1)
ReDim SalesTarget(1 To Dimension1)
For i = 1 To Dimension1
SalesAmounts(i, 1) = SalesTarget(i, 1) * 2 (This is where the error is)

Next i

Range("E2", Range("E2").Offset(Dimension1 - 1, 1)).Value = SalesTarget


End Sub

Your help is much appreciated

 

[FormR]

You are getting that error because SalesTarget array is a one dimensional array and you are trying to access it like a two dimensional array.

Try like this : SalesTarget(i)

The rest of that line of code doesn't make much sense though!

 

[mohadin]

In addition to what FormR said

column E to be equal to the current SalesAmounts X 2

Same line should be
And last line as well

Sub Arrytraining()

    Dim SalesAmounts() As Variant
    Dim i As Long, Dimension1 As Long
    Dim SalesTraget() As Variant
    SalesAmounts = Range("D2", Range("D2").End(xlDown))
    Dimension1 = UBound(SalesAmounts, 1)
    ReDim SalesTarget(1 To Dimension1)
    For i = 1 To Dimension1
        SalesTarget(i) = SalesAmounts(i, 1) * 2    ' ('This is where the error is)
    Next i
    Range("E2", Range("E2").Offset(Dimension1 - 1, 1)).Value = Application.Transpose(SalesTarget)
End Sub

 

[mohadin]

Correction

 Range("E2", Range("E2").Offset(Dimension1 - 1)).Value = Application.Transpose(SalesTarget)

 

[nmounir]

It

In addition to what FormR said

Same line should be
And last line as well

Sub Arrytraining()

    Dim SalesAmounts() As Variant
    Dim i As Long, Dimension1 As Long
    Dim SalesTraget() As Variant
    SalesAmounts = Range("D2", Range("D2").End(xlDown))
    Dimension1 = UBound(SalesAmounts, 1)
    ReDim SalesTarget(1 To Dimension1)
    For i = 1 To Dimension1
        SalesTarget(i) = SalesAmounts(i, 1) * 2    ' ('This is where the error is)
    Next i
    Range("E2", Range("E2").Offset(Dimension1 - 1, 1)).Value = Application.Transpose(SalesTarget)
End Sub


[/QUOTE]
It worked perfectly but I am a bit puzzled why we had to use the transpose function if the size of the array hasn't changed ? I did a similar  code without using the transpose and it worked fine so i am confused why it didn't work the first time and it did on the second time with transpose.  

here is the code I used in the second time but there was an if condition. it is a very similar example where we want to increase the salary of those earning less than $30,000 (in column D) by double the salary and spit out the result in column E.   

Any idea why the second code with If condition worked fine without tranpose and without i alone?

Sub TestDynamicArraySalaries()

Dim SalaryList As Variant
Dim i As Byte

    Sheet1.Activate
    SalaryList = Range("D2", Range("D1").End(xlDown)).Value
    For i = 1 To UBound(SalaryList, 1)
    If SalaryList(i, 1) <= 30000 Then
        SalaryList(i, 1) = SalaryList(i, 1) * 2
        
    End If
    Next i
    
    Range("E2").Resize(UBound(SalaryList, 1)) = SalaryList
    
End Sub

 

[mohadin]

See in the debugger
Notice that SalesTarget one dimensional array
While SalaryList tow dimensional array

 

[nmounir]

See in the debugger
Notice that SalesTarget one dimensional array
While SalaryList tow dimensional array

Thank you very much mohadin

 

[mohadin]

You are very welcome
And thank you for the feedback
Be Happy and safe