• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

EXCELL VBA cell reference

I am sure that this is an easy question, so here goes.

Currently in VBA i can reference a cell as (useing cell H8 as an example)

[H8]

Open in new window


or as
ActiveSheet.Range("H8").Text

Open in new window


However if i insert a column before column H then all the VBA's go out of whack.

Is there a way that i can name the columns and then refer to them by their name so that a column add/remove isnt breaking all the scripts? if it helps each column in row 1 holds the Unique identifyer for that column (Name, Address, Phone, etc....)

Cheers for any solutions.
0
Andrew Davis
Asked:
Andrew Davis
3 Solutions
 
krishnakrkcCommented:
You can use

MsgBox Range("Name").Column >> returns Column Number
MsgBox Range("Name").Address >> returns Range Address

where "Name" is a name range.

Kris
0
 
Pratima PharandeCommented:
try this

Sheets("Sheet1").Range("Employees").Cells(4, 1).Text

refer
http://stackoverflow.com/questions/11966342/vba-excel-rename-columns-for-easy-referencing
0
 
SteveCommented:
You can find the column in which the word "Name" first appears and use that in the range/cell refs:

NameColumn = ActiveSheet.Cells.Find(What:="Name", After:=ActiveSheet.Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

ActiveSheet.Cells(8,NameColumn).Text ' will reference row 8 in the name Column

Open in new window

0
 
Andrew DavisManagerAuthor Commented:
Hey guys thanks for your help. All answers were good and correct, got something from each of them, Split points, with slightly more for first answer, for no other reason than Kris was the quickest to post ;)

Cheers.
Andrew
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now