Free Microsoft Excel 2013 Quick Reference

AutoRecover not working?

Windows 7
Excel 2003

I have AutoRecover set to save my documents every 10 minutes.
The "Disable AutoRecover for this workbook" boxes are not checked.
The folder where Excel is allegedly saving my documents does not have my documents in it.

Please tell me there's a good reason to not throw my computer out the window right now.


Hi all,

for a project I need to fit a 2nd order polynomial (ax^2+bx+c) on a lot of data. From this fit I need to get the x,y that corresponds to its maximum.

Normally I did this with a graph, get the fit and was able to calculate the X and Y for the maximum. The project is getting bigger and bigger and I want to automate this task.

I found that I can use the LINEST function in excel but somehow it does not work for me.

The spreadsheet has been build so that on every row, there is data from 1 sample.

the formula I’m using and is not working (returning #value) :

=LINEST(AV16:AZ16;AQ16:AU16^{1,2})

the strange thing is that if I rearrange the data so that I can have I have my X and Y in a different column it does work (formula: =LINEST(AS19:AS23;AR19:AR23^{1,2})

now my question is, is there a way to get a 2nd order fit using excel formulas using the row data as an array instead of column data?

It is not possible to rearrange the data to columnar data in the sheet itself. The only solution if the LINEST function cannot work on rows is to use VBA and make it loop through the data put transpose my x, transpose my Y and store in temp in an array. Then do the LINEST function.. I'm not very good with VBA so I rather not start attacking this problem in this way.

Hope some of you have an idea how to tackle this problem!
tx!

Hi All

I'm trying to hide some sheets using the before close statement.


	VB:
	
) 
     
     
    Application.ScreenUpdating = False 
     
     
    Sheet3.Visible = False 
    Sheet4.Visible = False 
    Sheet13.Visible = False 
    Sheet14.Visible = False 
    Sheet15.Visible = False 
    Sheet16.Visible = False 
     ' Sheet17.Visible = False
    Sheet18.Visible = False 
    Sheet19.Visible = False 
    Sheet20.Visible = False 
     
     
     
     
    Sheet1.Activate 
     
    Sheet1.CheckBox1.Value = False 
    Sheet1.CheckBox2.Value = False 
    Sheet1.CheckBox3.Value = False 
    Sheet1.CheckBox4.Value = False 
     ' Sheet1.CheckBox5.Value = False
    Sheet1.CheckBox6.Value = False 
    Sheet1.CheckBox7.Value = False 
    Sheet1.CheckBox8.Value = False 
    Sheet1.CheckBox9.Value = False 
     
    Range("A1").Select 
    Application.SendKeys "^{HOME}" 
     
     
    Application.ScreenUpdating = True 
     
End Sub 
 
 
Private Sub Workbook_Open() 
    Sheet1.Activate 
    Range("A1").Select 
    Application.SendKeys "^{HOME}" 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But it is not working and i've included this code in ThisWorkbook only.

Can someone please let me know how to solve this?

Hi guys,

This is my first post here and I am pretty new to VBA and completely new to UDF. Today I built this function to be used in a code, but I don't understand why it is not working.
This function should return true if finds in the array the value (myvalue) and false otherwise.


	VB:
	
 
     
    For i = 1 To UBound(myarray) 
        If myvalue = myarray(i) Then 
            ismember = True 
        Else 
            ismember = False 
        End If 
    Next 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
then I called the function

	VB:
	
 Equity(3) 
 
Equity(0) = "Common Stock": Equity(1) = "Mutual Fund": Equity(2) = "Preference": Equity(3) = "Depositary Receipt" 
X=Ismember(Equity,typ) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
typ is a string that takes the value from a cell.
The function returns false also when it should be true.
I managed to achive my goal using match, but I would like to understand why it is not working so I can learn a little more about UDF

Thanks

During some progressive migration of VBA projects from older 2003 versions of Excel and Office in general, the code below seems to work ok in VBA 2003,
but does not work in Excel 2007
The name of the worksheet was Sheet1 in 2003, but the only chnage I done was rename the new workbooks worksheet.
It is simply supposed to add a number sequence in Column A if any of the cells have values in them in Column B
The total rows are not always the same, sometimes there might be up 150 rows of data, other times as low as 7.
So it would end up looking something like:

Column A-----Column B
RANK-------- TITLE
1-------------"info"
2-------------"etc"
3
4
5
6
7
-------------------------
or other times
RANK
1
2
3
--infinately ( average upto 150, ~)
--------------------------------

	VB:
	
Sub RANK_NO() 
     
    Sheets("SCRAP").Range("A1").Select 
    For i = 1 To LastRow 
        Worksheets("SCRAP").Cells(i, 1).Value = i - 1 
        Worksheets("SCRAP").Range("A1").FormulaR1C1 = "RANK" 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Dear All,

I need help with my VBA code which is not working. I am trying to use the following VBA code to sort my database based on a particular column – i.e. column B:


	VB:
	
 Range) 
    If Not Intersect(Target(1, 1), Range("A:L")) Is Nothing Then 
        Range("A:L").Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

