I need to create code to place text 2 lines below the total. The total line
is one space below the data. I never know how many lines will be used as
this changes each month and for each file.
The text needs to go into clomun E.
Sched Prin
curt prin
int on curt
net sched int
prepayment penalty
losses and recoveries
stop advances
misc remit adjust

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1

Assume you mean two blank rows, then the text:

With cells(rows.count,"E").End(xlup)(4)

.Value = _
"Sched Prin" & chr(10) & "curt prin" & chr(10) & _
"int on curt" & chr(10) & "net sched int" & chr(10) & _
"prepayment penalty" & chr(10) & "losses and recoveries" & _
chr(10) & "stop advances" & chr(10) & _
"misc remit adjust"
.WrapText = True
.EntireRow.Autofit
End With

--
Regards,
Tom Ogilvy

"jln via OfficeKB.com" wrote:

> I need to create code to place text 2 lines below the total. The total line
> is one space below the data. I never know how many lines will be used as
> this changes each month and for each file.
> The text needs to go into clomun E.
> Sched Prin
> curt prin
> int on curt
> net sched int
> prepayment penalty
> losses and recoveries
> stop advances
> misc remit adjust
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200611/1
>
>

Im sorry tom i need each them to go into their own cell.

Tom Ogilvy wrote:
>Assume you mean two blank rows, then the text:
>
>With cells(rows.count,"E").End(xlup)(4)
>
> .Value = _
>"Sched Prin" & chr(10) & "curt prin" & chr(10) & _
>"int on curt" & chr(10) & "net sched int" & chr(10) & _
>"prepayment penalty" & chr(10) & "losses and recoveries" & _
>chr(10) & "stop advances" & chr(10) & _
>"misc remit adjust"
> .WrapText = True
> .EntireRow.Autofit
>End With
>
>> I need to create code to place text 2 lines below the total. The total line
>> is one space below the data. I never know how many lines will be used as
>[quoted text clipped - 8 lines]
>> stop advances
>> misc remit adjust

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1

Sub ABC()

With Cells(Rows.Count, "E").End(xlUp)(4)

.Resize(8, 1).Value = _
Application.Transpose(Split( _
"Sched Prin" & Chr(10) & _
"curt prin" & Chr(10) & _
"int on curt" & Chr(10) & _
"net sched int" & Chr(10) & _
"prepayment penalty" & Chr(10) & _
"losses and recoveries" & Chr(10) & _
"stop advances" & Chr(10) & _
"misc remit adjust", Chr(10)))
End With
End Sub

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

> Assume you mean two blank rows, then the text:
>
> With cells(rows.count,"E").End(xlup)(4)
>
> .Value = _
> "Sched Prin" & chr(10) & "curt prin" & chr(10) & _
> "int on curt" & chr(10) & "net sched int" & chr(10) & _
> "prepayment penalty" & chr(10) & "losses and recoveries" & _
> chr(10) & "stop advances" & chr(10) & _
> "misc remit adjust"
> .WrapText = True
> .EntireRow.Autofit
> End With
>
> --
> Regards,
> Tom Ogilvy
>
> "jln via OfficeKB.com" wrote:
>
> > I need to create code to place text 2 lines below the total. The total line
> > is one space below the data. I never know how many lines will be used as
> > this changes each month and for each file.
> > The text needs to go into clomun E.
> > Sched Prin
> > curt prin
> > int on curt
> > net sched int
> > prepayment penalty
> > losses and recoveries
> > stop advances
> > misc remit adjust
> >
> > --
> > Message posted via OfficeKB.com
> > http://www.officekb.com/Uwe/Forums.a...mming/200611/1
> >
> >

Thanks again Tom for the help.

Tom Ogilvy wrote:
>Sub ABC()
>
>With Cells(Rows.Count, "E").End(xlUp)(4)
>
> .Resize(8, 1).Value = _
>Application.Transpose(Split( _
> "Sched Prin" & Chr(10) & _
> "curt prin" & Chr(10) & _
> "int on curt" & Chr(10) & _
> "net sched int" & Chr(10) & _
> "prepayment penalty" & Chr(10) & _
> "losses and recoveries" & Chr(10) & _
> "stop advances" & Chr(10) & _
> "misc remit adjust", Chr(10)))
>End With
>End Sub
>
>> Assume you mean two blank rows, then the text:
>>
>[quoted text clipped - 22 lines]
>> > stop advances
>> > misc remit adjust

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200611/1

Here's a slight variation on Tom's excellent idea:

Sub Demo()
Dim M As Variant
M = Array( _
"Sched Prin", _
"Curt Prin", _
"Int on Curt", _
"Net Sched Int", _
"Prepayment Penalty", _
"Losses and Recoveries", _
"Stop Advances", _
"Misc Remit Adjust")

Cells(Rows.Count, "E").End(xlUp)(4). _
Resize(UBound(M) + 1) = WorksheetFunction.Transpose(M)

End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Office 2003


"jln via OfficeKB.com" <u25956@uwe> wrote in message
news:6954f3d7ca848@uwe...
> Im sorry tom i need each them to go into their own cell.
>
> Tom Ogilvy wrote:
>>Assume you mean two blank rows, then the text:
>>
>>With cells(rows.count,"E").End(xlup)(4)
>>
>> .Value = _
>>"Sched Prin" & chr(10) & "curt prin" & chr(10) & _
>>"int on curt" & chr(10) & "net sched int" & chr(10) & _
>>"prepayment penalty" & chr(10) & "losses and recoveries" & _
>>chr(10) & "stop advances" & chr(10) & _
>>"misc remit adjust"
>> .WrapText = True
>> .EntireRow.Autofit
>>End With
>>
>>> I need to create code to place text 2 lines below the total. The total
>>> line
>>> is one space below the data. I never know how many lines will be used
>>> as
>>[quoted text clipped - 8 lines]
>>> stop advances
>>> misc remit adjust
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200611/1
>

Tom what would be the best way to theses 3 appear with red text?

"losses and recoveries"
"stop advances"
"misc remit adjust"


Tom Ogilvy wrote:
>Sub ABC()
>
>With Cells(Rows.Count, "E").End(xlUp)(4)
>
> .Resize(8, 1).Value = _
>Application.Transpose(Split( _
> "Sched Prin" & Chr(10) & _
> "curt prin" & Chr(10) & _
> "int on curt" & Chr(10) & _
> "net sched int" & Chr(10) & _
> "prepayment penalty" & Chr(10) & _
> "losses and recoveries" & Chr(10) & _
> "stop advances" & Chr(10) & _
> "misc remit adjust", Chr(10)))
>End With
>End Sub
>
>> Assume you mean two blank rows, then the text:
>>
>[quoted text clipped - 22 lines]
>> > stop advances
>> > misc remit adjust

--
Message posted via http://www.officekb.com