Copying a function for each row in a table | Excel forum

Hi,

I am trying to add some functions to a table at the press of a button to prepare it for DB import. The functions must copy down to the last row of the table which is variable.

Everything works fine until i exceed 30,000 rows and then i receive 'error 6' Overflow.

I have only tested this with the one function and for information I am taking the last three characters of a user ID as this is the unique section.

I have declared the function as a string as it contains letters and numerals.

Please see code below, i have commented out the line i wished to use and hard-coded a number which works fine until it exceeds 30k rows.

Sub addID()
intTargetRowCount = 2

For intRowCount = 1 To 100
'Sheets("Data").Range("A2").CurrentRegion.Rows.Count

Sheets("Data").Cells(intTargetRowCount, 11) = _
muws_id(Sheets("Data").Cells(intTargetRowCount, 1))

intTargetRowCount = intTargetRowCount + 1

Next intRowCount
End Sub

Any help on this error would be appriciated.