Determining Column Address in Excel VBA

[Dawber]

I am attempting to perform a function, dependent upon whether the active cell is in column A or B.

eg.

If Column = A then X
Else If Column = B then Y

 

[xlbo]

if Activecell.column = "A" then
msgbox "Column is A"
else if Activecell.column = "B" then
msgbox "Column is B"
else
msgbox "Column is neither A nor B"
end if

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Dawber]

The following gives a "Type mismatch error", why?

If ActiveCell.Column = "A" Then
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = Now
ElseIf ActiveCell.Column = "B" Then
ActiveCell.Offset(1, -1).Range("A1").Select
ActiveCell.Value = Now
End If

 

[PHV]

If ActiveCell.Column = 1 Then
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.Value = Now
ElseIf ActiveCell.Column = 2 Then
ActiveCell.Offset(1, -1).Range("A1").Select
ActiveCell.Value = Now
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

If you had actually looked at the column property in the help files:

Column Property
the number of the first column in the first area in the specified range. Read-only Long.

Remarks
Column A returns 1, column B returns 2, and so on.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question