UtterAccess Discussion Forums > Appending Text To Each Column Header In Used Range

Full Version: Appending Text To Each Column Header In Used Range

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

corrieann

Apr 25 2012, 02:58 PM

I have a spreadsheet where I need to append "-C" to the end of each column header within my used range of columns. I am already relying on my used column range to insert a formula like this:

CODE

For c = 1 To maxC
Cells(c).Formula = "=counta(" & getColLtr(Cells(c).Column) & "2:" & getColLtr(Cells(c).Column) & maxR & ")-1"
Next c

What I am unsure of is how to enter edit mode for each of the existing column headers and append characters. My headers are in row 2 of the spreadsheet.

norie

Apr 25 2012, 03:09 PM

You don't need to enter edit mode.

CODE

Dim rng As Range
Dim col As Range

  Set rng = Worksheets("Sheet1").UsedRange

   For Each col in rng.Columns
       col.Cells(1,1) = col.Cells(1,1) & "-C"
   Next col

By the way what is that other code supposed to do?

Are you trying to add a count to the bottom of each column in the used range?

ipisors

Apr 25 2012, 03:12 PM

I'm a little unsure of how to provide code that 'fits' into what you mention you're already using.

If you want to add a -C to A1:G1, you can use;

CODE

Sub Test()

Dim myRange As Range
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("SheetName")

For Each myRange In ws.Range("A1:G1")
myRange.Value = myRange.Value & "-C"
Next myRange

End Sub

corrieann

Apr 25 2012, 03:40 PM

QUOTE (norie @ Apr 25 2012, 01:09 PM)

You don't need to enter edit mode.

CODE

Dim rng As Range
Dim col As Range

  Set rng = Worksheets("Sheet1").UsedRange

   For Each col in rng.Columns
       col.Cells(1,1) = col.Cells(1,1) & "-C"
   Next col

By the way what is that other code supposed to do?

Are you trying to add a count to the bottom of each column in the used range?

That line adds a formula in row one for each column header in my used range. I had to grab the column letter in order to construct the formula, and I leverage my calculation for MaxColumns used in an earlier part of my scrilpt. I am trying to determine how many cells contain data in each column. It works great!

corrieann

Apr 25 2012, 03:49 PM

QUOTE (norie @ Apr 25 2012, 01:09 PM)

You don't need to enter edit mode.

CODE

Dim rng As Range
Dim col As Range

  Set rng = Worksheets("Sheet1").UsedRange

   For Each col in rng.Columns
       col.Cells(1,1) = col.Cells(1,1) & "-C"
   Next col

By the way what is that other code supposed to do?

Are you trying to add a count to the bottom of each column in the used range?

Norie,

I used your code and it works except for one thing: my headers are on row 2. With your code, it inserting my desired text (-C) in row 1. How do I adjust that?

norie

Apr 25 2012, 03:56 PM

In can be done without a loop.

CODE

Range("A1").Resize(, maxcol).FormulaR1C1 = "=COUNTA(R2C:R" & maxrow & "C)"

norie

Apr 25 2012, 04:05 PM

Change Cells(1,1) to Cells(2,1).

corrieann

Apr 25 2012, 04:16 PM

Awesome!

If I run this script more than once, it is going to keep adding "-Client Data" to the end of the headers. I need to test to see if has been done already, so I thought I would do something like this:

CODE

'Append -Client Data to the end of each column header in sheet tab Client

  Set rng = Worksheets("Client").UsedRange
       For Each col In rng.Columns
       If Search(A2, "-Client Data") = 0 Then
       col.Cells(2, 1) = col.Cells(2, 1) & "-Client Data"
       End If
   Next col

It doesn't like my formula!! (surprise, surprise...) It thinks that A2 is a variable.

norie

Apr 25 2012, 04:22 PM

Are you sure that's the only problem?

I think the main problem is the use of Search, which is not a VBA function.

Use either the InStr function or the Like operator.

Oh, and it will thing A2 is a variable.

CODE

If Not col.Cells(2,1) Like "*-Client Data") Then

corrieann

Apr 25 2012, 04:33 PM

Are you making fun of me? LOL It's ok, I am used to it. I was just trying to keep my explanation simple.

Thanks for the help! It works.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.