List based on criteria

[Noz2k]

I currently have a listbox which allows me to look through all the records in my data table. The code for that is below

Dim lngLastRow As Long
    lngLastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
        
    If lngLastRow <> 3 Then
        cboID.List = Worksheets("Sheet1").Range("A3:A" & lngLastRow).Value
    Else
        cboID.List = Worksheets("Sheet1").Range("A3:A4").Value
    End If

However now I want to add the criteria to change the range. So that rows are only included where the corresponding cell in column M = "No"

And I'm not sure how to do this, so any help would be appreciated, thanks

 

[Noz2k]

If I'm not explaining very clearly, then please let me know

 

[bertie]

Loop through column A and check the value in column M.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
   PopulateComboBox
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]



[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateComboBox()
   [COLOR=green]'=========================================[/COLOR]
   [COLOR=green]'loop though column A until an empty[/COLOR]
   [COLOR=green]'cell is detected an poopulate a combobox[/COLOR]
   [COLOR=green]'=========================================[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
   
   [COLOR=darkblue]Set[/COLOR] rng = Sheets("Sheet1").Range("A3")
   
   [COLOR=green]'check column M[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      [COLOR=darkblue]If[/COLOR] UCase(rng.Offset(0, 12).Value) = "NO" [COLOR=darkblue]Then[/COLOR]
         [COLOR=green]'add the value in column A to the control[/COLOR]
         cboId.AddItem rng.Value
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=green]'next row[/COLOR]
      [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
   [COLOR=darkblue]Loop[/COLOR]
   
   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR] [COLOR=green]'clears the memory set aside for the range object[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Noz2k]

Thank you Bertie, that works perfectly.

The problem I have now is that currently a lot of data is generated based on the .listindex of cboID, which now doesn't match.

So now although the 2nd item in the list of cboID is 5 rows down it still returns the data from 2 rows down.

As the code is like

txtFirstName.Value = Sheets("Sheet1").Range("B" & cboID.ListIndex +3)

 

[Noz2k]

Ok I sort of have it working, the only thing I'm struggling with is getting lngLastRow to work when setting the range to look in.

The code I have come up with is

Dim rng As Range, _
ws As Worksheet, _
lngLastRow As Long
 
Set ws = Sheets("Sheet1")
'lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(x1Up).Row
With ws.Range("A3:A500") ' & lngLastRow)
Set rng = .Find(cboID.Value, LookIn:=xlValues)
txt1.Value = rng.Offset(, 1)
txt2.Value = rng.Offset(, 2)
txt3.Value = rng.Offset(, 3)
txt4.Enabled = True
txt5.Enabled = True
lst6.Enabled = True
txt7.Enabled = True
cmdSend.Enabled = True
imgCross1.Visible = False
txt1 = Format(Date, "dd/mm/yyyy")
End With

But if take out the 's and put ws.Range("A3:A" & lngLastRow) I get "run time error 1004 : application defined or object defined error"

 

[bertie]

OK, I will be away from my pc for a couple of hours but here are my initial thoughts.

When we populate the cboID control we could populate an array which contains both the row number and the value for cboId.

Then when needed loop through the array and retrieve the row number using the cboId value as a reference.

Or if the cboId values are unique we could just use a FIND statement. And get the row number that way.

Let me know your thoughts and I will look at iwhen i get back.

Bye for now.

 

[Noz2k]

Thanks,

They are Unique, I seem to have the .Find working.

The only problem is that I can't make the range to search dynamic. Usually I use the lngLastRow As Long method (as seen in my code), but it doesn't seem to be working for this.

Alternatively I could set the range very high, but although the range might eventually need to be very large, it seems seems counterproductive to set it from A3:A1000000 when at first it only needs to be A3:A4.

 

[bertie]

Hi Noz,

Because you have code which uses the cboId.ListIndex my first thoughts were to preserve any code you have already written by using an array of row numbers.

Declare a global array arrRowNum variable and populate this array with row numbers at the same time as cboId is being populated.

