making cell reference variable

james potter

New Member
Joined
Aug 15, 2005
Messages
45
I already had a topic related to this topic but I think I was not making myself easy to understand. So I'll make another attempt.

First what will my vba-code do for me. It generates a email message where certains fields are filled with value of cells in my worksheet. This macro is triggered by changes in a certain cell.

This is the vba-code to trigger to call the macro. (part1)
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
     If Target.Address = "$K$7" Then 
          Application.EnableEvents = False 
          Target = Now 
          Application.EnableEvents = True 
     Call report_b7

This next part is the actual vba-code of sub report_b7 (part2)
Code:
Sub report_b7() 
Dim objOL As New Outlook.Application 
Dim objMail As MailItem 
Set objOL = New Outlook.Application 
Set objMail = objOL.CreateItem(olMailItem) 
With objMail 
    .To = Format(Range("$J$7")) 
    .Subject = "text" & Format(Range("$B$7")) 
    .Body = "text" & Chr(13) & Chr(13) & "text" & Format(Range("$B$7")) & _ 
    "text" & Chr(13) & Chr(13) & Chr(13) & "text" _ 
    & Chr(13) & "Afdeling Reporting" 
    .Display 
    SendKeys "^{end}" 
End With 
Set objMail = Nothing 
Set objOL = Nothing 
End Sub

I would like to make this second part (part2) vba-code cell reference independent. As you can see, is every cell reference in the same row. So I think there is a way to put the cell reference of the first vba-code (part1) in memory, like a name. Then in the second part vba-code (part2) I can refer to this name in combination with offset.

I think it's possible to do, but I do not know how. I have tried but failed.

Any help is appriciate!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
     If Target.Address = "$K$7" Then 
          Application.EnableEvents = False 
          Target = Now 
          Application.EnableEvents = True 
     Call report_b7(Target)
'rest of code

Sub report_b7(Rng As Range) 
Dim objOL As New Outlook.Application 
Dim objMail As MailItem 
Set objOL = New Outlook.Application 
Set objMail = objOL.CreateItem(olMailItem) 
With objMail 
    .To = Format(Rng.Offset(0, -1)) 
    .Subject = "text" & Format(Rng.Offset(0, -9)) 
    .Body = "text" & Chr(13) & Chr(13) & "text" & Format(Rng.Offset(0, -9)) & _ 
    "text" & Chr(13) & Chr(13) & Chr(13) & "text" _ 
    & Chr(13) & "Afdeling Reporting" 
    .Display 
    SendKeys "^{end}" 
End With 
Set objMail = Nothing 
Set objOL = Nothing 
End Sub
 

Forum statistics

Threads
1,136,878
Messages
5,678,301
Members
419,753
Latest member
Sallylwy

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
Top