Posts by colin_l

Re: Count Alphanumerics In Single Cell

Does the order of the characters matter to you?

For example, when checking for "1ad", would you want this dataset:
A1 1ad
A2 1abd
A3 abd1

To count as 2 or 3?

Colin

Re: Static Date & Time Corresponding To Cell Change

No grumpiness here and I certainly didn't mean to convey any either... and who said anything about removing it?

Re: Static Date & Time Corresponding To Cell Change

I understand where you are coming from and I'm all for keeping things simple, but introducing the notion that it is effectively the same as the offset property only confuses the situation. People who are learning from this thread would be learning incorrectly and will run into problems later on. These two properties are different to each other, just as they are different to the cells property, and each should be treated separately.

Taking a simple example:

Private Sub Worksheet_Change(ByVal Target As Range)
    MsgBox "item " & Target(1).Address
    MsgBox "offset " & Target.Offset(0).Address
End Sub

If one pastes this into a sheet class module and then selects a multicell range, say A1:A10, types in the formula =1 and completes the entry with CTRL+SHIFT+ENTER, the two properties yield different results. They are not effectively the same.

Colin

Re: Static Date & Time Corresponding To Cell Change

Just to be clear on this, it's not an abbreviated form of the offset property. It's an abbreviated form of the item property. The reason this syntax is permissable is that the default property of the range object is the item property....

That might sound pedantic, but there are clear differences.

As you mentioned, the item property is 1-based.

Also, the item property will only ever return a single cell range, whereas the offset property may return a multi cell range. For example, compare these two:

Sub foo()
    MsgBox Range("a1:c1")(2).Address
    MsgBox Range("a1:c1").Offset(1).Address
End Sub

Also, note that these two return different results:

MsgBox Range("a1:d1")(2).Address
    MsgBox Range("a1:a4")(2).Address

The differences are less apparent when working with a single-cell parent range, but I think that precise understanding is required to avoid confusion.

MsgBox Target(1, 1).Address

Is the same as/shorthand for:

MsgBox Target.Item(1, 1).Address

Colin

Re: Wildcard With No Automatic Spaces

Hi

Try this:

