# Convert Integer Number To Column Alphabet?

May 8, 2014I 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)

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)

ADVERTISEMENT

I am building a Workbook which takes data from SheetA and inserts it into SheetB.

Part of the data is only entered when a positive value exists.

I then do an export from SheetB.

The problem is that I need to get the column number and pass it to the cell reference based on the field name in row 1.

Dim sFindstring As String

Dim rFindcell As range

Dim iR As Integer

Dim iC As Integer

sFindString = " Find this string in the cell"

'Using cells find the findstring

Set rFindCell = Cells.Find(What:=sFindString, After:=[A1], LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

'OK so look here

iR = 3

'I am trying to pick up the column number

iC = rFindCell.Column

rFindCell throws an object or with block variable not set error. Is there some property that I need to set.

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.

I've got an odd program that exports time data formatted as a text string. For example....

0:10:25

Since it has no numerical value, then I can't simply "format as number". I'm thinking that I will need to use one of the string methods eg: mid() to check each character individually and then convert that into the equivalent numerical value.

So the numerical output I am looking for in the above example would be 625.

i am a having a text in 5th column. now i want to know the alphabet corresponding to the column i.e E (for 5th column)in my code

View 9 Replies View Relatedi wish to number the cells bases on the cell on the right.

Heading

A

A

A

A

A

A Total

B

B

B

B

B Total

C

C

C Total

D

D

D

D Total

to

Heading

1A

1A

1A

1A

1A

1A Total

2B

2B

2B

2B

2B Total

3C

3C

3C Total

4D

4D

4D

4D Total

The values in place of A,B...can range between 1 to 6.

How to get the Column Alphabet based on the Data it contains or the Column Heading using VBA?

Is it possible to get the Column Alphabet using any Macro or any function that within a Module it can always take the New Column Name during Execution..

Example: I have certain Columns where I have Yes and No Tick using the Wingdings P and Y..

Now these Columns are alternately Placed and there are six columns in all and they are spread over 12 Columns as the Alternate COlumns is reserved for Manual Entries.

Now I intend to increase the Manual Entry Columns but the problem is that every time I do that I need to make changes in my VBA Code.

Is it possible that even when the Columns are Inserted or Deleted in between before or after these columns I dont need to change the Explicit references by changing some approach.

I am not fuly conversant in VBA but use it whenever things are not completely feasible with Formulas AFTER GOOGLING.

Code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("X3:X13")) Is Nothing Then ' You can Change the range here

Cancel = True

[Code]....

As you can see the Ranges are Alternately Placed and there are about 6 such Columns with the above TICKS..

Now, how do I keep it FLoating so that even after changing the COlumn Location it does not need to get Updated i.e. X and Z Column Alphabets..

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?

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 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"?

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]....

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?

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?

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 have a user form and to input the hourly wage i need to convert the text entry to a number whose type is LONG and not integer.

Does anyone know how to do this?

Or is there a way to input a number in a userform without a text box entry?

I would like to round this number (3.5) to become 4, but by using this formula =CEILING(A1,0.5) the result will be 3.5

i need a formula that turns 3.5 to 4

when i use this formula =ROUND(A2,0.5), 3.5 becomes 4, but 3.2 becomes 3 not 3.5

i.e. a formula turns 3.2 to 3.5 and 3.5 to 4

I set a column width to some integer like 10, and the next time I open the file it has changed to something like 9.89. Is there some configuration that prevents this from happening and leaves my setting alone?

View 4 Replies View RelatedI have written a macro to record some information to cells on a spreadsheet. Below is my code. As you can see I already have row setup in the code, but I now want to add column as an integer. So that if something were recorded in col m, then it would add 1 to colu and the next information would be added in col n.

Col = 13 (M) to start with.

Dim row As Integer

Dim colu As Integer

If Trim(txt(8, 10, 25)) = Workbooks("Book1").Worksheets(1).Range("K" & row) Then

MyScreen.Area(8, 4, 8, 5).Copy

MyScreen.Area(22, 40, 22, 41).Paste

SendKey "<ENTER>"

If MyScreen.Area(7, 57, 7, 57) = "A" Then

Workbooks("Book1").Worksheets(1).Range(colu & row) = Trim(txt(7, 57, 74)) 'getting my error here.. something wrong with colu