My database has 12 columns (A to L). The first 5 rows take up the headings – with all the headings formatted the way we require them. This means that – for some of the row headings, some of the cells are merged; the heading cells are not all equal in size. However, from Row 6 – the row from which we start entering data, all the cells are of the same size.

We use Excel User form to enter data onto the database. This is working OK.

The problem is with the sorting of the database based on column B (which contains Names of members). The first data entry row is Row 6 and the cell containing the first name is B6. In order to run the sort code, I right-clicked the sheet name (at the VBA editor) and pasted the code. But it does not seem to work. At some point, it comes up with an error message as follows:

“This operation requires the merged cells to be identically sized)."

I need help on what I should do to get the code to work. We require the database heading rows (rows 1 to 5) to remain the way they are – formatted. Please is there anything I can do so that upon clicking the ‘Add Data’ command button on the user form, the data will be added to the database and the sort code will run automatically and sort the database based on column B (not minding the fact that the cells making up the headings in rows 1 to 5 are not identically sized.

I have attached an abridged copy of the database (zip file)

Thanks for your anticipated help.

Buddy

Hi All,

This is my first post to this forum, but the answers to many of my excel questions I have been able to find on these threads.

I have a workbook right now that is trying to automate a monthly process. The formula in question is of the format:

=IF(COUNTIF('DMR - All Data'!A$2:A$2000;">"&(EOMONTH(A4;0)))>=1;C4/B4;NA())

Currently, 'DMR - All Data'!A$2:A$2000 references a range of dates, A4 represents the first of the month, C4 and B4 are non-zero values.

For instance, in 'DMR - All Data' I have data from January 2011 to March 2011. However, I am instructing the formula to only put in March values when the first April 2011 date shows up. On my computer, if I test this with 1-Apr-2011, the formula works fine; however, when I transfer this workbook to a colleague's computer and test the same way, it will not work. When they put in 1-Apr-2011, all the values will show up as NA. Furthermore (after closing the program without saving and reopening), if I simply click in the formula bar and press enter (without changing anything), the values will go to NA.

Again, these only happen on a colleague's computer (and I have tried it on 5-6 different computers), and each time NA pops up. When I try it on mine, it works fine.

Is there some sort of data lock that is preventing the calculation from working? Does anybody have any idea what's going on here?

Hi
I have two columns set up with drop box list from data validation. One is subdependent on first one using indirect(substitute function). I am trying to use sumproduct function to count number of occurence such as fire and fire alarm. Sumproduct function will not work for some reason with drop box input values but instead if I type Fire and Fire Alarm into column 1 and 2, It will register? What is the problem? does excel treat drop down list values differently from manual input? Please Help, greatly appreciated, thanks.

I have a macro created that works fine. However, when I try to get it to run from an ActiveX button, it does not work at all.

Is there a certain mode or option I have to toggle to get it to work?

Macro is as follows:

	VB:
	
 Combine() 
     '
     ' Combine Macro
     '
     '
    Application.ScreenUpdating = False 
     
     'Clears current list
    Sheets("Sheet3").Select 
    Columns("J:J").Select 
    Selection.ClearContents 
     'Copy paste from other sheets
    Sheets("Sheet2").Select 
    Range("C2:C1500").Select 
    Selection.Copy 
    Sheets("Sheet3").Select 
    Range("J2").Select 
    ActiveSheet.Paste 
    Sheets("Sheet1").Select 
    Range("C2:C1500").Select 
    Application.CutCopyMode = False 
    Selection.Copy 
     'Selects first empty row
    Sheets("Sheet3").Select 
    Range("J2").Select 
    Do 
        If IsEmpty(ActiveCell) = False Then 
            ActiveCell.Offset(1, 0).Select 
        End If 
    Loop Until IsEmpty(ActiveCell) = True 
     
    ActiveSheet.Paste 
     
     
     'Removes duplicates and sorts column
    Columns("J:J").Select 
    Application.CutCopyMode = False 
    ActiveSheet.Range("$J$1:$J$3157").RemoveDuplicates Columns:=1, Header:=xlNo 
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear 
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("J1:J3157") _ 
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 
    With ActiveWorkbook.Worksheets("Sheet3").Sort 
        .SetRange Range("J1:J3157") 
        .Header = xlGuess 
        .MatchCase = False 
        .Orientation = xlTopToBottom 
        .SortMethod = xlPinYin 
        .Apply 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance,

I have attached the code where I am trying to select a user range thru a subroutine and use this generically to copy from one sheet and paste it to another sheet I am using pastespecial as I want to copy only the format and not the values. The code dos not work and gets stuck at highlighted line in the attachment.the message i get is Run time error 1004 . pasteSpecial method of range class failed Any suggestions to correct this error?


	VB:
	
 
Public UserRange As Range 
 
Sub SelUserRange() 
     'Selects Range based on user inpout
    Set UserRange = Application.InputBox(Prompt:="Please Select Range", Title:="Delete Blank Rows ", Type:=8) 
    Application.Goto UserRange 
     'Msgbox("UserRange is" & UserRange, vbOKOnly, "JRM", 8) As VbMsgBoxResult
End Sub 
 
Sub RangeCopyPaste() 
    Sheets("Sheet1").Select 
    SelUserRange 
    Application.CutCopyMode = False 
    Selection.Copy 
     
    Sheets("COGNI").Activate 
    SelUserRange 
    UserRange.Activate 
    UserRange.PasteSpecial xlPasteFormats 
    Application.CutCopyMode = False 
    Exit Sub 
     
Canceled: 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The crux of the problem
First sub selects a range to be copied in Sjheet1 as specified by the userSecond subroutine activates a sheet “COGNI”: and tried to paste into a location specified by user thru input boxThe macro is stuck at highlighted point

I'll try to explain the best I can.


	VB:
	
 Hyperlink) 
    If Target.Address = "/public" Then 
        Range("$C$16") = Range("C16") + 1 
    End If 
    If Target.Address = "/private" Then 
        Range("$F$16") = Range("F16") + 1 
    End If 
    If Target.Address = "/private" Then 
        Range("$I$16") = Range("I16") + 1 
    End If 
    If Target.Address = "/public" Then 
        Range("$L$16") = Range("L16") + 1 
    End If 
    If Target.Address = "/public" Then 
        Range("$R$16") = Range("R16") + 1 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code works fine but here is the thing:

