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

 

[mumps]

Try:

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("W10") = .Range("T" & firstrow & ":T" & lastRow).Address
    End With
End Sub

 

[julhs]

Thank you Mumps'

Your additional one line:-

.Range("W10") = .Range("T" & firstrow & ":T" & lastRow).Address

was the key, but particularly

 " & lastRow).Address”

I had adapted & tested umpteen similar versions, BUT I NEVER hit on the key part in yours,

 ” & lastRow).Address”

As a by the way I will resort to using “sht.Range” rather than just “ .Range”, because I KNOW in 12 months I will be scratching my head over the ref of “.Range”.
This will also mean ALL my various codes use the same ref format.
Again many thanks!

 

[mumps]

You are very welcome.

 

[julhs]

Other point I failed to make was that your version completely DOES AWAY for the need to “Copy” to the Clipboard and then have to paste it back into W10 ( this point is ONLY meant for future readers)

 

[julhs]

Sorry to come back to you on this having marked it as solved (not sure if this is breaching forum rules?)
But I have realised I need an additional aspect to the code.

Having established firstrow and lastrow of the range, I need to find the ACTUAL used range of Col S WITHIN that range.
(eg firstrow = S9, lastrow = T20, actual used range of ColS = S9:S15)

Or put another way, find the last USED cell in Col S BETWEEN the lastrow ColT and firstrow ColS
I think looking up ColS from lastrow is best as that will overcome the presence of any rouge blank cell ColS

 

[mumps]

Try:

Sub MeUsedRangetest()
    Dim firstrow As Long
    With ActiveSheet
        firstrow = .Range("S:S").Find(what:="CF rules", LookIn:=xlValues, LookAt:=xlWhole).Row
        .Range("W10") = .Range("S" & firstrow).CurrentRegion.Address
    End With
End Sub

 

[julhs]

Done some testing.
Based on “CF rules” being in S7, that version is returning:-
$R$6:$S$8 (WITH blank cells in S8 & S9 and first value in S10)
WITHOUT, blank cells in S8 & S9 and the first value in S10, it returns $A$6:$S$337
Now; $S$337 IS the ACTUAL last used cell/row in the range between “firstrow = .Range("S:S").Find(what:="CF rules”…….and “lastRow = .Range("T:T").Find(what:="Cash Paid"……..
But I need to return $S$10:$S$337 as the ACTUAL used range, and not $A$6:$S$337.
Because as I’m going to be CLEARING the range identified, the result of $A$6:$S$337 instead of $S$10:$S$337 is going to be a bit of a disaster.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet showing the different scenarios. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[julhs]

Fully appreciate request for Xl2bb or equivalent.
Been desperately trying to avoid that as the active sheet I’m running test on is the REAL sheet (A1:AT600), absolutely FULL of formula/condition format/blank cells/merged cell, you name it!!!
So all I can do right now is defer/delay your suggestion to suppling (Xl2bb……) until tomorrow as it’s going to take me a while to condense it down.
Fully understand that without Xl2bb…. it makes your task THAT much more difficult or nay on impossible!!