Selecting a variable number of cells in a row
 

Beginner questions: I want to write a macro to enter a sequence of monthly
cash flows in a row, say $10 for 5 periods, followed by $15 in the next four
months. If doing it manually, I would select a range of cells in a row for a
Fill | Series operation.

Automating it, I know:

B2= dollar value in first cash flow series
C2= number of months in first cash flow series
B3= dollar value in second cash flow series
C3= number of months in secon cash flow series

1. In the first operation, I want to fill a range, call it 'Range 1' with a
series of values contained in B2. I know the left most cell in the range,
let's say B15, but the number of cells to the right captured in the range
will vary according to data input into C2. So, for example, if B2=$10, and
C2 is '5', then the selected range would be B15:F15, and each cell in the
range would contain the number $10. How is that written in Visual Basic?

2. In the second operation, I am filling the adjacent range of cells to the
right of 'Range 1' above, so in this case, I will not know the location of
the first empty cell in Row 15, until the selection created in 'Range 1' is
filled with the data series. And then, this next range, 'Range 2', needs to
be selected based on a variable contained in cell C3, and filled with the
value in B3, let's say $15.

So if B2=$10 and B3=4, 'Range 1' would be B15:F15, and the cells would
contain the value $10, and 'Range 2' would be G15:J15, and the cells would
contain $15.

Pretty basic, I'm sure, but way over my head.

Selecting a variable number of cells in a row
 

try this
S
ub fill()
For m = 2 To 1 + Range("c2").Value
Cells(15, m).Value = Range("b2").Value
Next
For j = m To m + Range("c3").Value - 1
Cells(15, j).Value = Range("b3").Value
Next
End Sub

"Bob C" wrote:

Beginner questions: I want to write a macro to enter a sequence of monthly
cash flows in a row, say $10 for 5 periods, followed by $15 in the next four
months. If doing it manually, I would select a range of cells in a row for a
Fill | Series operation.

Automating it, I know:

B2= dollar value in first cash flow series
C2= number of months in first cash flow series
B3= dollar value in second cash flow series
C3= number of months in secon cash flow series

1. In the first operation, I want to fill a range, call it 'Range 1' with a
series of values contained in B2. I know the left most cell in the range,
let's say B15, but the number of cells to the right captured in the range
will vary according to data input into C2. So, for example, if B2=$10, and
C2 is '5', then the selected range would be B15:F15, and each cell in the
range would contain the number $10. How is that written in Visual Basic?

2. In the second operation, I am filling the adjacent range of cells to the
right of 'Range 1' above, so in this case, I will not know the location of
the first empty cell in Row 15, until the selection created in 'Range 1' is
filled with the data series. And then, this next range, 'Range 2', needs to
be selected based on a variable contained in cell C3, and filled with the
value in B3, let's say $15.

So if B2=$10 and B3=4, 'Range 1' would be B15:F15, and the cells would
contain the value $10, and 'Range 2' would be G15:J15, and the cells would
contain $15.

Pretty basic, I'm sure, but way over my head.

Selecting a variable number of cells in a row
 

Ben,

Thanks. Worked better than I expected. Was able to copy down to add
subsequent cash flows to the stream.

Next question: Now I want to sum the cash flows, but won't know the range
to sum until the range is built using your sub. I tried the macro recorder,
pointing first to the leftmost cell in the range, then hitting
shift-end-right to select the range, but in the vba editor, the cell
references were absolute. Any more ideas?

Bob.

"ben" wrote:

try this
S
ub fill()
For m = 2 To 1 + Range("c2").Value
Cells(15, m).Value = Range("b2").Value
Next
For j = m To m + Range("c3").Value - 1
Cells(15, j).Value = Range("b3").Value
Next
End Sub

"Bob C" wrote:

Beginner questions: I want to write a macro to enter a sequence of monthly
cash flows in a row, say $10 for 5 periods, followed by $15 in the next four
months. If doing it manually, I would select a range of cells in a row for a
Fill | Series operation.

Automating it, I know:

B2= dollar value in first cash flow series
C2= number of months in first cash flow series
B3= dollar value in second cash flow series
C3= number of months in secon cash flow series

1. In the first operation, I want to fill a range, call it 'Range 1' with a
series of values contained in B2. I know the left most cell in the range,
let's say B15, but the number of cells to the right captured in the range
will vary according to data input into C2. So, for example, if B2=$10, and
C2 is '5', then the selected range would be B15:F15, and each cell in the
range would contain the number $10. How is that written in Visual Basic?

2. In the second operation, I am filling the adjacent range of cells to the
right of 'Range 1' above, so in this case, I will not know the location of
the first empty cell in Row 15, until the selection created in 'Range 1' is
filled with the data series. And then, this next range, 'Range 2', needs to
be selected based on a variable contained in cell C3, and filled with the
value in B3, let's say $15.

So if B2=$10 and B3=4, 'Range 1' would be B15:F15, and the cells would
contain the value $10, and 'Range 2' would be G15:J15, and the cells would
contain $15.

Pretty basic, I'm sure, but way over my head.

Selecting a variable number of cells in a row
 

Sub WriteCashflow()
For i = 1 To Range("C2").Value + Range("C3").Value
If i <= Range("C2").Value Then
Range("B15").Offset(0, i - 1).Value = Range("B2").Value
Else
Range("B15").Offset(0, i - 1).Value = Range("B3").Value
End If
Next

End Sub

--
Regards,
Tom Ogilvy


"Bob C" wrote in message
...
Beginner questions: I want to write a macro to enter a sequence of
monthly
cash flows in a row, say $10 for 5 periods, followed by $15 in the next
four
months. If doing it manually, I would select a range of cells in a row
for a
Fill | Series operation.

Automating it, I know:

B2= dollar value in first cash flow series
C2= number of months in first cash flow series
B3= dollar value in second cash flow series
C3= number of months in secon cash flow series

1. In the first operation, I want to fill a range, call it 'Range 1' with
a
series of values contained in B2. I know the left most cell in the range,
let's say B15, but the number of cells to the right captured in the range
will vary according to data input into C2. So, for example, if B2=$10,
and
C2 is '5', then the selected range would be B15:F15, and each cell in the
range would contain the number $10. How is that written in Visual Basic?

2. In the second operation, I am filling the adjacent range of cells to
the
right of 'Range 1' above, so in this case, I will not know the location of
the first empty cell in Row 15, until the selection created in 'Range 1'
is
filled with the data series. And then, this next range, 'Range 2', needs
to
be selected based on a variable contained in cell C3, and filled with the
value in B3, let's say $15.

So if B2=$10 and B3=4, 'Range 1' would be B15:F15, and the cells would
contain the value $10, and 'Range 2' would be G15:J15, and the cells would
contain $15.

Pretty basic, I'm sure, but way over my head.