Reduce duplicates vba code with variables

[james potter]

Hello,

I have made a vba code which will run a macro when a certain cell is being modify. I have duplicates this code for several cell in the sheet. In the code only the absolute reference of the cell change. Is there a way to make those reference variable so that there less code necessary?

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


If Target.Address = "$M$7" Then
          Application.EnableEvents = False
          Target = Now
          Application.EnableEvents = True
     Call cobras_b7
End If


Sub cobras_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 = "Cobras Charts " & 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) & "text"
    .Display
    SendKeys "^{end}"
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub


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) & "text"
    .Display
    SendKeys "^{end}"

 

[Andrew Poulsom]

From what you have posted it's not clear what you want to make variable.

 

[james potter]

I see it too. I was not making meself clear.

I would like to make the cell reference variable. I have make the cell references bold in the code below. I think the first two are difficult to make variable, but the subs report & cobras can be made variable if I able to put the cellnumber/row in the memory of the code. How can I do that?

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 


If Target.Address = "$M$7" Then 
          Application.EnableEvents = False 
          Target = Now 
          Application.EnableEvents = True 
     Call cobras_b7 
End If 


Sub cobras_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 = "Cobras Charts " & 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) & "text" 
    .Display 
    SendKeys "^{end}" 
End With 
Set objMail = Nothing 
Set objOL = Nothing 
End Sub 


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) & "text" 
    .Display 
    SendKeys "^{end}"

 

[james potter]

please help make my vba code shorter

I'll make another attempt to make myself clear.

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 tiggered by changes in a certain cell.

This is the vba-code to trigger to call the macro.

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

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 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 (macro trigger) in memory, like a name. Then in the second part vba-code 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