Echo Back the Cell Location

Is there a VBA routine that will echo back the cell 
location based on the value in another cell ? For 
example, consider the following

     A      B      C       D
1   101           110.5  
2   102
3   103.9 
4   104
5   110.5
6   121

I'm looking for the cell location for the value (110.5) 
contained in cell C1 and place the returned location (A5) 
in cell D1.
I tried a few things, but I'm just getting started in 
VBA. Any help would be appreciated. Also, is it possible 
to do the same task with worksheet functions ? 

Thanks in advance,

0
anonymous (74721)
7/28/2004 3:09:56 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
288 Views

Similar Articles

[PageSpeed] 49

Hi
with worksheetfunction:
="A" & MATCH(C1,A1:A10,0)

-- 
Regards
Frank Kabel
Frankfurt, Germany


Davey Boy wrote:
> Is there a VBA routine that will echo back the cell
> location based on the value in another cell ? For
> example, consider the following
> 
>      A      B      C       D
> 1   101           110.5
> 2   102
> 3   103.9
> 4   104
> 5   110.5
> 6   121
> 
> I'm looking for the cell location for the value (110.5)
> contained in cell C1 and place the returned location (A5)
> in cell D1.
> I tried a few things, but I'm just getting started in
> VBA. Any help would be appreciated. Also, is it possible
> to do the same task with worksheet functions ?
> 
> Thanks in advance,
0
frank.kabel (11126)
7/28/2004 4:52:20 AM
Reply:

Similar Artilces:

IN CELL CALCULATIONS
DONT KNOW IF I CAN DO THIS BUT.. IM TRYING TO ADD A FORMULA INTO A CELL TO SPLIT THE NUMBER I PUT INTO THAT PERTICULAR CELL, FOR INSTANCE IF I ENTER THE NUMBER 2 INTO A CELL I NEED IT TO THEN SPLIT THAT NUMBER INTO FRACTIONS, THIS IS SO I CAN CALCULATE MY FOOD STOCK, AS IF I NEED TO CHECK STEAKS FOR INSTANCE, I MAY HAVE ONLY SOLD HALF FOR A PERTICULUR MEAL SO IF I ENTER THAT 2 HAVE BEEN SOLD, I NEED THE CELL TO CALCULATE THAT IN TOTAL IVE ONLY SOLD ONE WHOLE STEAK... Hi! It sounds to me as if you need to settle on a safe system of units. Fo example, if the smallest unit of steak is half a s...

powershell_ise always echos output
Hi, I am using powershell_ise if I write the following simple script $a = Get-Date -Format "yyyyMMdd" Write-Host "Filename $a" the output is PS C:\WINDOWS\system32\windowspowershell\v1.0> $a = Get-Date -Format "yyyyMMdd" Write-Host "Filename $a" Filename 20100305 when I would expect simply Filename 20100305 can anybody please tell me why every line is echoed to screen and if I can stop this behaviour please. I would like the editor to act more line the commandline powershell. thanks in advance. cheers martin. ...

Reports - front and back
I have received a new report request - and I'm stumped (again).The front page contains page header, detail section of text fields that can grow, page footer.The back page contains page header, detail section of boilerplate text, page footer.The page headers and footers are different on the front and back, but I have the code - thanks to this forums participants - that handles it.The dimensions I'm using are: top and bottom margin = .5 inch; page header = .5 inch; page footer = 2 inches. That should leave me 7.5 inches in the detail section - which I've doubled to handle the front...

Re: How do I make a macro remove last char only in a cell?
I don't know why this never showed up when I first sent it �200310261033! However, > Thank you all guys! I have yet to subsume each suggestion entirely but > thought it best to respond whilst the thread was still alive so thank you to > Don Guillett, Michael Bednarek, Tom Ogilvy, and Kent Schederin! I'm sure I > will be able to extract the required code from you reply posts. CHEERS GUYS! > > -- > Best regards > Martin Richecoeur [notsovilefriend] > http://homepages.paradise.net.nz/richka/ > "Kent" <schederin@home.se> wrote in message &...

Initializated cells
How can i do to open a sheet with some cells in zero or in blanks. ...

Contact List Location
This is my first attempt to use Outlook and I have googled the NG for an answer to the question below but did not find one. I try to keep all of my data on a partition other than the system partition. As an example, I keep My Documents and Outlook Express messages out on drive D:. I have just installed Outlook to use its Calendar and Contacts features and would like to keep their data out on D: also. How can I set up Outlook to store and look for my Calendar and Contact info there. Thank you very much. With Outlook closed, move its data file (PST file) to that drive. The next time you o...

More than one line of copy in a cell
Version: 2004 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have three columns per page. First column contains 6 numbers, second column 10 numbers (they fit easily), third column approx 100 words. The words flow on across to the next sheet. How do I constrict the information to the column where I want it to be! Select the cells, go to Format> Cells> Alignment, then check the box for 'Wrap text'... You may have to adjust the Row Height to your liking because it will increase based on the largest number of lines in any cell in a row. HTH |:>) Bob Jon...

cell lenght MUST be always 30
eHello: I need to always have 30 char in a given cell, if the original string is less than 30 chars, lets say 23 then I need to fill in at the right of the string with 7 blank spaces. The original string lenght may vary from 1 to 30. Is there a formula or VBA code to do this? Any and all help would be highly appreciated! PLK >The original string lenght may vary from 1 to 30. Using a formula... A1 = some string B1 = formula: =A1&REPT(" ",30-LEN(A1)) -- Biff Microsoft Excel MVP "JABAgdl" <JABAgdl@discussions.microsoft.com> wr...

Macro beginner
I have recorded a macro to set the active sheet to Landscape and enter my name in the left section of the footer as text and set the font size to 8. I have stored it in the Personal Macro Workbook. I use Excel 2003. I have gone into the VB Editor and deleted the parts of the Sub which I don’t need. It now reads: Sub LandscapeMyName() ' ' LandscapeMyName Macro ' Macro recorded 09/12/2005 by MyName ' Keyboard Shortcut: Ctrl+Shift+L ' With ActiveSheet.PageSetup ..LeftFooter = "&8MyName" ..Orientation = xlLandscape End With End Sub Years ago, in the anc...

Sort Descending with IF and empty cells
I am trying to sort a column descending. The cells functions are =IF(J38=0,"",I38/J38). I want nothing to appear in the cell if the result is true, but this poses a problem when I run my macro for sorting descending. It puts my blank cells at the top. Is there a way to sort descending but have my blank cells all at the bottom? Any assistance would be appreciated. -- Mike Instead of returning "", you could return a large negative value, like -9999. You can apply conditional formatting such that if the cell contains -9999 the foreground co...

Cell Navigation
how can i force cell navigation in a specific order via the tab key? i have done this before but it has been a while and i can't remember how i did it. thanks for helping. Lock all cells on a sheet (they are by default). Then unlock the cells you want to be able to automatically navigate to, and Protect the sheet, unchecking the option permitting users to select Locked cells. I hope that's what you're looking for. "LaDonna" wrote: > how can i force cell navigation in a specific order via the tab key? i have > done this before but it has been a w...

How to have TAB name to appear in a Excel cell?
How do I get the Worksheet name to appear in an Excel cell? See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- HTH RP "TAB name to appear in a Excel cell?" <TAB name to appear in a Excel cell?@discussions.microsoft.com> wrote in message news:2DBC0B15-12FC-43FA-9D11-AFD1B4B286BF@microsoft.com... > How do I get the Worksheet name to appear in an Excel cell? Hi see: http://www.xldynamic.com/source/xld.xlFAQ0002.html -- Regards Frank Kabel Frankfurt, Germany "TAB name to appear in a Excel cell?" <TAB name to appear in a Excel cell?@discussions.micr...

Data validation, my "in-cell dropdown" arrow is not showing
Using data validation, my "in-cell dropdown" arrow is not showing on the active cell. After a quick search, I tried removing frozen panes, and made sure I was "showing all" objects under Tool Options View tab. Any other ideas? Thanks, PTweety If you've tried the other solutions without success, maybe the repair will help, as described here: http://www.contextures.com/xlDataVal08.html#ArrowsNotVisible pickytweety wrote: > Using data validation, my "in-cell dropdown" arrow is not showing on the > active cell. After a quick search, I tried re...

setting to go back to inbox
I have Windows Vista and Windows Mail. When I delete an email message while in the viewing mode, Windows Mail automatically opens the next message. I want it to go back to my inbox. All my previous mail software did this. Does WMail allow this? -- cdo Uncheck the Preview Pane. I don't know what you used to use, but Outlook Express works the same. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "cdo" <cdo@discussions.microsoft.com> wrote in message news:E9B313C2-38F9-4131-9767-586AF7776E13@microsoft.com... >I have...

Check Boxes in Cells
I would like to have check boxes in cells to display any expression that evaluates to True (checked) or False (unchecked). And, I would like to change the cells' value to be "True" or "False" by clicking the box - one in each cell. I don't think this is the job for the check box control. Can this be done? A checkbox from the Forms toolbar will do exactly this. In the Format checkbox dialog, set the cell link to your target cell. Clicking the checkbox will toggle the cell's value between True and False. Entering 0 in the linked cell will clear the checkb...

Echo off
I know in Access when you run a macro you can turn the echo on or off so you don't have to watch the macro being executed. Is there cod that I can add to do the same in excel????? Any help I would appreciate it! Michae -- Message posted from http://www.ExcelForum.com use this : application.displayalerts=false (and of course, changing it to "true" turns it back on -- Message posted from http://www.ExcelForum.com Hi application.screenupdating=false -- Regards Frank Kabel Frankfurt, Germany "daniels012 >" <<daniels012.1b7ae6@excelforum-nospam.com> ...

