Copy MessageBox returned value to a cell

julhs

Active Member
Joined
Dec 3, 2018
Messages
404
Office Version
  1. 2010
Platform
  1. Windows
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.
VBA Code:
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Try:
VBA Code:
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
 
Upvote 0
Solution
Thank you Mumps'

Your additional one line:-
Excel Formula:
.Range("W10") = .Range("T" & firstrow & ":T" & lastRow).Address
was the key, but particularly
Excel Formula:
 " & lastRow).Address”
I had adapted & tested umpteen similar versions, BUT I NEVER hit on the key part in yours,
VBA Code:
 ” & 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!
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
Try:
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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!!
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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