I have a "pair" of columns that exist in many different spreadsheets that
our group receives data in. They are always in the same order, but not
always in the actual columns, i.e. compo is always the column just before
curorg, but they don't always live in columns D and E. What I need to do is
set the value of compo based on curorg. I was trying to modify a macro given
to me for cleaning up ssn's with missing leading zeros. In other words I
would like to just select the two columns and have whatever is already in
compo replaced with the proper value, if curorg is a 1 or S or U then set
compo to '1, and so forth. There are currently 19 values that could exist in
curorg and 3 values ('1, '2, '3) that would map to those curorg's and be
inserted into the compo column.

I hope this makes sense.

Bruce

Bruce,

No, it doesn't make sense - or, at least, your question isn't clear. Post a small example of the
table before and the table after, and that will help us help you.

HTH,
Bernie
MS Excel MVP


"Bruce Martin" wrote in message ...
I have a "pair" of columns that exist in many different spreadsheets that our group receives data
in. They are always in the same order, but not always in the actual columns, i.e. compo is always
the column just before curorg, but they don't always live in columns D and E. What I need to do is
set the value of compo based on curorg. I was trying to modify a macro given to me for cleaning up
ssn's with missing leading zeros. In other words I would like to just select the two columns and
have whatever is already in compo replaced with the proper value, if curorg is a 1 or S or U then
set compo to '1, and so forth. There are currently 19 values that could exist in curorg and 3
values ('1, '2, '3) that would map to those curorg's and be inserted into the compo column.

I hope this makes sense.

Bruce

Bruce,

Sounds like you are trying to locate the column number for "compo"

If "compo" is a header and headers are on row 1

Dim col as Long

col = worksheetfunction.Match("compo"),Rows(1),0)

Use caution - if compo isn't there - the match function will return an
error.

once you have the column number - you can build your code...

--
steveB

Remove "AYN" from email to respond
"Bruce Martin" wrote in message
...
I have a "pair" of columns that exist in many different spreadsheets that
our group receives data in. They are always in the same order, but not
always in the actual columns, i.e. compo is always the column just before
curorg, but they don't always live in columns D and E. What I need to do is
set the value of compo based on curorg. I was trying to modify a macro
given to me for cleaning up ssn's with missing leading zeros. In other
words I would like to just select the two columns and have whatever is
already in compo replaced with the proper value, if curorg is a 1 or S or U
then set compo to '1, and so forth. There are currently 19 values that
could exist in curorg and 3 values ('1, '2, '3) that would map to those
curorg's and be inserted into the compo column.

I hope this makes sense.

Bruce