I want to be able to use column numbers instaed of letters when selecting a range in VBA. My current code is: Range("G12:Z500").Select. The columns, however are reliant upon a variable, therefore I would like to be able to use that variable to change the columns when selecting this range.
View 2 RepliesI have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
how to create some code that can convert any column number to it's respective letter, from 1 = "A" to 16384 = "XFD". The code is posted below. Enjoy, or let me know how you would improve it.
Code:
Function colNumToChar(colNum) As String
Dim Num1, Num2, Num3 As Integer
Dim Let1, Let2, Let3 As String
[Code]....
All my columns headings are now numbers instead of letters. how do i change them back to letters
View 5 Replies View RelatedI have a excell spreadsheet that consolidate information from several other excel spreadsheets generated daily. To make this consolidation I have to open each daily excell file and copy some columns(always same ones) to my consolidate spreadsheet, but each daily information goes into a different column in the consolidated file.
So, I need a way to convert the columns names into numbers to be able to use loops on the VBA code where I can specify in each column the paste has to be done.
Anyone has the VBA code that can make this column name conversion into numbers?
how can I modify the formula below so that after every row (i+60) the letter D changes to E then F, then G..... and so on. I want the following with the formula below:
=MAX($D$2:$D$61)
=MAX($E$62:$E$121)
=MAX($F$122:$F$181)
and so on...
[Code] .....
I am attempting to sort a column with mixed letters and numbers. However, I have been totally unable to get them to sort the way I want them.
What I have is:
K600
K2
K2A
K2B
K159
K159A
K159B
K1
K1A
K1B
K428
K8
K8A
K8B
etc, etc. The prefix letter is only a K - no other letters. However, the suffix letters can be anything from A to E (at the present time)
I need to sort them numberically from K1 in descending order ie:
K1
K1A
K1B
K2
K3
K4
K4A
K4B
etc etc etc
First my goal is to sum a row and column. For example i want the sum of row 1 a to row 1 g. However, I'm using a loop for an automated macro. So i want to be able to say something like cells(1, count), so during the loop count goes from 1 to 7 representing the letters in the alphabet. But I'm not sure how to do so.
Secound, in my vba script to pull information from specific pages i say "sheet1.activate" how can i had the sheet switching during the process to speed up the calculation? and last but not least,
If Cells(1, locd) = "2008" Then
When i run my script, i get a runtime error 1004 application-defined or object defined error. The weird thing is, i can move what line it is on and then the macro will run ONCE and then it breaks again with the same error for that specific piece of code.
How do i turn column letters to numbers and vice versa
take y values from column and take x values from row
I have 'resolved' values in column A1:A10
I have 'received' values in row B11:K11
I need to fill out a table using the tables axis values stored in the column and row above.
I have a row of numbers that change as they go up. I will have 2 rows of numbers that will range from 1's all the way to 9's (B through BO) I will have different letter sequences that I will be entering as the criteria in column F either on the Up and Down sheet or the Even and Odd sheet. When I say different sequences. This is 1 group of letters. The next group of letters in column F will be in a different order. They will always be a U and D in one and E and O in the other but in row 13 it may have a U or D and it will be the same all the way down. The object is for me to be able to place the letters in either column F from rows 13 to 60, place 2 rows of numbers in rows 58 and 59, press the advance cell in G1 and it it populates all across the spreadsheet from I to BO. I will get all different number sequences since I am starting with a different number in rows 58 and 59. I have all the letters color coded and they move up based upon the color of the letters in row 1. If there is a green E in column M then I will be the column that the number advances from 1111 to 2111. If there is a green O then column K will advance up from 1111 to 1121.
I made a short video if you wish to view it.
The conditional formatting is very important so I need to have the green font with gray shade and red font with yellow shade [URL]....
i have a list of 2000 fields which have the same format IE "AB10014"
I need to remove the "AB" from every field and leave the #.
Besides putting a space and running text to columns I'm not sure how.
You can have code: For i = 1 to 20, and you then use "i" in the code to represent a number between 1 and 20 as you all know.
I'm wondering can you do something like: For i = A to D, so then "i" in the code would be either A, B, C or D?
Using the following data
R10-12128
R11-12x12x8
R11-12x12x8
R1-12x12x8
R1-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
R2-12x12x8
I want it to sort like this:
R1-12x12x8
R1-12x12x8
R2-12x12x8
R10-12128
R11-12x12x8
R11-12x12x8
R12-12x12x8
R14-12x12x8
R16-12x12x8
R18-12x12x8
What is the formula to achieve this?
Is there a VBA command to get the letters, instead of the numbers, of the column of a selected cell?
I have to letter a list whihc means setting up a loop using character codes.
I may have to go into double letters so I am working on how I would set up the loop for if and when it gets past 90 and starts on double letters. so far the highest is the letter "U"
of course the easiest would be to pick up a column value as a letter
NT values do not get a number
A_____ _____NT###
B_____1_______C####
C_____2_______RMK###
D____ _______NT####
i am guessing the loop might involve some arithmetic test between the count and the character set 65-90. or maybe a mod thing.
I have a spreadsheet wtih a number of rows that contain answers to different criertia in each column.
The row may therefore have cells that either have numbers or letters.
Column A will be the title of the row.
I want to write a function to find the average value of the numbers in any row with title 'x'.
So it would look down column A, and look for those called 'x', and then average all the numbers across all those rows.
I have tried to use =averageif, but I think the fact there are letters in the cells being assessed (which I just want to be ignored) creates an error.
I have a mass of data which look something like this:
table removed
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
table removed
and then apply the same procedure to the other ranges below this.
Tallying entires from a PDF into excel. Such that
c=1000
d=15000
e=50000
f=100000
g=250000
h=500000
i=1000000
j=5000000
k=2500000
l=50000000
In a cell I would like to type =c+d+c for example and have it say 17,000 rather then me having to type in =1000+15000+1000.
I created this data worksheet with many tabs and multiple users. On the main tab, something weird has happened and I have no clue how it happened or how to fix it....
View 5 Replies View RelatedThis formula will add up the letters D,N,H with one value
Like =SUM(COUNTIF(B4:AZ4,{"D","N","H"}))*12 will =36
How can I add bN =17 bmN =19 bD=24
=SUM(COUNTIF(B4:AZ4,{"bN","bmN","bD"})) to add up to 60
I'm working on a function that will sum a row of cells containing letters and numbers. I am currently using :
=SUMPRODUCT((0&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5:S5,"T",""),"TP",""),"TPE",""),"P",""))+0) as my base.
Right now the formula will work with T, TP, and P; however as soon as I type TPE I get #value.
Either way you get the point of what I am trying to do, however my cells can contain any one of the following combinations:
T,TP,TPE
P,PE,
E
In column A I have the following numbers
13710
14782H
9827
14782
14206B
a formula that looks at this range of cells and returns only the cells that have a letter at the end. The letter range goes A to M
I am a school teacher trying to adjust my tracking sheet to calculate pupils levels. I am looking for 2 potential formulas that will do the following.
1 - In cell AE I would like to generate a formula that will take the data entered in cells J:5, L:5, N:5, P:5, R:5, T:5, V:5, X:5, Z:5, AB:5 and AD:5 and give an average level.
2 - In cell AH is it possible to generate a formula that will calculate how many levels of progress the pupils are making - In other words I need Cell I to be calculated against cell J to see how much progress the pupils are making - for example if in cell I:5, a pupil is was given a 3a, and then in cell J:5 is given a 4b, they will have made 2 sub levels of progress. As well as this, can that progress then be averaged out across cells I:5, K:5, L:5, M:5, O:5, Q:5, S:5, U:5, W:5, Y:5, AA:5 and AC:5 to give an overall number of of levels of progress? An then..... can I colour co-ordinate the cell so that if the pupils are making 3 or more sub levels of progress it turns green, 2 sub levels orange and 1 sub level red?
Levels work like this
3c
3b
3a
4c
4b
4a
5c
5b
5a and so on
i want to convert numbers to letters in the excel Sheet
I.E: when the number is 1000.00$ it write in another cell
on thousand dollar only
1500.00$ one thousand five hundred dollar only
I have a word, for example ROCK. I asigned numbers to the word. R=1,O=2,C=3,K=4. My Question: If I type in ROC it must return 123. If I type in KR it must return 41, etc. Is there a basic formula I can use to solve this?
View 4 Replies View RelatedHow do I do data validation/cell formatting so that only alphabetic characters can be entered and no numbers?
(We use the cell for First Name, Last Name, etc. and I don't want potential entries like Mr. 56565 55454 because the cells are formatted as text and allow alphabet and numbers.)
I tried =NOT(ISNUMBER(H6)) for Data Validation, but that didn't work.
??
VR/Lost
I have in cell A1 the number 2 and in cell A2 the letter m.I want to add these two cells in cell A3 so as to appear 2+m.So i type in cell A3 =A1+A2 but it appears error.Is there a way to do what i am asking?
View 3 Replies View RelatedHow to autofill numbers with letters in excel 2007. Eg. 74cm, 80cm so on....?
View 4 Replies View RelatedIs there any formula or macro which gives the result from numbers to letters..? say like; 2,471,803.40 is in number and I'm looking for the result as 'RUPEE TWO MILLION FOUR HUNDRED SEVENTY ONE THOUSAND EIGHT HUNDRED THREE AND FORTY PAISE ONLY'. Is it possible to create macro to get this type of result..?
View 9 Replies View RelatedWe are doing a database conversion and I have been tasked with converting all our old promotion codes to a new system. The long and the short of it is this:
I am trying to write an equation that will look at two cells (say A1 & B1) which when put together could be one of the following numbers (01,02,03,04,05,06,07,08,09,10,11,12) and translate them to a letter (A,B,C,D,E,F,G,H,J,K,M,N)
So 01=A, 02=B and so on...
I was trying to use if statements, but there are too many.
I have a list I want to sort containing both letters and numbers. Right now the regular sort sorts like this
ABC-1
ABC-11
ABC-12
ABC-2
I want it to sort like this and don't have a clue how to do it
ABC-1
ABC-2
ABC-11
ABC-12
I am using excel 2003