Copy MessageBox returned value to a cell

[julhs]

How can I copy the returned range address that’s displayed in a MessageBox to a cell on the same sheet.
Having established what the used range is with the code below, I want to put that returned range address into a cell.
eg-: so W10 will = the rng.Address
Have tried numerous bits found on line, to no avail.
Have tried; copy to ClipBoard using Ctrl-C, then use Ctrl-V to paste in cell, but its pasting ALL the Msg box properties but I ONLY want the Range Address.

Sub MeUsedRangetest()
Dim lastRow As Long, firstrow As Long
Dim rng As Range
Dim sht As Worksheet
Dim sMsg As String

Set sht = ThisWorkbook.ActiveSheet
      firstrow = sht.Range("s:s").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row
      lastRow = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row
Set rng = sht.Range("T" & firstrow & ":T" & lastRow)

MsgBox "Range is " & rng.Address

End Sub

 

[julhs]

Sorry for any minor inconsistencies you might come across, but I DON’T believe they impact on the ultimate question?
So:-
firstrow = .Range("s:s").Find(what:="CF rules"….. = S7
lastRow = .Range("T:T").Find(what:="Cash Paid"….. = T24
ACTUAL used range IN ColS is = S10:S20
It’s the ACTUAL used range S10:S20 that I am looking to return the address of (to a cell on the sheet)

I have to use “Cash Paid” row as lastrow reference; because BETWEEN Row 10 and Row “Cash Paid” it is a dynamic range( rows are constantly being added)
There are rows BELOW “Cash paid” in Col T and they have values, but they are NOT relevant so to be ignored. So hence the need to first find “Cash Paid” row and then count UP ColS to find the ACTUAL last used cell/row (so we are ignoring ever thing below “Cash Paid” row in Col T)
I sincerely hope I’ve not rushed my reply and in the proses given you duff/incorrect info ?
Afraid had to resort to screen shot as Xl2bb was having a huge hissy fit

 

[julhs]

sXl2bb has gotten over the hissy fit!

Mumps
Code below is what I ran on the Xl2bb, it contains your new suggestion, but I’ve left lines in from your first version so I could compare the Debug.Print results

Sub MeUsedRangetest()
Dim lastrow As Long, firstrow As Long
With ActiveSheet
firstrow = .Range("s:s").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row
lastrow = .Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Row
.Range("s30") = .Range("S" & firstrow).CurrentRegion.Address
.Range("s32") = .Range("s" & firstrow + 3 & ":s" & lastrow - 1).Address
End With

Debug.Print firstrow
Debug.Print lastrow
Debug.Print Range("s" & firstrow + 3 & ":s" & lastrow - 1).Address
End Sub

Accounts Code TestingBook1.xlsm
	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
5
6		Date	Inv #	Payment Method	Payment Details List	Bank & Cash		Drawings	Purchases of Stock / Materials	Tool,Weather/Safety equip	Repairs & Renewals	Motor Expenses	Hire Charges	Liability Insurance	N.I cont / TGWU	Gen Insur Office Postage/ Stationary	Misc	Acquisition of Assets	Let Property	Bank Charges	Utilities / House	Income Tax
7	s	Leave these 2 row empty				CF rules
8	s					n
9	s					n
10	s	Apr-07		St Order	Rent	694.50															694.50
11	s	Apr-07		D. Debit	Amazon Prime	5.99		5.99
12	s	Apr-07	gs005	My Sol	Heating Oil	540.22															540.22
13	s	Apr-08		D. Debit	EE Mobile(1)	28.21											28.21
14	s	Apr-08		D. Debit	AA membership	39.61											39.61
15	s	Apr-11	gs010	My Sol	Fuel Other	127.00						127.00
16	s	Apr-12	gs015	My Sol	Tesco	29.44		29.44
17	s	Apr-12	gs020	My Sol	Tesco	113.55		113.55
18	s	Apr-13		My Sol	Amazon Prime	7.99		7.99
19	s	Apr-19		D. Debit	EDF energy	16.25															16.25
20	s	Apr-19	gs025	My Sol	Tesco	15.00		15.00
21	s
22	s
23	s
24	s					1617.76	Cash Paid	171.97				127.00					67.82				1250.97
25	s
26	s				Irrelevant	data	in this	area
27	s				vv	hh
28	s				vc	kk
29	s				cv	mm
30					CurrentRegion.Address	$A$6:$AJ$30
31
32					.Range("s" & firstrow + 2 & ":s" & lastrow - 1).Address	$S$10:$S$23
33
April 22 - 23 (3)

Cell Formulas
Range	Formula
S24,U24:AI24	S24	=SUM(S$7:OFFSET(S24,-1,0))
AJ24	AJ24	=SUM(AJ$7:OFFSET(AI24,-1,0))
T25	T25	=SUMIF(Q$7:$Q23,$T24,$S$7:$S26)