|
Excel Macro question
Hello, I have a spreadsheet that is supposed to have four entries for
each name. More often than not, it has 1, 2, or 3. I need a macro that will find like entries in a column, count them, and if they do not equal four, I need enough blank rows to equal four. Is it possible? Thanks! Example: Have: Doe, John Eye, Pop Eye, Pop Finch, Atticus Finch, Atticus Finch, Atticus Normal, Abby Normal, Abby Normal, Abby Normal, Abby Need: Doe, John Eye, Pop Eye, Pop Finch, Atticus Finch, Atticus Finch, Atticus Normal, Abby Normal, Abby Normal, Abby Normal, Abby |
Excel Macro question
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long Dim cRows As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = iLastRow To 1 Step -1 cRows = Application.CountIf(.Columns(1), .Cells(i, TEST_COLUMN).Value) If cRows < 4 Then .Rows(i + 1).Resize(4 - cRows).Insert End If i = i - cRows + 1 Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... Hello, I have a spreadsheet that is supposed to have four entries for each name. More often than not, it has 1, 2, or 3. I need a macro that will find like entries in a column, count them, and if they do not equal four, I need enough blank rows to equal four. Is it possible? Thanks! Example: Have: Doe, John Eye, Pop Eye, Pop Finch, Atticus Finch, Atticus Finch, Atticus Normal, Abby Normal, Abby Normal, Abby Normal, Abby Need: Doe, John Eye, Pop Eye, Pop Finch, Atticus Finch, Atticus Finch, Atticus Normal, Abby Normal, Abby Normal, Abby Normal, Abby |
Excel Macro question
On Apr 10, 5:32 pm, "Bob Phillips" wrote:
Yes. The problem is caused by NG wrap-around. Replace cRows = Application.CountIf(.Columns(1), .Cells(i, TEST_COLUMN).Value) with cRows = Application.CountIf(.Columns(1), _ .Cells(i, TEST_COLUMN).Value) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message oups.com... On Apr 10, 6:13 am, "Bob Phillips" wrote: Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long Dim cRows As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = iLastRow To 1 Step -1 cRows = Application.CountIf(.Columns(1), .Cells(i, TEST_COLUMN).Value) If cRows < 4 Then .Rows(i + 1).Resize(4 - cRows).Insert End If i = i - cRows + 1 Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message roups.com... Hello, I have a spreadsheet that is supposed to have four entries for each name. More often than not, it has 1, 2, or 3. I need amacro that will find like entries in a column, count them, and if they do not equal four, I need enough blank rows to equal four. Is it possible? Thanks! Example: Have: Doe, John Eye, Pop Eye, Pop Finch, Atticus Finch, Atticus Finch, Atticus Normal, Abby Normal, Abby Normal, Abby Normal, Abby Need: Doe, John Eye, Pop Eye, Pop Finch, Atticus Finch, Atticus Finch, Atticus Normal, Abby Normal, Abby Normal, Abby Normal, Abby- Hide quoted text - - Show quoted text - Thank you so much for your reply :) I ran the macro and (I am new to this) it returned a compile syntax error with this line highlighted. cRows = Application.CountIf(.Columns(1), .Cells(i, TEST_COLUMN).Value) Is there something I can do?- Hide quoted text - - Show quoted text - Hi Again, Thank you so much for your reply. I did make the change, but now the macro doesn't stop running. If I stop it myself, it leaves me with just one name. |
Excel Macro question
On Apr 11, 11:57 am, wrote:
On Apr 10, 5:32 pm, "Bob Phillips" wrote: Yes. The problem is caused by NG wrap-around. Replace cRows = Application.CountIf(.Columns(1), .Cells(i, TEST_COLUMN).Value) with cRows = Application.CountIf(.Columns(1), _ .Cells(i, TEST_COLUMN).Value) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message roups.com... On Apr 10, 6:13 am, "Bob Phillips" wrote: Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long Dim cRows As Long With ActiveSheet iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = iLastRow To 1 Step -1 cRows = Application.CountIf(.Columns(1), .Cells(i, TEST_COLUMN).Value) If cRows < 4 Then .Rows(i + 1).Resize(4 - cRows).Insert End If i = i - cRows + 1 Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message roups.com... Hello, I have a spreadsheet that is supposed to have four entries for each name. More often than not, it has 1, 2, or 3. I need amacro that will find like entries in a column, count them, and if they do not equal four, I need enough blank rows to equal four. Is it possible? Thanks! Example: Have: Doe, John Eye, Pop Eye, Pop Finch, Atticus Finch, Atticus Finch, Atticus Normal, Abby Normal, Abby Normal, Abby Normal, Abby Need: Doe, John Eye, Pop Eye, Pop Finch, Atticus Finch, Atticus Finch, Atticus Normal, Abby Normal, Abby Normal, Abby Normal, Abby- Hide quoted text - - Show quoted text - Thank you so much for your reply :) I ran the macro and (I am new to this) it returned a compile syntax error with this line highlighted. cRows = Application.CountIf(.Columns(1), .Cells(i, TEST_COLUMN).Value) Is there something I can do?- Hide quoted text - - Show quoted text - Hi Again, Thank you so much for your reply. I did make the change, but now the macro doesn't stop running. If I stop it myself, it leaves me with just one name.- Hide quoted text - - Show quoted text - Hi again, Sorry for the above. I reset the project and retried it and it works perfectly. Thank you so very very much!!! You're the best. :) :) :) |
| All times are GMT +1. The time now is 08:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com