Hi all, I am new to excel, so pardon my ignorance. I did a search, but
still can't figure this out:

1. Suppose I have many rows of dates: "MAY99", "JUN99", etc. Each row
always referes to the third Friday of the month. I want to add a column
to the file: the number of days from the starting day. So if we set the
starting date to be 05/01/99, for the row of "MAY99", let's say the
third Friday of MAY of 1999 is May 20th. Then for this new column, the
entry for the new column is 20. (Ideally, I want to count in business
days, rather than calendar days)

How can I do that?

2. In other language, to acess certain row of an array is easy: A[i], I
can do a loop on i. For example:
i = 1,4,7,10,etc.

But how can I do this in excel/VBA? Let's say I want to refer to cell
A1, A4, A7, A10, etc? (as in VBA: i write "A8" not "A[8]" where "8" can
cange, so i find it hard.

Thanks a lot, appreciate your help!

The third Friday of May99 is 5/21/99.

Courtesy of Chip Pearson:
http://www.cpearson.com/excel/DateTimeWS.htm

where A1 = 5/1/1999 (to which you apply a custom number format of MMMYY), to
find the date of the 3rd Friday
=DATE(YEAR(A1),MONTH(A1),1+((3-(6=WEEKDAY(A1)))*7)+(6-WEEKDAY(A1)))

Number of days from the starting date
=DATE(YEAR(A1),MONTH(A1),1+((3-(6=WEEKDAY(A1)))*7)+(6-WEEKDAY(A1)))-A1

Number of business days:
=NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1),1+((3-(6=WEEKDAY(A1)))*7)+(6-WEEKDAY(A1))))


Check help for Networkdays. There is an optional argument to exclude
holidays, but you will need to set that up.



" wrote:
[i]
Hi all, I am new to excel, so pardon my ignorance. I did a search, but
still can't figure this out:

1. Suppose I have many rows of dates: "MAY99", "JUN99", etc. Each row
always referes to the third Friday of the month. I want to add a column
to the file: the number of days from the starting day. So if we set the
starting date to be 05/01/99, for the row of "MAY99", let's say the
third Friday of MAY of 1999 is May 20th. Then for this new column, the
entry for the new column is 20. (Ideally, I want to count in business
days, rather than calendar days)

How can I do that?

2. In other language, to acess certain row of an array is easy: A, I
can do a loop on i. For example:
i = 1,4,7,10,etc.

But how can I do this in excel/VBA? Let's say I want to refer to cell
A1, A4, A7, A10, etc? (as in VBA: i write "A8" not "A[8]" where "8" can
cange, so i find it hard.

Thanks a lot, appreciate your help!

To answer your 2nd question, to create a range in VBA with a variable for the
row part, use, for example:

Range("A"&i)

--
Regards,
Fred


wrote in message
ps.com...[i]
Hi all, I am new to excel, so pardon my ignorance. I did a search, but
still can't figure this out:

1. Suppose I have many rows of dates: "MAY99", "JUN99", etc. Each row
always referes to the third Friday of the month. I want to add a column
to the file: the number of days from the starting day. So if we set the
starting date to be 05/01/99, for the row of "MAY99", let's say the
third Friday of MAY of 1999 is May 20th. Then for this new column, the
entry for the new column is 20. (Ideally, I want to count in business
days, rather than calendar days)

How can I do that?

2. In other language, to acess certain row of an array is easy: A, I
can do a loop on i. For example:
i = 1,4,7,10,etc.

But how can I do this in excel/VBA? Let's say I want to refer to cell
A1, A4, A7, A10, etc? (as in VBA: i write "A8" not "A[8]" where "8" can
cange, so i find it hard.

Thanks a lot, appreciate your help!

I forgot there was a second part to your question - which Fred has addressed
(and I will assume you probably know how to set his suggestion up in a
For/Next loop).

You might also check to see if SpecialCells method can help (if there is
something particular about the cells you want to work with that sets them
apart from other cells - say you want to loop through the cells in A1:A10
that are visible)

Sub test()
Dim rngcell As Range

For Each rngcell In Sheet1.Range("A1:A10").SpecialCells(xlCellTypeVisi ble)
MsgBox rngcell.Address
Next rngcell
End Sub






"JMB" wrote:
[i]
The third Friday of May99 is 5/21/99.

Courtesy of Chip Pearson:
http://www.cpearson.com/excel/DateTimeWS.htm

where A1 = 5/1/1999 (to which you apply a custom number format of MMMYY), to
find the date of the 3rd Friday
=DATE(YEAR(A1),MONTH(A1),1+((3-(6=WEEKDAY(A1)))*7)+(6-WEEKDAY(A1)))

Number of days from the starting date
=DATE(YEAR(A1),MONTH(A1),1+((3-(6=WEEKDAY(A1)))*7)+(6-WEEKDAY(A1)))-A1

Number of business days:
=NETWORKDAYS(A1,DATE(YEAR(A1),MONTH(A1),1+((3-(6=WEEKDAY(A1)))*7)+(6-WEEKDAY(A1))))


Check help for Networkdays. There is an optional argument to exclude
holidays, but you will need to set that up.



" wrote:

Hi all, I am new to excel, so pardon my ignorance. I did a search, but
still can't figure this out:

1. Suppose I have many rows of dates: "MAY99", "JUN99", etc. Each row
always referes to the third Friday of the month. I want to add a column
to the file: the number of days from the starting day. So if we set the
starting date to be 05/01/99, for the row of "MAY99", let's say the
third Friday of MAY of 1999 is May 20th. Then for this new column, the
entry for the new column is 20. (Ideally, I want to count in business
days, rather than calendar days)

How can I do that?

2. In other language, to acess certain row of an array is easy: A, I
can do a loop on i. For example:
i = 1,4,7,10,etc.

But how can I do this in excel/VBA? Let's say I want to refer to cell
A1, A4, A7, A10, etc? (as in VBA: i write "A8" not "A[8]" where "8" can
cange, so i find it hard.

Thanks a lot, appreciate your help!