Posts by mrmmickle1

mrmmickle1 · Jun 15th 2016

Re: vlookup from filename

What about using something like this:

Code

=IF(IF(ISERROR(FIND("456",A1,1)),"YES","NO")="NO","VLOOKUP GOES HERE","NO VLOOKUP NECESSARY 426 WAS NOT FOUND")

mrmmickle1 · Jun 15th 2016

Re: Userform - I need to get right checkbox and combo box in this work

Quote

Hello, i have a userform just with textbox and it works fine

Now i need to get right with combobox and checkbox.

You'll need to expand on what you mean. I've looked at your file and this does not make much sense to me. Can you please explain your issue in more detail?

mrmmickle1 · Jun 15th 2016

Re: Watch cell?

Quote

I need, every time when, for example, i run Sort Macro, inside that Sort macro to insert code "how long has it been from last entry in A1:A2"

This is the line that defines the amount of time that has passed between now and the last time an entry has been made in A1 or A2:

Code

wsTwo.Range("B1") = Now() - ThisWorkbook.CustomDocumentProperties("PreviousTimeVal").Value

mrmmickle1 · Jun 14th 2016

Re: Watch cell?

Maybe try this instead:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
     
    Dim ws As Worksheet
    Dim wsTwo As Worksheet
     
    Set ws = Sheets("Sheet1")
    Set wsTwo = Sheets("Sheet2")
    If Target.Address = "$A$1" Or Target.Address = "$A$2" Then
        wsTwo.Range("B1") = Now() - ThisWorkbook.CustomDocumentProperties("PreviousTimeVal").Value
        ThisWorkbook.CustomDocumentProperties("PreviousTimeVal") = Now()
    End If
    
End Sub

Display More

mrmmickle1 · Jun 14th 2016

Re: Listbox item row really long descrption - word wrap option not working even for t

You're welcome. Glad to help out.

mrmmickle1 · Jun 14th 2016

Re: Delete rows with zero and #NUM!

You're welcome. Glad you got everything straightened out.

mrmmickle1 · Jun 14th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Your quite welcome. Glad you were able to get your issue sorted out.

mrmmickle1 · Jun 13th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Maybe try using something like this. It may be simpler for you to manipulate:

Code

Sub Test2()
    
    Dim lr As Long
    Dim LngLp As Long
    Dim ws As Worksheet
    
    Set ws = Sheets("Table") 'Set ws variable
    lr = Sheets("Table").Cells(Rows.Count, "A").End(xlUp).Row 'Define LastRow
    
    For LngLp = 2 To lr
    
        Select Case ws.Cells(LngLp, "D")
            Case "apple", "pineapple", "guava" 'If one of these then assign Test1
                  ws.Cells(LngLp, "M") = "Test1"
            Case "butterfruit"
                  ws.Cells(LngLp, "M") = "Test2"
            Case Else
                  ws.Cells(LngLp, "M") = "Test3"
        End Select
    
    Next LngLp




End Sub

Display More

mrmmickle1 · Jun 12th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

Perhaps something like this will work for your needs:

Code

Sub Test()
         
    Dim lr As Long
    Dim LngLp As Long
    Dim ResrceArr As Variant
     
    lr = Sheets("Table").Cells(Rows.Count, "A").End(xlUp).Row 'Define LastRow
    ResrceArr = Array("Test1", "Test2", "Test3", "Test 4", "Test 5")
    
    For LngLp = 2 To lr
    
        If Sheets("Table").Cells(LngLp, "D") = "orange" Then
            'Assignment of Test1 for orange
            Sheets("Table").Cells(LngLp, "M") = "Test1"
        Else
            'Random Assignment
            Sheets("Table").Cells(LngLp, "M") = ResrceArr(Int((4 - 0 + 1) * Rnd + 0))
        End If
        
    Next LngLp
     
End Sub

Display More

mrmmickle1 · Jun 12th 2016

Re: Allow user to choose which text file to import

You can use code like this to accomplish your goal:

Code

Dim fNameAndPath As Variant
    
    fNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.TXT), *.TXT", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub


    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT; " & fNameAndPath, Destination:=Range("$A$1"))

mrmmickle1 · Jun 12th 2016

Re: Delete rows with zero and #NUM!

This should delete cells with 0 and with Errors in Column A:

Code

Sub Test()




    Dim lr    As Long
    Dim LngLp As Long
    
    lr = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    On Error Resume Next
    For LngLp = lr To 2 Step -1
        If IsError(Cells(LngLp, "A")) Or Cells(LngLp, "A") = 0 Then
            Rows(LngLp).Delete
        End If
    Next LngLp
    On Error GoTo 0
    
End Sub

Display More

mrmmickle1 · Jun 12th 2016

Re: Listbox item row really long descrption - word wrap option not working even for t

Did you set the multiline property of the textbox to true?

mrmmickle1 · Jun 12th 2016

Re: USB Port Communication

You may want to refer to this thread on Mr. Excel that I ran across from last month:

http://www.mrexcel.com/forum/excel-questions/940740-plz-help-masters-visual-basic-applications-capture-northing-westing-usb-gps-data-logger.html

mrmmickle1 · Jun 11th 2016

Re: Copy paste specific cells from one sheet to a "data dump" with a command button

Hey Getshu,

Welcome to the MessageBoard!

Here is a shortened up version of the above code:

