Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a cell for a value in VBA code
Below I have 2 lines of a code I am using. I have a value
in cell A1 on sheet2 anda value in cell A2 an sheet2. I would like to specify for the first code to look in cell A1 sheet2 to get the value "Your Stats" instead of hard coding it in the actual VBA code like done below. I would also like to specifiy the second code to look in cell A2 sheet2 to get the value "Here are your stats " instead of hard coding it in the VBA code. How do I do this? Thank you Todd Huttenstine objMessage.Subject = "Your Stats" objMessage.Text = "Here are your stats " & cell.Offset(0, 1).Value |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a cell for a value in VBA code
Todd,
objMessage.Subject = Worksheets("Sheet2").Range("A1").Value objMessage.Text = Worksheets("Sheet2").Range("A2").Value & cell.Offset(0, 1).Value -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below I have 2 lines of a code I am using. I have a value in cell A1 on sheet2 anda value in cell A2 an sheet2. I would like to specify for the first code to look in cell A1 sheet2 to get the value "Your Stats" instead of hard coding it in the actual VBA code like done below. I would also like to specifiy the second code to look in cell A2 sheet2 to get the value "Here are your stats " instead of hard coding it in the VBA code. How do I do this? Thank you Todd Huttenstine objMessage.Subject = "Your Stats" objMessage.Text = "Here are your stats " & cell.Offset(0, 1).Value |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing a cell for a value in VBA code
here is the current code I am using. I used the concept
Bob suggested but it didnt work. I keep getting a debug error on that part. I need it to look in that specific range and its not working. Any suggestions? Sub EmailNow() ' ' EmailNow Macro ' Macro recorded 11/16/2003 by SEGeneric ' ' Sheets("Team Management Database").Select Dim cell As Range, cell2 As Range Dim shRng As Range Dim sh As Worksheet, sharr() As String Dim wb As Workbook Dim i As Integer Dim objSession As Object, objMessage As Object, objOneRecip As Object Dim objAttachmt As Object Set sh = ThisWorkbook.Sheets("Team Management Database") i = 1 For Each cell In sh.Range("a3", Range("a3").End(xlDown)) Set shRng = cell.Offset(0, 9) ReDim sharr(1 To shRng.Offset(0, 50).End (xlToLeft).Column - _ shRng.Column + 1) For Each cell2 In sh.Range(shRng, shRng.Offset(0, 50).End(xlToLeft)) sharr(i) = cell2.Value i = i + 1 Next cell2 ThisWorkbook.Sheets(sharr).Copy Set wb = ActiveWorkbook wb.SaveAs Filename:="C:\Sheets.xls" Set objSession = CreateObject("MAPI.Session") objSession.Logon Set objMessage = objSession.Outbox.Messages.Add 'HERE IS WHERE THE PROBLEM STARTS objMessage.Subject = Worksheets("Team Data").Range ("K1").Value objMessage.Text = Worksheets("Team Data").Range ("K2").Value & cell.Offset(0, 'HERE IS WHERE THE PROBLEM ENDS 1).Value Set objAttachmt = objMessage.Attachments.Add objAttachmt.Source = ("C:\Sheets.xls") Set objOneRecip = objMessage.Recipients.Add objOneRecip.Name = cell.Offset(0, 2).Value objOneRecip.Type = 1 objOneRecip.Resolve objMessage.Send showDialog:=False objSession.Logoff wb.Close savechanges:=False i = 1 Kill "c:\sheets.xls" Next cell End Sub -----Original Message----- Todd, objMessage.Subject = Worksheets("Sheet2").Range ("A1").Value objMessage.Text = Worksheets("Sheet2").Range ("A2").Value & cell.Offset(0, 1).Value -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below I have 2 lines of a code I am using. I have a value in cell A1 on sheet2 anda value in cell A2 an sheet2. I would like to specify for the first code to look in cell A1 sheet2 to get the value "Your Stats" instead of hard coding it in the actual VBA code like done below. I would also like to specifiy the second code to look in cell A2 sheet2 to get the value "Here are your stats " instead of hard coding it in the VBA code. How do I do this? Thank you Todd Huttenstine objMessage.Subject = "Your Stats" objMessage.Text = "Here are your stats " & cell.Offset (0, 1).Value . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Cell Referencing | Excel Discussion (Misc queries) | |||
Referencing Cell Next To Today's Date Cell | Excel Discussion (Misc queries) | |||
Cell Referencing Code Error? | Excel Worksheet Functions | |||
Code referencing the system clock? | Excel Programming |