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!!!

 

[deadstack]

ok,

would you have any examples of how this is done, i can work away with it then,

i assume i am trying to do much at the same time i should

select the first column ie routing code

pass in the value for all the cells selected

then select pass the value for the the next column

then clear the -1 out of the other field

then reload userform

how does that sound??


that row index any examples of it


thanks again for all your help! your a gent!!

 

[mrmmickle1]

So let's start small....

Try fiddling around with the column width property with the data you currently have. This way you can see how it's interacting with the form.
Set values....
then change them....
Then change them with one set to 0...

Should be getting the hang of things now...

Once you see what's going on with that. Try adding the additional column you have created on the worksheet into the userform. It's in the .additem block of code that your going to look to do this. Remember you'll have to adjust the values to incorporate one additional column.

Troubleshooting from my phone at this point....sorry for the lack of examples. The good news is this should help you understand the code better and build your skill set.

 

[deadstack]

hey i got that working very well

set x as the first line at the .additem and set x = to 1 then just added to it at the end of the code block, it worked well

i also got the coloumns looking well set the first column to 40px the second to 200 px and the rest to 150px


so now i have to write the code to paste in values in cells that i have selected

how can we use x for this though

 

[mrmmickle1]

Data Like this:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	ID	Header	Header	Header	Header	Header	Header	Header	TRUE?
2	1	A1	B1	C1	D1	E1	F1	G1	-1
3	2	A2	B2	C2	D2	E2	F2	G2
4	3	A3	B3	C3	D3	E3	F3	G3
5	4	A4	B4	C4	D4	E4	F4	G4	-1
6	5	A5	B5	C5	D5	E5	F5	G5
7	6	A6	B6	C6	D6	E6	F6	G6	-1
8	7	A7	B7	C7	D7	E7	F7	G7
9	8	A8	B8	C8	D8	E8	F8	G8	-1
10	9	A9	B9	C9	D9	E9	F9	G9
11	10	A10	B10	C10	D10	E10	F10	G10	-1
12	11	A11	B11	C11	D11	E11	F11	G11
13	12	A12	B12	C12	D12	E12	F12	G12	-1
14	13	A13	B13	C13	D13	E13	F13	G13
15	14	A14	B14	C14	D14	E14	F14	G14	-1
16	15	A15	B15	C15	D15	E15	F15	G15
17	16	A16	B16	C16	D16	E16	F16	G16	-1
Sheet1

Code Like this:

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


   [COLOR=#0000ff] Dim [/COLOR]lstLp [COLOR=#0000ff]  As Integer[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] myID    [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] fndID [COLOR=#0000ff]  As[/COLOR] Range


   [COLOR=#0000ff] With [/COLOR]Me.ListBox1
     [COLOR=#0000ff]  For [/COLOR]lstLp = 0 To .ListCount - 1
      [COLOR=#0000ff]    If[/COLOR] .Selected(lstLp) = True [COLOR=#0000ff]Then[/COLOR]
[COLOR=#008000]                'Define ID[/COLOR]
                myID = .List(lstLp, 0)
[COLOR=#0000ff]                Exit For[/COLOR]
[COLOR=#0000ff]          End If[/COLOR]
   [COLOR=#0000ff]    Next[/COLOR] lstLp
[COLOR=#0000ff]    End With[/COLOR]


   [COLOR=#0000ff] Set [/COLOR]fndID = Columns(1).Find(myID, , xlValues, , xlByRows, xlNext, False) 'This finds the ID in Column A and Sets You up to update your values....
    
[COLOR=#008000]    'Then you just need to update them like this[/COLOR]
    fndID.Offset(, 2) = "MyValue" [COLOR=#008000]'Updates the Value B10 on the spreadsheet two rows to the right of my ID of 10.....[/COLOR]
    fndID.Offset(, 8) = 0[COLOR=#008000] 'Replaces -1 with 0[/COLOR]
[COLOR=#0000ff]    [/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]


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


 [COLOR=#0000ff]   Dim [/COLOR]wk [COLOR=#0000ff]As [/COLOR]Workbook
   [COLOR=#0000ff] Dim[/COLOR] ws1 [COLOR=#0000ff]As [/COLOR]Worksheet
  [COLOR=#0000ff]  Dim [/COLOR]rCopy  [COLOR=#0000ff]As [/COLOR]Range
 [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]Range


[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))


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

Column Count = 7
MultiSelect = fmMultSelectSingle

 

[deadstack]

thanks for this

have put this in i am aving some issues

i am getting a run time error 424 on this line

[COLOR=#333333]fndID.Offset(, 2) = "MyValue" [/COLOR]

it only completes 1 line and it is pasting onto the line below it when it works

 

[mrmmickle1]

What column is your ID in?

After myID is established.... See what the value is:

Debug.Print myID

Is it what you expect?
After the fndID is established see what the cell address is:

Debug.Print fndID.Address

Is it what you expect....

Sounds like something isn't jiving based on your description. Those would be my first guesses as to what's going wrong.

 

[deadstack]

Ok so a little more debugging on this

it will work on the first line of the listbox but then paste the values into the correct columns on the second row ( I dont know if it is looking at the second row its self or its just set to it)

when i try it on the second or third row it throws an object error

when i debug print these on the third line the Myid var is 3 and the finID is 10508393

 

[deadstack]

sorry my id is the very first column in the list box​

 

[mrmmickle1]

Try changing this line:

    Set fndID = Columns(1).Find(myID, , xlValues, xlByRows, xlNext, False)

To this:

    Set fndID = Columns(1).Find(myID, , xlValues,[COLOR=#ff0000][B] xlWhole[/B][/COLOR], xlByRows, xlNext, False)

I think your getting hits on partial matches.

check the Address for fndID not the value. Since fndID is a range.... it will have an address like $A$1. This should validate if the code is finding the correct row for your updates.

 

[deadstack]

i am getting an run time error 91 now object variable or block variable not set

it happens on the debug print line of fndID = when i hover the mouse it says fndID = Nothing

edit sorry that is with the new line of code, this was happening on the old line as well