Posts by RichardSchollar

Re: Populate Array From Cell Range

Oh didn't realise that - thanks for the info Mike.

Re: New addition to the family

Congratulations Kris!!

Re: Get The Cell Reference From A Given Value

Yes, because the third argument in the Match was 0 (equivalent to False) which tells Match to only return the position of an exact match (thus Match does not assume the list is sorted).

Richard

Re: Get The Cell Reference From A Given Value

=INDEX($A$1:$A$100,MATCH(B1,$A$1:$A$100,0)+7)

Assumes that the number you want to identify is in B1.

Richard

Re: Populate Array From Cell Range

Strictly speaking the count would depend on the number of dimensions, possibly the Option Base of the module, and how the array was created/dimensioned. For a single dimensioned array, the number of elements will only reliably be given by:

lngCntOfElements = UBound(yourArray)-Lbound(yourArray)+1

Richard

Re: Set Value Of Variable When Workbook First Opens

Hi Wigi

It does depend to a certain extend where the variable has been declared - if declared in a standard module then you are correct and an error would result (as we have here). If declared in a class module (such as a sheet module) then you would use a class prefix.

Richard

Re: Set Value Of Variable When Workbook First Opens

If WSOrig is a worksheet, then it *probably* doesn't have a counter property, hence your problem

EDIT: clarified my point.

Re: Find/ Replace Error

If they are already dates, then you have a much better option: just change the number format of the cells eg:

Range("A1:A100").NumberFormat = "yyyymd"

Amend the string number format (ie the "yyyymd" as appropriate).

Richartd

Re: Get Contact Details From Outlook Public Folders

Hi

You need to reference an existing instance of Outlook rather than trying to create a new one if Outlook is already open on the host computer - hence use GetObject rather than CreateObject.

There are certain, albeit minimal - I believe, syntactic differences if using Outlook 2007 than compared to 2003 so this may cause some difficulties too.

Richard

Re: Find/ Replace Error

Hi

What are these meant to be:

What = m / d / yyyy 
    repl = yyyymd

Are you trying to replace a date with another date? VBA will interpret yyyymd as a variable (an undefined one at that by the looks of it) and the same with m and d and yyyy (all undefined).

What exactly are you trying to do (it isn't obvious to me at least beacuse I do not know what data you currently have nor what you are trying to achieve)?

Richard

Re: Formula For Matching Dates And Returning Values

Hi

=INDEX(Data!$B$2:$AE$25,MATCH(Portfolio1!$B$1,Data!$A$2:$A$28,0),MATCH(Portfolio1!$A3,Data!$B$1:$AE$1,0))

In B3 copied down. See attached for the example.

Richard

Re: Sumif Exclude Dates Earlier Than Now

Hi Casey

Try:

=SUMPRODUCT((TEXT(E51:E188,"yymmm")="08Sep")*(F51:F188<>"Closed")*(E51:E188>=TODAY()))

Richard

Re: Run Time Error 424 When Search Term Found

Hi

It's telling you it isn't an object. To create an object stfound you need to use the Set statement (and note you do not try and activate an object unless you are sure it exists):

[B]SET[/B] stfound = Cells.Find(What:=searchterm, After:=Cells(1, 1), MatchCase:=False)

Then you ascertain if search term was found:

If stfound Is Nothing Then

and if it was found, then you activate it:

'continuing on next line from above
  strFound.Activate
Else
  Msgbox "Search term not found!"
End If

Make sense?

Richard

Re: Formula For Number Of Days In A Month

Quote from daddylonglegs

Here's another way

=42-DAY(A1-DAY(A1)+42)

42 can actually be replaced with any number between 32 and 57.....as long as you use the same number twice

Smartass

Re: Find Causes Runtime Error 91 When No Results

Hi

It would be a good idea to get into the habit of using object variables to refer to found instances - this has the added benefit of allowing you to test for an Object being nothing 9ie it wasn't found) and thus avoid runtime errors when you try and activate something which doesn't exist:

Dim rFound As Range


'...


Set rFound = Cells.Find(What:=searchterm, After:=Cells(1, 1), MatchCase:=False)


'then you can test to see if searchterm was actually found:


If Not rFound Is Nothing Then   'if rFound isn't nothing then it's something - ie it was found
  'take appropriate action eg:
  MsgBox "Term found at " & rFound.Address
Else
  'serach term not found - do something else!
  Msgbox "Search term not found!  Exiting Sub..."
  Exit Sub
End If

Richard

Re: Save Copy Of Workbook Without Closing Original

FileDialogs were exposed to VBA in xl2002, so it won't work if using a version below that. You have another very similar option which is to use GetSaveAsFilename method of the Application object. This returns a string:

Dim myFile As String
myFile = Application.GetSaveAsFileName()
If Len(myFile)>0 Then ActiveWorkbook.SaveCopyAs myFile

Richard

Re: Macro Code To Extract From Web Pages

Badger101

There's some good stuff from Tushar Mehta here:

http://www.dailydoseofexcel.co…rnetexplorer-and-xmlhttp/

Richard

Re: Formula For Number Of Days In A Month

Hi Robert

Something like this will work:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Richard

Re: Saving A Copy Of A Workbook

Hi Jeff & Welcome to Ozgrid!

Take a look at the SaveCopyAs method of the Workbook object in the VBA help files.

Richard

Re: Rnd Function Produces Predictable Numbers

What Badger101 was referring to was executing Randomize before you first call the Rnd function - this means a new seed value will be used by Rnd based on the System Clock. Without Randomize, you will get recurrent (ie predictable) returns from Rnd.

Randomize
For i = 1 to 100
  Debug.Print Rnd()
Next i

Richard