I have used the following code to put the formula in a specific column all
the way to the last cell. It is working good, except it misses the last row.
Is there a way to force it to go one row extra. Any help will be
apprecaited. Thanks!


Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"

Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count,
5).End(xlUp)).offset(1,0)


HTH
--
AP

"Jasmine" a écrit dans le message de
news: ...
I have used the following code to put the formula in a specific column all
the way to the last cell. It is working good, except it misses the last
row.
Is there a way to force it to go one row extra. Any help will be
apprecaited. Thanks!


Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"

Set r = ActiveSheet.UsedRange

nLastRow = r.Rows.Count + r.Row - 1
yields the last row
nOneStepBeyond = r.Rows.Count + r.Row
the next row
--
Gary's Student


"Jasmine" wrote:

I have used the following code to put the formula in a specific column all
the way to the last cell. It is working good, except it misses the last row.
Is there a way to force it to go one row extra. Any help will be
apprecaited. Thanks!


Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"

Use an offset kinda like this...

Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count,
5).End(xlUp).offset(1,0))
End With
rng.Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"
--
HTH...

Jim Thomlinson


"Jasmine" wrote:

I have used the following code to put the formula in a specific column all
the way to the last cell. It is working good, except it misses the last row.
Is there a way to force it to go one row extra. Any help will be
apprecaited. Thanks!


Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"

I think that will offset the entire range, not just add one more row to it...
I think the offset needs to be inside the final bracket...
--
HTH...

Jim Thomlinson


"Ardus Petus" wrote:

Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count,
5).End(xlUp)).offset(1,0)


HTH
--
AP

"Jasmine" a écrit dans le message de
news: ...
I have used the following code to put the formula in a specific column all
the way to the last cell. It is working good, except it misses the last
row.
Is there a way to force it to go one row extra. Any help will be
apprecaited. Thanks!


Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"

You are quite right! I misread the original code.
Should be:
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp).offset(1,0))

Sorry,
--
AP

"Jim Thomlinson" a écrit dans le
message de news: ...
I think that will offset the entire range, not just add one more row to
it...
I think the offset needs to be inside the final bracket...
--
HTH...

Jim Thomlinson


"Ardus Petus" wrote:

Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count,
5).End(xlUp)).offset(1,0)


HTH
--
AP

"Jasmine" a écrit dans le message de
news: ...
I have used the following code to put the formula in a specific column
all
the way to the last cell. It is working good, except it misses the last
row.
Is there a way to force it to go one row extra. Any help will be
apprecaited. Thanks!


Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"

It looks like you should be using another column besides E to get that last
row???

Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp))
End With
with rng
.resize(.rows.count+1).Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"
end with

Is one more way.

Jasmine wrote:

I have used the following code to put the formula in a specific column all
the way to the last cell. It is working good, except it misses the last row.
Is there a way to force it to go one row extra. Any help will be
apprecaited. Thanks!

Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"

--

Dave Peterson

Thank you! That worked great!

"Ardus Petus" wrote:

Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count,
5).End(xlUp)).offset(1,0)


HTH
--
AP

"Jasmine" a écrit dans le message de
news: ...
I have used the following code to put the formula in a specific column all
the way to the last cell. It is working good, except it misses the last
row.
Is there a way to force it to go one row extra. Any help will be
apprecaited. Thanks!


Dim rng As Range
With Worksheets("CIP Exceptions")
Set rng = .Range(.Cells(5, 5), .Cells(Rows.Count, 5).End(xlUp))
End With
rng.Offset(0, 1).Formula = "=TEXT(E5,""mmmm"")"