Hi all,
So, I currently have a sheet which dynamically calculates some ranges, based on a few countifs and a bit of concatenation. This spits out a reference for a range that I need to copy and paste into an e-mail. However, I'm struggling to find a way to feed this into my VBA script. The black spot is the follow:
Any ideas how I can get this done? I can give a little more detail if this is ambiguous.Dim rng As Range
Set rng = Worksheets("Merge").Range("D2").Value
Cheers
Hi
Do you mean this:
Please Login or Register to view this content.
where is teh reference value that you want currently saved? if it is in a cell you could do something like this:
Please Login or Register to view this content.
If you liked my solution, please click on the Star -- to add to my reputation
If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.
So, the broader context is that I'm trying to e-mail several people all the records relevant to them.
The ranges that contain thae data are calculated through a few countifs and a bit of tweaking, ultimately spiting out something like Data!B2:D34.
My endgame is to write a loop of this code, sending the relevant ranges of information to everyone on the list.The calculation is necessary because this is a weekly task based on a changing data-set.
I've tried subbing my line of code with both of the above and it gives me 'Application or runtime defined error', code 1004.
Post the code so we don't have to guess. Sentences like: The ranges that contain thae data are calculated through a few countifs and a bit of tweaking, ultimately spiting out something like Data!B2:D34. Are not translatable into VB code.
Ben Van Johnson
The sentence you highlight isn't VB code - that's a rough description of where the value that I want to feed into the code is derived from. I gave that for context. What's important is that it produces a string version of a cell range in another worksheet - which appears like Data!B2:D34.
The specific bit of VBA code that was broken was what I highlighted. But, if you think it'll help, this is the whole script:
Sub Send_Report()
Dim MTo As String
Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Set rng = Worksheets("Merge").Range(Worksheets("Merge").Range("D2").Value)
MTo = Range("B2")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
MsgBox (rng & MTo)
On Error Resume Next
With OutMail
.To = MTo
.CC = ""
.BCC = ""
.Subject = "Lies"
.HTMLBody = RangetoHTML(rng)
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
if the output you get is "Data!B2:B34" then you will need to modify that, in excel that is only a string, we need to turn that into a range. to do that in VBA we specify the sheet, so we will need to drop the "Data!" portion, as we will only use the B2:B34 portion. you can do this either in your original formula that generates it, or afterwards in VBA. try something like this:
Please Login or Register to view this content.
Hi,
I've tried plugging in the code you gave, modified slightly, and unfortunately it's still giving the error. This seems to happen whenever I try to get it to do the ".Value" way of reading something when it's setting a value declared as a range. I think what I'm effectively trying to do is to find a way of getting VBA to behave like the INDIRECT() formula by treating a string as a reference.
I've attached the whole workbook this time.
try this
you can use this code to test:Please Login or Register to view this content.
Please Login or Register to view this content.
Please, in the future, remember [CODE][/CODE] tags.
Then try:
Please Login or Register to view this content.
Gentlemen, a massive thank you so for your collective assistance. The problem is now solved.
The issue was that in my code I'd asked it to perform:
In the interest of demonstrating that it was picking up the correct value.Please Login or Register to view this content.
Once I'd applied some of the suggested techniques above (all work, incidentally!) and removed the offending line, it all works beautifully.
Last edited by Sccye; 04-02-2012 at 10:27 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks