How To Check For A Particular Letter In A Column

Jun 5, 2009

Basically, I need to be able to determine whether a particular letter is stored with another letter in the 'CG column' of the attached spreadheet.

The criteria is this:

1. Letter J can be present as long as there is no other letter in that column.
2. Letter B and D cannot be together in the column, but B can be with any other letter and likewise for D.

Basically, I could have a load of Js in the CG column and that would be fine. If another letter was with those Js, I need a warning box. I could have a B,H,S,T etc.....and that would be fine, but as soon as a D is entered, I need a warning. Similarly, if I had D,H,I,U,T etc......that would be fine too, but as soon as a B is entered, I need a warning again.

Return Column Letter Based On The Letter In A Cell.

Dec 31, 2009

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)),"")

Check If End Of Cell Is A Certain Letter

Nov 10, 2008

I need to check if a cell ends with {"a","b","c","d","e","S","t","v"}. and if it doesn't diplay a warning

this is what i have

--------C
10----4444s

=IF(RIGHT(C10,1)={"a","b","c","d","e","s","t","v"},"GOOD", "Must enter Suffix Letter")

Check Last 4 Letter Of A String

Nov 18, 2008

how I check the last 4 letters of a String to make sure they say '.xls', '.csv', etc, etc, for varification purposes.

Check If Cell Contains Letter

Dec 7, 2006

I need an excel cell formula that will detect whether or not the word in a cell contains the letter 'R' or not.

ie. If cell A1 contains a word that contains the letter 'R' then return a '1' otherwise return a '0'.

Check If Character Is A Letter Or A Number

Sep 3, 2009

I have a cell range that is passed as a String to a function, and within that function I need to extract only the Column letter. If it was just 1 letter it would be simple, but it may be 2, so does anybody know of a way of testing to see if the second character is a letter or a number?

Check If A Cell Value Starts With A Letter

Nov 2, 2009

I have a workbook and I want to check in a cell, if the value in the cell starts with "S" or "D". In VB.net there is a method .StartsWith to check the starting letter in a file. Is there a way to check the starting letter in the cell?

Check For Letter In Range & Count Numbers

Aug 13, 2008

I need to compare for each trip whether there is a trip with Es and no Es in it and mark every line in column E with tag "Mixed Use". In the data below trip 2 has mixed use. I have attached a sample file.

Trip Employee EntertainmentMiles

Trip 1Employee 1E827
Employee 2E0
Employee 3E0
Employee 4E0
Trip 2Employee 1758
Employee 20
Employee 30
Employee 40
Employee 50
Employee 60
Employee 70
Employee 80
Employee 9E0
Employee 10E0

Code Change Below To Find The Customer Column By Name Rather Than Column Letter

Jun 24, 2009

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

Keyboard Shortcut To Select Column By The Column Letter?

Jul 24, 2014

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.

Check If End Of Cell Is A Certain Letter And Match With Other Cell

Nov 18, 2008

I have two cells where data is input in the form

1234A the suffix letters can be a,b,c,d,e,t,s,v

I need to check if the suffix letter from both cells match up...

a can only match a
b can only match b
c can only match c
d can only match d
e can only match e
a-e can match t
s can only match s
v can only match v

is this possible without any macros?

Use Of Column Number Instead Of Column Letter In Formulas

Jun 16, 2013

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.

Convert Column Number To Column Letter

Nov 20, 2009

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.

Converting A Column Number Into A Column Letter

Mar 9, 2007

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?

Change Column Letter Into A Column Number

Sep 12, 2007

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.

Subtracting From Column Letter

Oct 22, 2007

Is it possible to subtract a number from a column letter? I'm trying to create a sheet that does some helpful calculations. In order for me to finish it without a macro i need to be able to subtract a number in a cell from a column letter. Example F-5=A, The end i'm trying to achieve is to get a sum range to move left and right in this data set based on that number.

Way To Get The Column Letter, Instead Of Number?

Nov 13, 2006

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

Return The Letter Value Of A Column

Aug 28, 2008

When I used "activecell.column" it returns the numerical value of a column (For Example: Column B would be 2).

I use the following in my
Dim StrRef As String
StrRef = "AN"

I want to be able to refer to column "AN" without having to hardcode "AN" into my code becuase it may change.

Below is part of the Code where I refer to "AN" in context of a loop---

For Each icell In ActiveSheet.Range(StrRef & "7" & ":" & StrRef & LastRow)

I tried Activecell.Address but that return the entire cell reference.

Column Letter Instead Of Number

Jul 26, 2006

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

Return Last Used Column Letter

Apr 22, 2008

I need to find the last used column in the excel sheet. I used the below mentioned code to find the last used column but it gives the ouptut in integer.. for eg) output is 13 if the column is M, but I need to get M as output.. how do i do that

Sub FindLastColumn()
Dim LastColumn As Integer
If WorksheetFunction. CountA( Cells) > 0 Then
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
MsgBox LastColumn
End If
End Sub

Convert Lower Letter To Capital Letter Automatically

Mar 2, 2009

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?

Convert Column Letter To Number

Nov 22, 2005

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

Letter Color And Column Sorting

Dec 20, 2008

I have Danish Office 2007.

1) Let's say I have 3 columns (horisontal) and many rows (vertical. Each row goes together, as the first column can be text that describes a transaction. The second column will be the amount of money and the third can be something else.
I know how to sort these by whichever column I want to. But the problem is every cell needs to be the same size. And I have merged 3 cells in the first column and only merged 2 cells in the last two colums.
So Excel tells me I cannot merge unless each cell is the same size.

Is there a solution here? I need the 3 cells in the first column, so I have enough room to describe the transaction. And to avoid wasting space, I need to only have the other two columns be the size of two merged cells each.

Example, though with the text in another column:
Picture of it

2) The other problem is that I would like a cell to display the number in red if below zero, green if above zero. I cannot do this. I know where to put in the format codes, but I don't know what to write.
At the same time, I need the cell to show the currency in Danish Kroner. So I need a format code that does this. Someone told me this:
[Blue]dk #.##0;[Red]dk #.##0;[Green]*dk #.##0

But that doesn't work, nor if the color is in danish. What do I do?

Change Column Letter When Dragging Down

Oct 28, 2013

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.

Column() To Return A Letter Instead Of A Number

Feb 14, 2006

Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible?

=INDIRECT(row() & column())?

Convert Column Letter To Number

Nov 13, 2006

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?

Column() - Give Letter Instead Of Number?

Nov 27, 2007

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')?

Remove The First Letter In Each Cell In A Column

Mar 5, 2009

I have a list with a column where all cells begins with the letter D. I would like to remove that D from all cells. For instance;

Dxxx1
Dxxx2
Dxxx3
Dxxx4

Should be;

xxx1
xxx2
xxx3
xxx4

Using .Column After An .Offset - Need The Letter But Get A Number

Sep 22, 2009

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)

Convert Column Number To Letter

Jan 17, 2004

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?