Capture column headers in a spreadsheet

[catlair]

Hi,

How do I adapt the "IF/THEN" statement to capture the first or second
line column headers and convert them? For example,


IF Worksheet.Column = "Name" THEN
Name = A
ELSE Worksheet.Column = "Country" THEN
Country = B
ELSE End

Forgive my lousy attempt.

catlair

 

[Bernard Rey]

catlair :

How do I adapt the "IF/THEN" statement to capture the first or second
line column headers and convert them? For example,

IF Worksheet.Column = "Name" THEN
Name = A
ELSE Worksheet.Column = "Country" THEN
Country = B
ELSE End

I'm not sure to understand clearly what you intend to do. Let's say you want
to check the content in the Header cells in a series of columns and, if they
match certain criteria, you'd like to change it to some other content.

If this is right, then the following lines could match your needs:

------------------------------------------------------
Sub ChangeIt()

Dim ThisCell As Range

WhateverA = "A": WhateverB = "B"

For Each ThisCell In Worksheets("Sheet1").Range("A11")
If ThisCell.Value = "Name" Then
ThisCell.Value = WhateverA
ElseIf ThisCell.Value = "Country" Then
ThisCell.Value = WhateverB
End If
Next ThisCell

End Sub
------------------------------------------------------

This one would check the contents of the first cells in columns A to D and,
if the content is "Name" it'll be replaced by the value of the variable
"WhateverA" which is "A" (but could be anything else).

If this doesn't help you out, could you give some more details about what
you'd like to have?

 

[catlair]

Hi Bernard,

Yeah, that's exactly what I was looking for. Will try it next week. Am
on a business trip. Will update you on the outcome. Thanks!

catlair

 

[catlair]

Hi Bernard,

Another question. How do I then capture the column header itself. For
example, if "Name" is under column A and "Country" is under column C,
is there a way to capture the column tags?

catlair

 

[Bernard Rey]

catlair :

Another question. How do I then capture the column header itself. For
example, if "Name" is under column A and "Country" is under column C,
is there a way to capture the column tags?

Yes. The "column" property returns the column number, which you can convert
to it's text equivalent. And the macro becomes:

------------------------------------------------------
Sub ChangeIt()

Dim ThisCell As Range

For Each ThisCell In Worksheets("Sheet1").Range("A11")
If ThisCell.Value = "Name" or ThisCell.Value = "Country" Then
ThisCell.Value = Chr(ThisCell.Column + 64)
End If
Next ThisCell

End Sub
------------------------------------------------------

This will replace "Name" with "A" and "Country" with "C". If you'd prefer to
have the value of the cell in the 2nd row, change the active line to:

ThisCell.Offset(1, 0).Value = Chr(ThisCell.Column + 64)



--
Bernard Rey - Toulouse / France
MVP - office:macintosh http://faq.office.macintosh.free.fr

 

[catlair]

Hi Bernard,

Ok. That works. Hopefully this is my last question. ;-) How do I
copy the column header value into another cell, for example, at the end
of that column and without changing the value of the column header
property? Hence, "Name" will not change and the column value will be
found at the end of the "Name" column.

catliar

catlair :

Another question. How do I then capture the column header itself. For
example, if "Name" is under column A and "Country" is under column C,
is there a way to capture the column tags?

Yes. The "column" property returns the column number, which you can convert
to it's text equivalent. And the macro becomes:

------------------------------------------------------
Sub ChangeIt()

Dim ThisCell As Range

For Each ThisCell In Worksheets("Sheet1").Range("A11")
If ThisCell.Value = "Name" or ThisCell.Value = "Country" Then
ThisCell.Value = Chr(ThisCell.Column + 64)
End If
Next ThisCell

End Sub
------------------------------------------------------

This will replace "Name" with "A" and "Country" with "C". If you'd prefer to
have the value of the cell in the 2nd row, change the active line to:

ThisCell.Offset(1, 0).Value = Chr(ThisCell.Column + 64)

 

