macro to add an auto serial number column
 

Hi,
Need help with VB codes.
I am extracting data from a worksheet form into a data collection worksheet.
The worksheet has pre-existing rows of headers and labels.

I want to auto generate the serial numbers in column A of the data
collection worksheet. I am stuck trying to establish the first serial number
as "1".

Is it possible to code such that:
If the cell above is in column A < "integer" then value in active cell = 1
I get a syntex error with the following:-

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

If historyWks.Cells(nextRow, "A").Offset(-1, 0) < integer Then
historyWks.Cells(nextRow, "A").Value = 1
Else
historyWks.Cells(nextRow, "A").Value = historyWks.Cells(nextRow,
"A").Offset(-1, 0).Value + 1
End If

'Perhaps there is a better way to do this. Appreciate some help.

macro to add an auto serial number column
 

hi guys.
Manage to resolve the "integer" boolean statement with IsNumeric.

Luckily I do not use numbers as the label for column A. Otherwise it would
not work.

Here is how I coded it:

With historyWks

'Adding a serial numbers into column A starting with value 1
If IsNumeric(historyWks.Cells(nextRow, "A").Offset(-1, 0))
Then
historyWks.Cells(nextRow, "A").Value = historyWks.Cells(nextRow,
"A").Offset(-1, 0).Value + 1
Else
historyWks.Cells(nextRow, "A").Value = 1
End If
oCol = 2
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

"Blubber" wrote:

Hi,
Need help with VB codes.
I am extracting data from a worksheet form into a data collection worksheet.
The worksheet has pre-existing rows of headers and labels.

I want to auto generate the serial numbers in column A of the data
collection worksheet. I am stuck trying to establish the first serial number
as "1".

Is it possible to code such that:
If the cell above is in column A < "integer" then value in active cell = 1
I get a syntex error with the following:-

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

If historyWks.Cells(nextRow, "A").Offset(-1, 0) < integer Then
historyWks.Cells(nextRow, "A").Value = 1
Else
historyWks.Cells(nextRow, "A").Value = historyWks.Cells(nextRow,
"A").Offset(-1, 0).Value + 1
End If

'Perhaps there is a better way to do this. Appreciate some help.

macro to add an auto serial number column
 

Glad you found your problem, I was about to say that you were going to have
problems if the column was initially empty, since it would find row 1 as the
result of the .End(xlUp).Row statement and there is no row 0 so later when
you used the .Offset(-1,0) function, it would fail.

"Blubber" wrote:

hi guys.
Manage to resolve the "integer" boolean statement with IsNumeric.

Luckily I do not use numbers as the label for column A. Otherwise it would
not work.

Here is how I coded it:

With historyWks

'Adding a serial numbers into column A starting with value 1
If IsNumeric(historyWks.Cells(nextRow, "A").Offset(-1, 0))
Then
historyWks.Cells(nextRow, "A").Value = historyWks.Cells(nextRow,
"A").Offset(-1, 0).Value + 1
Else
historyWks.Cells(nextRow, "A").Value = 1
End If
oCol = 2
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

"Blubber" wrote:

Hi,
Need help with VB codes.
I am extracting data from a worksheet form into a data collection worksheet.
The worksheet has pre-existing rows of headers and labels.

I want to auto generate the serial numbers in column A of the data
collection worksheet. I am stuck trying to establish the first serial number
as "1".

Is it possible to code such that:
If the cell above is in column A < "integer" then value in active cell = 1
I get a syntex error with the following:-

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

If historyWks.Cells(nextRow, "A").Offset(-1, 0) < integer Then
historyWks.Cells(nextRow, "A").Value = 1
Else
historyWks.Cells(nextRow, "A").Value = historyWks.Cells(nextRow,
"A").Offset(-1, 0).Value + 1
End If

'Perhaps there is a better way to do this. Appreciate some help.

macro to add an auto serial number column
 

Wow, that did not occur to me. However I have permanantly defined labels in
the worksheet so I am ok.

Thanks A mil JLatham.

"JLatham" wrote:

Glad you found your problem, I was about to say that you were going to have
problems if the column was initially empty, since it would find row 1 as the
result of the .End(xlUp).Row statement and there is no row 0 so later when
you used the .Offset(-1,0) function, it would fail.

"Blubber" wrote:

hi guys.
Manage to resolve the "integer" boolean statement with IsNumeric.

Luckily I do not use numbers as the label for column A. Otherwise it would
not work.

Here is how I coded it:

With historyWks

'Adding a serial numbers into column A starting with value 1
If IsNumeric(historyWks.Cells(nextRow, "A").Offset(-1, 0))
Then
historyWks.Cells(nextRow, "A").Value = historyWks.Cells(nextRow,
"A").Offset(-1, 0).Value + 1
Else
historyWks.Cells(nextRow, "A").Value = 1
End If
oCol = 2
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

"Blubber" wrote:

Hi,
Need help with VB codes.
I am extracting data from a worksheet form into a data collection worksheet.
The worksheet has pre-existing rows of headers and labels.

I want to auto generate the serial numbers in column A of the data
collection worksheet. I am stuck trying to establish the first serial number
as "1".

Is it possible to code such that:
If the cell above is in column A < "integer" then value in active cell = 1
I get a syntex error with the following:-

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

If historyWks.Cells(nextRow, "A").Offset(-1, 0) < integer Then
historyWks.Cells(nextRow, "A").Value = 1
Else
historyWks.Cells(nextRow, "A").Value = historyWks.Cells(nextRow,
"A").Offset(-1, 0).Value + 1
End If

'Perhaps there is a better way to do this. Appreciate some help.