ActiveCell.Formula = _
        "=IF(ISNA(VLOOKUP(LEFT(c2,10)& ""*"",NAME,2,FALSE)),"""",(VLOOKUP(LEFT(c2,10)& ""*"",NAME,2,FALSE)))"

Colin

Re: Add More Columns?

No, that is the maximum number of columns in all versions of Excel prior to 2007.

You do have up to 65536 rows you can use though.

Colin

Re: Return Inverse Matrix Of Large Matrix

Nice pickup, Shg!

Re: Minverse Num! Error

No. Number data types in an Excel worksheet are, in fact, doubles.

Re: Minverse Num! Error

Hi Mark,

I think the clue to this is in the Excel helpfiles for the MINVERSE worksheet function:

Quote from Helpfile

Some square matrices cannot be inverted and will return the #NUM! error value with MINVERSE. The determinant for a noninvertable matrix is 0.

Do you get the same error if you directly use the MINVERSE worksheet function within a formula within the worksheet?

Colin

Re: Professional Development: The Definitive Guide ...

If you want an Excel VBA reference book then (IMHO) the best one around is Excel 2007 VBA Programmer's Reference by Green, Bullen, Bovey and Alexander. It's the complete package and is much more comprehensive than PED as a reference book.
Warning: The 2003 version of this book is awful.

Professional Excel Development (PED) focuses on many advanced areas - as it says on the synopsis, it goes beyond other books. Unless your Excel/Excel VBA is already very competant then you will not be able to follow most of the material since it is written with the assumption that you do.

So should you buy PED? That totally depends on what you're after....

Just my 2c.... [I own both these books]

Re: Minverse Num! Error

Hi Mark,

It'll be easier for us to identify the problem if you post the UDF code you are running and show us how you are calling it from the worksheet....

Colin

Re: Pull Each Each Digit From Number Into Single Cells

If anyone wants a breakdown on how the formula works then I would be happy to oblige...

Colin

Re: Pull Each Each Digit From Number Into Single Cells

Here's another alternative.

Set up an named array constant called Arr which is defined as:
={1,2,3,4,5,6,7,8,9}

Then select cells C3:K3 and complete this array range formula with CTRL+SHIFT+ENTER:
=MID(TEXT($A3,"0.00"),(SUBSTITUTE(SMALL((ISNUMBER(-(MID(TEXT($A3,"0.00"),Arr,1)))*Arr),Arr),0,LEN(TEXT($A3,"0.00"))+1)),1)

Then fill the formula down the table.

Example attached.

Colin

Re: Create List Depending On Lookup Value

Why can't you use a pivot table to create the list?

To correct your formula:
=IF(ISERROR(INDEX('NEW stores'!$A$2:$E$436,SMALL(IF('NEW stores'!$A$2:$A$436=Report!$R$4,ROW('NEW stores'!$A$2:$A$436)-1),ROW(1:1)),4)),"",(INDEX('NEW stores'!$A$2:$E$436,SMALL(IF('NEW stores'!$A$2:$A$436=Report!$R$4,ROW('NEW stores'!$A$2:$A$436)-1),ROW(1:1)),4)))

HTH
Colin

Re: Numeric Validation Of User Form Textbox

Hi,

You'll need an identifier so that you can exclude them. For example, you could use the textbox's tag property and extend the If condition in the loop, or you could group the numeric/string textboxes in different frames and only loop through the textboxes in the relevant frame.

HTH
Colin

Re: Next Greatest Maximum

Yes, the SMALL worksheet function.

Re: Find A Few Values At Once Time

Hello,

This should give you the general idea:

Sub SelectValues()
    Dim rngFound As Range
    Dim rngAllFound As Range
    Dim sFirstAddress As String
    Dim sarrFindString(0 To 1) As String
    Dim i As Integer
    
    sarrFindString(0) = "99"
    sarrFindString(1) = "71"
    
    With ActiveSheet.Range("A:A")
        For i = 0 To 1
            Set rngFound = .Find( _
                            What:=sarrFindString(i), _
                            LookIn:=xlValues, _
                            lookAt:=xlWhole)
             
            If Not rngFound Is Nothing Then
                If rngAllFound Is Nothing Then
                    Set rngAllFound = rngFound
                Else
                    Set rngAllFound = Union(rngAllFound, rngFound)
                End If
                sFirstAddress = rngFound.Address
                
                Do
                    Set rngFound = .FindNext(rngFound)
                    If Not rngFound Is Nothing And rngFound.Address <> sFirstAddress Then
                        Set rngAllFound = Union(rngAllFound, rngFound)
                    Else
                        Exit Do
                    End If
                 Loop
            End If
        Next i
    End With
    
   If Not rngAllFound Is Nothing Then rngAllFound.Select
End Sub

Colin

Re: Next Greatest Maximum

Hi,

=LARGE(G19:G25,2)
Will return the second largest value from the range.

=LARGE(G19:G25,3)
Will return the third largest value from the range.

Colin

Re: Now() Updating Without Closing File

The value will be updated each time the worksheet is recalculated. (Press F9 to force this manually).

Change the format of the cell to seconds precision if you want to physically see the value change more frequently than each minute.

Re: Numeric Validation Of User Form Textbox

Hello

If you have a lot of textboxes then I'd do it like this:

Code in a Class Module called cTextboxes:

Option Explicit


Private WithEvents tbx1 As MSForms.TextBox


Sub SetTextbox(ByVal tbx As MSForms.TextBox)
    Set tbx1 = tbx
End Sub




Private Sub tbx1_Change()
    With tbx1
        If Not IsNumeric(.Value) And .Value <> vbNullString Then
            MsgBox "Sorry, only numbers are allowed"
            .Value = vbNullString
        End If
    End With
End Sub

Code in your Userform Class Module:

Option Explicit


Dim cTextBox() As cTextboxes


Private Sub UserForm_Initialize()
    Dim obj As MSForms.Control, i As Long
    
    For Each obj In Me.Controls
        If TypeName(obj) = "TextBox" Then
            i = i + 1
            ReDim Preserve cTextBox(i)
            Set cTextBox(i) = New cTextboxes
            cTextBox(i).SetTextbox obj
            obj.Tag = i
        End If
    Next
End Sub

This code was adapted from here:
http://www.xtremevbtalk.com/t61143.html

[edit]
Also, as it stands I have used the communal 'data validation' procedure you provided in your post. Personally I don't think it's overly user-friendly because if a user types in a 6 digit number and then accidentally catches a letter, your procedure wipes the entire entry and the user has to start over again. Instead, you might want to consider the approach demonstrated by Chip Pearson here which traps the textbox's KeyPress event:
http://www.cpearson.com/excel/TextBox.htm

HTH
Colin