Posts by mikerickson

Re: Sort the data with the same or nearly similar values in vba

I have no idea how you want column B to be sorted.

Re: Multiple Combo box in User form to open multiple workbooks

Any restrictions on which files? Like "all .xlsm in X folder and sub-folders?"

Re: Multiple Combo box in User form to open multiple workbooks

"Options for workbooks that can be opened."
Can you already get those file paths? and your problem is putting them into the combobox?

Or is your issue deeper and you are looking to read all the available files from your computer's DOS?

If the former, what code do you have that gets the filepaths?
If the latter, what computer are you using, what OS and what version of Excel?

Re: Sort the data with the same or nearly similar values in vba

How is the after-sort order of column B determined?

Re: Sort the data with the same or nearly similar values in vba

The formula that I posted will work for all of the examples provided.

Re: Sort the data with the same or nearly similar values in vba

It looks like if you put =RIGHT(A1,3) in B1 and drag down, then you will get the result you want.

Re: Copy Colors but not conditional formating

Quote from cytop;652900

Not that easy... colours applied by conditional formatting are not part of a cells' attribute - ....!

Depending on the version of Excel, one can use the DisplayFormat property of a cell

With Range("a1")
    If .DisplayFormat.Interior.Color = .Interior.Color Then
        MsgBox "CF condition not met (or non-existant)"
    Else
        MsgBox "CF condition met"
    End If
End With

Re: If Sheet Exists Function

I like to use

Function SheetExists(sheetName as String, optional WB as Workbook)
    If WB is Nothing Then Set WB = ActiveWorkbook
    On Error Resume Next
    SheetExists = (LCase(WB.Sheets(sheetName).Name) = LCase(sheetName))
    On Error Goto 0
End Function

Re: Leave cell Blank if no text in target cell

Alternatly

='1st Flight'!F8&""

Re: Copy Textbox Text To Clipboard

Take a look at this
http://blog.jonschneider.com/2…ser-defined-type-not.html

One quick/cheap way to enable the Forms library is to insert a userform, which will make the DataObject accesable even after the UF is removed.

Re: Copy Textbox Text To Clipboard

What happened if you copy pasted the code into the VBEditor and didn't use the drop-down?

Re: Display textbox if specific combobox values are selected

It might be easier if TextBox1 were used for the explanation in all cases. The combo box value selected would determine where the user response would be stored on the worksheet.

Re: Date Math "Mod" function

So you want something like =IF(MOD(A1,1)=0, 1, MOD(A1,1))

Re: SumIF Formula for Variable Length Spreadsheets

Try using the formula

ActiveCell.FormulaR1c1 =SUMIF(R1C:R[-3]C, """>1""", R1C2:R[-3]C2)

Re: Split Paragraph Content into Columns?

Could you attach a workbook with a representative sample of your input data and the desired result?

Re: Counting sheets based on sheet name

I think this will do what you want.

Sub test()
    MsgBox NextSheetName("sheet")
End Sub


Function NextSheetName(ByVal strPrefix As String) As String
    Dim tPrefix As String
    strPrefix = Split(strPrefix, "(")(0)
    NextSheetName = strPrefix & "(" & (HighestSheetNumber(strPrefix) + 1) & ")"
End Function


Function HighestSheetNumber(ByVal strPrefix As String) As Long
    Dim oneSheet As Worksheet
    Dim maxIndex As Long, cIndex As Long
    
    strPrefix = LCase(Split(strPrefix, "(")(0))
    
    For Each oneSheet In ThisWorkbook.Worksheets
        If LCase(oneSheet.Name) Like strPrefix & "*" Then
            cIndex = Val(Split(LCase(oneSheet.Name) & strPrefix & "(", strPrefix & "(")(1))
            If HighestSheetNumber < cIndex Then HighestSheetNumber = cIndex
        End If
    Next oneSheet
End Function

Re: VBA Find & Replace based on cell reference within a range

Here's my attempt

Sub test()
    Dim rngData As Range
    Dim arrData As Variant
    Dim rNum As Long, i As Long
    Dim arrRef As Variant, arrVal As Variant
  
    With Sheet1.Range("A:A")
        Set rngData = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
    End With
    arrData = rngData.Resize(, 4).Value
    
    With rngData
        For rNum = 1 To .Rows.Count
            arrRef = RefArray(CStr(arrData(rNum, 1)), .Cells)
            arrVal = ValArray(CStr(arrData(rNum, 1)), .Cells)
           
            For i = 1 To UBound(arrRef, 1)
                arrData(rNum, 4) = Replace(arrData(rNum, 4), arrRef(i, 1), arrVal(i, 1))
            Next i
            
            If arrData(rNum, 4) Like "*" & Chr(164) & "*" Then arrData(rNum, 4) = CVErr(xlErrNA)
        Next rNum
        .Resize(, 4).Value = arrData
    End With
End Sub


Function RefArray(refStr As String, ByVal rngRef As Range) As Variant
    Dim rngCode As Range, rngVal As Range
    Dim xStr As String
    Set rngRef = rngRef.Columns(1)
    Set rngCode = rngRef.Offset(0, 2)
    Set rngVal = rngRef.Offset(0, 1)
    xStr = "IF(" & rngRef.Address(, , , True) & "=""" & refStr & """,char(164) &" & rngCode.Address(, , , True) & "&char(164),char(5))"
    RefArray = Evaluate(xStr)
End Function


Function ValArray(refStr As String, ByVal rngRef As Range) As Variant
    Dim rngVal As Range
    Dim xStr As String
    Set rngRef = rngRef.Columns(1)
    Set rngVal = rngRef.Offset(0, 1)
    xStr = "IF(" & rngRef.Address(, , , True) & "=""" & refStr & """," & rngVal.Address(, , , True) & ",char(5))"
    ValArray = Evaluate(xStr)
End Function

Re: Reset running total when 0 occurs

In D2, put the formula
=IF(B2="UP",COUNTIF($B$2:$B2,"Down")-SUM($D1:$D$2),"")

Re: Round Up to certain decimal place, end in 5 or 9

This works on integer values in A1. The OP data should put INT(A1) where needed

=IF(MOD(A10,10)<=5, CEILING(A10+(MOD(A10,10)=0),5), CEILING(A10,10)-1)

Re: Round Up to certain decimal place, end in 5 or 9

No, the formula is at fault. Let me work on this one.