Unique reference in Column B not increasing by 1 each time

[learning1984]

Hiya,

this is driving me mad!

I have this code, which worked fine when my unique reference was transferrign to column A, but the log has changed and Column A is used for buttons and such, so the log in effect starts at column B. ive changed all the rows in the userform transfer code, but each time I submit the form, the unique reference stays at TUSCMI 000, before it increased by one for each new entry, ie TUSCMI 001 etc

Code I have is shown below

Function NextNumber(aRange As Range) As Long
NextRow = Range("B" & Rows.Count).End(xlUp).Row + 1
End Function

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim emptyRow As Long
'Make Log active
log.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("b:b")) + 1
'Transfer information
Cells(emptyRow, 3).Value = TextBox7.Value
Cells(emptyRow, 4).Value = TextBox78.Text
Cells(emptyRow, 5).Value = ComboBox9.Value
Cells(emptyRow, 6).Value = ComboBox8.Value
Cells(emptyRow, 7).Value = TextBox9.Value
Cells(emptyRow, 8).Value = ComboBox10.Value
Cells(emptyRow, 9).Value = ComboBox1.Value
Cells(emptyRow, 10).Value = ComboBox2.Value
Cells(emptyRow, 11).Value = ComboBox3.Value
Cells(emptyRow, 12).Value = ComboBox14.Value
Cells(emptyRow, 13).Value = ComboBox12.Value
Cells(emptyRow, 14).Value = ComboBox13.Value
Cells(emptyRow, 15).Value = TextBox5.Value
Cells(emptyRow, 16).Value = TextBox6.Value
Cells(emptyRow, 17).Value = TextBox13.Value
Cells(emptyRow, 19).Value = ComboBox11.Value
Cells(emptyRow, 20).Value = TextBox10.Value
Cells(emptyRow, 21).Value = TextBox11.Value
Cells(emptyRow, 22).Value = TextBox12.Value
Cells(emptyRow, 23).Value = TextBox14.Value
Cells(emptyRow, 24).Value = TextBox15.Value
Cells(emptyRow, 25).Value = TextBox16.Value
Cells(emptyRow, 26).Value = TextBox17.Value
Cells(emptyRow, 27).Value = TextBox18.Value
Cells(emptyRow, 28).Value = TextBox19.Value
Cells(emptyRow, 29).Value = TextBox20.Value
Cells(emptyRow, 30).Value = TextBox21.Value
Cells(emptyRow, 31).Value = TextBox22.Value
Cells(emptyRow, 32).Value = TextBox23.Value
Cells(emptyRow, 33).Value = TextBox24.Value
Cells(emptyRow, 34).Value = TextBox25.Value
Cells(emptyRow, 35).Value = TextBox29.Value
Cells(emptyRow, 36).Value = TextBox28.Value
Cells(emptyRow, 37).Value = TextBox27.Value
Cells(emptyRow, 38).Value = TextBox26.Value
Cells(emptyRow, 39).Value = TextBox31.Value
Cells(emptyRow, 40).Value = TextBox34.Value
Cells(emptyRow, 41).Value = TextBox30.Value
Cells(emptyRow, 42).Value = TextBox33.Value
Cells(emptyRow, 43).Value = TextBox32.Value
Cells(emptyRow, 44).Value = TextBox36.Value
Cells(emptyRow, 45).Value = TextBox37.Value
Cells(emptyRow, 46).Value = TextBox38.Value
Cells(emptyRow, 47).Value = TextBox39.Value
Cells(emptyRow, 48).Value = TextBox40.Value
Cells(emptyRow, 49).Value = TextBox41.Value
Cells(emptyRow, 50).Value = TextBox45.Value
Cells(emptyRow, 51).Value = TextBox44.Value
Cells(emptyRow, 52).Value = TextBox43.Value
Cells(emptyRow, 53).Value = TextBox42.Value
Cells(emptyRow, 54).Value = TextBox35.Value
Cells(emptyRow, 55).Value = TextBox49.Value
Cells(emptyRow, 56).Value = TextBox46.Value
Cells(emptyRow, 57).Value = TextBox48.Value
Cells(emptyRow, 58).Value = TextBox47.Value
Cells(emptyRow, 59).Value = TextBox50.Value
Cells(emptyRow, 60).Value = TextBox51.Value
Cells(emptyRow, 61).Value = TextBox52.Value
Cells(emptyRow, 62).Value = TextBox53.Value
Cells(emptyRow, 63).Value = TextBox54.Value
Cells(emptyRow, 64).Value = TextBox57.Value
Cells(emptyRow, 65).Value = TextBox56.Value
Cells(emptyRow, 66).Value = TextBox55.Value
Cells(emptyRow, 67).Value = TextBox69.Value
Cells(emptyRow, 68).Value = TextBox68.Value
Cells(emptyRow, 69).Value = TextBox67.Value
Cells(emptyRow, 70).Value = TextBox66.Value
Cells(emptyRow, 71).Value = TextBox62.Value
Cells(emptyRow, 72).Value = TextBox63.Value
Cells(emptyRow, 73).Value = TextBox64.Value
Cells(emptyRow, 74).Value = TextBox65.Value
Cells(emptyRow, 75).Value = TextBox77.Value
Cells(emptyRow, 76).Value = TextBox76.Value
Cells(emptyRow, 77).Value = TextBox75.Value
Cells(emptyRow, 78).Value = TextBox74.Value
Cells(emptyRow, 79).Value = TextBox71.Value
Cells(emptyRow, 80).Value = TextBox72.Value
Cells(emptyRow, 81).Value = TextBox70.Value
Cells(emptyRow, 82).Value = TextBox73.Value
Cells(emptyRow, 83).Value = TextBox58.Value
Cells(emptyRow, 84).Value = TextBox59.Value
Dim theRange As Range, theCell As Range, nextLong As Long
Set theRange = log.Range("b4", log.Range("b" & Rows.Count).End(xlUp))
Set theCell = log.Range("b" & Rows.Count).End(xlUp).Offset(1)
nextLong = NextNumber(theRange)
theCell.Value = nextLong
theCell.NumberFormat = """TUSCMI"" 000"
Unload Me

End Sub

can anybody see where I am going wrong