Return Column Number Not Letter

Sep 4, 2006

How to return column number (not letter)?

View 5 Replies


ADVERTISEMENT

Column() To Return A Letter Instead Of A Number

Feb 14, 2006

Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible?

=INDIRECT(row() & column())?

View 14 Replies View Related

Return Column Letter Based On The Letter In A Cell.

Dec 31, 2009

For the below formula is it possible to replace the B's (column location) with a cell Say Z146 which contains the letter B (or a number if thats easier and someone can tell me the numbers for each column).

When the formula is dragged into the next cell (down) it takes its column reference from Z147 and then my life becomes so much easier.

=IF(INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0))"",INDEX('Overs-Unders'!B:B,MATCH($C145,'Overs-Unders'!$A:$A,0)),"")

View 9 Replies View Related

Return Number Based On Letter Position In Alphabet

Aug 27, 2007

i wish to number the cells bases on the cell on the right.

Heading
A
A
A
A
A
A Total
B
B
B
B
B Total
C
C
C Total
D
D
D
D Total

to

Heading
1A
1A
1A
1A
1A
1A Total
2B
2B
2B
2B
2B Total
3C
3C
3C Total
4D
4D
4D
4D Total

The values in place of A,B...can range between 1 to 6.

View 9 Replies View Related

Return The Letter Value Of A Column

Aug 28, 2008

When I used "activecell.column" it returns the numerical value of a column (For Example: Column B would be 2).

I use the following in my
Dim StrRef As String
StrRef = "AN"

I want to be able to refer to column "AN" without having to hardcode "AN" into my code becuase it may change.

Below is part of the Code where I refer to "AN" in context of a loop---

For Each icell In ActiveSheet.Range(StrRef & "7" & ":" & StrRef & LastRow)

I tried Activecell.Address but that return the entire cell reference.

View 9 Replies View Related

Return Last Used Column Letter

Apr 22, 2008

I need to find the last used column in the excel sheet. I used the below mentioned code to find the last used column but it gives the ouptut in integer.. for eg) output is 13 if the column is M, but I need to get M as output.. how do i do that

Sub FindLastColumn()
Dim LastColumn As Integer
If WorksheetFunction. CountA( Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
MsgBox LastColumn
End If
End Sub

View 3 Replies View Related

Column() Function: Return The "letter Form" Of The Column Position Rather Than The "number Form"

Oct 12, 2008

I am using the Column() Function for referencing purposes... what I want this function to do is return the "letter form" of the column position rather than the "number Form"... is there anyway to change this without switching to R1C1 form?

i.e. Column(B4) yields "2"

I want it to give me "B"

View 2 Replies View Related

Search Row For Text And Return Column Letter Of First Occurrence?

Dec 14, 2011

Is there a way to have a formula search an entire row containing certian text, for example "x" and have the formula return the letter of the first column that text is found?

View 8 Replies View Related

Does A Property Exist To Return The Letter Reference For A Column

Jan 24, 2008

I need to build a formular in a cell that uses various columns in it's row such as: =A2+A4+A17+A24. In the past I've used simple number references to the columns (a is 1, b is 2,.....) I can't do tht though if I'm using a macro to automate the creation of a formula, right?

Is there a property I'm missing that will return the letter reference to the column instead of just the number as Activecell.column does?

My alternative is to write my own code to do this, but you know the bit about not reinventing the wheel....

View 9 Replies View Related

Way To Get The Column Letter, Instead Of Number?

Nov 13, 2006

Is there a way to get the column Letter, instead of Number?

like in A1 Column() would equal 1 in B1 Column() would equal 2

I would like

in A1 Column() to equal A and in B1 Column() to equal B

View 9 Replies View Related

Column Letter Instead Of Number

Jul 26, 2006

is there a way to make the following code return the letter of the column instead of the number? currently if the 'String' value that is in 'ColumnFind' is in column B this code returns a value of 2. i Need the 'B' for later code to work.

ColumnLetter = ColumnFind.Column

View 5 Replies View Related

Data Validation Format Letter Number Letter Number Etc.

Jul 11, 2009

I want to apply Data Validation to a cell, so that only the following combination of letters and numbers can be entered.

Letter Letter Number Number Number Number Number Number Letter.
e.g AB123456C.

View 14 Replies View Related

Convert Column Letter To Number

Nov 22, 2005

I need to convert a column letter to a column number for the following line of code in my VBA application:

VB:
strAverageBaseFormula = "=AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":K" & Trim(Str(lngFirstRowOfIncrement + 1)) & ")"

I am inserting a formula into a cell using VBA, but the problem is that I don't know the letter value of column "K" (used in the middle of the line); instead, I know it as column number 11. Is there a simple way to convert the column number 11 to the column letter "K"?

View 9 Replies View Related

Convert Column Letter To Number

Nov 13, 2006

Some bits of code I have learned use column numbers and some bits use column letters.

Can someone share a line or two that I could add to my macro that will convert the F representing column F into a 6, and vice versa, so that I can continue using my pre-existing bits?

View 9 Replies View Related

