Free Microsoft Excel 2013 Quick Reference

Get cell address in a UDF

Hello all,

I am writing a UDF that needs to be aware of the cell address for which it will return a value. (For example, the UDF needs to be able to return one value if the cell is B12 and a different value if the cell is A3.) Short of passing in the cell's own address as an argument (lots of probs with that) or passing in a unique name where I can create a kind of lookup table (inelegant), I can't seem to find a way to do this. Ideally, I would love the UDF to simply be aware of the cell address where it is executing. Any thoughts?

Thanks in advance.


---|A B C
------------------------
1 |12 32 44
2 |14 5 19
3 |54 23 77
4 |23 2 25
5 |54 12 66
6 | 3 54 57
7 | 23 2 25
8 |
9 | ##
10|c5

Need a formula in ## so:

In the cell ##, can we get value of the cell, whose address is indicated in cell A10.

As currently in A10 we have written C5, so 66 should appear in place of ##.

If we change the cell address in A10 to, say, B3, 23 should appear in ##.

In other words, we want cell formula with a varible cell address and this variable's value(C5, here) is in other cell (A10).

:) Hello All,
I would like to know how I could change the cell address in a formula without having to edit the address.
Example:
Month listed in range C4:N4 (one month per column)
In the cells listed below each month is data that I would like to reference into a summary page.
The existing addresses are
='U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$E$11
='U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$F$11
='U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$G$11
='U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$H$11
etc...

Each month I must manually change the cell address column letter to extract the data.
Is it possible to write a formula that will change as the month reference changes?

I have got as far a getting the cell reference column to change but it will not work as a formula should.
My attempt is

Final result:- =U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!$E$11
Column reference:- =CHOOSE(MID($W$13,FIND(" ",$W$13,1),3),3,4,5,6,7,8,9,10,11,12,13,14)
Path to address:- U:Inventory Reporting[2005 PLN Inventory.xls]Monthly DOH'!

Any advise would be appreciated or a better idea.

Thank you and best regards

Max

when i am doing the coding

may i know if there is a way to replace the cell address $A9 below:


	VB:
	
Range("A1").Formula = "=IF($A9="""","""",SUMPRODUCT((LEFT(" & myFilePath & "!$C$2:$C$30,8)=$A9)*(" & myFilePath &
"!$D$2:$D$30=B$1)))" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
with a variable for use in VBA?
I have tried using string like below but it doesn't work

	VB:
	
 
CellAddr = ActiveCell.Address 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have 2 Sheets, "Template" and "Notes".