Code

Sub CommandButton2_Click()




    Sheets("Interface").Range("C6,C10,C14,C18,C22,C26,C30,C34").Copy
    Sheets("LOG").Range("D3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("INTERFACE").Range("C7,C11,C15,C19,C23,C27,C31,C35").Copy
    Sheets("LOG").Range("E3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("INTERFACE").Range("C8,C12,C16,C20,C24,C28,C32,C36").Copy
    Sheets("LOG").Range("F3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("INTERFACE").Range("C3").Copy Sheets("LOG").Range("C3")
    Sheets("INTERFACE").Range("C2").Copy
    Sheets("LOG").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets("LOG").Select
    Sheets("LOG").Range("B3").AutoFill Destination:=Range("B3:B9")
    Sheets("LOG").Range("C3").AutoFill Destination:=Range("C3:C9")
  
End Sub

Display More

For the most part you can work directly with objects rather than selecting them. YOu'll notice a lot of that code has been removed.

mrmmickle1 · Jun 11th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

If you want to reassign values like in your example:

Quote

"Newresourcealias"
test1
test2
test3
test4
test5

Display More

Use this:

Code

Sub Test()




    Dim lr As Long
    Dim LngLp As Long
    
    lr = Sheets("Table").Cells(Rows.Count, "A").End(xlUp).Row 'Define LastRow




    For LngLp = 2 To lr
        Sheets("Table").Cells(LngLp, "M") = "Test" & LngLp - 1
    Next LngLp
    
End Sub

Display More

mrmmickle1 · Jun 11th 2016

Re: search string & delete entire rows; then reassign the task - Bulk ticket reassign

This code will quickly delete items that meet your criteria:

Code

Sub Test()


Dim lr As Long
Dim ArrLp As Integer
Dim myDelArr As Variant


lr = Sheets("Table").Cells(Rows.Count, "A").End(xlUp).Row
MyDelArr = Array("apple", "orange", "pineapple")


For ArrLp = 0 To UBound(MyDelArr)
    With Sheets("Table").Range("A1:M" & lr)
        .AutoFilter 4, "=" & MyDelArr(ArrLp) 'Filter Column 4 for deletion based on Criteria
        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Filter for all delete matches
        .AutoFilter 'Unfilter
    End With
Next ArrLp


End Sub

Display More

I don't really understand what you mean in number 2....can you elaborate or show a before and after?

mrmmickle1 · Jun 11th 2016

Re: Error in finding a row number within a for loop

you need to use syntax like this:

Code

Dim SrchVal              As Range
 Dim myRow              As Long


 Set  SrchVal = assetAllocate.Sheets("Asset_Allocation").Range(assetAllocate.Sheets("Asset_Allocation").Cells(1, 1), assetAllocate.Sheets("Asset_Allocation").Cells(numAssetAllocateRows, 1)).Find(PortName, , xlValues, xlPart, xlByRows, xlNext, False)
 myRow = SrchVal.Row

mrmmickle1 · Jun 11th 2016

Re: TOPLEFTCELL used to activate seperate sheet

In this case let's use a named range...

Name the cell that contains your team name: "ShtSelect"

Your Code will now work using this syntax:

Code

Sub Test()




Dim mySht As String




mySht = Range("ShtSelect").Value
Sheets(mySht).Activate




End Sub

Display More

mrmmickle1 · Jun 11th 2016

Re: Selecting only certain cell range VBA

You may want to take a look at this example from Allen Wyatt... depending on how your workbook is designed this method may work for your needs:

http://excelribbon.tips.net/T010256_Stopping_the_Deletion_of_Cells.html

mrmmickle1 · Jun 11th 2016

Re: Watch cell?

B.W.B.,

Try this:

1.format cell B1 dd-hh:mm
2.Create a Custom Document Property named "PreviousTimeVal"
Go To File > Properties (In BackStage View) > Advanced Properties > Custom > Create Your Property (Store as the Number Type you can type in a number like today's date 42532) > Add

Now paste this code to your worksheet module:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
     
    Dim ws As Worksheet




    Set ws = Sheets("Sheet1")
    If Target.Address = "$A$1" Then
            ws.Range("B1") = Now() - ThisWorkbook.CustomDocumentProperties("PreviousTimeVal").Value
            ThisWorkbook.CustomDocumentProperties("PreviousTimeVal") = Now()
    End If




End Sub

Display More

Posts by mrmmickle1

vlookup from filename

Userform - Checkbox and Combobox

Run code when cell value changes

Run code when cell value changes

Listbox item row really long descrption - word wrap option not working even for txtbx

Delete rows with zero and #NUM!

search string & delete entire rows; then reassign the task - Bulk ticket reassignment

search string & delete entire rows; then reassign the task - Bulk ticket reassignment

search string & delete entire rows; then reassign the task - Bulk ticket reassignment

Allow user to choose which text file to import

Delete rows with zero and #NUM!

Listbox item row really long descrption - word wrap option not working even for txtbx

USB Port Communication

Copy paste specific cells from one sheet to a "data dump" with a command button

search string & delete entire rows; then reassign the task - Bulk ticket reassignment

search string & delete entire rows; then reassign the task - Bulk ticket reassignment

Error in finding a row number within a for loop

TOPLEFTCELL used to activate seperate sheet

Selecting only certain cell range VBA

Run code when cell value changes