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]

The column isn't specifying the list box column. it's specifying the worksheet column. What column is the ID on the worksheet? Can you provide some sample data so I have a better idea of the data structure?

 

[deadstack]

Yeah no problem... can u pm me your email address and i will send it on thanks

 

[mrmmickle1]

This should fix your issue:

[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] Dim[/COLOR] wk [COLOR=#0000ff]As[/COLOR] Workbook
 [COLOR=#0000ff]   Dim[/COLOR] ws [COLOR=#0000ff]As [/COLOR]Worksheet
    
  [COLOR=#0000ff]  Set [/COLOR]wk = ThisWorkbook
    [COLOR=#0000ff]Set[/COLOR] ws = wk.Sheets("sheet1")


[COLOR=#0000ff]    With [/COLOR]Me.ListBox1
     [COLOR=#0000ff]  For[/COLOR] lstLp = 0 [COLOR=#0000ff]To[/COLOR] .ListCount - 1
        [COLOR=#0000ff]  If[/COLOR] .Selected(lstLp) = [COLOR=#0000ff]True 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]   Debug.Print[/COLOR] myID
 
  [COLOR=#0000ff]  Set[/COLOR] fndID = ws.Columns(1).Find(myID, , xlValues, , xlWhole, xlByRows, xlNext, False) 'This finds the ID in Column A and Sets You up to update your values....
    
    [COLOR=#0000ff]Debug.Print[/COLOR] fndID.Address
    UserForm6.Hide
[COLOR=#008000]    'Then you just need to update them like this[/COLOR]
    fndID.Offset(, 29) = Right((ComboBox1), 4) [COLOR=#008000]'Updates the Value B10 on the spreadsheet two rows to the right of my ID of 10.....[/COLOR]
     [COLOR=#0000ff] Debug.Print[/COLOR] fndID(, 29)
    fndID.Offset(, 31) = ComboBox2[COLOR=#008000] 'Replaces -1 with 0[/COLOR]
      [COLOR=#0000ff]Debug.Print[/COLOR] fndID(, 31)
    fndID.Offset(, 37).Value = "Val"
   [COLOR=#0000ff]   Debug.Print [/COLOR]fndID(, 37)
    fndID.Offset(, 38).Value = 0
  [COLOR=#0000ff]    Debug.Print[/COLOR] fndID(, 38)
    UserForm6.Show
[COLOR=#0000ff]    [/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

The only thing you were really missing was the actual ID data on your worksheet "Sheet1" this is what you are using to index the row ffor updates. Once that's smoothed out. Looks like your good to go.