In the Notes Sheet I have 3 Columns:
- Column A contains hyperlinks that activate only if the corresponding cell in Column B has a cell address in it. The hyperlink links back to the "Template" Sheet and to the cell address displayed in the corresponding cell in Column B. Column C is simply the text associated with that cell address on the "Template" sheet.
- So, the "Notes" sheet, e.g., has a list of 10 lines of Hyperlinks, cell addresses, and notes in their respective columns.
- The cell address in Column B and the Note in Column C are populated using a Macro ("Submit _Note" attached to a Command button on the "Template" sheet.
- Each time a user submits a note, a new line is created at the bottom of the existing list of hyperlinks and notes.

I would like to add code to the "Submit_Note" Macro that returns the user to the cell that that they just recorded the note for, e.g., user a writes a note in cell C3 of "Template" Sheet and clicks on the command button that runs the "Submit_Note" macro, which copies the note from C3 (and related cell address from C2**) to the "Notes" Tab.
** a separate macro records the cell address which is the subject of the note to cell C2

Thank you in advance.
Incuss

Hello,

I'm trying to write a macro that will take the value of a selected cell in one workbook and copy the formating of a matching cell in a seperate workbook.

Being new to VBA I'm sort of stumbling along. So far this is what I'm trying. This is a portion of a more complex macro, which is why there is a lot of selection offsetting.

Made a function to get cell format that I need:

Function GetCellFill(Cell As Range) As String
GetCellFill = Cell.Interior.ColorIndex
End Function

And the Macro:

Selection.Offset(0, 3).Select
Selection.FormulaR1C1 = "=MATCH(T(R[-1]C[-14]),'[SecondWorkBook.xls]Rates'!C3,0)"

Selection.Offset(0, 1).Select

Call NextMacro

Dim VRN As Long
VRN = Selection.Offset(0, -1).Value

Selection.FormulaR1C1 = "=getcellfill('[Billing Rates Xref Activity Type with description.xls]Rates'!RVRNC4)"
(this is where the portion that does not work, i can't seem to figure out how to get the correct reference)

Selection.Offset(0, -4).Select
Selection.Interior.ColorIndex = Selection.Offset(0, 4)

What I tried to accomplish with this was to find the row number of the matching cell in the second workbook (column remains constant). That value becomes the variable VRN value. Then use my function to pull the cell format in the second workbook and apply to desired cell.

Any help would be appreciated. If there is an easier way about this I'm all ears, this is just how I thought it out with my limited VBA knowledge.

Thanks

I know that I can get the address in integer format of an active cell by:

ActiveCell.Column

My question is say I'm in 57th column or "BE". How could I get that "BE" instead of the number 57. Is there a method I could use?

Hi,

Is it possible to store a cell range in a cell and use it in a formula. For
example store A1:A10 in a cell say B1 and use it in a formula say
=SUM(XX(B1)) so that sum of A1..A10 is returned. When ever value of B1 is
changed, formula should be re-calculated for fress address

I have a 10X12 Range of Input Values.
I want to select a cell within this range with my mouse.
I want to use the cell address coordinates as values in a Macro.
So: if ActiveCell Adress is (x,y) - [these can be column/row # or range
coordinates]
Then I want to say something like:
RowValue = x
ColumnValue = y
So that I can use the RowValue and ColumnValue as offset values to create a
standard macro for any cell selected within the range.
I hope this makes sense - and I thank you in advance for all advice offered.
Michael

I noticed that Excel will accept any number of arguments for a worksheet
function if the parens are doubled, and that all of the arguments work with
Excel's SUM and AVERAGE functions (I haven't tried any other functions). For
example, this doesn't work because it exceeds the maximum number of
arguments:

=AVERAGE(A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25, A26,A27,A28,A29,A30,A31)

But this works fine, and the average is correct for all 50 arguments. Note
the doubled parens:

=AVERAGE((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25 ,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49,A50 ))

And this works too:

=SUM((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24,A25,A26 ,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49,A50))

(BTW, I'm using Excel 2003 and haven't tried this with other versions.)

I tried to get that to work in a UDF, and found a way to get access to all
the values. But, then I realized I seemed to have access to ALL of the
values in the worksheet -- even those which weren't passed to the function
in an argument -- which seems very strange. Here's what I did:

First, the UDF uses ParamArray:

Public Function MyFunction(ParamArray arglist() As Variant)

Here's how I called it from the worksheet:

=MyFunction((A1,A2,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16,A17,A18,A19,A20,A21,A22,A23,A24, A25,A26,A27,A28,A29,A30,A31,A32,A33,A34,A35,A36,A37,A38,A39,A40,A41,A42,A43,A44,A45,A46,A47,A48,A49, A50))

This gives me access to the values in A1:A50:

arglist(0)(1)
.....
arglist(0)(50)

So far, so good. BUT, this gives me access to the value in A51, which wasn't
passed to the function:

arglist(0)(51)

And, this gives me access to the value in cell J10, which also was not
passed to the function:

arglist(0)(10,10)

So, I seem to have access to all the values in the worksheet, just by
passing A1. But as weird as that seems, it wouldn't be a problem if I could
somehow determine what cell references were passed. But I haven't been able
to find a way. I've tried setting the paramarray to an array declared in the
UDF, but that didn't help.

Any suggestions?

Many Thanks,

Greg

P.S. -- Is it well known that you can pass any number of arguments to
Excel's SUM and AVERAGE functions (and others for all I know), and still get
a correct result, just by doubling the parens? I'd never heard of that
before I stumbled into it looking for a way to get more than 29 arguments
into a UDF.

How can I retrieve the address of a cell in a A1 format?
Example: I have a cell that has something like 28.010D. How can I use a
formulad to retrieve the reference of that cell in a worksheet, let's say the
cell that contains the 28.010D is the D45. I want to apply a formula so that
the result is D45.
Thank you

Please I need a formula to look for a string in a range of cells that can have text, numbers or both, and return the cell address. In the example below I need in B1 to have a formula that will look after this string "Sub-Total by Work Function:" in the range A1:A22. I tried =LEFT and SEARCH functions but did not work. I can't use as search string the whole phrase (Sub-Total by Work Function: Production, Sub-Total by Work Function: Studio) etc because I have a few hundreds Work functions. That's why I need to find the cells that have only the beginning of the string.

A1 Accnt Serv
A2 2.00
A3 3.55
A4 Editorial
A5 Sub-Total by employee
A6 Sub-Total by Work Function: Production
....

A10 Sub-Total by Work Function: Studio

A22 Sub-Total by Work Function: Managers

Thank you so much

Vera

Is there a way of getting the cell references in a formula change following a change in the content of a cell in the worksheet ?? Please see attached file.
Any help will be much appreciated

hi all,

i have an excel worksheet which has some data a row. the last column in
that row is for email addresses. i am entering multiple email addresses
in
a single cell. when i click one of the email addresses in that cell, it
should transfer all the email addresses into a word mailmerge document
which i am printing for multiple users.

But when i click, only the first email address comes into the field in
the
Word Mailmerge document.

Please note that I dont want to place each email address in a single
cell
in that last column as one row needs to have multiple email addresses.

I also donot want to merge each cell in a column with the one below it,
inorder to have the last column showing each address in each cell one
below the other.

PLEASE HELP ASAP

I have a spreadsheet in the following form:

            Col1   Col2   Col3   Col4   Col5   Col6   Col7  
Col8
Row1        10                   30            50
Row2        50     40     30     20     30
Result      60                   90            30
I'd like to sum Row2/Cols2~4 based on a) Row1/Col4 having a value and b) the first preceding non-blank column in Row1 is Col1. Thus, the Result for Col4 would be 90 (40+30+20).

I believe I need a function to determine the cell address of the previous non-blank cell in Row1 in order to sum Row2 from that endpoint.

Any suggestions for how do to this?

What I would like to do is store a cell location in a cell on another sheet. So on a worksheet_change event it stores the cell location, old value, new value on another sheet. Here is code that is most likely flawed. I'm also looking for the correct way to get the target.address that has the full location including worksheet name:

Dim oldvalue,
newvalue As Variant
With Application
.EnableEvents = False
.Undo
oldvalue = Target
.Undo
newvalue = Target
.EnableEvents = True
End With

Sheet7.Cells(5, 3) = oldvalue
Sheet7.Cells(5, 4) = newvalue
Sheet7.Cells(5, 5) = target.address
Then eventually I want VBA to put the oldvalue in the cell location in Sheet7.Cells(5, 5).

In a nutshell, I'm logging changes on a seperate worksheet and trying to have code that would revert all of the changes to the original value. Like a clear changes button. I think I've got most of it figured out except for the correct way to get the cell location. thanks in advance.

Hi everyone,

Can someone please guide me as to how would i store a cell address in a varaible


	VB:
	
If Not rngX Is Nothing Then 
    MsgBox "Found at " & rngX.Address ' the address it prints out when it finds it
    rngX1 = rngX.Address 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
with the above code im trying to find the month value in the cells and once it finds the location how do i store that location as in a varaible so that i can paste the copied values

Thanks

Hi All,

Is there an easy way to identify range or cell addresses in a formula. I want to find the alphanumeric part of the address.

For example in the SUM Formula =Sum(A4:A230), I wish to separate out A4:A230 part. Or at least I want to remove the number part in the address and just want to get Sum(A:A).

Is there a function that can do this? I don't want to do this using usual string/text operations as it becomes a laborious process and has to consider all possible cases of range addresses.

Thank you,

MG.

how do I know which cell called my userdefined function?. for example, I have a macro in a module and user used the function in several cells. I call, Application.CalculateFull, which will execute the user defined functions.

Is there anyway I can find out from which cell the UDT is being called?

hi,

the answer to this is probably simple but i wonder if anyone can help?

i am trying to find a way to reference the cell address of a cell that contains a value in a specific row.

eg

blue 1 4 8 3 6 2 9
red 9 3 6 2 5 4 1
green 8 4 1 7 3 2 5

first i want to search by row "blue", then for the cell address thats contains value "2" for example.

so the returned address in this example would be G3 (assuming the cell containing blue is A1)

the numbers are formula generated and their values will change.

many thanks

I have read about this but i cant seem to figure it out.

I am looking to return the cell address in column A that holds the string "IDK"

I have heard something about a combo of address and match but i am having the worste time figuring it out.

I hope i have explained it correctly.

I have column A, somewhere in column A lies the value "IDK" I would like to have a function that returns that cells address into cell B1. "IDK" will only appear once in column A

Bonjour,

I am working on a reports that resume information found on dozens of reports. Unfortunately I have 3 reports that are not normalize. I need to Identify the Cell Address of a specific metrics in a close workbooks.

Let Say I need Overtime. I need to find where the word "Overtime" is find in a close workbook![sheet] and have the address return in a Cell

Column A Column B
Overtime A23

Any help would be appreciate. I have been searching on many forum on the three days.

Thanks.

Cstlaurent

Hi,

I am trying to create a variable that stores the cell address at the end of a dynamic list.

With Sheets("Sheets")

Set RNG = .Range("A1")
Do Until RNG.Offset(1, 0).Value = ""
Set RNG = RNG.Offset(1, 0)
Loop
Variable = Selection Cell Address

End With

Any ideas would be appreciated

Cheers - Grizzlybear

Suppose I have data in a range of B550. In col B (B5:B50) I have
certain values.
Like this:

B1 tt
B2 bb
B3 gg
B4 ss
B5 pp
B6 kk
B7 bb
B8 dd
and so on... to B50

I want that when I enter any value (which is present in B5:B50) in cell
B52, formula in cell B53 should check it in range B5:B50 and return cell
name in which that value resides.
suppose I enter bb in B52 now cell B53 shoud return B2 (its cell name
in the range). Note that if values appears two times it should return
cell name of first value appeared in the range. (as in the case of bb)

thanking in anticipation of quick reply.

--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=538333

Anyone know of a way to jump to a cell referenced in a formula, when it is
not the first reference...e.g. Formula bar reads
"=D5+G5"
CTRL+{ will take me to D5 but is there a way to get to G5 easily? Especially
helpful in long formulas and off sheet links.

thanks