Posts by BrianGG

Users will select a cell between rows 5 and 12 in a random column. I need the code to look at row 2 of the column the user has chosen. I'm not even sure how to start this.

Any help would be greatly appreciated.

I have fixed my problem...Thank you both for your help and information.

Here's how I fixed it...if you're interested:

Application.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.

Application.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.

Sub 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.499984740745262

Thanks 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).

Select 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.

Cells.Find(After:=ActiveCell).Activate

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 set

z:  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.