VBA Subscript out of range

M

micro man

Joined
Jul 26, 2011
Messages
9
Reaction score
0
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
 
ReDim allMfsPrices(numlistedItems), selectedMfsPrices(numlisedItems)

i believe should be

ReDim allMfsPrices(numlistedItems), selectedMfsPrices(numlisTedItems)

you left the 't' out of 'numlistedItems'
 
Thank you. I have one last query. How do I output the users selection into an excel sheet?

Thanks again
 
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 -
 
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.
 
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
 
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?
 
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
 
Back
Top