Greetings folks. Lets say in B1:M1 I have
"1/31/2008","2/29/2008"..."12/31/2008". Simply the last day of each month
for 2008, as headers for the rest of the rows. In B2:M20000 I have integer
values starting in various months. In A2:A20000 I want to calculate the date
in row 1 minus an integer value, 120, I enter in A1 based on the first column
for each row with a value entered. Let's say, for example, that in row 2 the
first column where I have a value populated is G2, or June. What I need to
do is have a formula subtract A1 from 6/30/2008. This would be easy, but the
problem is that the first month I have a value populated varies for each row.
I could have column K be the first month with a value, in which case I would
need the formula to calculate 10/31/2008 - A1. Does this make sense? My
layout is for scheduling jobs, and I need to calculate a material need date
based on the start date. Thank you.

I beilieve this would locate your beginning column for each row.
I incorporated it into a For...Next loop to check the status of column
A cells. I assume some of those might already have dates entered.

For i = 2 To 2000
If Cells(i, 1) = "" Then
If Cells(i, 2) = "" Then
x = Cells(i, 2).End(xlToRight).Column
Cells(i, 1) = Cells(1, x) - A1
Else
Cells(i, 1) = Cells(1, 2) - A1
End If
End If
next

"Greg Snidow" wrote:

Greetings folks. Lets say in B1:M1 I have
"1/31/2008","2/29/2008"..."12/31/2008". Simply the last day of each month
for 2008, as headers for the rest of the rows. In B2:M20000 I have integer
values starting in various months. In A2:A20000 I want to calculate the date
in row 1 minus an integer value, 120, I enter in A1 based on the first column
for each row with a value entered. Let's say, for example, that in row 2 the
first column where I have a value populated is G2, or June. What I need to
do is have a formula subtract A1 from 6/30/2008. This would be easy, but the
problem is that the first month I have a value populated varies for each row.
I could have column K be the first month with a value, in which case I would
need the formula to calculate 10/31/2008 - A1. Does this make sense? My
layout is for scheduling jobs, and I need to calculate a material need date
based on the start date. Thank you.

I had a typo in th e x = line. Change it to the one below.

x = Cells(i, 1).End(xlToRight).Column

"Greg Snidow" wrote:

Greetings folks. Lets say in B1:M1 I have
"1/31/2008","2/29/2008"..."12/31/2008". Simply the last day of each month
for 2008, as headers for the rest of the rows. In B2:M20000 I have integer
values starting in various months. In A2:A20000 I want to calculate the date
in row 1 minus an integer value, 120, I enter in A1 based on the first column
for each row with a value entered. Let's say, for example, that in row 2 the
first column where I have a value populated is G2, or June. What I need to
do is have a formula subtract A1 from 6/30/2008. This would be easy, but the
problem is that the first month I have a value populated varies for each row.
I could have column K be the first month with a value, in which case I would
need the formula to calculate 10/31/2008 - A1. Does this make sense? My
layout is for scheduling jobs, and I need to calculate a material need date
based on the start date. Thank you.

Thanks JLGWhiz. My programming knowledge is limited to Access and SQL
Server. Where do I put this code in Excel? I am assuming there is a place
to put a private sub, like Access?

"JLGWhiz" wrote:

I beilieve this would locate your beginning column for each row.
I incorporated it into a For...Next loop to check the status of column
A cells. I assume some of those might already have dates entered.

For i = 2 To 2000
If Cells(i, 1) = "" Then
If Cells(i, 2) = "" Then
x = Cells(i, 2).End(xlToRight).Column
Cells(i, 1) = Cells(1, x) - A1
Else
Cells(i, 1) = Cells(1, 2) - A1
End If
End If
next

"Greg Snidow" wrote:

Greetings folks. Lets say in B1:M1 I have
"1/31/2008","2/29/2008"..."12/31/2008". Simply the last day of each month
for 2008, as headers for the rest of the rows. In B2:M20000 I have integer
values starting in various months. In A2:A20000 I want to calculate the date
in row 1 minus an integer value, 120, I enter in A1 based on the first column
for each row with a value entered. Let's say, for example, that in row 2 the
first column where I have a value populated is G2, or June. What I need to
do is have a formula subtract A1 from 6/30/2008. This would be easy, but the
problem is that the first month I have a value populated varies for each row.
I could have column K be the first month with a value, in which case I would
need the formula to calculate 10/31/2008 - A1. Does this make sense? My
layout is for scheduling jobs, and I need to calculate a material need date
based on the start date. Thank you.

Ok, I found the VB editor, and how to enter a new sub, but I am missing how
to relate it to my cells. I am used to just clicking and dragging down. Is
there something I have to do to "activate" the sub?

"JLGWhiz" wrote:

I beilieve this would locate your beginning column for each row.
I incorporated it into a For...Next loop to check the status of column
A cells. I assume some of those might already have dates entered.

For i = 2 To 2000
If Cells(i, 1) = "" Then
If Cells(i, 2) = "" Then
x = Cells(i, 2).End(xlToRight).Column
Cells(i, 1) = Cells(1, x) - A1
Else
Cells(i, 1) = Cells(1, 2) - A1
End If
End If
next

"Greg Snidow" wrote:

Greetings folks. Lets say in B1:M1 I have
"1/31/2008","2/29/2008"..."12/31/2008". Simply the last day of each month
for 2008, as headers for the rest of the rows. In B2:M20000 I have integer
values starting in various months. In A2:A20000 I want to calculate the date
in row 1 minus an integer value, 120, I enter in A1 based on the first column
for each row with a value entered. Let's say, for example, that in row 2 the
first column where I have a value populated is G2, or June. What I need to
do is have a formula subtract A1 from 6/30/2008. This would be easy, but the
problem is that the first month I have a value populated varies for each row.
I could have column K be the first month with a value, in which case I would
need the formula to calculate 10/31/2008 - A1. Does this make sense? My
layout is for scheduling jobs, and I need to calculate a material need date
based on the start date. Thank you.