Solved: Why i get Vba Error in this code??

[Rayman]

I dont understand why if i use the "Load Saved Price" in the form and the price saved are only 1 i get error n. 381.
If the prices saved ad more than 1 all go well.
The price are saved in sheet "PrezziPerCantiere".
See aattached file.

Anyone have time and/or patience for help me??

Thanks in advance

[mdmackillop]

Can you please list all the steps to create this error? I don't know what your form is meant to do.

[Rayman]

Can you please list all the steps to create this error? I don't know what your form is meant to do.

Its simple Md.

Go to sheet " Giornale di Cantiere"
Push button "Price"
The Form is showed

Push The button "Load Saved Price" and the error show.

The button "Load Saved Price" fill the listbox "CmbMateriali" with the materials saved with button "Save Price" wich write in sheet "PrezziPerCantiere" the price selected with listbox "CmbCosti" and added to "CmbMateriali" with button "AddPrice".
In the sheet "PrezziPercantiere" the price to load are in row 7 , and there is only one price. This cause the error ; if you fill Row 8 all is ok.
The code go well if i saved more than 1 price.

Hope this let you understand, with my bad english is difficult for me explain better...sorry.

Thanks for reading

[mdmackillop]

[VBA]Private Sub CmdVediPrezzi_Click()

Dim NumRiga2 As Integer, MioCantiere, RngCantieri, Contatore As Integer, PrimaRiga As Integer, ultimariga As Integer, i
Application.ScreenUpdating = False

Worksheets("PrezziPerCantiere").Activate


If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
NumRiga2 = Cells(Rows.Count, 1).End(xlUp).Row
Set RngCantieri = Sheets("PrezziPerCantiere").Range("A2:A" & NumRiga2 + 1)
MioCantiere = TxtCantiere.Text
Contatore = 0

'Why not test from the bottom for the fisrt match, instead of from the top for the last
For Each MioCantiere In RngCantieri
If Not MioCantiere Is Nothing And MioCantiere = TxtCantiere.Text Then
MioCantiere.Select
ultimariga = MioCantiere.Row
End If
Next MioCantiere

For i = ultimariga To 2 Step -1
If Cells(i, 1) = TxtCantiere.Text Then
Contatore = Contatore + 1
Else: Exit For
End If

Next
PrimaRiga = ultimariga - Contatore + 10

If ultimariga > 0 And PrimaRiga > 0 Then
Dim r As Range
Set r = Range("B" & PrimaRiga, "B" & ultimariga)
If r.Count = 1 Then
CmbMateriali.AddItem r.Value
CmbPrezzo.AddItem r.Offset(, 1).Value
CmbData.AddItem r.Offset(, 2).Cells.Value
Else
CmbMateriali.List = Range("B" & PrimaRiga, "B" & ultimariga).Cells.Value
CmbPrezzo.List = Range("C" & PrimaRiga, "C" & ultimariga).Cells.Value
CmbData.List = Range("D" & PrimaRiga, "D" & ultimariga).Cells.Value
End If
Else
MsgBox "Non ci sono prezzi salvati per questo cantiere"
End If


End Sub[/VBA]

[Rayman]

[vba]Private Sub CmdVediPrezzi_Click()

Dim NumRiga2 As Integer, MioCantiere, RngCantieri, Contatore As Integer, PrimaRiga As Integer, ultimariga As Integer, i
Application.ScreenUpdating = False

Worksheets("PrezziPerCantiere").Activate


If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
NumRiga2 = Cells(Rows.Count, 1).End(xlUp).Row
Set RngCantieri = Sheets("PrezziPerCantiere").Range("A2:A" & NumRiga2 + 1)
MioCantiere = TxtCantiere.Text
Contatore = 0

'Why not test from the bottom for the fisrt match, instead of from the top for the last
For Each MioCantiere In RngCantieri
If Not MioCantiere Is Nothing And MioCantiere = TxtCantiere.Text Then
MioCantiere.Select
ultimariga = MioCantiere.Row
End If
Next MioCantiere

For i = ultimariga To 2 Step -1
If Cells(i, 1) = TxtCantiere.Text Then
Contatore = Contatore + 1
Else: Exit For
End If

Next
PrimaRiga = ultimariga - Contatore + 10

If ultimariga > 0 And PrimaRiga > 0 Then
Dim r As Range
Set r = Range("B" & PrimaRiga, "B" & ultimariga)
If r.Count = 1 Then
CmbMateriali.AddItem r.Value
CmbPrezzo.AddItem r.Offset(, 1).Value
CmbData.AddItem r.Offset(, 2).Cells.Value
Else
CmbMateriali.List = Range("B" & PrimaRiga, "B" & ultimariga).Cells.Value
CmbPrezzo.List = Range("C" & PrimaRiga, "C" & ultimariga).Cells.Value
CmbData.List = Range("D" & PrimaRiga, "D" & ultimariga).Cells.Value
End If
Else
MsgBox "Non ci sono prezzi salvati per questo cantiere"
End If


End Sub[/vba]

Perfect!! Work Great Md. THANKS YOU

and you are right its better search for match from bottom as you suggest.

Then my mistake was the use of .List for the first item instead .AddItem?

[mdmackillop]

.
Then my mistake was the use of .List for the first item instead .AddItem?

For a single item it must be that List does not see an Array, so we find an alternative.

[Rayman]

For a single item it must be that List does not see an Array, so we find an alternative.

Yes, it make sense Md.
Thanks again for your help