using lastrow perhaps

Danny54

Active Member
Joined
Jul 3, 2019
Messages
295
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
If I am understanding correctly then

Code:
[COLOR=#333333]Cells(LastRow + 10, "A").Value = Range("N25") & " workstations missing " & Range("M21") & " approved tickets. "[/COLOR]
 
Last edited:
Upvote 0
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
 
Upvote 0
You can get last row with data in column N like this:
Code:
Dim lr as Long
lr = Cells(Rows.Count, "N").End(xlUp).Row

Then replace your:
Code:
Range("N25")
reference with:
Code:
Range("N" & lr)
 
Last edited:
Upvote 0
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. "


??????
 
Upvote 0
You have double-quotes in there, like you are populating a formula, not a value (which is not correct):
Code:
[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.
 
Upvote 0
Solution
Thanks all.

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

Forum statistics

Threads
1,224,884
Messages
6,181,569
Members
453,053
Latest member
Kiranm13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top