How to get a value to call a function in excel

Question

I am trying to get a value return to run a call function in Excel

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

ws.Range("A" & LastRow).Value = "=MAX(A2:A" + CStr(ActiveCell.Row - 1) + ")+1"    'Adds the ID into Col A & Last Blank Row"

My current code is:

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" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

ws.Range("A" & LastRow).Value = "=MAX(A2:A" + CStr(ActiveCell.Row - 1) + ")+1"    'Adds the ID into Col A & Last Blank Row"
ws.Range("B" & LastRow).Value = Environ("userdomain") & "\" & Environ("username") 'Adds the UserName into Col B & Last Blank Row
ws.Range("C" & LastRow).Value = Format(Now(), "dd/mm/yyyy hh:mm") 'Adds the Date_Time into Col C & Last Blank Row
ws.Range("D" & LastRow).Value = Sheets("Form").Range("C5") 'Adds the Name into Col D & Last Blank Row
ws.Range("E" & LastRow).Value = Sheets("Form").Range("C7") 'Adds the Service into Col E & Last Blank Row
ws.Range("F" & LastRow).Value = Sheets("Form").Range("F7") 'Adds the Team into Col F & Last Blank Row
ws.Range("G" & LastRow).Value = Sheets("Form").Range("C9") 'Adds the Cycle into Col G & Last Blank Row
ws.Range("H" & LastRow).Value = Sheets("Form").Range("F5") 'Adds the Date into Col H & Last Blank Row
ws.Range("I" & LastRow).Value = Sheets("Form").Range("C11") 'Adds the Section into Col I & Last Blank Row
ws.Range("J" & LastRow).Value = Sheets("Form").Range("C13") 'Adds the Functionality into Col J & Last Blank Row
ws.Range("K" & LastRow).Value = Sheets("Form").Range("C15") 'Adds the Form into Col K & Last Blank Row
ws.Range("L" & LastRow).Value = Sheets("Form").Range("C17") 'Adds the Report into Col L & Last Blank Row
ws.Range("M" & LastRow).Value = Sheets("Form").Range("C19") 'Adds the System1_ID into Col M & Last Blank Row
ws.Range("N" & LastRow).Value = Sheets("Form").Range("F19") 'Adds the System2_ID into Col N & Last Blank Row
ws.Range("O" & LastRow).Value = Sheets("Form").Range("B22") 'Adds the Comment into Col O & Last Blank Row


End Sub

Any help would be greatly appreciated.

Answer 1

Replace CStr(ActiveCell.Row - 1) with CStr(LastRow - 1)