Place a word in a specific cell

[vba317]

I have a spreadsheet that I calculate data every day on. I need the word current to be placed in a specific cell about 22 less than the last row. I have tried a couple of combinations of code that don't work. I was hoping for some help. Here is what I have:
Note: R5 is the calculation of the cell where I want the word to go.

R1 = ActiveSheet.UsedRange.Rows.Count
    R2 = "F" & R1
    'R3 = "A" & R1
    R4 = R1 - 17
    R5 = "I" & R4
    SR = R1 - 22
    ActiveWindow.ScrollRow = SR
    Range(R2).Select
    
    Worksheets("wbResults").Range("R5").Value = "Current"

 

[HarleyQuinn]

How about something like (no need for the other variables unless something else needs them):

Worksheets("wbResults").Cells(ActiveSheet.UsedRange.Rows.Count - 22, 6) = "Current"

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[SkipVought]

Hi,

Assuming that the data in column R is contiguous and begins in R1...

[R1].end(xlDown).offset(-22).value = "Current"

or

with activesheet.usedrange
  .cells(.row+.rows.count-23,"R").value = "Current"
end with

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

How on earth did I read that meaning the OP wanted it to go in column F?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

http://lessthandot.com

 

[SkipVought]

Harley,

Your solution assumes that the used range includes row 1, which may not necessarily be true.

And I should have included the Workbook and sheet objects

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I guess you wanted to replace this:
Worksheets("wbResults").Range("R5").Value = "Current"
with this ?
Worksheets("wbResults").Range(R5).Value = "Current"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I ran the following code:

Worksheets("wbResults").Cells(ActiveSheet.UsedRange.Rows.Count - 22, 6) = "Current"

I got a runtime error '9' Subscript out of range

 

[SkipVought]

maybe...

With Worksheets("wbResults")
  .Cells(.UsedRange.Rows.Count - 22, 6).value = "Current"
end with
[/code
This assumes that the usedrange begins in row .



Skip,
[sup][glasses]Don't let the [b]Diatribe[/b]...
talk you to death![tongue][/sup][sub]
[glasses]Just traded in my old subtlety...
[b]for a NUANCE![/b][tongue][/sub]