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?
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?
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?
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 Relatedin 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 RelatedIs 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)
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?
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 RelatedIs 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.
Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible?
=INDIRECT(row() & column())?
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)
How to return column number (not letter)?
View 5 Replies View RelatedIs 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.
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?
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 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 RelatedFor 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)),"")
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?
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 RelatedIm 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]....
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.
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".
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)