I think this is a relatively easy issue, but I don't know how to do it. I call the Sub SortRange in another Sub SortDeliver. A range (ran5) is defined in Sub SortRange that I also need to use in Sub SortDeliver. How do I pass the range or get the second sub to recognize the range?
View 5 Replies
It seems to work. Are there any problems with it? Is there a better way?
Code:
Function myRangePassExample(RangeA As Range) As Double
Dim ArrayA As Variant, ArrayB As Variant
Dim iLo As Integer, iHi As Integer, i As Integer
Dim jLo As Integer, jHi As Integer, j As Integer
Dim Sum As Single
ArrayA = RangeA.Value
iLo = LBound(ArrayA, 1)
iHi = UBound(ArrayA, 1)
[code].....
I'm trying use a worksheet that contains named ranges as the source in a macro that will dynamically create pivot tables. The named range could be anything so it can't be hardcoded in the vba and has to come from the worksheet.
In my example, the string strI = "policy" which I know because the msgbox displays the string.
Code:
Dim strI As String
strI = Chr(34) & rngIn.Cells(1, 4).Value & Chr(34)
MsgBox strI
Application.Goto (strI)
But when I try to use strI in place of a hardcoded named range, it always gives me (Reference is not valid) error using the string variable in the following code but never when it's hardcoded in as "policy".
Code:
'This errors out
Application.Goto (strI)
'This works
Application.Goto ("policy")
I'm trying to pass a vector with in Strings (words) to a VBA variable, but something is not working.
That's how I'm writing: ...
I have a formula which is using named ranges-
=SUMPRODUCT(xxx-30,Ship_30)/SUM(Ship_30)
where Ship_30 name references the following-
='sheet 1'!$C$20:$C$38
The value of 20 is static, the value of 38 is dynamic. Each new time period adds another value (i.e. 39,40,41)
Having to edit each name range reference in the name manager is not acceptable since there is potentially over 30 to edit each month.
I would like to edit a cell which would have contained the value 38 (or C38) with a new value of 39 (or C39) so the range reference would be updated with the new value.
The goal would be to have a couple of rows with the following that could be edited with new values
Not sure if possible but im trying to pass a range to a formula in excel from another cell.
[URL] .......
There is a sum formula in range B5 that sums the range (B9:13). I would like to have a formula in cell D5 that passes the new range in cells(D9:D13) to the formula in cell B5 and give the result in cell D5. Is this possible using Excel functions or would i need to create some sort of user defined function for this?
I will post my code and point out where the error occurs.
Sub GetProd()
'Averages Daily Production over the month
Dim WellRange, MonthRange As Range
Dim Month_ As Integer
TotWells = Sheets("R").Cells(1, 2)
TotMonths = Sheets("R").Cells(1, 4)
Month_ = Sheets("R").Cells(2, 4)
For Flag = 1 To TotWells
Set WellRange = DefineWellRange(Flag)
WellRange.Select <-----Used this To check If Range gets passed back properly.
For c = 1 To TotMonths
Set MonthRange = DefineMonthRange(Month_, (WellRange)) <---Error Here
Next c
Next Flag
End Sub ...
So the error occurs in the main "GetProd" Sub at the point when it calls the DefineMonthRange Function, its a 424 "Object Required" Error. Just before this is called I have a "WellRange.Select" which I was using to make sure that WellRange is in fact a range, and it does select the appropriate area.
So My question is of course, why I get an object error even though I am passing a range to a function which is expecting a range?
Passing Named Range into User Defined Function
MrExcel.com | Excel Resources | Excel Seminars | Excel Products mcm91201
Depending on time of day and computer I am sitting in front of I am using:
WinXP Pro SP2 with Excel 2003
Win7 Pro SP2 Excel 2007
Win7 Pro SP2 Excel 2010 on PC
Win7 Pro SP2 Excel 2010 on Mac Mini running Boot Camp
OSX Excel for Mac 2011
I have only tried this on Win7 Pro SP2 Excel 2007 but need it to work on all.
I enter the values 0, 1, 2 ... 89, 90 in cells A1 to A91
I select A1:A91 and name the range 'angle'
I create a user defined function:
Public function sindeg(value As Double) as Double
sindeg = sin(worksheetfunction.radians(value))
end
I want 'value' for the function in a cell to be replaced by the corresponding value in the same row (or column) in the named range 'angle'. For example (using commas as column separators). This works for Excel functions like sin, cos, radians, etc.
********** Worksheet Contents **********
A1 = 00, B1 = sin(radians(0)), C1 = sin(radians(A1)), D1 = sin(radians(angle)), E1 = sindeg(0), F1 = sindeg(A1), G1 = sindeg(angle)
A2 = 01, B2 = sin(radians(1)), C2 = sin(radians(A2)), D2 = sin(radians(angle)), E2 = sindeg(1), F2 = sindeg(A2), G2 = sindeg(angle)
A3 = 02, B3 = sin(radians(2)), C3 = sin(radians(A3)), D3 = sin(radians(angle)), E3 = sindeg(2), F3 = sindeg(A3), G3 = sindeg(angle)
......
A91 = 90, B91 = sin(radians(90)), C91 = sin(radians(A91)), D91 = sin(radians(angle)), E91 = sindeg(90), F91 = sindeg(A91), G91 = sindeg(angle)
Column A = input. Columns B, C, D, E and F all calculate the same value by row. Column G fails with a #VALUE. In row 1 the value of angle[1] = 0 therefore column D = C = B = sin(0) = 0. In row 2 angle[2] = 1 therefore B = C = D = 0.017452
How can I get the user defined function sindeg(value) in column G to accept the named range variable 'angle' like the Excel function radians(value) accepted it in column D?
This functionality should work horizontally as well as vertically. For example enter 'angle' A1 to CM1 then have sindeg(angle) filled from A2 to CM2. It should also work in the case where the named range 'angle' is a single cell.
I am sure that this is a simple variable type definition problem in my user defined function: should the input variable be defined as type Range? Or something more exotic?
The brute force approach is to have the function determine the input value by passing in the named range, working out dimensions, calculating offset between the cell the function is in and top (left) of named range, then counting down (right) to pick the correct value. However I cannot see adding all that code to EVERY function. Occam's Razor says there has to be an easier way since Excel built in functions seem to do it readily.
code to pass a range say (A1:A3) as an arguement among other arguements to a custom function and then reading this as an array inside the custom funtion
View 4 Replies View Relatedhow to pass a cell content into a formula as a named range? For instance, I have 3 named range (Budget, Actual, Forecast) and I use the named range in vlookup formulas. But instead of using the named range in the formula directly, I want to refer to a cell where I can type in which named range I want to use. Can this be done? I am attaching a sample spreadsheet to clarify what I mean.
View 6 Replies View RelatedHow do I pass values between procedures? I have two command buttons in the worksheet.
If I have a procedure A that asks user to input a value "x", when a command button is pressed.
Then another command button is pressed that passes the value onto another procedure B, which iterates a msgbox as many times as the number x,
How do I pass that value x from one procedure to another?
I am building a tool that uses Pivot Tables (so I can't use track changes) and I have found cool code that will insert timestamp and username in the comment when a change occurs. But I need to modify the code to also take the old value and put it into the comment as well. Here is the code to register the change into the comment:
View 11 Replies View RelatedI'd like to know how to send a different value to a cell based on what is selected in the combobox. Like a vlookup, but from within the box.
Something like :
Passing Array to VBA. I have the following
View 2 Replies View RelatedI've a userform (UserForm1) that on closing will open one of 5 other userforms. Which of the userforms that opens will be determined at runtime, depending on an option a user has previously selected. The name of the userform is held in a cell in a workbook and is opened as follows:
VBA.UserForms.Add(sheets("Sheet1").Range("A1")).Show
where the value in Sheets("Sheet1").Range("A1") is "UserForm3"
UserForm1 contains TextBox1. How can I pass the value in TextBox1 to a textbox in UserForm3 when the name of UserForm3 is held as a variable in a cell in a worksheet?
Option Base 1
Dim Covariance(1 To 5, 1 To 5) As Double
Dim Mean(1 To 5) As Double
Dim Portfolio(1 To 10, 1 To 2, 1 To 5) As Double
Dim PortfolioValues(1 To 10, 1 To 2) As Double
And my main function:
Function Generate_Portfolio_Values()
Dim a As Integer, b As Integer, c As Integer
Dim m As Integer
Fill_Covariance_Array
Fill_Mean_Array
Fill_Portfolio_Array
I have following code which identifies two worksheets, a source (wbSRC) and a destination (wbDEST). In the following code I am trying to get the values for wbsrc and wbdest to be written to the merged spreadhseet. But I am not sure how to go about this in VBA. The commented out approach does not work.
For Each rcell In wbList.Sheets(1). Range("A1"). CurrentRegion.Columns(1).Cells
Set wbDest = Workbooks.Open(rcell.Value)
Set wbSrc = Workbooks.Open(rcell(1, 2).Value)
wbSrc.Sheets(1).Range(copyAddress).Copy _ Destination:=wbDest.Sheets(1).Range(destAddress)
'wbDest.Sheets(1).Range("q3").Formula = "Workbooks.Open(rcell.Value)"
'wbDest.Sheets(1).Range("q4").Formula = "Workbooks.Open(rcell(1, 2).Value)"
I have the following code in 'ThisWorkbook':
VB:
Private Sub Workbook_Open()
Call Meetdata
End Sub
It calls this macro which is in a standard module:
VB:
Public firstvariablename As String
Public secondvariablename As String
Sub Meetdata()
regionname = InputBox("Enter the name of the Region.", "Region Name: North, South, East, West")
meetdate = InputBox("Enter the date of the Meet.", "Date of Meet")
End Sub
This set-up should make the variables available to all the other macros in the workbook. I have two other macros that need to use the values stored in these two variables that are entered into the two 'InputBox' statements. These two macros are in the same module and follow the 'Meetdata' sub. When I run the first macro, it recognizes the variables. When I run the second macro for some reason the variables are not recognized. To test the values returned by the variables, I placed the following code at the end of the first macro and again at the beginning of the second macro .
VB:
MsgBox(firstvariablename & " " & secondvariablename)
At the end of the first macro, the MsgBox returns the correct values stored in the variables which means that when the macro completes its run, the variables still hold their values. When I run the second macro with the MsgBox at the beginning, the Msgbox returns a blank. Somehow, the variables have been re-set to a null value. I can't figure out why the variables have been re-set to null. [URL]
I have created a few User Defined Types in a Main subroutine, but when I pass them into another sub the intelisense for the elements doesn't come up. I can type in the elements manually - and it all seems to work okay - but with a lot of types and elements I really want it to save me the effort.
View 2 Replies View RelatedHow can I pass a column as a parameter when I execute the sub? ex:
View 2 Replies View RelatedI want to pass the name of the routine as a parameter.
View 6 Replies View RelatedI think I am confused as to how to declare a variable in one sub and use it as an input into another sub. I have attached some code below that assigns a value to two variables and then calls a sub that uses those values. I am brand new to programming so I guess I am confused how to implement this.
View 3 Replies View Related I'm having issue with passing along a variable. I learned this morning how to pass these along. But for this instance something isn't working correctly.
The code is below:
I cut out all the subs in between so you can see the problem.I think it has something to do with "File search"
The problem is after sub New_Pro_Test runs then returns back to Sub RunFolder I get subscript out of range error. It is suppose to open the next workbook in that folder.
I am trying to eliminate a lot of the global variables from my program by passing the variables to my functions and subs as arguments. I am stuck though when it comes to variables created in userforms.
The program starts with a series of userforms that asks the user for information that will be used throughout the rest of the program. Data is assigned to the variables on the click events. Is it possible, without using global variables, to pass those variables to the rest of the program?
Example:
I am using the following code to open a userform. Once the userform is open, the user has the option to choose a continue button or a cancel button. If the user is choosing continue, the code is not working (it always skips to the "else" portion of my "if" statement. I can't figure out why it is doing this.
View 4 Replies View RelatedMy code passes a string created by a function to procedure. It all works great, but i need to add something so that if the string = false then sub doesn't run. What's the best way to do that. Here is some of the
View 6 Replies View RelatedDim lngrow As Long.
i want the above variable to hold the value of endxlup . so for example :
lngrow = Cells(.Rows.Count, "A").End(xlUp)
Why doesnt it hold the # of the last cell with data in column A ?
I have some code I'm running:
Code:
Sub Main ()
Dim Value1 as integer
Value1=5
Call firstroutine
Call secondroutine
End Sub
[code].....
Somehow I need to pass Value1 & Value2 to secondroutine.
I want to select a cells value from a Data Validation Box ( Achieved ) then based off of that selection ( value ) pass it into a slicercache, as follows :
Select a value from a drop down list in cell A1 Based on selection update slicercache by passing the value of A1 to it
So far i have : The bold is the part where i need to replace with whatever is selected from cell A1. Must be possible ...
With ActiveWorkbook.SlicerCaches("Slicer_Contract_15")
.SlicerItems("BRUTE").Selected = True
End With
Would be best if this code ran on cell A1 click ...
I'm trying to figure out how to pass parameters to a date field in a macro to control the display in a pivot table.
The macro currently uses a With statement to set the properties of 177 of the 180 dates to false, leaving the remaining three dates true and visible.
