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 Repliesi 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 this macro below and would like the code changed to find the "Customer Number" column by name rather than by column B. Note that the "Customer Number" column will always be somewhere in row 1.
View 12 Replies View RelatedIn 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 have a formula in cell C2, =Summary!B2
I want the formula in cell C3 to be =Summary!C2
Then the formula in cell C4 to be =Summary!D2
etc etc.I need to do this for 43 rows so just want to drag the formula and the column number rather than the row number change.
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 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"?
Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible?
=INDIRECT(row() & column())?
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?
How to return column number (not letter)?
View 5 Replies View RelatedI 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
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 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...
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 have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).
I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.
Demo1.xlsm
I would like to change a number to a letter and then drop a digit from the end.
Say my data in A1 reads 81234568, and I would like it to display in cell A2 as h123456.
I know that this could be a stupid question, but was wondering if it could be possible. I would like to select a column by a keyboard shortcut. If I would press a key combination adding the letter of a column as example F, it would select the whole column. I was trying to search for this in google, but no luck.
View 10 Replies View RelatedI 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"
i need a formula for a sign in sheet for the academy i work at. we have night and day classes of the same sessions, in the planner they are listed as d1, d2 d3 and n1 n2 n3 and so on for each.
so basically im trying to create a formula to quickly ender the d1, d2, d3 ect into the cells on eachpage for the students to sign in for each day it works out to be about 190 calls and 9 diferent classes to you can see why id like to speed this up. problem is i need the number to change not the D and excel want to use the cell allocation.
as you can see from the document i have not attatched, i have created a formula to add in the dates automatically, i would like to do the same with the session numbers or the D numbers, this is one of the shorter courses and has about 190 days
I have got a code in VBA, and I wanna change number into column letter
ex: I got 50, and this is equivalent to AX column.
Is there any way for changing this (50 to AX)???
This spreadsheet will be tracking sales and purchases. I want to be able to have any purchases formatted as an expenditure (negative sign or brackets) to show it is money out. Column C "Action" has a drop down with either Buy (infrequent) or Sell. I want to be able to have column I "Unit Price" be a negative number if the corresponding cell in the same row in Column C = "Buy". I do not have these prices formatted as currency as their are 2 different currency used and I don't know how to make the correct currency format appear automatically, though I do have a drop down validation column for which currency the transaction was in, so maybe that is possible. I know this is probably simple, but I don't spend that much time using Excel. I have some sample data in their for the moment.
View 2 Replies View RelatedI 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.