VBA Subscript out of range

[micro man]

Hi I'm new at VBA I have been writing up a code to output car manufacturers names and prices, through a user form. However when I try to execute my code it says "Subscript out of range". It refers to this line:

selectedMfsPrices(numMfsSelected) = allMfsPrices(i)

Any help would be much welcomed. Heres the rest of my code. Thanks


Private Sub cmd_ShowSelected_Click()
Dim i As Integer



'numListedItems holds total no. of manufacturers.
'numMfsSelected is used to count how many times Mfs are selected
'allCarMfs() holds the name of Cars.
'SelecetedCarMfs() holds the selected Cars.
Dim numlistedItems As Integer, numMfsSelected As Integer
Dim allCarMfs() As String, selectedCarMfs() As String

'First part holds prices of Mfs
'Second part holds selected prices of Mfs
Dim allMfsPrices() As Double, selectedMfsPrices() As Double



'list_Car...ListCount. This counts no.of items in the listbox
numlistedItems = list_CarManufacturers.ListCount
numMfsSelected = 0


ReDim allCarMfs(numlistedItems), selectedCarMfs(numlistedItems)

ReDim allMfsPrices(numlistedItems), selectedMfsPrices(numlisedItems)

'Fills allMfsPrices array from sheet 1
For i = 1 To numlistedItems
allMfsPrices(i) = Cells(i + 2, 1)
Next i

'Fills allCarMfs array from sheet 1
For i = 1 To numlistedItems
allCarMfs(i) = Cells(i + 1, 1)
Next i



'This fills in the users selected cars.
'Listboxs count from 0, so we use i-1
'If the item is selected increase the counter by 1. numMfsSelected +1
'If its selected add that Mfs to the array selectedCarMfs (line 3 of code in for loop 2)
'If selected add Mfs price to array called selectedMfsPrices
For i = 1 To numlistedItems
If list_CarManufacturers.Selected(i - 1) = True Then
numMfsSelected = numMfsSelected + 1
selectedCarMfs(numMfsSelected) = allCarMfs(i)
selectedMfsPrices(numMfsSelected) = allMfsPrices(i)
End If
Next i

'Redim preserve rediminsion array so no extra empty cells in listbox
'last lin first part name of our list box to be populated. Second part the array containing selected Mfs
ReDim Preserve selectedCarMfs(numMfsSelected)
ReDim Preserve selectedMfsPrices(numMfsSelected)
Sheet2.Activate
list_SelectedMfs.List = selectedCarMfs




End Sub

 

[dhack]

ReDim allMfsPrices(numlistedItems), selectedMfsPrices(numlisedItems)

i believe should be

ReDim allMfsPrices(numlistedItems), selectedMfsPrices(numlisTedItems)

you left the 't' out of 'numlistedItems'

 

[micro man]

Thank you. I have one last query. How do I output the users selection into an excel sheet?

Thanks again

 

[dhack]

hrm. - sorry not sure about how to answer that

are you using access? - what are you exporting?.. are they editing a table?.. what are you trying to do?

thanks -

 

[micro man]

Im basically trying to allow the user to select a car manufacturer. Along with this I want the prices of the car manufacture selected to be selected and show up in another worksheet in excel. Hope that helps.

 

[dhack]

hrm..

make your last for loop look like this--- does this help?

For i = 1 To numlistedItems
If list_CarManufacturers.Selected(i - 1) = True Then
numMfsSelected = numMfsSelected + 1
selectedCarMfs(numMfsSelected) = allCarMfs(i)
selectedMfsPrices(numMfsSelected) = allMfsPrices(i)
Sheet2.Cells(i, 1) = allCarMfs(i)
Sheet2.Cells(i, 2) = allMfsPrices(i)
End If
Next i

 

[micro man]

That worked thanks again.

 

[micro man]

Hi thanks again for you help. Do you have alot of experience with VBA?

 

[dhack]

yeah.. i've been using it for about 10 years or so..

 

[micro man]

wow. I going to be using alot more of vba for an up coming school work. Would it be possible to ask for your help from time to time if that's okay?

 

[micro man]

Hi I have a chart in excel and I want the chart to be displayed into a user form, Im not quite sure where Im going wrong, any help would be greatfull. Below is my code to output the chart.


Private Sub CommandButton1_Click()
Dim FilePathNameOfChart As String, CurrentChart As Object

Set CurrentChart = Sheets(1).ChartObjects(1).Chart

FilePathNameOfChart = Lecture_10 & "\Chart1.gif"

CurrentChart.Export FileName:=FilePathNameOfChart, FilterName:="GIF"

Image1.Picture = LoadPicture(FilePathNameOfChart)



End Sub



Thanks