# Return Column Number Not Letter

Sep 4, 2006How to return column number (not letter)?

View 5 RepliesHow to return column number (not letter)?

View 5 RepliesADVERTISEMENT

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

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

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)),"")

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.

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.

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

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"

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 RelatedI 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....

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

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

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.

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"?

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?

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')?

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)

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?

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?

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 Relatedhave 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

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.

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 RelatedI 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?

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 RelatedIs 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 RelatedI 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...

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 RelatedI 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),)))

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.

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