Excuse the code if it cumbersome but I’m still learning.
I’m trying to generate a ID# record number for a row with data in the col_data fields, blank rows would show nothing.
ID#’s would be placed in the 3rd column and identical values placed 2 columns to the right of the col_data_last on the same row.
This code works but only for the first record but I have several hundred more to go. Each record row can have both text and numeral fields.
Can someone HELP me get past the first row or suggest a better way to get the same results.
Variables
I’m trying to generate a ID# record number for a row with data in the col_data fields, blank rows would show nothing.
ID#’s would be placed in the 3rd column and identical values placed 2 columns to the right of the col_data_last on the same row.
This code works but only for the first record but I have several hundred more to go. Each record row can have both text and numeral fields.
Can someone HELP me get past the first row or suggest a better way to get the same results.
Code:
''''record id#
For row_data = row_data_start To row_data_last + 1
For col_data = col_data_start To col_data_last
If Not IsEmpty(Cells(row_data, col_data)) Then
Cells(row_data, 3).Value = "ID# " & Format(Val(row_data - row_data_start) + 1, "0000-" & UCase(Mid(ws.Range("A4"), _
(Application.WorksheetFunction.Find(".", ws.Range("A4"), 1) + 1), 3)))
Cells(row_data, col_data_last + 2).Value = "ID# " & Format(Val(row_data - row_data_start) + 1, "0000-" & UCase(Mid(ws.Range("A4"), _
(Application.WorksheetFunction.Find(".", ws.Range("A4"), 1) + 1), 3)))
Cells(row_data, 3).Interior.Color = RGB(205, 128, 114) 'salmon
Cells(row_data, col_data_last + 2).Interior.Color = RGB(205, 128, 114) 'salmon
Cells(row_data, 3).Borders.LineStyle = xlContinuous
Cells(row_data, col_data_last + 2).Borders.LineStyle = xlContinuous
Else
Cells(row_data, 3).Value = ""
Cells(row_data, col_data_last + 2).Value = ""
Cells(row_data, 3).Interior.Color = RGB(154, 205, 50) 'yellow green
Cells(row_data, col_data_last + 2).Interior.Color = RGB(154, 205, 50) 'yellow green
Cells(row_data, 3).Borders.LineStyle = xlNone
Cells(row_data, col_data_last + 2).Borders.LineStyle = xlNone
End If
Next
NextVariables
Code:
'''Row variable (not affected by data)
Dim row_data_start As Long
row_data_start = 5 'column "C" in 5th row
'''Row variable affected by data
Dim row_data_last As Long 'depends on data records
row_data_last = Cells(Rows.Count, "C").End(xlUp).Row
'''Column variable (not affected by data)
Dim col_data_start As Long
col_data_start = 5 'column "E" in 4th row
'''Column variable affected by data
Dim col_data_last As Long 'last header in 4th row
col_data_last = Cells(4, Columns.Count).End(xlToLeft).Column
