 |
I have macros running but sometimes #N/A errors stop the running macro and I get lost with the current update. I need a macro that will run through the sheet and change all #N/A errors to a blank cell. Please find attached a sheet showing my problem.
Many thanks.
1) Select all the data
2) Press F5
3) Select Constants > Errors (uncheck the rest)
4) Press delete
You can record those simple steps into a macro if you really need to.
Thank you JB!
Ok now I cannot handle it any more. When the macro finds no #NA errors it stops with a error! Any ideas?
Please have a look at attached file with button.
Attachment
1) Use this instead:
Code:Sub DelNAErrorsOnEmbSheet()
On Error Resume Next
Sheets("Emb").Activate
Cells.SpecialCells(xlCellTypeFormulas, 16).ClearContents
End Sub
2) I noticed all your macros do a lot of UNPROTECT/PROTECT activity. There's no need to do that.
Excel has a hidden protection flag called UserInterfaceOnly that sets the protection to humans only. That leave VBA free to do what it wants on the same sheet(s) protected this way.
The only downside is that this flag does not survive closing and reopening the workbook. But that's easy to fix with a macro that resets the flag on each sheet needed each time the workbook is opened.
Put this macro into the ThisWorkbook module and edit the Array() to list the sheet names you need to reset the protection flag on.
Code:Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Sheets(Array("Emb", "Prod A", "Prod B"))
ws.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, UserInterfaceOnly:=True
Next ws
End Sub
3) Your macros do a LOT of selecting. You can and should edit all of that out to increase the efficiency and readability of your macros. For instance, here's your Module39 macro after I edited it down:
You could remove the Unprotect/Protect stuff, too, if the sheet this macro runs on is added to the ThisWorkbook macro array of sheets I gave you earlier.Code:Sub UpdateSchedule()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Columns("L:L").Font.ColorIndex = 0
Columns("F:F").Copy
Columns("A:A").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("N401:AB401").Copy
Range("N3:N400").Select
Range("N400").Activate
ActiveSheet.Paste
Range("H2") = 1
Range("H2").Interior.ColorIndex = xlNone
Range("H2").Copy
Range("A3:A401,K3:K401").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
Application.CommandBars("Task Pane").Visible = False
Application.ReplaceFormat.Clear
Range("A3:A401,K3:K401").Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A300") = "Line D"
Range("A200") = "Line C"
Range("A100") = "Line B"
Range("B3:B401").Replace What:="EMB", Replacement:="Emb", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B3:B401").Replace What:="PRINT", Replacement:="Print", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B3:B401").Replace What:="PRT", Replacement:="Print", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B3:B401").Replace What:="RIVET", Replacement:="Riv", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B3:B401").Replace What:="STUDS", Replacement:="Stud", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("B3:B401").Replace What:="BUTTON", Replacement:="Stud", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A2") = "MH no"
Range("B2") = "O/W"
Range("C2") = "Del"
Range("D2") = "Fab"
Range("K2") = "# no"
Range("L2") = "Style & wash"
Range("M2") = "Qty"
Range("A3:A99,A101:A199,A201:A299,A301:A401").Font.ColorIndex = 3
Columns("F:F").ClearContents
Range("A3").Select
ActiveSheet.Protect
Application.ScreenUpdating = True
MsgBox "Updated!"
End Sub
4) Your Module42 has a lot of copy/pastevalues. This, too, can be sped up by just using the .Value property of the cell your putting the data into. Like so:
Code:Sub MonthEndUpdate()
Application.ScreenUpdating = False
Sheets("SPPA").Activate
Range("G31").Value = Range("I18").Value
Range("G54").Value = Range("I41").Value
Range("G76").Value = Range("I63").Value
Range("G98").Value = Range("I85").Value
Range("G30").Value = Range("G24").Value
Range("G32").Value = Range("G28").Value
Range("G33").Value = Range("G29").Value
Range("G53").Value = Range("G47").Value
Range("G55").Value = Range("G51").Value
Range("G56").Value = Range("G52").Value
Range("G75").Value = Range("G69").Value
Range("G77").Value = Range("G73").Value
Range("G97").Value = Range("G91").Value
Range("G99").Value = Range("G95").Value
Range("G100").Value = Range("G96").Value
Range("G78").Value = Range("G74").Value
Range("G101").Value = Range("L86").Value
Range("G79").Value = Range("L64").Value
Range("G34").Value = Range("L19").Value
Range("I12,J12:J13,I18,J18:J19,I41,J41:J42,I63,J63:J64,I85,J85:J86,J109,K103:L104") = "0"
Range("K105:L106").Select
Sheets("HO plan").Select
Range("R47") = Range("R47") + Range("R43")
Application.ScreenUpdating = True
MsgBox "Updated!"
End Sub
Jeez was not necessary to do all that work, but thank you very much. I am not very good at macros and I am teaching myself, I have learned so much from you already.
We are a village... and this is a teaching forum, so we're doing good, yes?
I am struggeling with "UserInterfaceOnly= True" where I have a password to unprotect. It keeps on asking for the pass though. Otherwise if no pass no problem.
UserInterfaceOnly:=True is a parameter you set in a PROTECT command, not an unprotect command.
Once you PROTECT a sheet and include this flag when you do it, you no longer need to unprotect that sheet for the rest of the day. Is that clear what I mean?
The point is to never have to bother unprotecting a sheet, all your macros have a free pass to do what they want event though the sheet is protected from humans changing it.
So protect your sheet with that flag added, and remove all the unprotect codelines for that sheet and you're good to go.
It is clear and I understand, and it works fine if there is no password when the sheet is protected, otherwise before the macro will continues to do its work asks for the password.
This is not correct. If you are experiencing this behavior then something is going on unexplained or improperly implemented. I'd have to see what you're seeing.
Refer " Packing list" sheet where there is a button " "Email to humin", this is so that the file can be made smaller to email and also to restrict usage of the file and all my work put into this freely. It always asks for the password. Hope this will clear things up. I had to clear some sheets to save space, no information needed for this macro anyway.The pass on all sheets is 123. Many thanks JB.
1) You failed to ADD the password to the workbook open macro so the flags could be set properly when the workbook first opens.
Code:Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Sheets(Array("Recording", "SAD500-1st", "2nd", "3d", "4th", "5th"))
ws.Protect "123", DrawingObjects:=True, Contents:=True, _
Scenarios:=True, UserInterfaceOnly:=True
Next ws
End Sub
3) The password on sheets SAD500-1st was wrong, I had to set that to 123 to get it to work, you had it as 1234.
4) Your macro Email to Humin still had a ActiveSheet.Unprotect line of code in it. Didn't I say to remove all unprotect code lines?
5) The sheet EDI is one you're trying to edit, but it's NOT one of the sheets in your workbook_open macro. Add it to the array of sheets if it's one you want editable by VBA.
Add any others, too.
Or, remove the protection from that sheet.Code:Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Sheets(Array("Recording", "SAD500-1st", "2nd", "3d", "4th", "5th", "EDI"))
ws.Protect "123", DrawingObjects:=True, Contents:=True, _
Scenarios:=True, UserInterfaceOnly:=True
Next ws
End Sub
6) Some edits on the Email to Humin macro:
Code:Sub EmailReadyToHumin()
'
' EmailReadyToHumin Macro
'
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Sheets("Invoice")
.Cells.Value = .Cells.Value
End With
With Sheets("EDI")
.Cells.Value = .Cells.Value
End With
Sheets(Array("Recording", "SAD500-1st", "2nd", "3d", "4th", "5th")).Select
Sheets("5th").Activate
ActiveWindow.SelectedSheets.Delete
Sheets("Packing list").Select
Range("A1").Select
'To save on any desktop and per cells content
Dim fName As String, DTAddress As String
DTAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
'And per cells content
fName = Range("L3").Text & "Packing list"
ActiveWorkbook.SaveAs DTAddress & fName & ".xls"
MsgBox "Saved and ready to email from desktop!"
End Sub
Thank you the macro is running smoothly, jip I'm still missing simple things and cause me huge problems!
| All times are GMT -7. The time now is 12:13 PM. |