I have a spread sheet with personal records. so for example row 1 is name address and that kind of information. Row 2 is benefit information. Row 3 is spouse benefit information. The sheet contains thousands of people.
What I am trying to do is insert headers for each individual so that the sheet is easy to read. Each line begins with a code. So column A is always 01, 02, 03, or 04. I am trying to make a macro that will recognize what is in column A, insert a line and then fill each row with the proper header.
I have some code but it will only insert a header line over the first "02" row. I'm not sure why it won't loop through the whole spread sheet. Can you help
Sub test()
Dim Counter As Integer
For Counter = Cells(Rows.Count, "A").End(xlUp) To 1 Step -1
If Cells(Counter, 1) = 2 Then
Cells(Counter, 1).EntireRow.Insert
Cells(Counter, 1).FormulaR1C1 = "Record Type"
Cells(Counter, 2).Value = "Process Date/Time"
Cells(Counter, 3).Value = "Customer Number"
Cells(Counter, 4).Value = "Customer Name"
Cells(Counter, 5).Value = "Enrollment Type"
Cells(Counter, 6).Value = "Filler"
End If
Next Counter
End Sub
Help with looping process excel macro
Simon Allardice
Hi Mary
Try the below, your row counter does not give the right end row.
Sub test()
Dim Counter As Integer
Dim LastRow As Long
LastRow = Range("A65500").End(xlUp).Row
For Counter = LastRow To 1 Step -1
If Cells(Counter, 1) = 2 Then
Cells(Counter, 1).EntireRow.Insert
Cells(Counter, 1).FormulaR1C1 = "Record Type"
Cells(Counter, 2).Value = "Process Date/Time"
Cells(Counter, 3).Value = "Customer Number"
Cells(Counter, 4).Value = "Customer Name"
Cells(Counter, 5).Value = "Enrollment Type"
Cells(Counter, 6).Value = "Filler"
End If
Next Counter
End Sub