Error - can you see whats wrong with this - any suggestions for improvements - listbox

[deadstack]

hi All

The below code is giving me a mis-match error, this is something i have copied and tried to mod to fit for use

what i am trying to do is for ever row that has a -1 this is entered by another macro, i want just these rows to appear in my listbox

Private Sub UserForm_Initialize() 
 Dim wk As Workbook
 Dim ws1 As Worksheet
 
 Set wk = ThisWorkbook
 Set ws1 = wk.Sheets("Sheet1")
 
 Dim rCopy  As Range
    Dim c As Integer
    Dim r As Integer
    
    With Sheets("Sheet1").Select
         
        c = Range("a1").End(xlToRight).Column
        r = Cells(Rows.Count, c).End(xlUp).Row
        
       End With
        'Set rCopy = Range(Cells(1, 3), Cells(r, 8))
        
       For r = 1 To r
        'ws1.Cells(r, 37).Value = "-1"
        Me.ListBox1.RowSource = ws1.Cells(r, 37) = "-1" ' rCopy.Address
        
        
   
Next r




End Sub

When i have this up i want to highlight a number and i press the button below i want to fill data against this

Private Sub cmdOkay_Click()Dim wk As Workbook
Dim the_sheet As Worksheet
Dim uf6 As UserForm
Dim combob1 As ComboBox
Dim combob2 As ComboBox
Set the_sheet = Sheets("Sheet1")
Set uf6 = UserForm2
Set wk = ThisWorkbook
Set combob1 = Me.ComboBox1
Set combob2 = Me.ComboBox2




Range("AC" & (ActiveCell.Row)) = Right(combob1.Value, 4)
Range("AE" & (ActiveCell.Row)) = combob2.Value
Range("AL" & (ActiveCell.Row)) = "0"
Range("AK" & (ActiveCell.Row)).Clear


UserForm6.Hide
Call RouteVal3
     
End Sub

then re-run this again untill all the "-1" are all gone


any one got any idea's!! i'm going crazy looking at it!!!

 

[mrmmickle1]

This will add Items in Column 1 to your list based on if Column 37 has a -1

