Unable To Record Error, Long Formula Macro
Nov 25, 2008
I am working on using an excel workbook as part of a roll playing game and I am trying to record a macro and then put that in a command button which uses the randbetween, multiple if statements and multiple vlookup functions. I have successfully done similar things by just recording a macro and then pasting that into the VB editor. However this time I get a unable to record macro after putting the formula in the selected cell. I have tried copy and paste and just typing the formula with the same results.
Upon executing the formula I then want it to copy and paste special- values the result.
I'm assuming I have to put the VB code in manually but when I tried to do the formula I got a syntax error.
The worksheet I am working on is named new and I've included the formula in the a text box. I would like the result to show in J12 and be triggered by pressing the cmnbutton in k12.
View 9 Replies
ADVERTISEMENT
Sep 1, 2009
This problem seemed to revolve around "digital signing" with further macro changes done from a different machine (without the proper certificate). Excel warns of the problem and then "unsigns" the project.
I've got a problem with spreadsheets that I've been working on. Now, when I tell it to record a macro, I get the macro name, shortcut assignment dialog box and then an error "Unable to record." After "Ok"ing that dialog box, the normal recording macro dialog comes up. Also, lo and behold, there will not be a macro recorded.
I've got no protected cells or sheets and it doesn't matter what security level is set. It is a "signed" macro but I can take off the signature and still have the problem. I checked for "Help - About" for deactivated modules and there was none.
View 2 Replies
View Related
Jan 6, 2007
Im trying to record a macro that will enter a formula in a cell each time I run the macro
Drop the Lowest2:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10)
Drop Lowest1:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10)
I know I can write the formula in a simpler way, but I was asked to make it like this.
The problem is that when I enter the formula in the cell while recording. I got a message: "Unable to record" so this part can't be recorded
View 14 Replies
View Related
Aug 18, 2009
I need to write a formula but excel shows an error message "Formula Too Long." There are two long strings of the formula that I repeat several times. Is there any way to put these strings in other cells and reference them within the formula? Each of the strings looks very similar to this:
View 9 Replies
View Related
May 15, 2007
I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.
View 5 Replies
View Related
Jul 9, 2009
I have worked on this really difficult long formula, but the problem is, I have been working from my Mac. And my office, which this formula was made for, is all PC. Now generally this isn't a problem, but apparently PC's have a limit on how long the formula can be. Is there anyway to overcome this? I really need this formula to work..
View 9 Replies
View Related
Mar 20, 2009
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
View 13 Replies
View Related
Mar 4, 2014
I'm working on a spreadsheet that i have a long if statement and it keeps till me i have a error. I stated reading and come to find out you can only have 7 statement.
=IF($R$6="X",VLOOKUP($A25,VISION,2,FALSE),IF($R$7="X",VLOOKUP($A25,VISION,3,FALSE),IF($R$9="X",[code]....
View 14 Replies
View Related
Nov 13, 2013
Code:
For Each src In Worksheets
If src.Name Like "EF Spend*" Then
Sheets(src.Name).Select
Else
Next src
End If
This is an extract of a larger code, but this section is producing a Compile Error: Next without For but I cannot workout how to resolve it.
View 9 Replies
View Related
May 11, 2014
I am new to VBA. I have been getting compile error on the below code:
Public Sub hourCalculation()
Dim rcount As Integer
Dim rindex As Integer
Dim logintime As Date
Dim logoutime As Date
[Code] ..........
View 9 Replies
View Related
Oct 4, 2007
I have the following code written:
If InStr( Cells(i, 3).Value, "Other") > 0 Then _
Cells(i, 3).Replace What:="Other", Replacement:=Cells(i, 4).Value
This seems to work fine for the most part. However, if the value in Cells(i, 4) is too long, I seem to get a Run-time error '13': Type mismatch. Is there any way to rework this code so it can replace even if the string in Cells(i, 4).Value is too long?
View 2 Replies
View Related
Sep 4, 2013
Error in data validation: "A named range you specified cannot be found." However, the named ranges are in the Name Manager, and if I go to the sheet and select the range, I can see it is already named.
Full description:
I created a spreadsheet model (no VBA, just formulas, etc) in a workbook that another coworker took and added VBA to. However, he was not working with the most updated version. So last night I checked the sheets which had the same names, made sure there were no differences except the changes I had made since that version, and then moved the ones from my current workbook into his workbook.
On the sheet where the user was to input details of the report, there are 4 cells that each contain different lists (non-dependent). I want the user to only be able to choose one of the 4 drop down menus - that is, if any of the 4 cells are filled in, I would like the other 3 to default to N/A (or somehow not be allowed to be chosen). This was solved in a previous thread:
Using A1: =IF(COUNTA(A2:A4),NA(),G2:G6) from the other thread solution, I used a named range instead of G2:G6 and it worked well.
When I merged the files together, the 4 cells no longer worked at all. It was suggested that I look at the Name Manager, and some of te named ranges I had were in there multiple times. I cleaned them up.
I tried to enter the same formula again, and it gave me the specific error: "A named range you specified cannot be found." However, the named ranges are in the Name Manager, and if I go to the sheet and select the range, I can see it is already named.
I am unable to load the spreadsheet due to sensitive data, and removing the data would make the workbook make no sense.
View 6 Replies
View Related
Mar 30, 2014
I am executing code in a workbook that has been working successfully previously.
Suddenly I get a run time 1004 error in any macro in the workbook that tries to execute a activeworkbook.save command?
View 1 Replies
View Related
Oct 27, 2011
I have a sub that I have copied out of a previous workbook where it still works fine. In the previous workbook it was assigned to a button. Now I am trying just to do it as an automatic action when the workbook is closed
It will lock any cell within the specified range that is not blank then protect the worksheet, save the workbook then save a dated archive copy
When I pasted it in to a new workbook I changed the range to A1:AP49 and strFileName
Now when I run it now I get an Error 1004 - Unable to set the Locked property of the Range class.
On Debug this line is highlighted:-
c.Locked = c.Value ""
The full code is:-
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' Lock_cells Macro
' Macro compiled 10/26/2011 by G CARNCROSS
' Locks used cells, saves the workbook then saves a password protected copy with the days date in the file name then closes the workbook
'
'
ActiveSheet.Unprotect Password:="SHES"[code].....
View 2 Replies
View Related
Jul 15, 2014
I'm trying to tweak this code from a previous form I created but I'm getting a compile error message. On my old form...the first box was a combo list box where the person entering data would select a value. On the new form, its a text box where the person will enter the value. I thought I could just change the me.cbo[name].listindex to me.tbo[name].listindex - but that seems to be causing the issue. I'm not sure what I would put after the me.tbo[name]. to get the code to run...
Code:
Private Sub cmdAdd_Click()Dim lRow As LongDim lPart As LongDim ws As WorksheetSet ws = Worksheets("DataNEW")'find first empty row in databaselRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).RowlPart = Me.tboProdCode.ListIndex
View 8 Replies
View Related
Jul 10, 2014
I use Excel 2010 andexample.xlsx cannot make my code working because of the so famous errore above.
I have several pivot tables (pt) in different worksheets (ws), and a list of items stored in an array I created. I've written a macro for setting ON all items in pt except those ones in the array (listOffnet within the code). Everything works properly.
Now, I would like to do the complementary action: setting OFF all items that are not in the array. Unfortunately, I get the error at line:
[Code]....
View 1 Replies
View Related
Feb 24, 2010
I am getting the following error when i am clicking a Button on sheet1 which is generating duplicate of sheet1.I am trying to delete the button when duplicate is created..Error is:" run-time error '1004' Unable to get the buttons property of the worksheet class" at this line in code"ActiveSheet.Buttons("CmdHistory").Delete"
View 2 Replies
View Related
Jun 25, 2013
I am using this code (below) to hide certain rows or columns depending on what number is entered. Everything is working fine and I am at the point where I would like to share this excel sheet with others but I would like to lock certain cells so that others cannot alter the formulas. As soon as I lock the cells and then enter values into the unlocked cells, I get the "run-time error '1004': Unable to set the Hidden property of the Range class".
I am using excel 2010 x64.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("B3")) Is Nothing Then
If Range("B3").Value = 0 Then
Columns("G:P").EntireColumn.Hidden = True
Else
If Range("B3").Value = 1 Then
Columns("H:P").EntireColumn.Hidden = True
[code]....
View 3 Replies
View Related
Aug 12, 2007
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....
View 9 Replies
View Related
Dec 2, 2008
I have a macro that is taking a long time, so long that I think something is wrong but I dont really have a great way to check. I want to put up a little window showing the progress either by saying "i'm on row XX" or having a progress bar.
Looks like msgbox requires action before the code continues so it is not a good thing to use if you want to get progress. My formula looks for bad values in a data file and fixed them. It takes forever. I would like to have a box there that simply tells me what row it is working on.
Clearly I dont want to hit the OK button for it to continue processing. I want it to update the message box on its own and then take away the message box when it is finished processing.
View 8 Replies
View Related
Jul 3, 2013
I am using the sum function to sum the following cells and the formula is too long. How can I make this work?
'Raw Data'!B3,'Raw Data'!D3,'Raw Data'!F3,'Raw Data'!H3,'Raw Data'!J3,'Raw Data'!L3,'Raw Data'!N3,'Raw Data'!P3,'Raw Data'!R3,'Raw Data'!T3,'Raw Data'!V3,'Raw Data'!X3,'Raw Data'!Z3,'Raw Data'!AB3,'Raw Data'!AD3,'Raw Data'!AF3,'Raw Data'!AH3, 'Raw Data'!AJ3, 'Raw Data'!AL3, 'Raw Data'!AN3, 'Raw Data'!AP3, 'Raw Data'!AR3, 'Raw Data'!AT3, 'Raw Data'!AV3, 'Raw Data'!AX3, 'Raw Data'!AZ3, 'Raw Data'!BB3, 'Raw Data'!BD3, 'Raw Data'!BF3, 'Raw Data'!BH3, 'Raw Data'!BJ3, 'Raw Data'!BL3, 'Raw Data'!BN3, 'Raw Data'!BP3, 'Raw Data'!BR3, 'Raw Data'!BT3, 'Raw Data'!BV3, 'Raw Data'!BX3, 'Raw Data'!BZ3, 'Raw Data'!CB3, 'Raw Data'!CD3, 'Raw Data'!CF3, 'Raw Data'!CH3, 'Raw Data'!CJ3, 'Raw Data'!CL3, 'Raw Data'!CN3, 'Raw Data'!CP3, 'Raw Data'!CR3, 'Raw Data'!CT3, 'Raw Data'!CV3, 'Raw Data'!CX3, 'Raw Data'!CZ3,
View 7 Replies
View Related
Feb 8, 2010
im not really sure what div/0's are na dhow to counter them but i understand from the error that im dividing by something that is not valid yet.
below is my formula, among others like this. i need some assistance in getting the ISERROR code in as i am struggling; ....
View 9 Replies
View Related
Oct 3, 2012
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub
View 2 Replies
View Related
Nov 13, 2008
how can in compress the following formula
=IF(B3="Monthly",IF(B25="Windows 2003 Std",15,IF(B25="Windows 2003 Ent",30,IF(B25="Windows 2008 Data Center ",40,IF(B25="RHEL 5","30",IF(B25="Windows 2008 Web","15",0))))))+(IF(B3="Quarterly",IF(B25="Windows 2003 Std",40,IF(B25="Windows 2003 Ent",75,IF(B25="RHEL 5","89",IF(B25="Windows 2008 Data Center ",120,IF(B25="Windows 2008 Web","45",0))))))+(IF(B3="Semi-Annually",IF(B25="Windows 2003 Std",75,IF(B25="Windows 2003 Ent",140,IF(B25="Windows 2008 Data Center ",200,IF(B25="RHEL 5","149",IF(B25="Windows 2008 Web","90",0)))))))+(IF(B3="Annually",IF(B25="Windows 2003 Std",150,IF(B25="Windows 2003 Ent",300,IF(B25="RHEL 5","299",IF(B25="Windows 2008 Data Center ",400,IF(B25="Windows 2008 Web","180",0))))))))
View 5 Replies
View Related
Oct 31, 2009
I need to add more arguments to a countif formula, but I have maxed out the 1024 character limit.
Is there another way of writing this formula or is there a way I can add more arguments to this formula.
Formula is:
=countif(A2:A9999,"AH")+countif(A2:A9999,"ARHC")+countif...
I have a list of the arguments, can I do something like:
=countif(A2:A9999,B2:B50)
(with the list of arguments in columns B2 to B50)
View 13 Replies
View Related
May 23, 2006
Got a formula that goes like this: =IF((L1=1),"Product 1",IF((L2=2),"Product 2",IF((L2=3),"Product 3",IF((L2=4),"Product 4".... and so on for a further 100 or so products. Obviously, this formula is too long for one cell.
View 2 Replies
View Related
Aug 25, 2009
I have a spreadsheet with about six sheets.
On sheet one: I am entering data.
On sheet two: I am running the following macro: The macro is copying formula from row 6 to the a row number specified in cell A5......
View 9 Replies
View Related
Oct 21, 2008
I want to do is copy a formula from one workbook to another workbook that I have open - have tried just copy and paste (only get the result, no formula is copied) and have tried paste special, but there doesn't seem to be anything relevant on the list. I'm sure there is a simple way of doing it.
View 2 Replies
View Related
Dec 21, 2009
I have a formula which is currently too long and which I'm sure could be done more efficiently. That plus Excel is saying the formula is too long. I have pasted the formula below and broken it up for clarity:
---------------------------------------------------------------------------------
=IF(AND(E3="Three",I3<=100),
(I3*VLOOKUP(C3,costsnetwork,10,FALSE)/100),
IF(AND(E3="Three",I3<=250),
(100*VLOOKUP(C3,costsnetwork,10,FALSE)/100)+((I3-100)*VLOOKUP(C3,costsnetwork,11,FALSE)/100),
IF(AND(E3="Three",I3<=850),
(100*VLOOKUP(C3,costsnetwork,10,FALSE)/100)+(250*VLOOKUP(C3,costsnetwork,11,FALSE)/100)+((I3-350)*VLOOKUP(C3,costsnetwork,12,FALSE)/100),
View 14 Replies
View Related
Nov 22, 2006
I'm trying to get the max value in an array that has 3 different values that are:
array(.0027, -.0001, -.0083)
...with worksheetfunction.max(). I get the correct return, however it's 2.70000007003546E-03! How can I get it to return .0027?
View 3 Replies
View Related