colu = colu + 1

End If

wondering is there a VBA equivalent of --() in excel that turns trues and falses to 1's and 0's?

View 14 Replies View RelatedEssentially, I have a column of integer values which I would like to iterate through. The integers in the column vary from 12 to -12 with the exception of 0, so 24 possible integer values here.

For each possible value, I would like the activecell.offset(0,7) value added to a variable.

So if I iterate from cell A1 to cell A10, each time a value of "1" appears, I would like the cell 7 spaces to the right of the "1" cell added to a "total" variable for "1" e.g.: "totalOne".

The same applies for every value within my range. So if there are three instances of the value "8" between A1 and A10, then each value 7 cells right of those "8" values will be added to a total value for the value 8.

I started to use:

select case range("u4:u150"). value

case 1

Dim value1 = value1 + ActiveCell.offset(0, 7).Value

However, at this point I realised that a switch statement stores the range I have given it as an array, so my 'case 1' cannot work as it is trying to compare an integer with an array.

Finally, if it stores an array, surely it wouldn't be able to get cell.offset values as it would have no references to those locations given that the array is just a group of integers, not actual cell references? Will I have to use lots of "for each" statements? hope not!

I have a first column, which contains random integers typically with gaps of between 10 to 50 blank cells. The data may have up to 5,000 rows in total.

In the next column along I would like a calculation which averages the integer in the first column out across all rows until another integer is reached in the first column.

E.g. In the first column, the first number is 60, then 9 blank cells till the next entry. In the second column the first 10 cells each contain the number 6 ( = 60/10).

Cell A1 = 3

Cell A2 = 4

Is there a formula to calculate

(3*4)+(2*4)+(1*4)

I need to multiply every integer less than the number in cell A1 and greater than zero by the number cell in A2. I was thinking factorial, but that's not it... Can't remember from my math days.

I need a formula to tell me whether a number is a square number, or I can do it so to see if the square root of a number is an interger. So far Iv been doing

A1 = 2

A2 = AND(RIGHT(A1,1)=INT(A1))

True or false outcome is fine, and it has been working fine on some examples, but the problem comes when I have 49, as it is 2 digits long. I've seen a formula similar to find the root symbol, and look at all the numbers upto this point (maybe FIND).

I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.

[URL]

Does clipboard method gettext retreive the text from clipboard only, not number? What if numbers are copied (Ctrl C) to clipboard?

View 9 Replies View RelatedI have a excelsheet that looks like this:

Column A | Column B | Column C

Los Angeles | Fire Dept | 3

Los Angeles | Health Services | 12

New York | Fire Dept | 8

New York | Health Services | 22

New York | Internal Services | 100

New York | Public Works | 7

Chicago | Health Services | 15

Chicago | Public Works | 56

Chicago | Social Services | 4

And I am trying to make it look like this:

Fire Dept

Health Services

Internal Services

Public Works

Social Services

Los Angeles

3

12

New York

8

22

100

7

Chicago

15

56

4

i have this function that poulates data from a userform into a sheet.

For lLoop = 1 To MAWBNoVar

With Range("B" & Rows.Count).End(xlUp)

.Offset(1, 0) = Controls("txtMAWB" & lLoop)

.Offset(1, 1) = Left(Controls("cbDestination" & lLoop), 3)

.Offset(1, 2) = Controls("txtGW" & lLoop)

.Offset(1, 3) = Controls("txtCBM" & lLoop)

.Offset(1, 4) = "= ROUND(E" & Y & "*167,0)"

.Offset(1, 5) = "=IF(D" & Y & ">F" & Y & ",D" & Y & ",F" & Y & ")"

.Offset(1, 6) = "=IF(F" & Y & ">D" & Y & ",G" & Y & "-D" & Y & ",G" & Y & "-F" & Y & ")"

Y = Y + 1

End With

Next lLoop

My problem is that the data being entered into the sheet for the lines highlighted in red is going in as text, and not true numerics, so that the subsequent formulae are not working correctly.

I've found a few threads, but I'm not finding my answer. I need to start with AA in L2 and Fill down until Column I is "".

View 2 Replies View Related
Copyrights 2005-15 www.BigResource.com, All rights reserved