View Full Version : Solved: Why i get Vba Error in this code??
Rayman
04-02-2011, 05:01 AM
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
04-02-2011, 05:40 AM
Can you please list all the steps to create this error? I don't know what your form is meant to do.
Rayman
04-02-2011, 06:53 AM
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
04-02-2011, 07:59 AM
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
Rayman
04-02-2011, 08:46 AM
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
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
04-02-2011, 11:39 AM
.
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
04-02-2011, 03:32 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.