title: How to get a value to call a function in excel
body: <p>I am trying to get a value return to run a call function in Excel</p>
  
  <p>Sub ID_Num is what i want to call on in the first ws.range of Sub SubmitButton, but i cannot work out how to call a function, so i have tried pulling in the code from the function as shown below. This works once and adds a number but after that it stops - something to do with the ActiveCell.Row part</p>
  
  <pre><code>ws.Range("A" &amp; LastRow).Value = "=MAX(A2:A" + CStr(ActiveCell.Row - 1) + ")+1"    'Adds the ID into Col A &amp; Last Blank Row"
  </code></pre>
  
  <p>My current code is:</p>
  
  <pre><code>Sub ID_Num()
  
   Dim LastRow As Long, ws As Worksheet
  
  Set ws = Sheets("Data")
  
  ActiveCell.Formula = "=MAX(A1:A" + CStr(ActiveCell.Row - 1) + ")+1"
  
  ActiveCell.Formula = ActiveCell.Value 'Convert formula to a value
  
  End Sub
  
  
  
  Sub SubmitButton()
  
  Dim LastRow As Long, ws As Worksheet
  
  Set ws = Sheets("Data")
  
  LastRow = ws.Range("A" &amp; Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
  
  ws.Range("A" &amp; LastRow).Value = "=MAX(A2:A" + CStr(ActiveCell.Row - 1) + ")+1"    'Adds the ID into Col A &amp; Last Blank Row"
  ws.Range("B" &amp; LastRow).Value = Environ("userdomain") &amp; "\" &amp; Environ("username") 'Adds the UserName into Col B &amp; Last Blank Row
  ws.Range("C" &amp; LastRow).Value = Format(Now(), "dd/mm/yyyy hh:mm") 'Adds the Date_Time into Col C &amp; Last Blank Row
  ws.Range("D" &amp; LastRow).Value = Sheets("Form").Range("C5") 'Adds the Name into Col D &amp; Last Blank Row
  ws.Range("E" &amp; LastRow).Value = Sheets("Form").Range("C7") 'Adds the Service into Col E &amp; Last Blank Row
  ws.Range("F" &amp; LastRow).Value = Sheets("Form").Range("F7") 'Adds the Team into Col F &amp; Last Blank Row
  ws.Range("G" &amp; LastRow).Value = Sheets("Form").Range("C9") 'Adds the Cycle into Col G &amp; Last Blank Row
  ws.Range("H" &amp; LastRow).Value = Sheets("Form").Range("F5") 'Adds the Date into Col H &amp; Last Blank Row
  ws.Range("I" &amp; LastRow).Value = Sheets("Form").Range("C11") 'Adds the Section into Col I &amp; Last Blank Row
  ws.Range("J" &amp; LastRow).Value = Sheets("Form").Range("C13") 'Adds the Functionality into Col J &amp; Last Blank Row
  ws.Range("K" &amp; LastRow).Value = Sheets("Form").Range("C15") 'Adds the Form into Col K &amp; Last Blank Row
  ws.Range("L" &amp; LastRow).Value = Sheets("Form").Range("C17") 'Adds the Report into Col L &amp; Last Blank Row
  ws.Range("M" &amp; LastRow).Value = Sheets("Form").Range("C19") 'Adds the System1_ID into Col M &amp; Last Blank Row
  ws.Range("N" &amp; LastRow).Value = Sheets("Form").Range("F19") 'Adds the System2_ID into Col N &amp; Last Blank Row
  ws.Range("O" &amp; LastRow).Value = Sheets("Form").Range("B22") 'Adds the Comment into Col O &amp; Last Blank Row
  
  
  End Sub
  </code></pre>
  
  <p>Any help would be greatly appreciated. </p>