check the header for duplicates

[Shawn23]

Hi,

Is there a way i can check the header if there are any duplicate? If so, I would like to use VBA to change it. For example, both Column D and Column F has the header named "WEEK". I would like Column D now to be "Week 1" and Column F would be "Week 2". This table will be populated, so every time it sees the word "week" in the header it will add one to it. WEEK #

Thank you for your help.

ITEMS	Price	item sold	week	item sold	week	total item sold	total
Books	$2.00	2	$4.00	7	$28.00	9	$32.00
Pens	$1.50	4	$6.00	10	$60.00	14	$66.00
Papers	$0.50	7	$3.50	15	$52.50	22	$56.00

<tbody>
</tbody>

 

[hiker95]

Shawn23,

What about item sold?

 

[hiker95]

Shawn23,

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H
1	ITEMS	Price	item sold	week	item sold	week	total item sold	total
2	Books	$2.00	2	$4.00	7	$28.00	9	$32.00
3	Pens	$1.50	4	$6.00	10	$60.00	14	$66.00
4	Papers	$0.50	7	$3.50	15	$52.50	22	$56.00
5
Sheet1

After the macro:

Excel 2007
	A	B	C	D	E	F	G	H
1	ITEMS	Price	item sold	week 1	item sold	week 2	total item sold	total
2	Books	$2.00	2	$4.00	7	$28.00	9	$32.00
3	Pens	$1.50	4	$6.00	10	$60.00	14	$66.00
4	Papers	$0.50	7	$3.50	15	$52.50	22	$56.00
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub NumberWeek()
' hiker95, 04/13/2013
' http://www.mrexcel.com/forum/excel-questions/697026-check-header-duplicates.html
Dim n As Long, i As Long, fc As Long
n = Application.CountIf(Rows(1), "week")
If n > 1 Then
  For i = 1 To n
    fc = 0
    On Error Resume Next
    fc = Application.Match("week", Rows(1), 0)
    On Error GoTo 0
    Cells(1, fc) = Cells(1, fc) & " " & i
  Next i
End If
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the NumberWeek macro.

 

[Shawn23]

HI Hiker95,

It Works! Thank you so much for helping me out again! You're the best!

 

[hiker95]

Shawn23,

Thanks for the feedback.

You are very welcome. Glad I could help you again.

And, come back anytime.

 

[Shawn23]

Hey Hiker95,

Excuse me for asking so much question. Still learning this language.

n = Application.CountIf(Rows(1), "week")

I know this starts from the beginning of row 1 but how do i specify to start at row one, Column F. Meaning the code will skip column D and start from Column F and so on. Ive tried changing countif but no luck.

Thank you

 

[hiker95]

Shawn23,

Something like?

'from this:
n = Application.CountIf(Rows(1), "week")


'to this:
n = Application.Countif(Range("F1:Z1"),"week")

 

[Shawn23]

Hey Hiker95,

Thats what I had in mind but its not working. Instead of the first "WEEK" column being skipped. its actually the last one being skipped.

 

[hiker95]

Shawn23,

In order to continue:

Can you post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


Even better, so that we can get it right the next time:

You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Shawn23]

before macro

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
5	ITEMS	Price	item sold	week	item sold	week	total item sold	week	week	week	total
6	Books	$ 2.00	2	$ 4.00	7	$ 28.00	9				$ 32.00
7	Pens	$ 1.50	4	$ 6.00	10	$ 60.00	14				$ 66.00
8	Papers	$ 0.50	7	$ 3.50	15	$ 52.50	22				$ 56.00
Sheet3

after macro

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
5	ITEMS	Price	item sold	week 1	item sold	week 2	total item sold	week 3	week 4	week	total
6	Books	$ 2.00	2	$ 4.00	7	$ 28.00	9				$ 32.00
7	Pens	$ 1.50	4	$ 6.00	10	$ 60.00	14				$ 66.00
8	Papers	$ 0.50	7	$ 3.50	15	$ 52.50	22				$ 56.00
Sheet3