I cant seem to figure out why the macro in this workbook quits working after any error.
I can Break from the error but then the macro ceases to function at all. I am forced to close the workbook and re-open it.
FYI
This macro writes to the registry. Simple text values only.
Example: Retrieving data. Its currently generic in format.
"sLogin" is a user entered name
"TargetValue" is a selected cell value (activecell).
I have wrote a simple macro/ function that used to work before I decided to improve it. When I run the macro, I can step F8 until the before last line in the following code
Function bla() As Boolean
Set rCell = Cells(1, 1)
If rCell.Value <> "" Then
x = rCell.Value * 0.02 'Works
rCell.Value = rCell.Value - x 'Doesn't work
End If
bla = False
End Function
Excel does stop running the macro, but it doesn't show any error so I don't know what to fix.
Steps to reproduce the problem:
1. Create new Excel document
2. Type any number in the very first cell
3. Open VBA, add module and copy above function
4. Type in another cell "=bla()"
This first macro works fine:
Sub UnhideHBPorCholesterolQ1toQ3()
'
ActiveSheet.Unprotect "password"
Rows("58:67").Select
[Code]....
why the second macro does not seem to work. I get a Run-time error that says "Unable to set the Hidden property of the Range Class".
the if stattement works perfectly and does exactly what i want except when it comes to the else part. if there is no error the statements are run perfectly but if there is an error (in this case the error is generated when a match cannot be found in the spreadsheet) the else statement doesnt kick in and post the msgbox.
the code just crashes. and returns an error 1004 on the line i have highlighted in yellow
res = WorksheetFunction.Match(invvar, Columns(1), 0)
If Not IsError(res) Then
Me.txtClientID.Value = ws13. Cells(res, 7)
Me.txtNumber.Value = ws13.Cells(res, 7)
Me.txtDate.Value = ws13.Cells(res, 8)
'save client id as a variable
'Print to invoice------------------------------------------------------------------.....................
Below is the code in which the macro is showing error
Run time error '1004'
Application-defined (or) Object defined error
I am trying to suppress the error message (green triangle top left corner of cell). I have tried
Code:
On Error Resume Next
On Error GoTo 0
At the start and end of code, but to no avail.
Code:
Sub mcrpasteformulaandcommentlist()
On Error Resume Next
On Error GoTo 0
ActiveWindow.SmallScroll Down:=23
Range("C50").Select
ActiveCell.FormulaR1C1 = _
[code]....
I have a Macro button in my Excel Spreadsheet, every time I close the spreadsheet it gives me this error "Microsoft Excel Stopped Working" .
I have attached Spreadsheet to show example : Inventory List.xlsm
I developed a program which uses Date function in several places. Problem is that on some machine this function is not working and I'm getting compile error.
What is the reason for that. Is this a system setting??
I'm scraping a website for some data using the following program. The program was working as expected until I decided to add a save file element that was a copy paste of some code a friend gave me (attached below). Funny thing is the save file functionality works but now I can't get IE to load the webpage - I get a 438 Error. I'm thinking there's a problem with something in the references menu.
Code:
Sub GET_PVI()
Dim Days_in_Month(1 To 12) As Integer
Dim Name As String
[Code]....
I am trying to group some dates in a pivot table.
Unfortunately I receive an error saying it can't group the selection.
I have done "text to columns" and made the cells - (date values) and I have had no luck.
As I am at work I can't use the HTML genie because it won't install but I have uploaded a dummy file to my dropbox which available from here [URL] .......
I pull this data from an Oracle POWER PIVOT and always have trouble grouping the dates when they come from Power Pivot. Currently working as analyst so figuring this out would make life so much easier as I could group by months etc.
Because countif cannot be used across multiple tabs, I'm using the following workaround where "MySheets" is the range of tabs and E8 is the cell I want "counted".
=SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented"))
This works perfectly, except that I really need COUNTIFS... I want Excel to count either "Fully implemented" or "Partially implemented" when in E8. I cannot get the formula to work and keep getting an error message.
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&MySheets&"'!E8"),"Fully implemented",(INDIRECT("'"&MySheets&"'!E8"),"Partially implemented")))
I have some code that, although works fine in Excel 2003, does not in Excel 1997. I receive this error when I try running it:
COMPILE ERROR:
NAMED ARGUMENT NOT FOUND
Sub HPVAL()
Dim r As Range, myStr As String
myStr = "HP"
Set r = Cells. Find(What:=myStr, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not r Is Nothing Then
r = r.Value
While Not r Is Nothing
Set r = Cells.FindNext(r)
If Not r Is Nothing Then
r = r.Value
End If
Wend
End If
End Sub
It looks like Excel is getting hung up on the "SearchFormat:=" portion of the code.
VB:
Selection.FormulaArray = _
"=IF(RC[-7]=""Weekly"",RC[-1],IF((ParentCode=RC[-10])*(ClassType=""Active""),IF((EndDate=EOMONTH(EndDate,0))*(RC[-7]
={""Monthly"",""Quarterly""}),RC[-1],0),IF(RC[-7]=""Daily"",IF(SUMPRODUCT(((ParentCode=RC[-10])*(FundType=""C"")*
(ClassType=""Inactive Class"")*(TermDate<>"""")*(TermDate>=StartDate)*(TermDate<=EndDate))+((ParentCode=RC[-10])*
[Code] .....
I recorded macro for this formula its giving me syntax error i did " _" after the break but its still not working..
"Compile error - Syntax Error"
and the following line highlighted in yellow
"Sub CreateWorkbooks()"
It worked on Excel 2000 but not now and dont work either in Excel 2007.
Heres the complete code .....
The attached workbook contains a macro ("Transfer_Data") that copies values from one cell to another when the "Update Progress" button (near cell A3) is selected. It works perfectly fine in this workbook but when I try the same code in another workbook, which looks and operates exactly like this one, nothing happens.
Is this due to a security setting or missing code line items?
copy&paste in next blank cell2.xlsm
Below is a code which I added to my 'Quick access toolbar' (and which is based off a hidden workbook). I use this macro to fulfill tasks to regular excel spreadsheets that are not macro enabled, however, I can't seem to get this macro to work for other workbooks. How to modify my code to work for other spreadsheets.
[Code] .....
It gives me a error and says my password is wrong. I have user names set to return the password so that it is unlocked for certain people. It works for unprotecting the sheets. I modified it and added it back in to try to do it for the workbook.
[Code] .........
I realize that they " and () around the password are differant, but that's how I last left them. I have tried several combinations and it just gives me more errors saying expected end of statement, etc.
I am using this code in a macro to find a substring from a string in cell G1.
View 11 Replies View RelatedI have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
I've got a semi-recorded macro to format a report I run, and towards the end it deals with sorting, subtotalling, formatting text etc. The main function of the macro is to split a large report into several smaller reports defined by whichever manager name appears in the relevant column. The smaller reports need subtotalling by 2 different columns so I have the following macro code :
Cells.Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(5), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
For some reason this only subtotals by the second column and not the first. I tried swapping them over so GroupBy1 then GroupBy2 but it's still the same. Anyone got any ideas why this might be? I appreciate it's not the smoothest code I could use but I don't know enough about code to do any better.
Have recorded a Macro which is doing a great job, but when i try to run the same macro from other pc its not running,pops an search error.
View 9 Replies View RelatedI've got a column that auto-populates from anther column depending on what's input, and that's working fine. I also have a button set up to select and copy the auto-populated data. I just recorded myself select the top cell and hit shift+ctrl+down to select just the values that are populated. What it does, however, is selects the blank ones too. I know the counta() function would work, but I don't know how to input that into a macro.
The macro I have now is:
Sub Copy()
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub
The column is only 25 rows tall.
I have a macro to export two worksheets and save as, but the save as part will not work, it just exports to Book1.
Ive checked the directory and everything is fine.
I want the value of a cell in one of the exported worksheet to be included in the save as name.
I'm just wondering if the code needs to somehow specify the worksheet to get the cell value from or will it default to the first worksheet. but either way my code is still not working and i can't tell why.
Should i just create a seperate macro for the save as?
Here is the ...
I got a problem with a macro i'm working on. I got column D which contains text as "Figures", "Toys", "3DS", "PS3" etc. I also got column N where i need the macro to display "Toys" if it's a toy, figure, etc; or "Games" if it's "PS3", "PS4" etc.
I'm using the following code, to also select only blank rows (so to ignore row 1 which is table header). Problem is that the result shows only "Games".
VB:
Columns("N:N").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=IF(RC[-9]=""*toys*"",""Toys"",""Games"")"
I'm having a problem with my macro .However, if i use the following script:
[Code]......
If i am correct all the rows with a value less than 1 should be hidden right? However, some rows are hidden when they shouldn't be.
Im working on a quotation with different products in it. And the rows with a 0 in it should be hidden and that's pretty much working. But it also hides some of the subtotals(which i dont want) and some stay unhidden.
I have macro for creating months. Code worked fine, but now I use merged cells in range where days should be created, and code no longer works (Error : "Cannot change part of merged cells").
Tried with unmerging cells, but then code inserts days in shorter range that I need, so code needs to be changed.
[Code] .....
Attached File : Macro_months.xls
Debug error points to:
BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"),
Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")
Sub is below:
Sub UpdateMonthForecast()
Dim Month As Integer
Dim Year As Integer
[Code]....
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
View 10 Replies View RelatedI found the following code elsewhere on the net, and have attempted to use it in my workbook to change the names of all 31 worksheets when a certain cell's (C7) value changes. The code looks good to me, so I cannot understand why it is not working. I am not getting error messages, just no changes to the sheet names.
I believe this may be due to the value of C7 on each sheet being dictated by a formula, so the value is not being changed with the sheet active, but indirectly with a formula that pulls the value from a cell on another sheet, and then adds.
(i.e. =TEXT('Populator Tools'!$C$25+1,"mm-dd-yy") )
Am I completely off base with this assumption, or on the right track; and if I am on the right track how do I fix this so it works?
Code Below.
The Workbook_SheetChange Code Stored in This Workbook Module
I wish I could have been more specific with my Title. This is what I am encountering. I used the McGimpsey code for generating a unique sequential number every time a workbook opens. I tailored the code to fit my needs and stuck it into my invoice sheet. For the longest time I was unable to make it work until I moved the code from a template to a standard workbook. Then I was able to make it work as long as the sheet was unprotected. I realized that to get the code to work I had to then unprotect the cells that the code was writing to. Now that I've done that I am getting errors in the code that weren't there before, rendering the macro useless. The code I am using is:
View 14 Replies View Related