Insert Rows based on Countif results

[NBVC]

Hi everyone,

I am trying to insert rows based on the result of Countif's. How would I go about doing this using VBA....still trying to grasp many VBA concepts.

In Sheet1 I have a bunch of names in A1:A30, many are duplicates.

In Sheet2 I have a unique list of names that are found in sheet 1. This list is in A1:A5

Starting at A10, I want to insert the name in A1 and insert rows below = the number of times that name is found in Sheet1!A1:A30. Immediately following I will insert next name and repeat procedure until all names have been cycled through.

Thanks in advance.

 

[Gates Is Antichrist]

Free advice, certainly worth its cost in gold

Don't dismiss the macro recorder. No "copout" shame either - even gurus can real real quickly get function names, syntaxes, etc. by adapting (or just looking at) the generated code. Keep in mind that it extremely often merely gives a starting point that would suck if untweaked.

Beating in a topic I began earlier: get fluent with range operations (properties). With Range and its .Offset you can directly manipulate any cell there is. Now, why am I running this point into the ground? Because in the above coding hard-coding is stinking up the place. Slay it while it's young! Using named ranges (and offsets where necessary) is a way you can combat hard-coding.

 

[NBVC]

Thanks very much for the advice....I'm slowly getting it all in. I've even got started reading some good books on VBA. Just finding it difficult to put 2 and 2 together sometimes.

 

[Gates Is Antichrist]

Note MrExcel's own book - sounds like just the right one for you

 

[Norie]

Gates

I've been having a wee look at this thread and I noticed you said the use of Resize was 'unconventional'. Why?

That's probably the same route I would have taken.

I was going to try some code for this, but I was a little confused by NBVC's original post, but that's probably me.

 

[Gates Is Antichrist]

Resize would seem the command to resize an array, not the command to populate it. However, since you can say e.g.
Range("MyRange") = 2
and it would fill in the existing 10 cells of the range, and
Range("MyRange").Resize(20, 1) = 2 'fills 20
or Range("MyRange").Resize(20, 3) = 2 'fills 60
then, yes, Resize really is doing nothing more than resizing. However it "looks" like you're using resize to populate it.

A more ccnventional - okay, make that just "simpler for simpletons to follow" - way would be
Range("MyRange").Resize(20, 3)
Range("MyRange") = 2 ' populate entire range with this

So okay, point taken. How about a "mass assignment via .Resize" usage of Resize?

 

[Norie]

Gates

I'm still a little confused at what you are trying to say.

When I first started using Excel VBA I'll admit I was a little confused by things like Offset/Resize, but I think I've got a handle on them now.

 

[NBVC]

When I first started using Excel VBA I'll admit I was a little confused by things like Offset/Resize, but I think I've got a handle on them now.

That's refreshing to hear I thought maybe I should just give up.

That means we all have a chance

 

[Gates Is Antichrist]

Norie:

You are right, I am wrong.

Range("MyRange").Resize(20, 3)
Range("MyRange") = 2 ' populate entire range with this

can be optimized to
Range("MyRange").Resize(20, 3) = 2 ' populate entire range with this

This is not a unique usage of Resize. You are right, I am wrong.

This is a not-immediately-intuitive-to-simply-brained-cretins-like-me usage of compact code. But not unique.

 

[Krishnakumar]

Thanks very much Kris!

Pretty much what I was looking for.

Now, how would I update the macro to auto insert a formula in column B adjacent to each entry made in column A....I am trying to understand your line of code:

If i > 0 Then StartCell.Resize(i, 1) = Ce

for argument's sake...how would i insert formula: =If(Left(A(i)="J","Y","N")

where A(i) is the occupied cells in column A

Hi,

Sub test()
Dim ws1     As Worksheet
Dim ws2     As Worksheet
Dim i       As Byte, StartCell  As Range
Dim nList   As Range, Ce        As Range

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set nList = ws2.Range("A1:A4")
Set StartCell = ws2.Range("A8")

For Each Ce In nList
    i = Application.WorksheetFunction.CountIf(ws1.Columns(1), Ce)
    If i > 0 Then
        With StartCell
            .Offset(0, 0).Resize(i, 1).Value = Ce
            .Offset(, 1).Resize(i, 1).FormulaR1C1 = "=IF(LEFT(RC[-1],1)=""J"",""Y"",""N"")"
            .Offset(, 1).Resize(i, 1).Value = .Offset(, 1).Resize(i, 1).Value
        End With
        Set StartCell = StartCell.Offset(i, 0)
    End If
Next Ce
End Sub

HTH

 

[NBVC]

Thanks very much again, Kris.

This is great!

If I could ask, out of curiosity what exactly does this statement do:

.Offset(, 1).Resize(i, 1).Value = .Offset(, 1).Resize(i, 1).Value

Whether I comment it or not, it seems to give the same result.