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.
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?
I want to put the letter A in cell a1(or any cell in column A, SHEET1) of SHEET 1 and get the information from SHEET 2, column A. Put this information in column B sheet 1. I will have about 40 columns in sheet 2. I used a if formula, but only 8 ifs can be used.
In sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones Column B : payroll number ie 123456 Column C : shift times ie 1245-2124 Column D : job title ie floor Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
I am trying to insert formulas to my cells in different columns and then fill down the formulas as many rows as in the reference column. So far I have put my formulas in a macro but I seem to get problems with " and ' symbols. My macro looks like this:
The colour of the percentage cell changes according to its percentage. I would like the S/N cells to have the same colour as the total percentage cells automatically. (Even when the percentage updates)
Secondly, for the cells under "Target", if the target dates are 1 day before today(the current date on a particular day) and the actual date is not filled, the cell fill will turn amber.
If today is on or after the target date and the actual date is not filled, the cell will turn red.
However, if the actual date is filled, the target date cell will be filled green, overwriting the above two condition.
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
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.
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.
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"?
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')?
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?