=Find formula removed from Excel 2010?

F

finaljustice

Board Regular
Joined
Oct 6, 2010
Messages
175
Hello, i've upgraded to the 2010 business version of office and have the office 2007 business installed.

The formula =FIND() appears to have been removed, on the other hand if i use =SEARCH(), when the description box appears it suggests =FIND() for case sensitive.

Has indeed the =FIND() function been removed from 2010?
If not is there a way to make it appear since I cannot use that function any more on 2010.


On another note how would I code so that when I creat a new sheet, it automatically runs a macro (so I can configure to take the gridlines etc etc.)

I don't know how to code so that the action of the user to creat a newsheet CALLS my formating macro.



Thanks for your attention.
yours sincerely
Final
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
For the second question, in the ThisWorkbook module

Code: 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call MyMacro
End Sub
 
Upvote 0
VoG said:
For the second question, in the ThisWorkbook module

Rich (BB code): 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call MyMacro
End Sub
Click to expand...

Thanks for the reply, here is my macro. This is inside a class moduel in my in my PERSONAL.XLSB file.
Code: 
[SIZE=3][FONT=Calibri]Private Sub XLApp_NewWorkbook(ByVal Wb As Workbook)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.ScreenUpdating = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]ActiveWorkbook.Sheets.Select<o:p></o:p>[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]Columns("A:A").ColumnWidth = 0.6[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]Cells(1, 1).RowHeight = 6<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Cells.Select<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   With Selection<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .HorizontalAlignment = xlCenter<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .VerticalAlignment = xlBottom<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .WrapText = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .Orientation = 0<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .AddIndent = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .IndentLevel = 0<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .ShrinkToFit = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .ReadingOrder = xlContext<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .MergeCells = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   End With<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Selection.NumberFormat = "#,##0"<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]For i = 1 To Application.Worksheets.Count<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Sheets(i).Select<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Columns("A:A").ColumnWidth = 0.6<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Cells(1, 1).RowHeight = 6<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Cells(1, 1).Select<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]Next<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
<o:p></o:p>
[SIZE=3][FONT=Calibri]ActiveWorkbook.Sheets.Select<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]If ActiveWindow.DisplayGridlines = True Then ActiveWindow.DisplayGridlines = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.Sheets(1).Select<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]Application.CutCopyMode = False<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
[SIZE=3][FONT=Calibri]Application.ScreenUpdating = True<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]End Sub<o:p></o:p>[/FONT][/SIZE]

And this is in the Workbook inside the PERSONAL.XLSB file.
Code: 
Dim myApp As New clsApplication
 
Private Sub Workbook_Open()
    Application.OnKey "{F1}", ""
    Set myApp.XLApp = Application
End Sub
Sub AutoFill_ShortCut()
ExecuteExcel4Macro "data.series(,4)"
End Sub

Now my macro works perfectly when I open a new workbook, but what i would like is that for every sheet I add it automatically runs this to format the new sheet.
Code: 
[SIZE=3][FONT=Calibri]Columns("A:A").ColumnWidth = 0.6<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Cells(1, 1).RowHeight = 6<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri][SIZE=3][FONT=Calibri]Cells.Select<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   With Selection<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .HorizontalAlignment = xlCenter<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .VerticalAlignment = xlBottom<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .WrapText = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .Orientation = 0<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .AddIndent = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .IndentLevel = 0<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .ShrinkToFit = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .ReadingOrder = xlContext<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       .MergeCells = False<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   End With<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Selection.NumberFormat = "#,##0"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri][SIZE=3][FONT=Calibri]Cells(1, 1).Select[/FONT][/SIZE]
[/FONT][/SIZE][/FONT][/SIZE]<o:p>
</o:p>
<o:p></o:p>
<o:p>I still haven't been able to make it work any suggestion?</o:p>
 
Upvote 0
Try in the ThisWorkbook module

Code: 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Sh
    .Columns("A").ColumnWidth = 0.6
    .Rows(1).RowHeight = 6
   With .Cells
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlBottom
       .WrapText = False
       .Orientation = 0
       .AddIndent = False
       .IndentLevel = 0
       .ShrinkToFit = False
       .ReadingOrder = xlContext
       .MergeCells = False
       .NumberFormat = "#,##0"
   End With
End With
End Sub
 
Upvote 0
VoG said:
Try in the ThisWorkbook module

Code: 
Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Sh
    .Columns("A").ColumnWidth = 0.6
    .Rows(1).RowHeight = 6
   With .Cells
       .HorizontalAlignment = xlCenter
       .VerticalAlignment = xlBottom
       .WrapText = False
       .Orientation = 0
       .AddIndent = False
       .IndentLevel = 0
       .ShrinkToFit = False
       .ReadingOrder = xlContext
       .MergeCells = False
       .NumberFormat = "#,##0"
   End With
End With
End Sub
Click to expand...



Nope, not working still, when I press the new sheet button nothing happens, not even an error.
 
Upvote 0
It works for me. The code has to go in the ThisWorkbook module of the file that you want to apply it to. It won't work in Personal.xlsb.
 
Upvote 0
VoG said:
It works for me. The code has to go in the ThisWorkbook module of the file that you want to apply it to. It won't work in Personal.xlsb.
Click to expand...

Oh, ok, its works as you said, but it beats the purpose if I have to add this to every workbook i make. Is it even possible to make it work in the personal.xlsb so that it always happens
 
Upvote 0
Not as far as I know.

You could create a template file containing that code and save it as book.xltm in the XLSTART folder.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Excel/365 VBA crash issues recently
Replies
0
Views
516
picklefactory
P
A
  • Question Question
Purchase Order macros
Replies
5
Views
354
AndyB86
A
D
  • Solved
Show Tag of a Commandbutton in an Excel sheet (Vba Macro)
2
Replies
11
Views
719
Joe4
Joe4
C
  • Question Question
Removing Dups in same Cell
2
Replies
13
Views
633
chipsworld
C
J
  • Question Question
Excel Compiled VBA Corruption - Why Does It Happen?
Replies
2
Views
592
Fluff
Fluff

Forum statistics

Threads
1,224,612
Messages
6,179,890
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top