Posts by KadeFoster

Ok have attached my project. This is my first project in VBA, so i have been learning as i go along.

The above code is located on the CheckScaleForm

It is used to search DataBaseTasks sheet to create a list of Tasks related to the specific mine on separate sheets. I want the code to only show tasks that aren't Cancelled or Completed in these list boxes.

Those sheets then are used for the 3 of my list boxes to display the tasks for that mine.

You can then double click an a task and write a record for it and save it. Depending on your choices it may generate an email.

There is an error popping up to but i will ask for help about that after this is sorted. One problem at a time. I have managed to get this far on my own. My code may not be pretty but it mostly works.

Hey all,

I am wanting to exclude certain results from my search if one of the cells value is Completed and Cancelled.

I have managed to find some code that loops through my list looking for rows with a certain value, i just don't know how to make it exclude the rows with the above values.

Status1 & Status2 are the values i am trying to ignore. They are located in column 15 on the "DataBaseTasks" sheet.

Below is my code:

'********** SETS UP THE SOURCE FOR THE ENTERPRISE LIST BOX WITH ENTERPRISE TASKS **********************************************************************
Sub SetupEntLstBox()

    Dim RowNum As Long
    Dim SearchRow As Long
    Dim EntMine As String
    Dim Status1 As String
    Dim Status2 As String

    RowNum = 2
    SearchRow = 2
    EntMine = "Enterprise"
    Status1 = "Completed"
    Status2 = "Cancelled"
    
    Worksheets("DataBaseTasks").Activate
    
    Do Until Cells(RowNum, 1).Value = ""
             
        If InStr(1, Cells(RowNum, 3).Value, EntMine, vbBinaryCompare) > 0 Then
            Worksheets("EnterpriseTasks").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 2).Value = Cells(RowNum, 2).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 3).Value = Cells(RowNum, 3).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 4).Value = Cells(RowNum, 4).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 5).Value = Cells(RowNum, 5).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 6).Value = Cells(RowNum, 6).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 7).Value = Cells(RowNum, 7).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 8).Value = Cells(RowNum, 8).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 9).Value = Cells(RowNum, 9).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 10).Value = Cells(RowNum, 10).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 11).Value = Cells(RowNum, 11).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 12).Value = Cells(RowNum, 12).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 13).Value = Cells(RowNum, 13).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 14).Value = Cells(RowNum, 14).Value
            Worksheets("EnterpriseTasks").Cells(SearchRow, 15).Value = Cells(RowNum, 15).Value
            SearchRow = SearchRow + 1
        End If
        RowNum = RowNum + 1
    Loop
    
    If SearchRow = 2 Then
        Exit Sub
    End If
End Sub

Hey Roy,

Thanks for the reply, hate to sound like a noob (that i am) but where about should i be looking in the code for what i am after?

Hey all,

Been a while since I have been here. Have had no projects at work to do. But at the moment I am working on a VBA Userform project and basically have to teach myself how to do it, or learn how to use other peoples code and modify it to my needs. My first ever VBA project really, kinda jumping straight into the deep end. My code may be messy but I am learning as i go. Optimisation will come later, i just need to get this working.

Any ways i am kinda stumped. On my Userform (CheckScaleForm) I have 3 list boxes that show my data. On double click selection, that data repopulates my other fields, then i fill in a textbox to create an update for that task on another Sheet (DataBaseCheckScales) with all my update entries. I want my Status Combo selection to update the Status Cell from the double click selection on (DataBaseTasks) but the rest of the update to go to the updates sheet (DataBaseCheckScales) (this code is working).

I have managed to get myself most of the way through the project but this idea has me beat. Any help would be awesome.

Re: Combinations

Sorry my descriptions sucks. OK i have included what i generated before. The macro does not crash your system it just takes a bit of time to generate that amount or combination outcomes. If you look in my list i have 0 which i would prefer to be blank. The current macro generates combinations with those zeros. I just need it to ignore those zeros/blanks and only generate with the input i provided. Hope this helps

b) 90 d) Significant 0 c) Intermediate
c )80 d) Significant c) Moderate c) Intermediate
d) 70 d) Significant c) Moderate c) Intermediate
e) 60 0 c) Moderate 0
f) 50 d) Significant c) Moderate c) Intermediate
g) 40 d) Significant 0 c) Intermediate
h) 30 d) Significant c) Moderate c) Intermediate

Re: Combinations

The macro does what its supposed to do. It just does not ignore blank cells. I ma trying to generate all possible combinations of the items in the list in the order that they are in. One list is 10 cells long the others are 4. What the macro does is just starts outputting the combinations from that first blank cell. I just fill them will 0s so that each list is 10 long. this gets around that problem but also generate a couple of 1000 more combinations which i have to sort and delete the rows as i don't need that data.

The combinations have to keep the order that they are already in. Just need the macro to ignore the blank cells and start out putting the combinations the row below all the input data.

Re: Combinations

Did not seem to work for me.... did not generate what i was after. The loop that i have does what i want just does not ignore the blank cells, and will just the combinations into the first available blank cell which basically fills in around where i want input to be. If you get what i mean

Hey All,

I am not an excel guru or coder hence why i am asking this question. I am trying to generate all possible combinations from a certain number of items keeping them in order as such. I have found some code and it nearly does what i want it to but not quite.

Public Sub combinations()




Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer




Worksheets(1).Range("A15:IV65536").ClearContents




For a = 2 To 11
    For b = 2 To 11
        For c = 2 To 11
            For d = 2 To 11
                For e = 2 To 11
                    Worksheets(1).Range("A" & Range("A65536").End(xlUp).Row + 1) = Worksheets(1).Cells(a, 1)
                    Worksheets(1).Range("B" & Range("B65536").End(xlUp).Row + 1) = Worksheets(1).Cells(b, 2)
                    Worksheets(1).Range("C" & Range("C65536").End(xlUp).Row + 1) = Worksheets(1).Cells(c, 3)
                    Worksheets(1).Range("D" & Range("D65536").End(xlUp).Row + 1) = Worksheets(1).Cells(d, 4)
                    Worksheets(1).Range("E" & Range("D65536").End(xlUp).Row + 1) = Worksheets(1).Cells(e, 5)
                Next e
            Next d
        Next c
    Next b
Next a
        
End Sub

It was originally 3 columns but i added in a couple more.

As you can see from my file i am trying to combine that data. When i run the macro it just starts to fill the combinations down from where ever is the first blank cell. I need it to ignore the blank cells and just generate combinations from whats there. Also to start to fill them down from row 12 or so and not in the data i am trying to use. Any help would be appreciated.