VBA Help - Code that will update a value with the header name

[Johnny Thunder]

Hello all,

I have a small grid of data that has dollar amounts in the data, I am trying to have a macro loop thru each column A:F and look at all rows of data in each column and replace any values with the header name. See my example of the Grid below. The code will need to ignore blank cells and move on to the next. The Column A can be used to determine the last row with data. Of course this is a small sample of what I need but I should be able to make any changes to make it scale for my actual data set.

Any help is appreciated. Thanks! ?

Original Data

_____xl2bb.xlam
	A	B	C	D	E	F
1		Yellow	Blue	Black	Red	Purple
2	Map 1	1500	3000	6000	12000	24000
3	Map 2	3000	6000	12000	24000	48000
4	Map 3		200		350
5	Map 4	2500	5000	10000	20000	40000
Sheet2

Desired result

_____xl2bb.xlam
	A	B	C	D	E	F
8		Yellow	Blue	Black	Red	Purple
9	Map 1	Yellow	Blue	Black	Red	Purple
10	Map 2	Yellow	Blue	Black	Red	Purple
11	Map 3		Blue		Red
12	Map 4	Yellow	Blue	Black	Red	Purple
Sheet2

 

[Fluff]

Is you data in B:F hard values or formulae?

 

[Johnny Thunder]

Just hard values. Thanks for looking into this Fluff!

 

[Fluff]

OK, how about

Sub JohnnyThunder()
   With Range("B2:F" & Range("A" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlConstants).FormulaR1C1 = "=r1c"
      .Value = .Value
   End With
End Sub

 

[Johnny Thunder]

That worked great! Thank you so much fluff.

 

[Fluff]

You're welcome & thanks for the feedback