[COLOR=Blue]Private arrRowNum() [/COLOR][COLOR=Blue]As [/COLOR][COLOR=Blue]Long[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateComboBox()
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
   [COLOR=Red]Dim lngCounter [/COLOR][COLOR=Red]As [/COLOR][COLOR=Red]Long[/COLOR]

   [COLOR=darkblue]Set[/COLOR] rng = Sheets("Sheet1").Range("A3")
   
   [COLOR=green]'check column M[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      [COLOR=darkblue]If[/COLOR] UCase(rng.Offset(0, 12).Value) = "NO" [COLOR=darkblue]Then[/COLOR]
         [COLOR=green]'add the value in column A to the control[/COLOR]
         cboId.AddItem rng.Value
         
         [COLOR=green]'add row number to array[/COLOR]
[COLOR=Red]         lngCounter = lngCounter + 1
         [/COLOR][COLOR=Red]ReDim [/COLOR][COLOR=Red]Preserve arrRowNum(1 [/COLOR][COLOR=Red]To lngCounter)
         arrRowNum(lngCounter) = rng.Row[/COLOR]
      
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=green]'next row[/COLOR]
      [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
   [COLOR=darkblue]Loop[/COLOR]

   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR] [COLOR=green]'clears the memory set aside for the range object[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

We can then use cboId.ListIndex to get the row to work with.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdProcess_Click()
   [COLOR=darkblue]Dim[/COLOR] lngRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=Red]lngRow = arrRowNum(cboId.ListIndex + 1)[/COLOR]
   
[COLOR=Green]    'and the rest of your code remains as is[/COLOR]
   MsgBox "Data is on row: " & lngRow
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

The full code for my sample form is:

[COLOR=darkblue]Private[/COLOR] arrRowNum() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] PopulateComboBox()
   [COLOR=darkblue]Dim[/COLOR] rng [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] lngCounter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]

   [COLOR=darkblue]Set[/COLOR] rng = Sheets("Sheet1").Range("A3")
   
   [COLOR=green]'check column M[/COLOR]
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rng = ""
      [COLOR=darkblue]If[/COLOR] UCase(rng.Offset(0, 12).Value) = "NO" [COLOR=darkblue]Then[/COLOR]
         [COLOR=green]'add the value in column A to the control[/COLOR]
         cboId.AddItem rng.Value
         
         [COLOR=green]'add row number to array[/COLOR]
         lngCounter = lngCounter + 1
         [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] arrRowNum(1 [COLOR=darkblue]To[/COLOR] lngCounter)
         arrRowNum(lngCounter) = rng.Row
      
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=green]'next row[/COLOR]
      [COLOR=darkblue]Set[/COLOR] rng = rng.Offset(1, 0)
   [COLOR=darkblue]Loop[/COLOR]

   [COLOR=darkblue]Set[/COLOR] rng = [COLOR=darkblue]Nothing[/COLOR] [COLOR=green]'clears the memory set aside for the range object[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]



[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdProcess_Click()
   [COLOR=darkblue]Dim[/COLOR] lngRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   lngRow = arrRowNum(cboId.ListIndex + 1)
   
   MsgBox "Data is on row: " & lngRow
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]



[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdClose_Click()
   Unload Me
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]




[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()
   PopulateComboBox
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[bertie]

Here is code for using the FIND method. I have highlighted where you may need to edit.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmdProcessFind_Click()
   [COLOR=darkblue]Dim[/COLOR] rngFound [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] lngRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] lngIndex [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   
   lngIndex = [COLOR=darkblue]CLng[/COLOR](cboId.Value)
   
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR] [COLOR=green]'in case a value is not found[/COLOR]
      [COLOR=darkblue]With[/COLOR] Sheets("[COLOR=Red]Sheet1[/COLOR]")
         [COLOR=darkblue]Set[/COLOR] rngFound = .Columns([COLOR=Red]1[/COLOR]).Find(What:=[COLOR=Red]lngIndex[/COLOR], _
                        After:=.Cells([COLOR=Red]3, 1[/COLOR]), _
                        LookIn:=xlValues, _
                        LookAt:=xlPart, _
                        SearchOrder:=[COLOR=Red]xlByColumns[/COLOR], _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0 [COLOR=green]'report errors as normal[/COLOR]
   
   [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] rngFound [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
      lngRow = rngFound.Row
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   
   [COLOR=green]'your code for processing goes here[/COLOR]
   MsgBox "Data is on row: " & lngRow
   
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[Noz2k]

Thank you very much, that has sorted out my issue with the Dynamic range, and I've used the same principles when referencing where to send the newly inputted date.

Once again, thanks