Name finder Macro?

[Gorky]

Hi,

Thanks to all who have helped me on my last question.

I need to know if there is a way of creating a macro to do the following:

Find the number of times a name ocurrs in a column and then on a different sheet in the same workbook, place the number of times into a different column. Preferably without making the first column the same one each time, although this is not a problem.

Therefore if the name Smith appears 5 times in sheet 2 column c then it will place the number 5 in column H cell 2 in sheet 1, and so on down the list of names. Obviously the cells in sheet 1 will change for each person's name.

I suspect that it will involve VLOOKUP, but I'm not well versed enough in Excel to get it right.

I need to do this for multiple names.

Thanks

Gorky

 

[DonkeyOte]

Gorky, why does this need to be in VBA as opposed to using say a COUNTIF on Sheet1, ie

ie Sheet1!H2 formula

=COUNTIF(Sheet2!$C:$C,H$1)

where H1 = "Smith"

 

[Gorky]

Hi,

I'm sorry, but I'm completely self taught on Excel, so my knowledge is very limited and I often do things the long way round.

I'm not sure what your code will do, and how to implement it?

Should this code go in the cell where I'm hoping the number of times the name appears is counted?

Thanks

Gorky

 

[DonkeyOte]

Yep...

I'm assuming that in cell H1 you have a name for which you wish to count occurences in Sheet2 column C... the COUNTIF works along the lines of

COUNTIF(data range, criteria)

so given my assumed layout above, if I want the count of Smith from Sheet2 Column C in Sheet 1 cell H2 and assuming Smith is entered in H1 I would simply enter the following into H2

=COUNTIF(Sheet2!C:C,H1)

Per your example Sheet1 H2 would = 5

 

[Gorky]

Yep, got it!

Thanks so much mate. Christ I need to get me some excel lessons!

 

[Gorky]

Sorry completely different subject!

Is there away of saying in macro "Select this column that I am in" rather than saying "Select column AG"?

By this I mean not having to specify which the column I want copied in the macro but having the macro copy from the column I happen to be in at that time.

Cheers

Gorky

 

[VoG]

You could use

ActiveCell.Columns.EntireColumn.Select

However, you should not need to select the column to work with it.

 

[Gorky]

Hmmm,

What I want to do is copy that column and then paste it somewhere else.

So that should do the trick...

Thanks

Gorky

 

[VoG]

For example

Sub test()
ActiveCell.Columns.EntireColumn.Copy Destination:=Sheets("Sheet3").Range("A1")
Application.CutCopyMode = False
End Sub

 

[Gorky]

Thanks, that works a treat!