Posts by Gary's Student

Re: Convert Text to Number

This is a small variation on Mumps' macro:

Sub formatCols()
    Dim r As Range
    Application.ScreenUpdating = False
    With Range("A:A") 'change column to suit your needs
        .NumberFormat = "General"
    End With
        
    For Each r In Range("A:A").Cells.SpecialCells(xlCellTypeConstants)
        With r
            .Value = Mid(.Value, 2)
        End With
    Next r
    Application.ScreenUpdating = True
End Sub

It assumes that the currency symbol is part of the string.

Re: Question about =IF

=if(iserror(match(a1,k:k,0)),"",k1)

Re: Filtering multiple columns in a non compounding way

Do you want to check only row#8 or other rows as well??

Re: Rank help

In the attached example, column C is the simple rank, column D is the unique rank, and column E is the place rank. I think you need column E.

Re: message box based on a cell value in a range

Help me to understand.........how can cells change on Sheet1 if you are on Sheet3?

Re: message box based on a cell value in a range

Try this version:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range, r As Range
    Set A = Range("A1:A20")
    If Intersect(Target, A) Is Nothing Then Exit Sub
    For Each r In Target
        If r.Value = "A12345v1" Then
            MsgBox "A12345v1 entered into cell " & r.Address
        End If
    Next r
End Sub

Re: Sharing Array constants across multiple functions

Consider:

• Declaring the array as Public in a module
• Store the values in worksheet cells (cells are cheap; why not use them?)
• Have a sub that fills the array from the cells (maybe the workbook open event macro)

Then all the functions would have access to the multi-dimensional array.

Re: Sub: SaveAsCell () problem

Too much whitespace:

Sub SaveAsCell()
        Dim strName As String
        On Error GoTo InvalidName
        strName = Sheet1.Range("A1")
        ActiveWorkbook.SaveAs strName
        Exit Sub
InvalidName:     MsgBox "The text: " & strName & _
        " is not a valid file name.", vbCritical, "Ozgrid.com"
End Sub

Re: automatically close a workbook when I close anuther workbook

Thanks for the feedback!

Re: automatically close a workbook when I close anuther workbook

Put this in the workbook code area of "Live PO.xlsm"

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wb As Workbook
    For Each wb In Workbooks
        If Split(wb.Name, ".")(0) = "data" Then
            wb.Saved = True
            wb.Close
        End If
    Next wb
End Sub

Re: message box based on a cell value in a range

I always watch.

Be more specific...........only that one combination or any combination ??

Re: Trying to create a Unique Sorted Data Validation List

Checkout the attached workbook for a formula-only approach:

Re: Filter or VBA? Or is this even possible?

So if an item in column C is <=150, the row must be hidden regardless of A or B ??

If this is true, then I would use a "helper" column, say column K..................in K2 enter:

=IF(C2<=150,"Hide",IF(OR(A2>325,B2>325),"Show","Hide")) and copy down

Then filter on column K

Re: Fixing a script to delete N/As in large data sets

This will be faster, but it will remove all errors, not just #N/A's:

Sub SuperKiller()
    Dim r As Range
    Set r = Range("A:A").Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
    r.EntireRow.Delete
End Sub

Re: Fixing a script to delete N/As in large data sets

Give this a try:

Sub NAKiller()
    Dim r As Range, rDel As Range
    Set rDel = Nothing
    For Each r In Range("A:A").Cells.SpecialCells(xlCellTypeFormulas)
        If r.Text = "#N/A" Then
            If rDel Is Nothing Then
                Set rDel = r
            Else
                Set rDel = Union(rDel, r)
            End If
        End If
    Next r
    If rDel Is Nothing Then Exit Sub
    rDel.EntireRow.Delete
End Sub

Re: VBA use of CDate to find date in range stopped working

You have demonstrated that FIND is "flaky". Perhaps a more robust method is to get the date as a String and loop down the column comparing to Range.Text. See the attached file.

Re: VBA use of CDate to find date in range stopped working

First verify that the dates in rngSearch are "real" dates. Then force the issue (using B5

Dim s As String, d As Date
    s = Range("B5").Text
    ary = Split(s, "/")
    d = DateSerial(ary(2) + 2000, ary(0), ary(1))

and in the FIND, use..........

What:=d

Re: Drop Down Selection Including Colour of Cell

I can't replicate what you are seeing.

Re: Drop Down Selection Including Colour of Cell

Here is the macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsh As Worksheet
    Set wsh = ActiveSheet
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    v = Target.Value
    If v = "" Then Exit Sub
    Sheets("Sheet2").Activate
    Dim rLook As Range
    Set rLook = ActiveSheet.Range("People")
    For Each r In rLook
        If v = r.Value Then
            Target.Interior.Color = r.Interior.Color
        End If
    Next r
    wsh.Activate
End Sub

It needs to be installed on any worksheet that you want to use for this behavior. Note that the macro assumes that the nice colored list of names can always be found on Sheet2.

Because it is worksheet code, it is very easy to install and automatic to use:

[INDENT]1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window[/INDENT]

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.
If you are using a version of Excel later then 2003, you must save
the file as .xlsm rather than .xlsx

To remove the macro:

[INDENT]1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window[/INDENT]

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-u…e814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Re: Drop Down Selection Including Colour of Cell

Here is a version for column A.