Posts by RichardSchollar

Re: A Young Michael Phelps

:lol: :lol: :lol:

Re: "excel 4.0 Macro" Warning

There are a number of other XLM4 functions that can be used in defined names like EVALUATE and LINKS (in fact there are an awful lot of them). Are you also searching in hidden workbook names? If not, then checking any such names may be worthwhile too...

Re: "excel 4.0 Macro" Warning

Do you have any defined names using XLM4 functions? Such names could use XLM4 like GET.CELL and GET.DOCUMENT. If you want to search in code, then you just need to search for the text "ExecuteExcel4".

Richard

Re: Professional Development: The Definitive Guide ...

Now I feel a little differently on Excel 2007 Programmer's Ref: it's a good book, but I would rather have PED. Maybe this is because I also have 2002 Programmer's Ref and don't use Excel 2007 a great deal? I currently don't view the 2007 version of Programmer's Ref as being essential...

Re: Welcome Back Dave

Good to have you & Ozgrid back Dave!

Work is like a fine red wine - it should be stored away in the dark until you are ready to sample it

Re: Calculating Time Elapsed Between Two Date And Time Stamps

I don't understand I'm afraid - the formula will do for any 2 date/time values used in Excel.

The formatting of the result is another issue altogether

Re: Calculating Time Elapsed Between Two Date And Time Stamps

Hi

Something wrong with just:

=B1-A1

if end is in B1 and start in A1?

Richard

Re: Select Visible Cells; Nothing To Select

Hi

Dim rng As Range
On Error Resume Next
Set rng = Range("F2:H" & lRow1).SpecialCells(xlCellTypeVisible)
On Error Goto 0
If Not rng Is Nothing Then
  'proceed with original clear contents operations
End If

Richard

Re: Avoid Quotation Replacing Carriage Returns Pasting To Notepad

You can do this just by copying what is in the formula bar rather than copying the entire cell ie highlight the text as displayed in the formula bar, Ctrl+C to copy and then Ctrl+V to paste into Notepad.

Richard

Re: Position Of The Last Occurrence Of A Character In A String

You don't need a UDF for that as the native InstrRev VBA function does exactly what you want - look it up in the VBA Help.

Richard

Re: Verify Whether Cell Address Is Valid

Hi Raja

Here's an example function you could use:

Function ValidAddress(strAddress As String) As Boolean
Dim r As Range
On Error Resume Next
Set r = Worksheets(1).Range(strAddress)
If Not r Is Nothing Then ValidAddress = True
End Function

Use in a sub like:

UserAdd = Inputbox("Enter your address")


'check if valid:
If ValidAddress(UserAdd) Then
  'it's valid!
Else
  'it ain't valid!
End if

Richard[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Of course you could just get the address via:

s = Application.InputBox("Enter address", , , , , , , 8)

which will do the validation for you.

Richard

Re: VBE Color Pallet

Ah thanks Tom - that's useful to know.

Richard

Re: VBE Color Pallet

Are userform colors (once suitably modified in the Properties window in the VBE) also restricted to the 56 color pallet of the workbook in which they reside? Anyone know?

Re: Value Error When Linking A Countif Function

Yep, that's a limitation with both Sumif/Countif - the source workbook needs to be open. You can get around this by using Sumproduct instead - Chris in your case this would be:

=SUMPRODUCT(('C:\Users\Chris Edwards\Documents\Work\New Stuff\July\Team 1\[Anna.xls]Input Sheet'!AP$22:AP$671=B6)+0)

Now, I see from your profile you are using xl97. I can't guarantee that this will definitely work in 97 (I'm pretty sure it will) - you will just have to give it a try

Richard

Re: Set Named Range As Constant

Hi Joe

I'm afraid Constants can only hold simple data types eg like a string or a numeric value. Perhaps you could just use a Public variable instead eg declare:

'declarations section of standard module:
Public tpnb_range As Range




'then in ThisWorkbook module:
Private Sub Workbook_Open()
Set tpnb_range =  Worksheets(" SQL").Range("tpnbs") 
End Sub

You can then refer to Worksheets(" SQL").Range("tpnbs") by using tpnb_range.

Richard

Re: Extract Only First Number In String

Hi

Given those values in a cell here is a potential native formula solution:

=LOOKUP(9.999E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0,"-"},A1&"1234567890-")),ROW(INDIRECT("1:"&LEN(A1)))))

Adjust cell reference to suit.

Richard

Re: Sum Value Part Of Cell Only

Hello Mike

Your easiest route would be to use a helper column with a formula like:

=MID(A1,2,255)+0

copied down the rows to extract the numeric portion out of the cell and then sum the results of this.

An alternative would be to use a single formula like:

=SUMPRODUCT(MID(A1:A1000,2,255)+0)

which would avoid the use of a helper column.

Richard

Re: Last Save Date

I would think you can shorten that to:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
     
    Sheet1.Range("A1").Value = Now 'format this cell how you want     
End Sub

Richard

Re: Error 1004 Porting Code From One Computer To Another

Hi

When at home with this file you go (within the VBE) Tools>References do you spot any references labelled MISSING? Something like this has happened to me before moving from one version of Excel to another (because the type libraries are different). However, you are staying within the same version onf Excel/Office, so this may be of no help.

Richard

Re: Function To Write An Array

Hi

This Board (as well as many others) generally don't like to hand hold when what is being asked looks like homework. With this in mind, I won't do the work for you. But I can give pointers.

What is the source of the 2D array of doubles? Is it an existing excel range or is it coming from somewhere else? From my understanding, you just need to take the array and iterate thru it comparing the values at each step and replacing the value with the cap or the floor if it is outside of the limits. Is this correct?

Richard