[COLOR=#0000ff]Private Sub[/COLOR] UserForm_Initialize()


   [COLOR=#0000ff] Dim[/COLOR] r [COLOR=#0000ff]As Integer[/COLOR]
    
    Sheets(1).Activate
        
    r = Cells(Sheets(1).Rows.Count, "A").End(xlUp).Row
        
[COLOR=#0000ff]    For[/COLOR] r = 1[COLOR=#0000ff] To[/COLOR] r
    
      [COLOR=#0000ff]  If [/COLOR]Cells(r, 37) = -1 [COLOR=#0000ff]Then[/COLOR]
            Me.ListBox1.AddItem Cells(r, 1)
[COLOR=#0000ff]        End If[/COLOR]
    
    [COLOR=#0000ff]Next[/COLOR] r


[COLOR=#0000ff]End Sub[/COLOR]

Is this what your trying to do?

 

[deadstack]

kind of

im am using the list box to select a number of rows, and then using a button to apply values into the cell,

im not sure that what oyu posted is going to work for this project, though, anyother ideas??

thanks

 

[deadstack]

sorry to explain this better

1. when i call up this userform, i want it to populate with only the columns that have -1
2. i then want to be able to select multiple lines (Which has been done through the properties window
3. i have two combo boxes which i fill with information and then apply them to two different cells on each row
4. i also clear the -1
5. unload the userform
6 .load it again and the above rows will be gone and i can select and repeat till nothing is left in the listbox (because there is no "-1" left) and then move on to the next sub

anyone help based on the code above


if you haven't guessed i am a vba newb!!

thanks in advance

 

[mrmmickle1]

Do you want to fill the list box with numbers signifying what row you are modifying or do you want the list box to pick up multiple columns with the (-1)....thus imitating your actual data row in full (If so how many columns have -1)? You will need to set the ColumnCount if this is the case.


Which procedure is erroring out? Does the first one work and the second one fail? or are they both not working?

 

[deadstack]

Thanks for the reply

sorry the -1 refers to rows not columns

yes i only want the rows with -1 on them, i have the code working now but showing all the rows

how would i rewrite the row count to do this

thanks again i never taught of row counting them !!

Private Sub UserForm_Initialize() 
 Dim wk As Workbook
 Dim ws1 As Worksheet


 Set wk = ThisWorkbook
 Set ws1 = wk.Sheets("Sheet1")


    Dim rCopy  As Range
    Dim c As Integer
    Dim r As Integer
    Dim rn As String
    
    With Sheets("Sheet1").Select
         
        c = Range("a1").End(xlToRight).Column
        r = Cells(Rows.Count, c).End(xlUp).Row
      Set rCopy = Range(Cells(1, 3), Cells(r, 8))
    
       
     End With
      
        
       For r = 1 To r
      
           
       Me.ListBox1.RowSource = rCopy.Address
         
   
Next r




End Sub

 

[Norie]

You can't use RowSource unless you are populating the listbox from a contiguous range on a worksheet.

Are you sure Matt's code in post #2 doesn't populate, using AddItem, the listbox as required?

 

[mrmmickle1]

If your data looks like this:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Header	Header	Header	Header	Header	Header	Header	Header	Evaluation
2	a1	b1	c1	d1	e1	f1	g1	h1	-1
3	a2	b2	c2	d2	e2	f2	g2	h2
4	a3	b3	c3	d3	e3	f3	g3	h3	-1
5	a4	b4	c4	d4	e4	f4	g4	h4
6	a5	b5	c5	d5	e5	f5	g5	h5	-1
7	a6	b6	c6	d6	e6	f6	g6	h6
8	a7	b7	c7	d7	e7	f7	g7	h7	-1
9	a8	b8	c8	d8	e8	f8	g8	h8
10	a9	b9	c9	d9	e9	f9	g9	h9	-1
11	a10	b10	c10	d10	e10	f10	g10	h10
12	a11	b11	c11	d11	e11	f11	g11	h11	-1
13	a12	b12	c12	d12	e12	f12	g12	h12
14	a13	b13	c13	d13	e13	f13	g13	h13	-1
Sheet1

This code:

[COLOR=#0000ff]Private Sub[/COLOR] UserForm_Initialize()


 [COLOR=#0000ff]   Dim [/COLOR]wk [COLOR=#0000ff]As [/COLOR][COLOR=#000000]Workbook[/COLOR][COLOR=#0000ff][/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] ws1[COLOR=#0000ff] As [/COLOR][COLOR=#000000]Worksheet[/COLOR][COLOR=#0000ff][/COLOR]
 [COLOR=#0000ff]   Dim[/COLOR] rCopy  [COLOR=#0000ff]As [/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#0000ff][/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]c [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] r [COLOR=#0000ff]As Integer[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] cll [COLOR=#0000ff]As [/COLOR][COLOR=#000000]Range[/COLOR]


[COLOR=#0000ff]    Set[/COLOR] wk = ThisWorkbook
[COLOR=#0000ff]    Set [/COLOR]ws1 = wk.Sheets("Sheet1")


    c = Range("a1").End(xlToRight).Column
    r = Cells(Rows.Count, c).End(xlUp).Row
[COLOR=#0000ff]    Set[/COLOR] rCopy = Range(Cells(1, 3), Cells(r, 8))


       For r = 1 To r
       [COLOR=#0000ff]    If[/COLOR] Cells(r, 9) = -1[COLOR=#0000ff] Then[/COLOR]
          [COLOR=#0000ff]      With[/COLOR] Me.ListBox1
          
                    .AddItem Cells(r, 3)
                    .List(myCol, 1) = Cells(r, 4)
                    .List(myCol, 2) = Cells(r, 5)
                    .List(myCol, 3) = Cells(r, 6)
                    .List(myCol, 4) = Cells(r, 7)
                    .List(myCol, 5) = Cells(r, 8)
                    myCol = myCol + 1
                    
[COLOR=#0000ff]                End With[/COLOR]
[COLOR=#0000ff]           End If[/COLOR]
   [COLOR=#0000ff]    Next[/COLOR] r


[COLOR=#0000ff]End Sub[/COLOR]

Will give you data in your listbox that looks like this:

Excel 2010
	C	D	E	F	G	H
2	c1	d1	e1	f1	g1	h1
3	c3	d3	e3	f3	g3	h3
4	c5	d5	e5	f5	g5	h5
5	c7	d7	e7	f7	g7	h7
6	c9	d9	e9	f9	g9	h9
7	c11	d11	e11	f11	g11	h11
8	c13	d13	e13	f13	g13	h13
Sheet1

Is that what you want?

 

[deadstack]

Thanks for this

I think we are getting closer

i am getting a run time 70 error permission denied


I did change two things

1. i dim the myCol as an integer and set it to 1 for the first column, i tried it at -1 as i seen somewhere you had to start counting at that
i notice you have cll as a range, but are not using it in the code i left this

2. i changed the column to 38 in the if statement

Private Sub UserForm_Initialize() 
  Dim wk As Workbook
    Dim ws1 As Worksheet
    Dim rCopy  As Range
    Dim c As Integer
    Dim r As Integer
    Dim cll As Range
    Dim myCol As Integer


    Set wk = ThisWorkbook
    Set ws1 = wk.Sheets("Sheet1")




    c = Range("a1").End(xlToRight).Column
    r = Cells(Rows.Count, c).End(xlUp).Row
    Set rCopy = Range(Cells(1, 3), Cells(r, 8))




       For r = 1 To r
           If Cells(r, 38) = -1 Then
                With Me.ListBox1
          myCol = 1
                    .AddItem Cells(r, 3)
                    .List(myCol, 1) = Cells(r, 4)
                    .List(myCol, 2) = Cells(r, 5)
                    .List(myCol, 3) = Cells(r, 6)
                    .List(myCol, 4) = Cells(r, 7)
                    .List(myCol, 5) = Cells(r, 8)
                    myCol = myCol + 1
                    
                End With
           End If
       Next r




End Sub

any ideas??

 

[deadstack]

write i debugged this a little bit

i put -1 in the 6th cell and f8 in vba

it went down to the 6th cell and passed a 1 for myCol, then when it went to the nex line .list add it threw the error of premission error 70

edit just to note - before i moved onto that line i pointed the mouse at the cells etc and it was seeing the right infromation