[Bernard Rey]

catlair :

Ok. That works. Hopefully this is my last question. ;-) How do I
copy the column header value into another cell, for example, at the end
of that column and without changing the value of the column header
property? Hence, "Name" will not change and the column value will be
found at the end of the "Name" column.

In that case, there's just one line to be modified again. You can replace it
with this one:

ThisCell.End(xlDown).Offset(1, 0).Value = Chr(ThisCell.Column + 64)

But, if one of the cells in the column is empty it'll place the value there.
So if there may be some empty cells in one of the concerned columns, then
you'd rather use something like this (its supposed to be on one line):

ThisCell.Offset(65535, 0).End(xlUp).Offset(1, 0).Value =
Chr(ThisCell.Column + 64)

Thus, it'll base on the last possible cell in that row, jump up to the first
non empty cell (the bottom one in your column) and jump back one cell down
in order to find the next one at bottom.

Well, I hop this will be clear enough (?)

 

[catlair]

Hi Bernard,

Rather than placing it in a cell is it possible to make the value a
variable like:

MyVariable = Chr(ThisCell.Column + 64)

regards,
richard

catlair :

Ok. That works. Hopefully this is my last question. ;-) How do I
copy the column header value into another cell, for example, at the end
of that column and without changing the value of the column header
property? Hence, "Name" will not change and the column value will be
found at the end of the "Name" column.

In that case, there's just one line to be modified again. You can replace it
with this one:

ThisCell.End(xlDown).Offset(1, 0).Value = Chr(ThisCell.Column + 64)

But, if one of the cells in the column is empty it'll place the value there.
So if there may be some empty cells in one of the concerned columns, then
you'd rather use something like this (its supposed to be on one line):

ThisCell.Offset(65535, 0).End(xlUp).Offset(1, 0).Value =
Chr(ThisCell.Column + 64)

Thus, it'll base on the last possible cell in that row, jump up to the first
non empty cell (the bottom one in your column) and jump back one cell down
in order to find the next one at bottom.

Well, I hop this will be clear enough (?)

 

[catlair]

Hi Bernard,

Rather than placing it in a cell is it possible to convert the value
into a variable like:

MyVariable = Chr(ThisCell.Column + 64)

catlair

catlair :

Ok. That works. Hopefully this is my last question. ;-) How do I
copy the column header value into another cell, for example, at the end
of that column and without changing the value of the column header
property? Hence, "Name" will not change and the column value will be
found at the end of the "Name" column.

In that case, there's just one line to be modified again. You can replace it
with this one:

ThisCell.End(xlDown).Offset(1, 0).Value = Chr(ThisCell.Column + 64)

But, if one of the cells in the column is empty it'll place the value there.
So if there may be some empty cells in one of the concerned columns, then
you'd rather use something like this (its supposed to be on one line):

ThisCell.Offset(65535, 0).End(xlUp).Offset(1, 0).Value =
Chr(ThisCell.Column + 64)

Thus, it'll base on the last possible cell in that row, jump up to the first
non empty cell (the bottom one in your column) and jump back one cell down
in order to find the next one at bottom.

Well, I hop this will be clear enough (?)

 

[Bernard Rey]

catlair :

Rather than placing it in a cell is it possible to make the value a
variable like:

MyVariable = Chr(ThisCell.Column + 64)

Yes, of course, just the way you write it...

 

[JE McGimpsey]

Yes. The "column" property returns the column number, which you can convert
to it's text equivalent.

Note that using Chr(ThisCell.Column + 64)

works very well for the first 26 columns. If there's a possibility that
the desired column is > column 26 (e.g., for cell AA45), then one method
would be:

With ThisCell
.Value = Mid(.Address, 2, InStr(2, .Address, "$") - 2)
End With

 

[catlair]

Hi Bernard,

Thanks! Will be trying it out next week. Appreciate the sound
consuling.

catlair