Posts by Tinbendr

Re: Populate ListBox from Multiple sheets

And is that working for you now?

I d/l the example and it seems ok.

I learned something new today (well re-remembered it) on the number of columns.

Re: Populate ListBox from Multiple sheets

There is a limit of 10 columns (0-9).

If you have more columns than 10, try to use the listbox not as a storage array, but as a 'representation' of the data. Pull in just enough data so the user will know what the record refers to. Then use the last column of the listbox (which is the row number of the original record) inside the Listbox click event to load more textboxes.

For A = 1 To 4 'Change the last digit to fill more textboxes.
    Controls("Reg" & A) = ListBox1.Column(A - 1)
Next

Re: Copying a BOM list and Generating a List ommitting 2 specific columns automatical

Quote

The issue I am arriving at currently is each part has their own BoM varying in size of material parts needed from ~3-25.

Your example only has 10 each. Could the list go to 25?

Quote

I am inquiring if there is a way to set up a macro or string of code that will recognize when the BoM has ended it's count whether that's line item 3 or 25 and then generate the text onto a separate sheet.

But it seems you're already doing that through linked cells.

You don't include any data or a completed page for us to visualize.

Each line in the BOM is to generate a Pick list?

Will the PKG-style and #-per-BD be reflected in the Picklist?

Re: userform cancel button call

Maybe

If Not Yeni_Sheet_Adi_Olustur.Sheet_Iptal Is Nothing Then

Re: Combo box dependent on Option Buttons

You're description is a little vague.

What you're trying should work. Does it not?

Search for cascading comboboxes.

Re: Populate ListBox from Multiple sheets

take a look and see if we're on the same page.

Re: Populate ListBox from Multiple sheets

I can't follow your logic of operation.

Can you tell me how you expect this to work? From what you see when the userform is open, to what happens when you select a item in the listbox and the combobox.

Re: Lookup giving formula instead of value

I'm not sure if this is what you want. See attachment.

Re: Auto Word Select in VBA VLOOKUP

You have to trap for N/A#, or rather, when text is not found.

Private Sub CentreNameList_Change()
    If Len(CentreNameList.Text) > 1 Then
        Dim NameCheck As Variant
        On Error Resume Next
        NameCheck = Application.WorksheetFunction. _
        VLookup(CentreNameList.Text, Worksheets("Database - Pre").Range("L2:M550"), 2, False)
        
        If Err.Number = 0 Then
            Label31.Caption = NameCheck
        Else
            Label31.Caption = "Not Found"
        End If
    End If
End Sub

But I use Range.Find instead. It's more flexible.

Private Sub CentreNameList_Change()
Dim WS As Worksheet
Dim LastRow As Long
Dim C As Range


    If Len(CentreNameList.Text) > 1 Then
        Set WS = Worksheets("Database - Pre")
    
        With WS
            LastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
        
            With .Range("L2:M" & LastRow)
                Set C = .Find(CentreNameList)
                
                If Not C Is Nothing Then
                    Label31.Caption = C
                End If
            End With
        End With
    Else
        Label31.Caption = "Not Found"
    End If
End Sub

Re: Importing a .txt to vba and get some datas to use in a useform

You can likely use Excel's built in data query to pull in the data you want.

Remember, vague questions get vague answers.

Re: Data Validation Drop Down based on a filtered table

Cross posted @ ExcelForum

Re: Error Handler: How to Handle a Range that does not exist

I guess we need to back up and try to test for Output_range before it errors. How are you assigning the value to Output_Range?

Re: Error Handler: How to Handle a Range that does not exist

If Range(Output_Range) is Nothing Then

Re: Shortening VBA code

You'd do better to study naming conventions vs shortening code at this stage. Writing sophisticated code that is short, but hard to read isn't always the best use of programming skills, IMHO.

Re: Check values cells with Formula or vba?

Not nearly as compact as Batman's, but it seems to work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TestValue As String
Dim A As Long


If Target.Cells.Count = 1 Then
    If Not Intersect(Target, Sheet1.Range("A2:G2")) Is Nothing Then
        For A = 1 To 7
            TestValue = TestValue & Sheet1.Cells(Target.Row, A)
        Next
        Application.EnableEvents = False
        Sheet1.Range("W2,W22,W42,W62,W82,W102,W122") = 0
        Select Case TestValue
            Case "1000000"
                Sheet1.Range("W2") = 1
            Case "1100000"
                Sheet1.Range("W22") = 1
            Case "1110000"
                Sheet1.Range("W42") = 1
            Case "1111000"
                Sheet1.Range("W62") = 1
            Case "1111100"
                Sheet1.Range("W82") = 1
            Case "1111110"
                Sheet1.Range("W102") = 1
            Case "1111111"
                Sheet1.Range("W122") = 1
        End Select
    End If
End If
Application.EnableEvents = True
End Sub

Re: Covert Current Formula based Sheet to Macro Based

I did not fill in every sheet. I think what you were looking for was how to parse by month.

Here is one from the Billed sheet.

=COUNTIFS(Base_Data_Trimmed!$G:$G,"N",Base_Data_Trimmed!$F:$F,$A4,Base_Data_Trimmed!$D:$D,"P1A",Base_Data_Trimmed!$J:$J, ">="&EOMONTH(B$3, -1)+1,Base_Data_Trimmed!$J:$J, "<="&EOMONTH(B$3, 0))

The two latter conditions, I think, what you were looking for.

Here is a video example by Mike Girvin.

Re: Mark data

Quote

VBA to find...

Your statement is a little vague.

Re: VBA how to find the next empty row with a constant empty space in between

I'm going to need a sample file. You can zip two together. One for master one for destination.

Re: Macro to create new sheet on the basis of given criteria

There might be simpler math for the dates, but I had to use the stop/start vars since I'm in the USA.

Sub BreakOut()
Dim WSsrc As Worksheet
Dim WSDest As Worksheet
Dim LRSrc As Long
Dim LRDest As Long
Dim A As Long, B As Long
Dim BODates As Long
Dim StartDate As Date, StopDate As Date


Set WSsrc = Worksheets("Before")
Set WSDest = Worksheets.Add(after:=Worksheets(Worksheets.Count))


With WSDest
    .Range("A1") = "Material"
    .Range("B1") = "Description"
    .Range("C1") = "Org"
    .Range("D1") = "Date"
End With


LRDest = 2


With WSsrc
    LRSrc = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    For A = 2 To LRSrc
    
        StopDate = DateSerial(Right(.Range("E" & A), 4), _
            Mid(.Range("E" & A), InStr(.Range("E" & A), ".") + 1, 2), _
            Left(.Range("E" & A), InStr(.Range("E" & A), ".") - 1))
            
        StartDate = DateSerial(Right(.Range("D" & A), 4), _
            Mid(.Range("D" & A), InStr(.Range("D" & A), ".") + 1, 2), _
            Left(.Range("D" & A), InStr(.Range("D" & A), ".") - 1))
            
        BODates = StopDate - StartDate
        
        If BODates <> 0 Then
            For B = 0 To BODates
                WSDest.Range("A" & LRDest) = .Range("A" & A)
                WSDest.Range("B" & LRDest) = .Range("B" & A)
                WSDest.Range("C" & LRDest) = .Range("C" & A)
                WSDest.Range("D" & LRDest) = Format(StartDate + B, "dd.mm.yyyy")
                LRDest = LRDest + 1
            Next
        End If
    Next
End With
                
End Sub

Re: Calculate business hours between two dates includin Sat Hours and exlcuding Sun

Here's an article about that.