Simple: State an If Block more concisely

**Doug Nguyen** · 02-21-2014, 01:00 PM

Dear Excel Forum,

First, I would like to note that technically this is two questions within a single thread, but it is the same problem just two possible solutions. I hope that is acceptable.

I have some statements here and could not figure out a neat way to combine them. What would be an effective way of conveying the code below?

    If deleteStr = "" And timeB < 59 Then
        MsgBox "The data tables have been cleaned." & vbCrLf & vbCrLf & _
            "Total Time Taken: " & (timeB Mod 60) & _
            " second(s)", vbInformation, "Log Summary"
    End If

    If deleteStr = "" And timeB >= 60 Then
        MsgBox "The data tables have been cleaned." & vbCrLf & vbCrLf & _
            "Total Time Taken: " & Round((timeB / 60), 0) & " minute(s) and " & (timeB Mod 60) & _
            " second(s)", vbInformation, "Log Summary"
    End If

    If deleteStr <> "" And timeB < 59 Then
        MsgBox "The data tables have been cleaned." & vbCrLf & vbCrLf & _
            "Respondent answers matching '" & deleteStr & "' have been removed." & vbCrLf & vbCrLf & _
            "Total Time Taken: " & (timeB Mod 60) & _
            " second(s)", vbInformation, "Log Summary"
    End If

    If deleteStr <> "" And timeB >= 60 Then
        MsgBox "The data tables have been cleaned." & vbCrLf & vbCrLf & _
            "Respondent answers matching '" & deleteStr & "' have been removed." & vbCrLf & vbCrLf & _
            "Total Time Taken: " & Round((timeB / 60), 0) & " minute(s) and " & (timeB Mod 60) & _
            " second(s)", vbInformation, "Log Summary"
    End If

An alternative route would of course be: How do I fix the syntax for my MsgBox Timer so that it displays run time properly when run time is less than 60 seconds? What happens currently is for times between 30 and 59, it displays "1 minute(s) and x second(s)" instead of "0 minute(s) and x second(s)".

"Total Time Taken: " & Round((timeB / 60), 0) & " minute(s) and " & (timeB Mod 60) & " second(s)"

Best,
Doug

**Olly** · 02-21-2014, 01:12 PM

Try:

"Total Time Taken: " & format(timeB,"m "" Minutes and "" s "" Seconds""")

**Leith Ross** · 02-21-2014, 01:25 PM

Hello Doug Nguyen,

    Dim Msg         As String
    Dim TimeTaken   As String
    
        Msg = ""
    
        If timeB < 59 Then TimeTaken = (timeB Mod 60) & " second(s)"
        If timeB >= 60 Then TimeTaken = Round((timeB / 60), 0) & " minute(s) and " & (timeB Mod 60) & " second(s)"

        If deleteStr = "" Then
            Msg = "The data tables have been cleaned." & vbCrLf & vbCrLf & "Total Time Taken: "
        Else
            Msg = "The data tables have been cleaned." & vbCrLf & vbCrLf & _
                  "Respondent answers matching '" & deleteStr & "' have been removed." & vbCrLf & vbCrLf & _
                  "Total Time Taken: "
        End If
    
        If Msg <> "" Then MsgBox Msg , vbInformation, "Log Summary"

**Doug Nguyen** · 02-21-2014, 01:39 PM

Dear Leith Ross,

That is wonderful. I really liked how you approached the logic.

Dear OllyXLS,

I just tried your format option and it also worked great.

Thank you both for responding so quickly. Thread solved and +rep.

EDIT 1: Spoke too soon. I tried the format option again and I am still getting the rounding problem. (Leith Ross' logic is of course, unaffected and therefore still sound)

EDIT 2: For future reference, I amended Leith Ross' code and went with this.

Dim timeTaken As String

    If timeB < 59 Then
            timeTaken = (timeB Mod 60) & " second(s)"
        Else
            timeTaken = Round((timeB / 60), 0) & " minute(s) and " & (timeB Mod 60) & " second(s)"
    End If

    If deleteStr = "" Then
            MsgBox "The data tables have been cleaned." & vbCrLf & vbCrLf & _
                "Total Time Taken: " & timeTaken, vbInformation, "Log Summary"
        Else
            MsgBox "The data tables have been cleaned." & vbCrLf & vbCrLf & _
                "Respondent answers matching '" & deleteStr & "' have been removed." & vbCrLf & vbCrLf & _
                "Total Time Taken: " & timeTaken, vbInformation, "Log Summary"
    End If

Simple: State an If Block more concisely

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Simple: State an If Block more concisely

Re: Simple: State an If Block more concisely

Re: Simple: State an If Block more concisely

Re: Simple: State an If Block more concisely

Thread Information

Users Browsing this Thread

Similar Threads

Is there a function that can color a block if that block is higher than another block?

[SOLVED] Can VB Save hidden/grouped columns state, unhide all, then restore columns to saved state

block chart has same colour for block, how to auto random the colours?

Simple IF Satement Road Block Please HELP

Simple if statement road block

Bookmarks

Bookmarks

Posting Permissions