Column() - Give Letter Instead Of Number?

Nov 27, 2007

When using the formula '=COLUMN()' in cell A1, it returns the number of the column - in this case, '1' (for column A). Is it possible to affect this formula so that it returns the column letter (in this case, 'A')?

View 9 Replies View Related

Using .Column After An .Offset - Need The Letter But Get A Number

Sep 22, 2009

ce.Offset(, 4) = "=" & ce.Offset(, -1).Address & "*" & ce.Offset(, 4).Column & "25"

If X29 were equal to ce.Offset(,4) then the value in that cell should be set to

"=S29*X25"

Currenctly it is returning "=S29*2425"

(FYI - ce is just a variable to capture a particular range/cell that is dynamic and used within a For Next Loop)

View 9 Replies View Related

Convert Column Number To Letter

Jan 17, 2004

I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number to a letter?

eg. somefunction(1) gives me column(A) as an answer?

View 9 Replies View Related

Method To Convert Column Number To Letter

Sep 15, 2014

I wanted to know if there was a way to either make a variable hold a column letter or else automatically convert a column number to a letter. This would be for use in the Range property.

All I could find is some complicated technique requiring division by 26 that I will never remember let alone code quickly.

Or if you have a variable holding a column number, is the only way to use it with Cells?

View 5 Replies View Related

How To Output Column Letter (not Number) With A Formula

Dec 23, 2009

Is there a function that will output the column letter? For example there's one I know of: =COLUMN(), which outputs column number, but not the letter. And if not, can a formula be written to output it without converting the spreadsheet to R1C1 style or using the lookup function that refers to a separate table within the spreadsheet?

View 3 Replies View Related

Look At Column For 3 Letter 3 Number Combos And Move

May 23, 2007

have thousands of rows and the cells look similiar to this:

FAKE NAME PARTNERS FTA048

some other combos could be FTB039 or BCL048 ETC

whats the best way of looking down a column
and moving any 3 letter 3 number combos to another column

so that FAKE NAME PARTNERS and FTA048 are in seperate columns

View 9 Replies View Related

Use Of Column Number Instead Of Column Letter In Formulas

Jun 16, 2013

In my spreadsheet the formula
=count(k:k)
gives 8 which is correct.

But I know ONLY that the column is 11 (that is 11 th column). Then how do I write the above count formula wilthout using column letter k.

View 4 Replies View Related

Convert Column Number To Column Letter

Nov 20, 2009

in a macro i have this: col=cell.column 'suppose col=16384. then i need to place in a cell this formula =(XFD2708*100/XFD2597)-100 so i'm looking for "something" that can convert 16384 in XFD.

View 2 Replies View Related

Converting A Column Number Into A Column Letter

Mar 9, 2007

I have a variable, ColumnX. I want to use VBA to generate some formula referencing ColumnX. For example: I want to sum the values in A1 to A10. ColumnX has been set to "1" which is the number of column A.

Range("A11")= "=sum("A1:" & ColumnX & "10)"

Obviously the above code doesn't work because I'm putting a number where VBA expects a letter. How can I rewrite my code so that A11 will entered with formula that sums A1 to A10?

View 5 Replies View Related

Change Column Letter Into A Column Number

Sep 12, 2007

I need code to change a letter such as A into a column number i.e 1. Everything i can find is to do with changing column numbers into letters, surely you can do it the other weay round aswell? So when the macro is given the letter AA it returns the column number as 27 etc, but i need it as a defined variable such as "i = column number" so i can use this information further on in my code.

View 2 Replies View Related

Convert Column Number To Letter From A Range Objec

Jul 7, 2009

Is there an easy way (without writing my own conversion function) to get the column letter from a range object, as opposed to the column number?

View 5 Replies View Related

Combine Found Row Number With Column Letter To Become Range And Then Count

Jul 8, 2012

I am having troubles finding a row number, using it to specify a range and then counting the cells containg values in that range.

This is what I have

Code:
Private Sub TextBox1_Change()
Dim test1 As String
Dim FoundRom As Range
Dim i as String
Dim abc1 As Range
Dim Core1 As String

test1 = TextBox1.Value

[Code] ........

I am thinking that I have my "Dim"'s wrong or something but I cant figure out how to define the range including the found row number and then count the number of cells containing data within that range...

View 8 Replies View Related

Fill Down But Have Column Letter In Formula Change And Not Cell Number

Dec 30, 2009

i want to fill down a column and instead of my formula changing from A6 to A7 i want it to change to B6.

View 9 Replies View Related

Return Column Header Based On Column Criteria And Number Value

Feb 7, 2014

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00

CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))

View 3 Replies View Related

Return The Column Number Of First Column In A User Inputted Range?

Feb 19, 2009

I'm creating a macro that creates a co-ocurrence matrix from variables that are adjacent to each other.

In order to proceed, I need to know how to return the column number of the first (leftmost) column in a range that the user selects.

View 2 Replies View Related

Search From Every Row Of Column In A Column And Return Row Number

Sep 3, 2008

how to find text from a row in column, where is a lot of text and return row number, of located text?

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved