# Returning The Column Letter Based On A Search

Jan 28, 2008

I'd basically like a formula that finds a number (eg 220) within either the first 10 rows (or with row 5 if this isn't possible) and returns the column Letter as the result. The number will only appear once and only in one column. If it matters the columns go all the way to EC.

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

## Search Down The First Column And Find Any Instance Of The Letter

Nov 16, 2006

I have this piece of code

Sub do()
Dim i As Integer
Dim x As integer
x = 5000
i = 1
Do Until i = x

If Cells(1, i).Value = "C" Then
ActiveCell.Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRigh

i = i + 1

End If
Loop
End Sub

I want it to search down the first column and find any instance of the letter C and shift it (along with the entire row) over two clumns to the right.

## Search Row For Text And Return Column Letter Of First Occurrence?

Dec 14, 2011

Is there a way to have a formula search an entire row containing certian text, for example "x" and have the formula return the letter of the first column that text is found?

## UserForm Combobox Wild Search Filter Based On First Letter Entry

Jun 18, 2014

I am looking to modify my combobox from a userform to Filter the list based on the first entry on what the user will add.

I have a code that will search for all entries in my "Control" sheet and passed this on to my combobox:

[Code] ......

I have, within a module, the following function code that the user find the entry by adding the first 3 letters of the search they need:

[Code] .....

I would like to know what to change in the function to filter the list, once the user adds a first letter and then press the dropdown from the combobox, to filter the list based on all entries from the first letter to only show entries with that letter.

If A is entered, then, show only the entries strating with the starting letter A, but at the same time if the user adds more letter to filter to the first then second letter:

If AB is inserted, then, show entries with AB only.

Where do I add this code also, is this another function or this could be inserted within my function?

## Insert Column Macro Based Variable String Letter

Jun 11, 2008

I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).

## Returning Value From Column Based On The Values From 2 Others?

Aug 7, 2014

I have a table of data-
IDDescriptionDatePax Count
316COACH01/07/20135
323COACH3/07/201410
324COACH18/07/20141
325COACH20/08/201420
333COACH21/09/201445
345COACH22/09/20143
351COACH23/09/20143
362COACH21/10/20142
371COACH22/11/20142
394COACH23/12/20140
416COACH23/12/201418
422COACH23/12/201439

Example, the real data is about 38,000 entries. I want to build a query to return the value of Column D (pax count) based on the values of Columns A (ID) and C (Date)

This would feed into a second, ID specific table, like this-

ID 536 Bookings JULY 2013

1/07/20132/07/20133/07/20134/07/20135/07/20136/07/20137/07/2013
(value) (value) (value) (value) (value) (value) (value)

SUMPRODUCT, usually my mainstay, is for some reason letting me down, returning a value of 0 for everything irrespective.

## Returning Values Based On Location In Column?

Feb 19, 2013

I'm trying to find a way to test is the max value in a range, columns A-F, comes after the min.

Example: Columns A-F have the values 4,1,3,5,2 respectively. I'd like a test to see if the min, in this case Column B, comes before the max, column D, and have an output that says yes or no. So in this example yes but if the numbers were instead 4,5,1,2,3 it would return a no.

## Excel 2010 :: Returning Value From Third Column Based On Two Other Columns

Jun 24, 2012

Basically; there are three main columns in the first worksheet (lets call it "Main Data"): OrderNumber, TaskName, SignOffDate with data listed as follows. The actual spreadsheet has hundreds of order numbers but i'll keep it simple and lets go with two.

OrderNumber
SignOffDate

1
OED
01/05/2012

[Code] ........

What I am trying to do; is sort this data in a second worksheet (lets lable it "Output") so that the sign off dates for each task; for each order; are listed within 1 single row. Ie:

OrderNumber
OED
CTN
RFS

1
01/05/2012
17/05/2012
22/05/2012
02/06/2012

2
03/05/2012
19/05/2012
27/05/2012
02/06/2012

There are many orders in the main data; and I'm not sure what to do exactly to return the sign off dates for each task for each order without creating separate worksheets for each task name; then using vlookups to find each date.

An order may have a sign off for all task names, or none at all. In addition to this; they may not always be in the same order as listed above.

I'm using Excel 2010.

## Search By Typing In First Letter Into Dropdown Box

Mar 14, 2013

I have been working on a Software Audit list that has been created by someone else. He has created an Application Colum and under that is a drop down box. I want to be able to search by typing in the first letter, i.e. 'S' but currently I just have to scroll down.

Do I need to create and Active X Combo Box to do this or is there another way? Also, is it possible to search on the first two letters, i.e. 'se'?

## Search By Letter In Dropdown On Sheet?

Jul 16, 2012

Is it possible to search my Worksheets("Register").Shapes("Drop Down 1") by selecting it and type in a letter? Similar to a combobox "0 - fmMatchEntryFistLetter"

## Match 2 Criteria And Search Column And Return Value Based On Value

Feb 14, 2014

I have a spreadsheet that contains data for a fiscal year broken out by month and quarter. I want the formula to return the data from that month and use whatever the latest quarter is. For example in the data below:

If it has only pulled data only thru Q1 it would return Q1 for Jan-Mar, however once I have Q2 data I would want the formula to return Q2 for the months of Jan-Mar.

2013Thru Q1STOLISTOLIJan-2013STOLI BASE
2013Thru Q1STOLISTOLIFeb-2013STOLI BASE
2013Thru Q1STOLISTOLIMar-2013STOLI BASE
2013Thru Q2STOLISTOLIApr-2013STOLI BASE
2013Thru Q2STOLISTOLIMay-2013STOLI BASE
2013Thru Q2STOLISTOLIJun-2013STOLI BASE
2013Thru Q3STOLISTOLIJul-2013STOLI BASE
2013Thru Q3STOLISTOLIAug-2013STOLI BASE
2013Thru Q3STOLISTOLISep-2013STOLI BASE
2013Thru Q4STOLISTOLIOct-2013STOLI BASE
2013Thru Q4STOLISTOLINov-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2013Thru Q4STOLISTOLIDec-2013STOLI BASE
2014Thru Q1STOLISTOLIJan-2014STOLI BASE

## Search Column Bottom Up Then Delete Rows Based On Value Found

Jul 7, 2009

I've attached a dummy worksheet that shows constant values of 1.00 in columns D and E. In the actual workbook, user action will sometimes cause these values to change to something other that 1.00. The user may wish to delete the action that caused the change.

I need a macro that will search from the last cell in column D upward to the first instance of a cell that is not equal to 1.00 and select that cell and those immediately above with the same value as the first found cell.

For example, in the attached worksheet the search would start in the last cell of Columd D and search upward. It would find cell D23 and upward to D19. These entire rows would be deleted.

## Macro For Multiple Search And Replace Based On Column Heading (First Row)

Oct 7, 2013

I am looking for a macro to replace the values in a range of cells with a specific array for each column heading

Here is what I have

Temp1
Temp2
Temp3

0
0
0

1
1
50

[Code] ....

And here is the expected result of the macro

Temp1
Temp2
Temp3

no
false
min

[Code] ........

Please note there is only one Active Sheet.

## How To Move Data In Various Cells To New Columns Based On Search On Primary Column

Feb 4, 2014

I have a sheet which I need to arrange and it looks like -

Column A
Column B
Column C
Column D

[Code]...

the values in Column B, C , D , E & F need to be in new columns based on what we have in Column A associated with "1"

Output should be -
Column A
Column B
Column C

[Code]....

so whatever is in Column A associated with "0" should remain in place whereas whatever is in Colimn A associated with "1" should get added to the new columns. 0 and 1 resembles the 1 Records which are used as a split to normalize the data.

## Macro For Returning URL Of First Search Result On Google From Keyword List In Excel

Apr 3, 2013

So basically I have an Excel sheet which has keywords that need to be entered in Google search. I need the URL of the first page of the search result that appears after that keyword is entered. IS there a macro for the same?

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

## Use Of Column Number Instead Of Column Letter In Formulas

Jun 16, 2013

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

## Update Table On Another Sheet With Information On ACTIVE SHEET Based On Column Search?

May 14, 2014

What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".

To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."

When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.

On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.

"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").

I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.

My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.

## Assign Folder Based On First Letter Of Name

Jan 2, 2014

I have a routine that looks a variable's first letter and then assigns it to a specific folder. I have tried using just the first letter (firstletter) and the ASCII value of the first letter but neither seem to work - see below.

firstletter = Left(rng.Value, 1)
iletter = Asc(firstletter)
MsgBox iletter
If iletter < 71 Then cmfolder = "CM A-F" Else
If iletter > 70 And iletter < 77 Then cmfolder = "CM G - L" Else
If iletter > 76 And iletter < 83 Then cmfolder = "CM M - R" Else cmfolder = "CM S - Z"
MsgBox cmfolder

## Sum Based On Left 3 Letter Criteria

Aug 10, 2006

I'm looking for is a way to sum a bunch of numbers using a sum if statement that start with PRJ and are followed by any combination of five numbers. Like this

=sumif( Range, PRJ#####, SUm range)

## Search Column For Cell Starting With X Then Copy Row To A New Tab Based On A Cell Value

Jan 13, 2014

I have an tab with 20,666 rows that I need to separate into different tabs based on what a cell in a specific row starts with.

Data Tab
001-020-002-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
001-020-005-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
001-020-006-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
001-020-007-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
001-020-008-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447

[|Code]...

So what I am looking to do is search the APN row in the Data Tab that starts with the number from the APN # row in the APN Tab and then copy the row to a new/existing tab named after the Description on the APN Tab.

I think that I have explained what I need to do

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

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

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