Moving back to Outlook 2000 from 2003
If I want to move back to 2000 from 2003 can I export my old emails to 2000? Is there any way to save emails and archive them on an external source? SteveS <anonymous@discussions.microsoft.com> wrote: > If I want to move back to 2000 from 2003 can I export my > old emails to 2000? Yes, but don't use export. Simply create a new PST with File>New>Outlook Data File>Outlook 97-2002 Personal Folders File (.pst). Then, copy the items you wish to transfer to OL2000 to the old-style PST. That PST will then open in OL2000 with File>Open>Personal Folders File >...

how to change cut/copy/paste shortcuts back to ctrl + x/c/v
Hello, I don't know how to change back this shortcuts to ctrl +X/C/V Now the copy/paste/cut is done with ctr + insert , shift + insert, and shift+del. Thanks in advance opetran Hold down the Alt key and then press + on the numeric keypad to display the Customize Keyboard dialog and in turn press each of those key combinations and use the Remove button to remove whatever association has been assigned to them in place of the default. -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid pr...

referencing cell range in different excel files
I have files that individuals are updating individual schedule data that I want to bring it together in a master sheet. I tried using reference for just one cell and keep getting the REF! error. I would like to reference the server name instead of the drive letter it is mapped to if possible Thanks for your help! Here is the syntax I am using - just trying to see cell C!0: =('\us1n50\PMO_office\XP_upgrade_schecules\Pub_Sevices\[pub_schedule.xls]Sheet1'!C10) us1n50 maps to my U: drive. ------------------------------------------------ Message posted from http://www.ExcelTip.com/...

Need to create a SUM for data in a row of cells containing IF fun.
I have a row of cells with IF functions that return a number in each cell. I have a SUM total at the end of the row which returns a zero valuve! Why is the SUM function returning a zero value? Likely the IF functions are returning text digits rather than numbers. Jerry Banana wrote: > I have a row of cells with IF functions that return a number in each cell. I > have a SUM total at the end of the row which returns a zero valuve! Why is > the SUM function returning a zero value? ...

enter data in cell but cannot save until click off cell in excel
When I enter values into a cell in Office 2003 I cannot click the save button to save the change until I click on another cell that I have not modified. This funcion used to work but has not for the last week. Can anyone help me? When you edit a cell, Excel is in Edit Mode, and many functions, such as Save, are disabled. Simply press Enter after typing in a cell and you should get full functionality back. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "T70McCains" <T70McCains@discussions.microsoft.com> wrote in mess...

how prevent formula in cell from deleting when deleting value????
example: =sum(a1-b1-c1) answer and formula in cell d1 if i delete the answer to this formula in cell d1 it also deletes the formula. i want the formula to stay in the cell d1 how can formula be locked or protected in cell d1 and still make and delete value entries in cell d1 ????? Your message isn't very clear. If you want the formula to remain in D1, then it will continue to calculate. If you mean that you want to override the formula, but have it return if you delete the value used to override it, that will require an event macro. One way: Put this in your worksheet code module (...

how to protect one cell and not others
Is there a way to protect one cell or a range of cells without protecting the entire worksheet? For example, enter data, but formulas are untouched. Thanks, Cyrus Walker Select whole sheet ( corner between ROW & COLUMNS ) Format > Cells > Protection > uncheck the LOCK , now all cells are unlocked Select the cells you want to lock do the same again Format > Cells > Protection > check the LOCK Locked cells have no use until you actually protect the Worksheet or book by selecting Tools> Protection >>>>> choose one "Cyrus" wrote: > Is t...

using value of another cell as row reference number
Hi! How can I use the value from another cell as current row refernce number, e,g., A(B2)? Thanks in advance. charles wrote: > Hi! How can I use the value from another cell as current row refernce > number, e,g., A(B2)? Thanks in advance. =INDIRECT("A"&B2) Dave dvt at psu dot edu Dave, Thanks a lot. It works great! "dvt" <dvt@nospam.psu.edu> wrote in message news:<ezR$3KqgDHA.2248@TK2MSFTNGP09.phx.gbl>... > charles wrote: > > Hi! How can I use the value from another cell as current row refernce > > number, e,g., A(B2)? Thanks in a...

Help Needed ... Probably simple for you gurus ... locking cell after data entered and
positioning next cell after enter is hit I am very computer illiterate and have searched and experimented for 2 days with limited success here is currently what the macro does when data is entered into a cell that cell is filled with the color green....simple...and works also there is 3 different columns with data being entered...A....B....C....after data is entered in C it automatically puts the current date in the corresponding cell in column D....that is somewhat ok...would like it to time stamp it also here is features that I would like to add Once data is entered to a cell I would l...