Free Microsoft Excel 2013 Quick Reference

Excel - Find cell address using vb

Hi there

I am after some help to find the address of the last populated cell in a row and then to paste data into the cell

I have a piece of code below which returns the column number but I need to convert to the cell letter reference - So 1 would become a etc

 
Dim ix As Long

ix = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
LastColumn = ix[
/CODE]

Thnaks for help always appreciated
Found a slight problem


Post your answer or comment

comments powered by Disqus
Hi all,

how to find cell address.

certain cells are filled with some strings i want to findout the correspoind cell value.

for example...

name----total----cat
a--------5-------3
b--------7-------3
c--------20------1
d--------18------2
e--------15------2

using vba code i want to find cell address of cat 3 name b.

by seeing we can say the cell address of cat 3 of name b is (2,3) i.e 2nd row and 3rd column

i want to find the cell address.
is it possible or not if so can anybody provide me an example.

regards,
venu.

I need to run a report to see how many files have corrupted data due to wrong cell format, is there a way to check the current format of a cell? Im using vb.net interup.excel to access files. thanks

Hi,
I was wondering if it is possible to conect to MySQL database and transfer only the data in a cell (in Excel) to MySQL , without using VB script?

Let me explain.

I actually want to create a form in excel. The form will be connected to a MySQL database.
I want to define a cell that shall take the input and send it to MySQL. I don't want to use any script. Is this possible????

I would like to find a number in an array, then use the cell address of that
number to pull out more info from that row. Here's an example:

A B C D
1 Part # Cost Use Total (= Cost x Use)
2 A1 $5 100 $500
3 A2 $7.50 10 $ 75
4 A3 $100 8 $800

I would like to find the part number of the highest total use. In this
example, that's part A3. I'd like to search using the =LARGE function, find
the highest values, and use the addresses of those values to find part
numbers and other information on those parts.

Any suggestions?

Find the cell address where the user created button placed in excel. Please help in this.

I work at a plant and need to take inventories on items. In one column, I need to be able to find values that are less than or equal to 15. I know enough to be able to find those values using COUNTIF, but I do not want the cell to display a number value. For example: =COUNTIF(N75:N10000, "<=15") displays "1." What I want to do is to be able to use this simple function but display the cell address(es) where this criteria is true. I guess I am looking for a formula that would use that criteria i gave, and output any cell addresses that are <=15. Say there were 4 cells that had values <=15, I want to search through them, and say N75, N80, N82, and N95 were <=15, I want to output them somewhere to make it easier to find these numbers in a large spreadsheet. Any ideas or hints would be much appreciated, thanks.

Using Excel 2003. I have a formula that obtains data from another sheet. I use several copies of this sheet within the same book and in every sheet the formula needs to point to a different row. Within the formula, could I use the content of a third cell to act as the cell reference inside the formula. The formula is ='Sheet 1'!a2. When I make a second copy of this sheet in the same book I want the formula to read ='Sheet 1'!b2. In this sheet there is a cell that has as content B2 (cell A10). Could I have the cell address used in the formula come from the content of cell A10? Each sheet has about 15 similar formulas and I may end up using over a hundred instances of this sheet. Manually changing each formula in each sheet would be tedious and time consuming.

I have some data in A1:B20. These cells contains few words, all words are unique. In cell D1, if I write any word, I want excel to search that word in the range A1:B20 and return the cell address in E1. Is this possible through a formula based solution?

Hello! I usually come here for help when I can't find what I'm looking for, so maybe someone can help. Most of the questions I've seen are asking how to get a cell address. I would like to go TO a cell having the address.

