Posts by BrianGG
-
-
I have fixed my problem...Thank you both for your help and information.
Here's how I fixed it...if you're interested:
CodeApplication.DisplayAlerts = True 'stops suppressing prompts and alerts while code is running. Application.Workbooks(2).Close SaveChanges:=False 'closes generated file. Application.Quit 'Quits Excel. Application.ActiveWindow.Close SaveChanges:=False 'closes the active window...required for quit. ActiveWorkbook.Close SaveChanges:=False 'closes the active workbook...required for quit.
-
I've tried the above recommendations with no help (Thank you).
At the time of the Application.Quit only this workbook with the macro is open. The following code is the last few lines.
Code
Display MoreApplication.ScreenUpdating = True 'turns on screen activity. Application.DisplayAlerts = False 'suppress' prompts and alerts while code is running. Filename = Application.GetSaveAsFilename(InitialFileName:="S:\ESO_SMG\Operations\Maintenance Tracking\" + docYear + "\" + docMonth + "\" + docDay + "\" + docDate + "_Maintenance Report", _ fileFilter:="Excel Files (*.xls), *.xls") 'sets variable to file name, extension and action. ActiveWorkbook.CheckCompatibility = False 'prevents performing compatibility check. ActiveWorkbook.SaveAs Filename:=Filename, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False 'saves file to filename and format. Application.DisplayAlerts = True 'stops suppressing prompts and alerts while code is running. Application.Workbooks(2).Close SaveChanges:=False 'closes generated file. '************************************************************. Application.Quit '************************************************************.
I use the asterisks to line my code problems, makes them easier to find later.
-
Application.Quit is producing Run-Time Error 1004. I use a workbook with a macro to generate a second workbook, then save the second workbook and close it. After that I'm trying to use Application.Quit to close the workbook that has the macro in it. When I run the macro from start, I get the Run-Time error at the Application.Quit line of code. When I select "Debug" Application.Quit works, without showing anything.
NOTE: When I use F8 to step through the code, everything works fine; no Run-Time error.
-
Re: String Array Fails on Zero
Thanks rory. It's always the simple little things that trip you up. Honestly I didn't know of that function.
-
I'm having a problem with a string array. The code below works as long as the value is not a zero. It fails at the asterisk with a Run-time error '9': Subscript out of range.
The cell data is a date and time; example: 5/21/15 0:00:00
I need the first digit of the time to compare to a set value and then perform an action if not equal to three. The code works great unless the first digit of the time is a zero.
NOTE: timeChck and ChckNum are variables set using Public.
Code
Display MoreSub TimeCheck() Dim string_in As String Dim string_out As String Dim array_out As Variant Do While IsEmpty(ActiveCell.Offset(0, 0)) = False string_in = ActiveCell.Value array_out = Split(string_in, " ") [COLOR=#ff0000]* array_out = Split(array_out(1), ":")[/COLOR] timeChck = array_out(0) If timeChck <> ChckNum Then With Selection.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent6 .TintAndShade = 0.399975585192419 .PatternTintAndShade = 0 End With 'ends With statement. ActiveCell.Offset(1, 0).Select If IsEmpty(ActiveCell.Offset(0, 0)) = False Then ActiveCell.Offset(1, 0).Select End If Else ActiveCell.Offset(1, 0).Select If IsEmpty(ActiveCell.Offset(0, 0)) = True Then ActiveCell.Offset(1, 0).Select End If End If Loop End Sub
-
I use a VBA macro to build a workbook with several sheets; in this new workbook, is it possible to limit what colors can be used to color each sheet's tab?
I need to limit the colors to:
Color = 5296274
Color = 65535
Color = 49407
Color = 12611584
Color = 255
ThemeColor = ThemeColorDark1, TintAndShade = -0.499984740745262Thanks in advance.
-
Re: Control Tab colors in a Workbook
Thanks royUK, I'll give it a try when I can.
-
I use a VBA macro to build a workbook from a separate spreadsheet. In this new workbook: (1) Is there a way to control what colors can be used on spreadsheet tabs? (2) When changing a spreadsheet's tab color, is there a way to change a cell's color (on a different spreadsheet in the workbook) to match?
-
Re: Write VBA code with VBA code.
Thanks iwrk4dedpr, the link you provided is what I needed.
-
Re: Write VBA code with VBA code.
Thanks! I'll check the link and get started. I don't believe the "BASIC" Tickets.xls idea will work. The Tickets.xls file is generated from another program. But thanks for the idea.
-
I have an Excel file (MNX Report Gen.xlsm) with VBA code that I use to open a second Excel file (usually Tickets.xls) and manipulate all the data. Is it possible to use MNX Report Gen.xlsm to write VBA code into the Tickets.xls file? If so, what's the code?
-
I have a workbook with data listed by rows, 10 cells (F-O). I need to copy the rows to a worksheet that lists the data by column (B7-B16). I would like to use a macro to perform this function. Is there a fast way to write the code? or does it have to be done cell by cell?
Any help is most appreciated!
-
Re: Cell Search and Column Select for Deletion
Thanks StephenR and cytop,
Its working just the way I need it too.
BrianGG
-
Re: Cell Search and Column Select for Deletion
In there lies the problem. The value is listed more than once and those values must be removed.
-
The following code is suppost to search all the row 1 cells with data and when it finds one matching the "What:=uplinkID" it should select the column and delete it. However, I'm getting a "Run-time error '91': Object variable or With block variable not set" error on the "Cells.Find" coding (not sure why).
Code
Display MoreSelect Case uplinkID Case "NBR" uplinkID = "AUS" Case "MON" uplinkID = "CHI" Case Else End Select Do While ActiveCell.Value <> "" Cells.Find(What:=uplinkID, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate ActiveCell.EntireColumn.Select Selection.Delete Shift:=xlToLeft ActiveCell.Offset(0, 1).Select Loop
Odd thing is on one file it fails on the second column and on another file on the last column and not at all on others. I'm at a loss on this one.
Any help greatly appreciated.
-
Re: Fix "On Error GoTo" problem
cytop,
I removed the "On Error GoTo" so I could see what errors may be happening. I then made the change you suggested. I'm now getting a "Run-time error '450': Wrong number of arguments or invalid property assignment" on the line of code you recommend I change.
Do you have any ideas I could try to fix?
Thanks
-
My macro starts by opening a workbook. Then performs the following code on a worksheet with no problems. A second workbook is then opened and the worksheet in it is copied to the first workbook and then closed. The following code is then ran a second time with a GoTo z: statement at which time it fails. On the second run, the code (in Bold red) fails with an Error:
Run-time error '91':
Object variable or With block variable not setCode
Display Morez: Select Case uplinkID Case "NBR" uplinkID = "AUS" Case "MON" uplinkID = "CHI" Case Else End Select On Error GoTo b: Cells.Find(What:=uplinkID, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate ActiveCell.EntireColumn.Select Selection.Delete Shift:=xlToLeft ActiveCell.Offset(0, 1).Select Do [COLOR=#FF0000][B]C[B]ells[/B].FindNext(After:=ActiveCell).Activate[/B][/COLOR] ActiveCell.EntireColumn.Select Selection.Delete Shift:=xlToLeft ActiveCell.Offset(0, 1).Select Loop On Error GoTo 0
I'm at a loss as to why this code works the first time but not the second time. Any help would be greatly appreciated.
-
Re: Find minimum value greater than 0 in a range
I forgot the Ctrl-Shift-Enter. Works great, thank you cytop!
-
Re: Find minimum value greater than 0 in a range
I want to find the minimum value. I tried your suggestion and its returning #VALUE!.
Sorry for the lack of detail, I've been at work for 13-hrs. I have 1122 cells to comb through and I can't sort the data (company rules).
Your help is greatly appreciated.