UDF: How To "get To" The Cell That The Function Is Being Called From (NOT Activecell)
Aug 5, 2008
Suppose I have this UDF:
Public Function test()
test = ActiveCell.Column
End Function
This is great only when I evaluate the function test() one cell at a time. When I copy this formula to other cells or perform autofill Excel thinks that the active cell is the first cell in the series.
What I really want is for the function to return the column of the CELL that contains the function, and not the cell that's last clicked.
View 9 Replies
ADVERTISEMENT
May 12, 2006
1. Can a Subroutine be called from within a user-defined Public Function? How?
2. Is there a difference between calling a subroutine with a 'Call Sub_Name' statement, vs. calling the subroutine with an 'Application.Run'(?? or similar) statement?
View 5 Replies
View Related
May 29, 2009
Function being called at inappropriate time
I have the following code in the active worksheet:
View 4 Replies
View Related
Apr 15, 2013
I have a series of procedures I am calling from a function that each run fine when executed individually. These procedure are titled: DataValidation, Test1, Test2
The function will not run past Test2- I do not get the msgbox "Stage 2"
Code:
Public ArrayD
Public ArrayD2 As Variant
Public RngD3 As Range
Public Function Test(arg2 As Range)
DataValidation arg2
[Code] ............
View 4 Replies
View Related
Jun 26, 2007
I have a macro that calls functions from another workbook.
For example:
Private Sub Worksheet_Change(ByVal target As Range)
If ThisWorkbook.OpenCommon <> -1 Then _
Application.Run Workbooks("common.xls").name & "!Arkusz1.CellChange", target
End Sub
But when I get an error in CellChange function from "common.xls" workbook, debugger points only to the third line of the code above, not to the bad line in called function.
Is there any way to change this behaviour (maybe some tool etc.)?
View 5 Replies
View Related
Jun 27, 2006
I have a function in a cell triggered on the value returned by a DDE link in another cell.
e.g. cell A1 contains =function1(A2) and cell A2 contains
=ADVFN|NYSE_CAT!CUR
function1 is triggered each time the DDE link updates regardless of whether the result returned has changed or not. How do I prevent function1 being triggered if the returned value has not changed?
View 9 Replies
View Related
Feb 24, 2008
I'm trying to replicate the same functionality using Excel Analyzer that Lotus 123 had with it's function called Backsolver.
Below is a simple matirx of expenses, that shows a sum total of 3297. I want to change the number 3297 to 5000 and I want the rows and columns to add up to that total based on the same % relationship that existed when this matrix added to 3297
This technique is useful when you are trying to spread changes in a plan and you need to develop placeholders that add up and accross to the new target i.e, 5000 based on the previous targets % relationships that existed when the row and columns added up to 3297
I can use the analyzer, but when it finds its solution it returns negative values as part of the solution. I'm missing something in trying to set the parameters so that it won't return me negative #'s.
View 9 Replies
View Related
Nov 5, 2008
i need to create a function macro Called FSTD that inserts words into a cell and conditionally formats them depending on th number in the cell on the left.
in the sheet there are ranges that the number will fall between.
Example
Cell B1 is the cell were i want to run the function in
Cell A1 is the cell with the number that is used in the function
Cells A3:A7 are that ranges that i need to use in the function
E.g. if the number in A1 is less than A6 = i want "P" to appear in the cell with a red backround
if the number is greater than A6 but less than A5 = i want "M" to apear with an orange back round
if the number is Greater than A5 but less than A4 = i want "E" to apear with a green backround
even if it is possible to do without changing the backrounds, just to insert the letters
View 9 Replies
View Related
Mar 28, 2009
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.
For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.
My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.
View 6 Replies
View Related
May 3, 2014
I am trying to use the simplest code possible (being a novice) to execute a macro which makes a copy of Cell A1:A4 and paste its values to Cells B1:B4 if cell B5=1 (or whatever). The Macro with copy and paste works (I recorded it and in my testing it was ok). And also the function, that contains If-Then status of cell B5, works and calls correctly the Macro.
Yet when the macro is called by the function (=CopyMyCells(B5)), the Macro (MacroCopy) runs - I tested this with a Msgbox - but stops just in doing what I need, i.e. in doing the copy-paste process. Here the code which is placed in a module of the proper Worksheet (Excel 2003, on WinXPSp3):
[Code] ........
View 6 Replies
View Related
Apr 6, 2007
Does this code copy all cells from the active cell up to the last non-blank cell, or is it up to the first blank cell after the last non-blank cell?
View 9 Replies
View Related
Dec 2, 2009
I need "PC" to show up if I type a sentence containing "Called Parent", "Called Dad", or "Called Mon". Here is what I'm trying.
View 2 Replies
View Related
Jun 23, 2006
How would I add this formula as a worksheet function with VBA. I can't see INT, MOD or Year in VBA. Also want to change C2 to change to activecell column + row 2.
=INT(((C2-1461)- SUM(MOD( DATE(YEAR(C2-MOD(C2,7)+3),1,2)-1461,{1E+99,7})*{1,-1})+5)/7)
View 4 Replies
View Related
Jan 21, 2009
is it possible to deactivate an activecell?
the following code works well i have a range of cells(e1:o17) i click on one of those cells to enter a score into (a2) the next cell i click enters a score into (b2) ok so far but if i need to enter the same score consecutively as needs to be done from time to time i cant do it because the cell is still active.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("d1") = Range("d1") + 1
If Range("D1") = 2 Then Range("a2") = ActiveCell.Value: Range("c2") = 501 - Range("a2")
If Range("D1") = 3 Then Range("a3") = ActiveCell.Value: Range("c3") = Range("c2") - Range("a3")
If Range("D1") = 4 Then Range("a4") = ActiveCell.Value: Range("c4") = Range("c3") - Range("a4")
If Range("D1") = 5 Then Range("a5") = ActiveCell.Value: Range("c5") = Range("c4") - Range("a5")
View 9 Replies
View Related
Mar 4, 2009
I've got working routines that do some action upon double-clicking within a specific range of cells. I want to be able to "wrap" this routine within a routine that will record the activeCell prior to the double-click and, upon completion of my routine, return to that prior location.
View 3 Replies
View Related
Apr 16, 2012
with some basic code. I want to check the activecell, if it's blank then copy the contents from the cell above, if it's not blank, leave it & go down to the next cell & repeat until last row.
View 7 Replies
View Related
Jan 8, 2010
I need to name a cell that is the active cell. The active cell can be any cell that is selected at the time I need to name it. I can get it to name the cell using the cell address, but I don't know how to change it to the active cell. Below is the code that I tried, but the syntax that I tried to say what cell is to be named simply isn't correct.
View 2 Replies
View Related
Mar 14, 2007
I have a sheet were i want to insert a new blank line at a spcecific point.
When a cell, ex. A28 is marked, i have made a macro that makes a new line at that point. The only thing is, i cant figure out how i make alterations to a certain cell in that row (to make sure it is the same every time.. there will be many users to the document, and i dont want any to make some big stupid mistakes by altering something, so every new row should be formatted the same way every time..)
I have recorded the following:
Sub ActiveRowTest()
'
Rows("28:28").Select
Selection.Insert Shift:=xlDown
Range("K28").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("H28,F28,E28,K28").Select
Selection.NumberFormat = "#,##0".................
View 9 Replies
View Related
Feb 23, 2007
I have a macro called "Copying" and this runs perfectly well when run on its' own.
I have now created the following peice of
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("form").Range("B14").Value x Then
x = Sheets("form").Range("B14").Value
End If
Application.Run "Copying"
End Sub
Which (should) run the "Copying" macro if cell B14 is changed........here is my problem.
It seems to run this macro over and over again. How can I get it to just run it once?
View 9 Replies
View Related
Jan 9, 2008
I'm using the calander control in Excel which is called in VBA when a user clicks on a cell in a range - the code is as follows:
' Calander for Activity Start Date and end dates
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, [H11:I30,H33:I52,H55:I74,H77:I96,H99:I118,H121:I140,H143:I162,H165:I184,H187:I206,H209:I228,H231:I250,H253:I272,H275:I294 ,H297:I316,H319:I338,H341:I360]) Is Nothing Then
If Intersect(Target, [K11:L30,K33:L52,K55:L74,K77:L96,K99:L118,K121:L140,K143:L162,K165:L184,K187:L206,K209:L228,K231:L250,K253:L272,K275:L294 ,K297:L316,K319:L338,K341:L360]) Is Nothing Then
Calendar1.Visible = False
Exit Sub
End If
End If...........
View 9 Replies
View Related
Dec 18, 2006
Here's the situation:
I want to search through the cells in a column to locate text in one of the cells. If this text is found, I want to make that the ActiveCell and then insert a row underneath it. If this particular text is not found, I want to insert a row and put that text that wasnt found into the first cell in the created row.
I trimmed down my code a lot so I could post it here.
So, in this particular case, once the user selects one of two product lines (named "ADC" and "DAC"), I want to first search for that text and if it is found I want that to be the new activecell and insert a new row.
The main problem is that I can't seem to figure out how to set the "Foundcell" as being the new active cell. My initial activecell is set by locating and selecting the cell containing the text "Product Line".
Here's my
View 10 Replies
View Related
May 21, 2009
If I have several Forms check boxes assigned to the same procedure, is it possible to tell which check box called the procedure?
View 6 Replies
View Related
Dec 10, 2009
I want to thank you all for the solutions/support I received in completing my (for me) difficult workbook.
I still have a tricky problem. In the example below, can Multibeep "know" when it is being called from Beep versus being excuted directly? And if so can I direct a different sequence?
View 10 Replies
View Related
Jul 2, 2012
I am running this code it is rather long, but I need to show it all ?
Code:
Public Sub Monday()
Dim mName() As Variant
Dim lName() As Variant
Dim fName As String
Call lower(CStr(mName(q)), fName, CStr(lName(q)))
[Code] .........
How to do is to pass the value of convert to the other procedures that are being called? As you can see I know how to pass variables down to "Sub-routines" but how would I pass the variable up?
View 8 Replies
View Related
Nov 5, 2009
I am trying to sort a csv file through a VBScript. My problem is that Excel continues to run as a process on script completion. Running multiple variation of this script results in multiple instances of Excel being present in the WinXP Process Manager.
Set xlObj = CreateObject("Excel.Application")
xlObj.Visible = false
xlObj.Workbooks.Open "C:Test.csv"
xlObj.ActiveWorkbook.ActiveSheet.Range("A1").Sort xlObj.ActiveWorkbook.ActiveSheet.Range("A1"),,,,,,,0
xlObj.ActiveWorkBook.Save
xlObj.ActiveWorkBook.Close true
xlObj.quit
set xlObj = Nothing
As a second part to my question, is there any way to modify the code to allow multiple field sorts, i.e., A1 is Primary, C1 is secondary?
View 9 Replies
View Related
Aug 22, 2007
I'm having a hard time with this piece of
Private Sub Assign1Combo_Change()
If Range("ComboVisible") = False Then Exit Sub
Assign1_Download
End Sub
Symptoms:
- As soon as this is used, Excel/VBA can't select any range on the worksheet. For example, the following code (within the sub Assign1_Download) no longer works:
Range("firstdata").Select
Excel/VBA doesn't select that Named Range or any other range I try (ex. A1). - This problem only happens in Excel 2003. In Excel 2002 everything works fine (can make any selection).
View 5 Replies
View Related
Aug 7, 2009
How can I select the current activecell and the next 50 following rows of that column? I cannot define a range since it has to variable.
View 4 Replies
View Related
Nov 23, 2009
ActiveCell.Activate. I'm reading through a VBA book and one line of code was
View 4 Replies
View Related
Jan 1, 2010
I want to use the following command but with more to it:
X=0
Y=0
Z=10
ActiveCell.Offset(rowOffset:=X, columnOffset:=Y).Activate
I want to use this type of command to highlight an area...not just move rows/columns. I want it to highlight from a point on the sheet marked by X and Y and then down Z rows.
So if I'm in cell A1 and want to highight from A1 down to A10, I'm not sure how to write that part of the code.
View 9 Replies
View Related
May 30, 2006
I have a list of line entries for which I need to insert a variable amount of empty lines per line. (the variable amounts listed in a column to the right of my Active Row.
Unfortunatly I loose the ActiveCell's Value. During Debug it shows the correct cell and value, but after the Debug Step Over, the value shows 0 again. I include my
[code]
Sub IP_Insert_rows()
'
' IP_Insert_rows Macro
' Macro recorded 2006/05/29 by Joseph Clark
'
Dim GrpCtrVar As Integer
Dim LnCtrVar As Integer
GrpCtrVar = 3
LnCtrVar = Range("B8").Value
View 4 Replies
View Related
