Find Replace-Offset

I just finished this yesterday and Im very pleased with it.

The purpose is to Find every instance of a word in an entire workbook, and replace a cell X spaces to the right.

The code looks like this:

Sub findreplaceoffset()
Dim Stext As String
Dim Oset As Integer
Dim Rtext As String
Dim CheckVal As String
Dim ws As Worksheet
Dim result As Range

Stext = Application.InputBox("Type in the word you want to find")
Oset = Application.InputBox("Type in the Column offset to be replaced")
Rtext = Application.InputBox("Type in what you want replaced")

For Each ws In Application.ThisWorkbook.Sheets
ws.Activate
Range("A1").Activate

Do While CheckVal <> Rtext

On Error GoTo Skip:
   Set result = Cells.Find(What:=Stext, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
       
        If result Is Nothing Then
        GoTo Skip:
        End If
       
        result.Activate   
   
        CheckVal = result.Offset(0, Oset).Value
       
        result.Offset(0, Oset).Value = Rtext   
       
Loop
CheckVal = ""

Skip:
Next ws
End Sub

In this sample the workbook contains quotations with items and amounts.

You can now download the sample sheet that contains the macro and follow along.

Find_Replace_Offset_.xlsm

Today lets suppose we need to change the price of all of the Apples in every sheet to $100

Run the macro and an input box request what word you want to find.

Type "Apples"

Another input box requests the offset

Type: "1" as the pricing we want to replace is 1 space to the right of Apples

The last inpub box is the replacement value

Type:"100"

Now every ammount 1 cell to the right of apples has been changed.

File Hosting Test

I have been trying to find a place to host the Excel files so I can make them available for download.
Sometimes just a blog with pictures and descriptions isnt enough.


Click the link below to download a spreadsheet with the Count Color macro from my previous Post
File Test (Count-Color - Excel Macro)

This hosting is from Google Sites.

Report view and data view Macro

This macro is for when you have a large spreadsheet that you maintain and update.

Usually there is a lot of data hidden when you present this as a report.

To complete your work you unhide everything, and make your edits, then go through the tedious process of hiding the data that doesnt need to be seen.

My solution is to have a pulldown in cell A1 to choose between Report view and Data view.

Start by adding a pulldown in A1.

Select A1, then click the data tab and choose data validation.

In the settings Tab, choose List, check the two boxes, and type in Data,Report in the source Input.

Now were ready to insert the code.

Right click the sheet dab and click view code.

Now paste the following into the text area.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Range("A1").Value = "Data" Then
        Range("B:C,E:H,K:N").EntireColumn.Hidden = False
    End If

    If Range("A1").Value = "Report" Then
        Range("B:C,E:H,K:N").EntireColumn.Hidden = False
    End If

End Sub

This will hide Columns, B,C,E,F,G,H,K, AND N.

Adjust this to fit your needs.

Now we can toggle the sheet from Data View to Report view at the click of a button.
Click A1 and choose Data, and everything is unhidden.

Now click A1 again and choose Report and the columns are hidden.

I hope this makes your day a little easier.

Engineering Stationing Custom Format

This is going to be a short but useful one for those who need to work with Stations.

To keep values as numbers and display them as Stations we will need to create a custom format.
Select the cells you want to display as stations, rightclick and choose format.
Under the Number Tab choose Custom at the bottom.

Input this"##+#0.00" as the Type:

Now our raw number of 8676.70 in N3 is displayed as 86+76.70 



Advance Filters

The best way to set up a sheet to use Advanced filters is to add a few rows above your data.

We will start with this data: Its the Invoice table from the famous Northwind Trading Company. (A Sample database provided by Microsoft)

And add a few rows at the top.

For this example we can add references in row 1 to mirror the tables headers in A4

Now we can add some criteria: For the names I like to just copy and paste data from the tables.

Copy both Andrew and Robert under the Salesperon header of the filter. Under Total I only want ones that are by Andrew AND greater than 100 OR by Robert AND greater than 200.

After adding the criteria we can apply the filter.

In the data tab, click advanced

The Advance filter window will pop up.

The list range, is the data in the table A5:I53

The criteria range is A1:I3 this will include the filter header and all rows below it that contain data.



Note: Be sure all of the rows you select in the filter data range contain data, or excel will include all results with no constraints. (It returns all of your data)

Click OK and now only the data were looking for is shown.



When you are finished reviewing the data or want to try another combination, press the clear button and try again.

Top Tip: When adding criteria 2 constraints in the same row will be calculated as AND, constraints in seperate rows will be calculated as OR. For our example above we are looking for invoices that were completed by Andrew AND were greater than 100, OR, by Robert AND greater than 200.





Calculating Filtered Values - The Subtotal

Excel filters are great, but wouldnt it be powerful to be able the to run calculations on the data as the filters change? The underutilized SUBTOTAL function should be added to your tool belt.

Syntax:
SUBTOTAL(Function,ref1,ref2,..)
There are seperate functions that will include or ignore Hidden values.
Note: Subtotal will automatically ignore any hidden data that is hidden becuase of the use of a filter.

Include Hidden Values	Function	Ignores Hidden Values
1	AVERAGE	101
2	COUNT	102
3	COUNTA	103
4	MAX	104
5	MIN	105
6	PRODUCT	106
7	STDEV	107
8	STDEVP	108
9	SUM	109
10	VAR	110
11	VARP	111

Lets look at an example:
The example below is counting the number of entries currently shown.

We have a list of fruit that we want to count.
In cell B2 the forumula =SUBTOTAL(103,C3:C35) will (CountA, the Range C3:C35), there are 33 records.

Note: CountA returns the number of non blank cells (Used to count cells with text or numbers)
          Count returns the number of cells that contain a number. (Used to count cells with numbers only)

If we add a filter to only show Apples and Oranges, this list shrinks.

The Subtotal is now 21, the calculation is ignoring the hidden data.

The next time your working with Filteres or hidden data, dont forget to make use of SUBTOTAL.

Counting Cell Background Colors

I have had several requests over the years for a function to count cells based on their background color.
Here is my solution. 2 Custom Functions.

If you dont know how to add a new function I will cover this in a later blog but for today we will skip that part.

The first function is CellColor and will help you identify the color index of a cell.

Function CellColor(CRange As Range)
 Dim cell As Range
        For Each cell In CRange
         CellColor = cell.Interior.ColorIndex
        Next cell
End Function

In the image above Cell B2 has the formula =Cellcolor(A2) which returns 38. Now we know what were looking for.

Now that we know the color of a cell we can use the next function CountColor to count the cells.

Function CountColor(CRange As Range, Color As Integer)
 Dim Ccount As Integer
 Dim cell As Range
        For Each cell In CRange
         If cell.Interior.ColorIndex = Color Then
         Ccount = Ccount + 1
         End If
        Next cell
        CountColor = Ccount  
End Function

 In the image above Cell B23 has the forumula =countcolor($A$2:$A$22,38) which returns 10.

Top Tip: Use them together and you don't have to remember that 38 is the index color of red.
=countcolor(A2:A22,CELLcolor(A2)) will return 10 as well.

First Post

Thanks for checking out my Excel blog. My goal here is to share the exciting world of Excel to everyone I can. If your looking for a solution to a problem send me a message and Ill be more than happy to help you out.