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
ADVERTISEMENT
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
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
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
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
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
Jul 23, 2009
Is there simple function that anyone knows of (or has written) that will convert a letter to its alpha-numeric equivalent?
For instance, A = 1, B = 2, AA = 27, etc (a = 1, b = 2, aa = 27)
View 6 Replies
View Related
Sep 4, 2013
I have a drop down menu using data validation and the values are B,C,D
I want to keep the letter in a cell, but make each one become number, so B=5, C=10, D=20
I have another dropo down menu that contains 6,10,16
What I require is that once the 2 drop down menus have 'values selected' I need to multiply the 2 cells together
so for example
If B and 6 was selected, I require the 3rd cell to be 5*6 so what is the formula?
View 1 Replies
View Related
Mar 2, 2009
When I type a single lower case letter into a cell, what formula or conditional formatting should I use to always convert it to a capital letter automatically?
View 9 Replies
View Related
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
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
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
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
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
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
Sep 4, 2006
How to return column number (not letter)?
View 5 Replies
View Related
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
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
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
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
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
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
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
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
Dec 27, 2013
A column:
18
42
55
11
65
72
80
48
..
I want to put/write to B column the some numbers (42,11,72,48..) in A column.
B column:
=A2
=A4
=A6
=A8
..
How can I achieve it?
View 12 Replies
View Related
Jul 30, 2014
convert the data in column B to Number so that i can perform action in Column D. see attachment for the query.
View 3 Replies
View Related
Jan 4, 2014
Im trying to convert the data in certain columns to number. I need to select the rows in those columns based on the rows counted in another column with a different heading, this is because there can be breaks in the data half way down the columns.
The Code I have so Far is:
Code:
Sub ConvertTonumber()
'Convert Certain Columns to numbers
'Use the "x"column to Calculate how many rows are required to fill the columns.
Dim ColX As Range
[Code]....
View 8 Replies
View Related
Jan 20, 2009
How Do I Convert Date:
10/3/2008 into October
6/1/2008 into June
7/3/2008 into July
I have date 10/3/2008 in cell and if I convert format to M into Just gives me first letter of Month, so O, I want it to read October, etc.
View 2 Replies
View Related
Jun 21, 2007
a formula that would take letter grades (A, B, C, D, and E) and produce an average letter grade?
For example, a student gets a D on one assignment and a B on another. I want a formula that will produce "C".
View 3 Replies
View Related
May 8, 2014
I have M106 = 4
I need a simple formula to convert that into column Alphabet + 1
So 4 will be Column E (A,B,C,D and 4th will be D+1= E)
View 3 Replies
View Related
