Posted to microsoft.public.excel.misc
|
|
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.
|