Posts by mikerickson

**mikerickson** · Oct 23rd 2016

Re: How to filter through multiple “Arrays” at once?

Is this the same issue as
http://www.excelforum.com/exce…tiple-arrays-at-once.html

Did my response not help you?

**mikerickson** · Oct 19th 2016

Re: UnSelecting or DeSelecting a Range and or Cell using a Method or Function?

Quote

After an Excel Macro finishes does it always have to hide the selected cell in some way in order to give the worksheet a clean look?

I'm not sure what you mean by a "clean look" or why a "clean look" is important.

Some cell has to be selected at any time. "Nothing selected" is not possible in Excel.

**mikerickson** · Oct 6th 2016

Re: Loop through every combination in Array

Its not clear when you want to stop.

In the attached, it will loop through all 2^18 possibilities, I'm not sure what you want to do with the results.

**mikerickson** · Oct 4th 2016

Re: Name worksheet as today's date on open

Code

On Error Resume Next
If ThisWorkbook.Sheets("Sheet" & Date).Name <> ("Sheet" & Date) Then
    ThisWorkbook.Sheets.Add.Name = "Sheet" & Date
End If
On Error Goto 0

**mikerickson** · Sep 24th 2016

Re: use empty rows to seperate data

Assuming the Customer ID is in Column A

Code

For i = Cells(Rows.Count,1).End(xlup).Row To 2 Step - 1
    If Cells(i,1).Value <> Cells(i-1, 1).Value Then
        If Cells(i,1).Value <> vbNullString and Cells(i-1,1).Value <> vbNullString Then
            Rows(i).Insert 
        End If
    End If
Next i

**mikerickson** · Sep 17th 2016

Re: Possible to Autofilter records based on comparison of values between two columns?

If you have headers, you could use AdvancedFilter.

If your data is in columns A,B,C and D then leave column E blank, and cell F1 blank and put the formula =(B2<C2) in F2 (do not drag down).

Then invoke AdvancedFilter and use F1:F2 as the criteria range.

Note that to use Advanced filter, every column of the data range has to have a unique header and there can be no blank rows.
The rows above and below and the columns to the right and left of the data range have to be empty.

If one were to drag the formula down, you would want it to return TRUE for those rows displayed. But don't drag the formula down.

**mikerickson** · Sep 15th 2016

Re: Fill following pblanks with preceding cell text

Adjust the range to suit:

Code

Range("1:1").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-1]"

**mikerickson** · Sep 12th 2016

Re: Find part of a number to create a specific word

Try
=SUBSTITUE(A1, "W", "FQR")
or
="FQR"&(MID(A1,2,255)+0)

**mikerickson** · Sep 10th 2016

Re: Disable error checking via vba

One note, if this file is for some other user, they would be really mad if your code turned their ErrorChecking on (as opposed to their choice of Off).

Like all global settings, one should restore the initial settings rather than resetting them to MS defaults.

Code

Dim PriorErrorChecking As Boolean


Private Sub Workbook_Open()
    PriorErrorChecking = Application.ErrorCheckingOptions.BackgroundChecking
    Application.ErrorCheckingOptions.BackgroundChecking = False 
End Sub 


Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Application.ErrorCheckingOptions.BackgroundChecking = PriorErrorChecking
End Sub

Display More

**mikerickson** · Sep 10th 2016