I have a cell that contains a cell address using the the =CELL("address".... formula. I'm creating a macro and I want another cell value to go TO that address. So for example, I want this to happen (I know this doesn't work, but hopefully you can see what I'm trying to explain):

cell value: '[macro working file.xlsm]Innovation'!$B$13

code:
range('[macro working file.xlsm]Innovation'!$B$13).select

so I guess I'm asking if there's a way to take that whole address and use that. If not, I can break it up into book, sheet, and cell reference. with some some .value manipulations.

Thank you so much,
Robert

Col a............... Col B

27-Aug-2005 ..................03-Mar-2006
10-Feb-2006
19-nov-2006
25-Jan-2007

I am looking for a function that gives cell address in range A1:A4 ,date that
is(nearest) greater than B1 date.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200702/1

(I'm using MS Excel 2003 (11.8120.8122) SP2)

Please change the priority of Text Only to override the process by which
Excel flags cell addresses. True, "HP2" is a potential cell address, but
"CCC2-0307, HP2-0106" is not, especially if the cell is set for text only.
It is exasperating to have to change an entire database just because Excel
insists on reading text with potential cell addresses as actual partial
formulae.

Again, designating a cell/column/row/worksheet as Text should override any
automatic search by Excel for cell addresses. The other option is to be able
to disable this searching process in the Options menu.

--
Mike S.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hello,
Does anyone know the formula that produces the address (e.g. $A$1) of a cell within a table? I found the formula that gives the address of a single cell within a single row (or the address of a single cell within a single column). But I need the formula that gives the address of a cell where the row intersects the column. If I know the column header name and the row header name, how do I find the cell address for the two dimensional intersection? Thanks a billion.

I'm seeking for a formula for finding a cell address of the last blank cell after a value and before a cell with another value.
There can be many blank cells between two cells which values.
I've included a spreadsheet with a value in D1 and a formula in E1 which find the cell address of the value looking up in colom A.

Who can help me?

Thank you.

I'm trying to find the cell address for the lowest number in a column. This does not seem to be working when the number is 0. Can someone please help me figure out what's wrong with this code. It works when there aren't any 0's in the column.

Thanks in advance
Trevor

Function MinAddress(rng)

Set rng = Columns(22)

' Sets variable equal to minimum value in the input range.
MinNum = Application.Min(rng)

' Loop to check each cell in the input range to see if equals the
' MinNum variable.
For Each cell In rng.Cells
If cell = MinNum Then
' If the cell value equals the MinNum variable it
' returns the address to the function and exits the loop.
MinAddress = cell.Address
Exit For
End If

Next cell

End Function

Hi,

I need a formula to find out the Cell Address of the last cell in a range that has a value (numbers) in it.

any suggestion...??

I am creating a sheet that will compare info from different sources and display it side by side. In this sheet data will be displayed that will need explanation and I decided the only way to make it look decent was to utilize comments. I however cannot figure out how to set the text of a cell comment using VB Code. Currently the info to be put into the cells' comment boxes are held in an array called Comments(X). There are 23 cells that need commenting.

I have tried using

	VB:
	
Activecell.comment = Comments(X) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but this results in a error stating that the object does not support this property or method. I would appreciate any help you all can give.

I am trying to display a selected cells address (Like C1) using the keyboard or the mouse and I would like the address put into a string for later use.

Here is what I have, but it displays the contents of the cell not its address. I am stuck and need some help.

Code:
Sub DisplayCell()
Dim vData
Dim sCell As String
    On Error Resume Next
        Application.DisplayAlerts = False
 
            vData = Application.InputBox _
             (Prompt:="Please click on a single cell, " _
             & "or enter the cell address using the keyboard (ex:C1).", _
             Title:="Input the cell address", Type:=2 + 8)
 
    On Error GoTo 0
        Application.DisplayAlerts = True
 
    If vData  0 Then
        'Convert the cell address into a string
        'using sCell
        'Display the cell address
        MsgBox sCell
    Else
       Exit Sub
    End If
End Sub


i had this formula at one time but lost it.

i am trying to figure out how to evaluate a column and get the cell address of the cell with the last entry

example

--A
1 e
2
3
4 g
5
6 h

all other cells under A6 are blank

the answer is A6

anyone have a formula for this?

Hi All,
I have three values in three cells, like cells are A1, B1 and C1 and values are 3,2,4 respectively. If I make MAX() formula then I will get value 4. But I need the address where this value is. For this call cell address will be C1.

If any one know please help me.

Thanks in advance,
Suman Biswas

We have an existing report created using VB and it is run from Access... now
they want it on the web... how can I use that VB code and "launch" it from a
web page using the info they've entered into an HTML form? Any points in the
right direction would be appreciated!

Thanks,
Carmen

Hi,

Here's a quick question followed by a more indepth explanation. Obviously I'd love it if someone would to help me solve my bigger problem but I'm sure that most people will only want to take a stab at the relatively easy step that I'm currently stuck on. So I figured I'd just give you both sets of questions and let you decide what you want to tackle. Thanks either way!!

QUICK:
Is there a way to use text and an ampersand (aka "&") to describe a cell's address inside a formula? For example:
 or even 
	

	
Now maybe VALUE was the wrong function to use in this example for some reason but, it's not what I'd actually be using
anyway, so don't sweat the small stuff - if you want to use a different function (i.e. not VALUE) feel free. I just want to
figure out how to get the stuff in the brackets to work.... Any suggestions???

INDEPTH:
Background: I'm trying to sort & transpose data from two long columns (1st="Data", 2nd="Names") into a series of much shorter columns ("IndivNames") - where each column represents one Name and is filled with all the Data that fits that category. Basically I want to a combination of AutoFilter & PivotTable but I also want it to switch from a vertical to a horizontal set-up.

Workbook Setup: Anyway, I've set up the original table of values on Sheet1 and entered the "IndivNames" column headers into Sheet2. I also used a COUNTIF function to count the number of cells associated with each unique Name and have put the results in the 2nd row of Sheet2, below the column headers. Let's just assume that there are 10 "Data" values for every "Names" value and 60 values in total (i.e. 6 unique Names).

My imperfect solution: I figured that to do what I want I'll need to use INDEX/MATCH or VLOOKUP to list all the values in my Sheet1 "Data" array that correspond to values in "Names" that, in turn, match the "IndivNames" column headers on Sheet2. And I can get that to work great, except that each "IndivNames" column has to contain 60 cells of formula (since it has to cycle through ALL the cells in Sheet1's "Data" column) - that means that each "IndivNames" will contain FIFTY error messages that I'll have to filter out by hand. Yuck! If I were writing a macro I'd just use a loop to cycle through the "Data" column and to only insert values in an "IndivNames" column if there was a match. But I don't want to use VBA this time if I can possibly help it....
So here's what I've got so far:
 If I sort Sheet1 according to "Names" then I only need to use this in 10 cells for the 1st "IndivNames"
column, not 60. But I still have a problem once I get to the 2nd column. And that's why I wanted to figure out a way to
construct a cell address using strings: because if I can say something like 
	

	
 in Sheet2, then I could use the COUNTIF results (in Row 2 of Sheet2) to offset the starting point of the INDEX array (but
not the whole array like OFFSET would do).

I'm guessing that I'll need to use an array formula ({}) somehow but I'm too confused now to figure out where/why. Any ideas?
If you don't have a solution that uses worksheet functions, what about something in VBA?? I know I said that I didn't want to use it, but that's just because I don't want to have to figure out the coding (I just finished a BIG project & I'm all coded out) - however I would be very grateful if someone else could suggest a coded solution...

Well, if you've managed to get this far, then you deserve a medal. Thanks for sticking with me. And many more thanks if you are willing to help!!

Hi there.

I calculated a cell address using

=IF(D26="botline",CELL("address",C26),"")

, which in this case returns $C$2.

From here I wish to add a sum of 15 to get a result of $C$17.

As a follow-up, once I achieve this I am looking to fill cells $C$2 to $C$17 with a value -- but am in need of adding the cell addresses first.

Any insight on how to go about this?

Thank you!!

Column A is the year. Column B is the corresponding interest rate for that year. This Data runs from A4:B35. In column N is a particular start year, and column O is a particular end year. What I need in Column P is the average between from the year in N to the year in O, which I want to be pulled from A4:B35.

Example
A B N O P
1980 5.0 1981 1982 Need average from B2:B3 here
1981 6.0 1980 1982 Need average from B1:B3 here
1982 5.5 1982 198x Need average from B3:Bx here

The columns in between these will be user entered. Then the spreadsheet will pull column N and O from what was user entered. So I need the formula in column P to be able to pull the average from any possible range of years that are available in A4:A35. So I need to be able to average A6:A8, A10:A20, A4:A30, etc. I've been able to get a cell address using an array formula, but that does me no good because the address cannot be used within another forumla.

If it matters, this will be averaged for the total year, i.e. 5.0%*365 days + 6.0%*365 days, etc. As always, this things make plenty of sense in my head, but may be confusing. Let me know if you need more information.

I apologize but I cannot post the file. I will work on creating a similar copy in case its needed.

Hello, please help me create a cell address using calculated data.

A1="P3" A2="right(A1,1)" B3="25"
B5 = B(A2) --or-- B(right(A1,1))

I wish for B5 to show 25.

I cannot figure out how to add the row number to the column letter.

Thanks!


No luck finding an answer? You could always try Google.