View Full Version : Excel ranges don't save as strings
jackdandcoke
10-11-2008, 08:24 AM
I'm getting a data type mismatch error for the following code. Any ideas?
Dim TopStore As String
Dim DMHour As String
Dim DMCumu As String
TopStore = Sheets("DM Rankings").Range("G4:H5").Value
DMHour = Sheets("DM Rankings").Range("D4:E17").Value
DMCumu = Sheets("DM Rankings").Range("A4:B17").Value
When I run the macro it says "Run-Time error '13':
Type mismatch.
georgiboy
10-11-2008, 08:27 AM
I would imagine its because you are using the .Value part.
mdmackillop
10-11-2008, 08:35 AM
You'll need to loop through each cell
Set tmp = Range("C23:C25")
For Each t In tmp
txt = txt & t
Next
MsgBox txt
jackdandcoke
10-11-2008, 08:46 AM
I don't know where to put this in my code then
Set tmp = Range("A4:H17")
For Each t In tmp
txt = txt & t
Next
MsgBox txt
maybe it would help if I put my whole code in
Sub Auto_Open()
If Environ("runme") = "YES" Then
Sheets("Mid Atlantic Lead Event Pivot").Select
Range("A7").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
Sheets("DM Rankings").Select
Range("A9").Select
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
Range("D9").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("G9").Select
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
Dim TopStore As String
Dim DMHour As String
Dim DMCumu As String
TopStore = Sheets("DM Rankings").Range("G4:H5").Value
DMHour = Sheets("DM Rankings").Range("D4:E17").Value
DMCumu = Sheets("DM Rankings").Range("A4:B17").Value
Dim OL As Object
Dim EmailItem As Object
Dim Wb As Object
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook
Wb.Save
With EmailItem
.Importance = olImportanceHigh
.Subject = "Mid Atlantic Lead Entry Webathon Results"
.Body = "Top Store" & vbCrLf & TopStore _
& "Hourly Rankings" & vbCrLf & DMHour _
& "Overall Rankings" & vbCrLf & DMCumu
.To = "me@mycompany.com"
.Attachments.Add Wb.FullName
.Send
End With
Application.ScreenUpdating = True
Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing
End If
End Sub
mdmackillop
10-11-2008, 09:28 AM
Can you post an image of how your email text should appear?
jackdandcoke
10-11-2008, 09:43 AM
Just a really simple e-mail
mikerickson
10-11-2008, 10:40 AM
You might try either Dimming them as Variant or using formulas likeTopStore = Join(Sheets("DM Rankings").Range("G4:H5").Value,", ")
jackdandcoke
10-11-2008, 10:48 AM
Gives me an invalid procedure or argument.
Bob Phillips
10-11-2008, 11:09 AM
See this (http://www.rondebruin.nl/mail/folder3/mail4.htm) to see how to embed ranges in an email.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.