Im trying to create a click counter to track what hyperlinks are clicked in my excel document.
If my excel document is located on the "public" drive, the only click counters that work are the ones located on the "private" drives.
For example: If my excel document containing the code above is located on the "public" drive the only IF statements that work are 2nd and 3rd.

If I copy the document and place it on the "Private" drive the only IF statements that work are the 1st, 4th, and 5th.

Creating a shortcut to files on the "public" drive and placing them on the "private" drive, (and/or Vica Versa), and hyperlinking to them does not work because the document thinks the shortcut may be unsafe.

I have macros referencing a group of merged cells that contain the hyperlink. The macros are placed on a image/picture.

So basically whats happening is:

Someone clicks a picture that has a macro on it, that macro references a group of cells that contain a hyperlink. Once clicked the counter increases by one.

Im just wondering why the click counters only work on some of the hyperlinks, depending on what drive my excel file is located on. If I flip the drives, the ones that previously didnt work, now work, and the ones that previsouly worked, no longer work.

I hope that makes sense.

Let me know if you need any clarification.

Thanks

Hi All,

I need help with vba codes that are not working properly. I have three worksheets in a workbook namely:

Menu
Balance Sheet
Income & Expenditure

On the ‘Menu’ sheet I created a textbox button each for the other two sheets including one button for ‘Exit’:

My aim is this: When the ‘Main Menu’ sheet is showing and I click on the button for e.g. ‘Balance Sheet’, it would take me to the ‘Balance Sheet’ sheet and hide the other sheets (i.e. the ‘Menu’ and the ‘Income & Expenditure’ sheets.

On the ‘Balance Sheet’ sheet, I have a button for ‘Menu’, and when I click the button, it should take me back to the ‘Menu’ sheet and hide the other sheet including the ‘Balance Sheet’ Sheet itself.

On the Menu sheet, I created textbox buttons and assigned macros to take me to the those other sheets. And on each of those other sheets I created a ‘Menu’ button to take me back to the Menu. For e.g. on the ‘Balance Sheet’ sheet, I placed the following codes:


	VB:
	
 TextBox1_Click() 
    With Sheets("Balance Sheet") 
        .Visible = True 
        .Activate 
    End With 
    Me.Visible = True 
End Sub 
 
 
Sub Button27_Click() 
    With Sheets("Menu") 
        .Visible = True 
        .Activate 
    End With 
     
    Me.Visible = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The first code takes me from the Menu to the Balance Sheet – hiding the Income & Expenditure Sheet but it DOES NOT HIDE the Menu sheet itself. However, the second code takes me from the ‘Balance Sheet’ back to the Menu. In this case it succeeds in hiding itself (the Balance Sheet ) and also the Income & Expenditure Sheet.

I also placed two such codes on the ‘Income & Expenditure’ Sheet and I get the same result.

My problem here is that I am unable to figure out the appropriate code to place in the Menu sheet or to attach to any of those other codes to achieve the ‘hiding’ of the Menu sheet. For e.g. When I click on the button for ‘Balance Sheet’ it should take me to the Balance Sheet while hiding both the Menu sheet and the Income & Expenditure sheet. The second part of it is OK i.e. when I am on the Balance Sheet and click on the Menu button, it takes me back to the Menu and successfully hides the other sheets.

Can someone help me please.

PS: It is possible I might have messed things up a bit by creating the Menu buttons using textbox buttons and the sheet buttons using command buttons. It is too late for me to scrap them and start all over. They work partially. All that I want is a tweaking of the codes so that when I go from the Menu, that particular sheet should be hidden along with the other sheet that I am not going to.

Thanks.

Buddy

Hello,

I have a userform that does the following code when a button is clicked:


	VB:
	
 
Me.Enabled = False 
Module1.SubStart 
 
If Input1.text   vbNullString Then 
    GLBL_URL1 = Module1.Sub1(Input1.text) 
    GLBL_URL2 = Module1.Sub2(Input1.text) 
    Input1.Enabled = False 
    Input1.BackColor = &H8000000F 
ElseIf Input2.text  vbNullString Then 
    GLBL_URL1 = Module1.Sub1(Input2.text) 
    GLBL_URL2 = Module1.Sub2(Input2.text) 
    Input2.Enabled = False 
    Input2.BackColor = &H8000000F 
Else 
    MsgBox "Error!" 
End If 
 
Txtbox1.Caption = Module1.Function1(GLBL_URL2) 
Txtbox2.Caption = Module1.Function2(GLBL_URL2) 
Button1.Caption = "Reset Ratings" 
 
Module1.SubEnd 
Me.Enabled = True '

Hi,Is there a limitation to the Dfunctions that makes it not work with Tables in Excel 2010?Here is a link to a sample I created to test the DCOUNTA function.https://skydrive.live.com/redir.aspx...752C4B76!110It works fine except when I use the table structure.I found no record for that issue...Thank you ,Tamir

I've to iterate through each cell in "Column C" in downward direction.

I've to fetch values from the corresponding cells for which I'm using vLookup. I need to fetch multiple values so I would be using more vLookups in the loop after I get this working.

All these fetched values would be clubbed and based on conditions counters ,like one of them being "A" would be updated and summed at last to find the sum of each counter variables.

Here I'm trying to do is use loop for application "VLookup", but for some reasons it is not working.

I've tried using
..VLookup( Range("C" & i), ...
..VLookup("C" & i, ...
..VLookup(Cells(i, "C"), ...

i is the counter and C is the column. Could you please help me with it? I've been searching for help from past 8 hours. Finally I had to paste. I'll attach the file too.


	VB:
	
 CalculateApril() 
     
    Dim i As Integer, A 
    A = 0 
    Dim isDecomm 
    Dim myRange As Variant, mySelectedArea 
     
    mySelectedArea = ThisWorkbook.Worksheets("MSL").Range("C2:G22") 
     
    For i = 2 To LastCellInColumn - 1 
        With ActiveSheet 
             ' Define Range
            myRange = "C" & i 
             '   Issued in April. Checks for the Month
            If Range(myRange).Value = 4 Then 
                A = A + 1 
                 '   Are they Decommissioned ? Yes = Ignore, No - Count
                isDecomm = Application.VLookup(Cells(i, "C").Value, Range("C2:G22"), 5, False) 
                Debug.Print Cells(i, "C").Value 
                 'MsgBox isDecomm
            End If 
        End With 
         '   Increment i/Row
    Next i 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I use a macro in Excel 03 that will not work in Excel 10, however I have made some progress.

	VB:
	
 
Application.Goto Reference:="R2503C2" 
ActiveCell.NoteText Text:="temp", Start:=1 
Range("A45:N2503").Select 
Selection.Sort Key1:=Range("A45"), Order1:=xlAscending, Header:= _ 
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _ 
xlTopToBottom 
Selection.SpecialCells(xlNotes).Select 
Application.Wait (Now() + TimeValue("00.00:01")) 
Application.SendKeys "%eg" 
Application.Wait (Now() + TimeValue("00.00:01")) 
Application.SendKeys "n41" 
Application.Wait (Now() + TimeValue("00.00:01")) 
Application.SendKeys "+{ENTER}" 
Application.Wait (Now() + TimeValue("00.00:01")) 
Application.SendKeys "%prs" 
End Sub 
 'Application.SendKeys "%fp"
 'Application.SendKeys "%p"

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The above code works and the last line sets the print area but after that %fp and %p
works when I use the keyboard but not in the macro.

Hello all:

I have numbers (or am supposed to) in my spreadsheet. My excel spreadsheet is acting weird. I am not able to sum columns are rows accurately.

I have tried the text to columns, and that did not work either.

e.g. H5: 3900
I5:
J5:
Sum

I am getting a blank instead of 3900.

I also tried inserting a column by column G and then I inserted this formula: G5*1, and dragged the formula down. The multiplication is giving me the same valude in H5, in H6 and for all of column H, whereas, the values in G5 and G6 and the rest of the rows are different. I have used this spreadsheet before with the formulas in it and didn't have a problem, so I don't know what happened. Please help.

Hi, I've been trying to find a way for my workbook to do the following:


	VB:
	
 Worksheet_Activate() 
    Sheet2.Unprotect(PasswordString) 
     '... Populate cells
     '... Run functions (which creates workbooks and worksheets, copies some sheets from thisworkbook over to the new
workbook)
     '... Some of these functions are in the code module (Module1) and not in the sheet - if that makes a difference
     '... Funny thing is that this code section still runs, it's just the Unprotect and the Protect functions that don't
work!
    Sheet2.Protect(PasswordString) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is done through the Worksheet_Activate sub as I want this to be done each time the sheet is activated.

The problem is that sometimes this doesn't fire properly, it works just fine if I manually create a new sheet and then activate the sheet in question again but not through the functions.

Well the Worksheet_Activate sub does fire but specifically the Sheet(x).Unprotect/Protect functions do not trigger for some reason.

After doing some research, there seems to be something to do with the Application.EnableEvents option. I've tried changing my code to:


	VB:
	
 Worksheet_Activate() 
    Application.EnableEvents = False 
    Sheet2.Unprotect(PasswordString) 
     '... Populate cells
     '... Run functions (which creates workbooks and worksheets, copies some sheets from thisworkbook over to the new
workbook)
    Sheet2.Protect(PasswordString) 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
But that still does not work.

Can somebody please explain to me what I am doing wrong?

Thank you very much.

I have the following code that I swear worked a week ago and now for some reason it is not working. It seems to only work for the first cell in the range but not the rest of them. The target cells are selected from a drop down. What I am looking to do is unhide the two rows below each target cell when I choose "custom" and hide them when it is anything other than "custom" Any help is appreciated.


	VB:
	
 Range) 
     
    Application.ScreenUpdating = False 
     
     
    Dim cell As Range 
    For Each cell In Range("E18, E29, E40, E51, E62, E73, E84, E95, E106, E117, E128, E139, E150, E161, E172, E183, E194,
E205, E216, E227") 
        If cell.Value = "Custom" Then 
            cell.Offset(1, 0).Resize(2, 1).EntireRow.Hidden = False 
        Else 
            cell.Offset(1, 0).Resize(2, 1).EntireRow.Hidden = True 
        End If 
         
        Exit For 
         
    Next 
     
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


auto sum feature...in my month to date workbook
A:1 contains the following formula:
=SUMIF('C:[RAW_5_18_2011.xls]Sheet1'!A$1:A$100,"="&"myteam*",'C:[RAW_5_18_2011.xls]Sheet1'!C$1:C$100) which returns a real number an example would be 75 so 75 shows up in A:1 on the "month to date workbook sheet1"
RAW_5_18_2011.xls is a daily data dump, I would like to pre populate the remaining cells in the"month to date workbook sheet1" A:2 through A:30 as an example: (=SUMIF('C:[RAW_5_19_2011.xls]Sheet1'!A$1:A$100,"="&"myteam*",'C:[RAW_5_19_2011.xls]Sheet1'!C$1:C$100) and auto sum say in A:31, but since the daily data dumps have not yet been generated the Auto sum in A:31 is not working since there is reference/ value errors due to the daily data dump files not yet being created. Any ideas would be appreciated.

Very amateur programmer here. I'm writing a function to condense a large rowing race schedule linked from the organizer's website to a more usable format. The function does a lot of stuff (I should probably break it into smaller chunks), most of which it does correctly. However, in the event that a race entry is listed as "scratched", the function needs to take the word "scratch" off the entry and add a strikethrough effect to the text instead. For some reason, the text never changes to strikethrough. Strangely, I added a color change to red as a test, and that works fine.
Incidentally, there is no conditional formatting being applied to the cell.

	VB:
	
) 
    Dim homeCell As Range 
    Dim offsetRow As Integer 
    Dim offsetCol As Integer 
    Dim schedStartRow As Integer 
    Dim schedStartCol As Integer 
    Dim trueSchedStart As Range 
    Dim teamCell As Range 
    Dim teamName As String 
    Dim teamNum As String 
    Dim isScratch As Boolean 
    isScratch = False 
    Dim scratchStart As Long 
    Dim entryNum As Double 
    Dim countTeam As Double 
    Dim scrEnd As String 
     
    On Error Goto FuncEnd 
    Set homeCell = Application.thisCell 
    With schedStartRef 'find the start of the schedule
        Set trueSchedStart = .Columns(1).Find(what:=schedStartInit, after:=.Cells(1, 1), _ 
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
        SearchDirection:=xlNext, MatchCase:=False) 
    End With 
    offsetRow = ((homeCell.Row - 2) * genOffset) + schedOffset + 1 'find target team name using offsets provided
    offsetCol = homeCell.Column - 7 
    teamName = trueSchedStart.Offset(offsetRow, offsetCol).Value 
    If Len(teamName) = 0 Then 'if target cell is blank, leave the cell blank and end
        teamName = vbNullString 
        Goto FuncEnd 
    End If 
    scratchStart = InStr(teamName, teamScratch) 'find if entry is scratched
    If scratchStart > 0 Then 
        isScratch = True 
        teamName = Left(teamName, Len(teamName) - Len(teamScratch)) 'take scratch text out of name
        scrEnd = scrFlag 'add scratch flag at end of name if user provides
        With homeCell 
            .Font.strikethrough = True 'strikethrough text, does not work!
            .Font.ColorIndex = 3 'at least I can make it red
        End With 
    Else 
        scrFlag = vbNullString 'not a scratched entry
        With homeCell 
            .Font.strikethrough = False 'no strikethrough
            .Font.ColorIndex = 1 'make text black
        End With 
    End If 
    If Val(Right(teamName, 1)) > 1 Then 'is it a second or third entry?
        teamNum = Right(teamName, 2) 'put entry number in memory
        teamName = Left(teamName, Len(teamName) - 2) 'trim text to just team name
    Else 
        teamNum = vbNullString 'no second entry
    End If 
    countTeam = Application.WorksheetFunction.CountIf(TeamNames, teamName) 'is this team name in the list?
    If countTeam > 0 Then 
        With TeamNames 'find the short version of team name.  Phew, much smaller!
            teamName = .Columns(1).Find(what:=teamName, after:=.Cells(1, 1), _ 
            LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _ 
            SearchDirection:=xlNext, MatchCase:=False).Offset(0, 1).Value 
        End With 
    End If 
FuncEnd: 'um, the end of the function
    If trueSchedStart = vbNullString Then 
        MKSCHED = "No Data" 'in case the schedule has not been posted
    Else 
        MKSCHED = teamName & teamNum & scrFlag 'add the short team name, entry number, and scratch flag
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Apologies about the length, I wonder if there is something else in the code screwing it up, and my inexperience leads to code which is probably three times longer than necessary.


	VB:
	
 
    MsgBox ("Only letters allowed in field.") 
    addFirstName = Left(addFirstName, Len(addFirstName) - 1) 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This works exactly how I want it to. As soon as the user inputs a number, the error message pops up and it deletes that number


	VB:
	
 
    MsgBox ("Only numbers allowed in field.") 
    addID = Left(addID, Len(addID) - 1) 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This is not working. When I type in a letter to the texbox it gives me the error message, deletes the letter, give me the same error message again, then crashes.

Thoughts? Thank you!

Excel 2007 zero suppress not working

I have the following line in an Excel 2003 macro.  When I upgrade to Excel 2007 it does not work.

  Application.ExecuteExcel4Macro "ZeroSuppress()"

Any ideas?

Hi everyone,

I am very new to VBA coding, and am trying to finish up an assignment based on displaying bond durations.

I am trying to write a procedure which sets the couponrate to 0 if the user did not enter anything(Optional), and sets the couponrate to the value the user has entered in the inputbox. However, my codes are not working!!

If I enter, say a 8, it sets couponrate to 8. But if I do not enter anything, it just loops through, and wont set couponrate to the default 0. The IsMissing function doesnt work!!

Please help!!
Thanks in advance!

	VB:
	
 getcoupon() 
    Dim couponrate, c As Variant 
    Dim test As Boolean 
     
     
     
     'Get the coupon rate of the bond
     
    couponrate = Application.InputBox("Please enter the coupon rate of the bond in its per annual percentage term, e.g enter
8 if the coupon rate is 8%", _ 
    "Coupon Rate of the bond", , , , , 1) 
     
    c = DetermineCouponRate(couponrate) 
     
    Debug.Print c 
     
     
End Sub 
 
 
 
Function DetermineCouponRate(Optional coupon As Variant) As Variant 
     
    Dim testt As Boolean 
     
     
     
    If Not IsMissing(coupon) Then 
        Do 
            If coupon >= 0 Then 
                testt = True 
                DetermineCouponRate = coupon 
                Debug.Print coupon 
            ElseIf coupon < 0 Then 
                MsgBox "Couponrate needs to be positive", vbCritical, "warning" 
                testt = False 
            End If 
        Loop Until testt 
    Else 
        DetermineCouponRate = 0 
        testt = True 
        Debug.Print coupon 
    End If 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hellay!

In m y workbook I have code that shall autoreolace certain strings when input to cells (found it on this excellent forum, thanks!):


	VB:
	
 
 
 ' Put code in worksheet!
Private Sub Worksheet_Change(ByVal changedCells As Range) 
    If Not Application.Intersect(changedCells, Range("B1:C30")) Is Nothing Then 
        AutoReplace changedCells 
    End If 
End Sub 
 
Sub AutoReplace(target As Range) 
    Dim cell As Range 
    Dim cellVal As String 
     
    For Each cell In target 
        Select Case UCase(Trim(cell.Value)) 
             
        Case "greyt" 
            cellVal = "Great" 
             
        Case "Lock" 
            cellVal = "Locked" 
             
        Case "Horse" 
            cellVal = "Arab" 
             
        Case "Tommy" 
            cellVal = "T. Brown" 
             
        Case Else 
            cellVal = cell.Value 
             
        End Select 
        Application.EnableEvents = False 
        cell.Value = cellVal 
        Application.EnableEvents = True 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Works great except when I am importing .txt files (Which I do all the time...) .

When I am importing .txt files into the B and C column the autoreplace code does not work; "Tommy" remains "Tommy etc. When I enter "Tommy" again though, in the same cell as the imported "Tommy" it will be replaced by "T. Brown"...

Is there a way to get the autoreplace code work directly when importing the text file, otherwise it seems quite meaningless, since I have to replace my entries manually.

Cheers!

/Horpe