Hi,
I have this problem, i have a userform where u can chose diffrent kind of hardware equipment, when selected it goes to one sheet, after that i want it to go to another sheet and in a specific row give me number "1"
See my code so far:
In code below the numer "1" goes direct to assigend cell : Blad2.Range("M98") ( but i want the number to go to the same row as a specific criteria.
The criteria is in column A (it is the distributers order number) i want excel to find that and but my numer in column M on the same row.
Thanks for your help
Private Sub CommandButton1_Click()
'For listbox to worksheet
Dim r As Long
For r = 1 To Me.lbxHW.ListCount - 1
If Me.lbxHW.Selected(r) Then
Blad28.Range("A" & Rows.count).End(xlUp).Offset(1, 0) = Me.lbxHW.List(r, 0)
For i = 2 To 20
If Blad28.Cells(i, 1) = "HP 700 Printer & Inks" Then
Blad2.Range("M98") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M737") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M739") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M740") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M741") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M742") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M743") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
I have this problem, i have a userform where u can chose diffrent kind of hardware equipment, when selected it goes to one sheet, after that i want it to go to another sheet and in a specific row give me number "1"
See my code so far:
In code below the numer "1" goes direct to assigend cell : Blad2.Range("M98") ( but i want the number to go to the same row as a specific criteria.
The criteria is in column A (it is the distributers order number) i want excel to find that and but my numer in column M on the same row.
Thanks for your help
Private Sub CommandButton1_Click()
'For listbox to worksheet
Dim r As Long
For r = 1 To Me.lbxHW.ListCount - 1
If Me.lbxHW.Selected(r) Then
Blad28.Range("A" & Rows.count).End(xlUp).Offset(1, 0) = Me.lbxHW.List(r, 0)
For i = 2 To 20
If Blad28.Cells(i, 1) = "HP 700 Printer & Inks" Then
Blad2.Range("M98") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M737") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M739") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M740") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M741") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M742") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")
Blad2.Range("M743") = Application.WorksheetFunction.CountIf(Blad28.Range("A:A"), "HP 700 Printer & Inks")