Try:

Value1 = "=IF(" & ActiveCell.Offset(0, 1) & "="""", """", MAX($B$3:" & ActiveCell.Address & ")+1)"