using lastrow perhaps

[Danny54]

i have the following code in a vba macro which works just find if I always insert the two cell numbers before executing the macro. (where the values are)

N25 and M21

My intent is to build a dynamic string and store it in Column A:Lastrow+10


Can I somehow make them dynamic and use the last row counter?


Cells(LastRow + 10, "A").Value = "=N25 & " workstations missing " & M21 & " approved tickets. "

Thanks

 

[alansidman]

If I am understanding correctly then

[COLOR=#333333]Cells(LastRow + 10, "A").Value = Range("N25") & " workstations missing " & Range("M21") & " approved tickets. "[/COLOR]

 

[Danny54]

what i need to have changed dynamically is the n25 and n21. Something like this "N" & LastRow. So when the content of my report changes, the output line will change as well

 

[Joe4]

You can get last row with data in column N like this:

Dim lr as Long
lr = Cells(Rows.Count, "N").End(xlUp).Row

Then replace your:

Range("N25")

reference with:

Range("N" & lr)

 

[Danny54]

Thanks,

my code looks like

Dim lr As Long
lr = Cells(Rows.Count, "N").End(xlUp).Row


Cells(LastRow + 10, "A").Value = _
"=Range(""N"" & lr) & "" workstations missing "" & M21 & "" approved tickets. """




The macro runs successfully but the report doesn't resolve the formula

the cell is marked

#Name ?

selecting edit formula in formula bar - it shows

=Range("N" & lr) & " workstations missing " & M21 & " approved tickets. "


??????

 

[Joe4]

You have double-quotes in there, like you are populating a formula, not a value (which is not correct):

[COLOR=#333333]Cells(LastRow + 10, "A").Value = _[/COLOR]
[COLOR=#333333]"=Range(""N"" & lr) & "" workstations missing "" & M21 & "" approved tickets. """[/COLOR]

You need to write it like Alan did in his post. All you need to do is change the Range reference I showed you how to do in his line of code.

 

[Danny54]

Thanks all.

sometimes its hard to see the forest for the trees. I was making it too hard.

 

[Joe4]

You are welcome.