Get Range To Pass Into Function
Jun 11, 2009I am attempting to call a Function from a cell and pass it a range and an Integer. The Integer works fine, but I cannot get the Range to pass into the function correctly.
View 8 Replies
ADVERTISEMENT
Pass Value From Function To Procedure
Aug 11, 2006If I have code like this
Private Sub cmdHex_Click()
Dim HexNum As String
HexNum = Me.txtHexVal
'Call a function
ToBinary (HexNum)
'Now do something else
End Sub
And the function ToBinary creates a value called BinVal. How do I pass it back to my subprocedure to do something with it.
Pass Function Result Between Subs
Oct 11, 2006I have a simple/dumb question... How do you "capture" a value that is returned by a custom function.
I have tried searching the forums for this & I know that I should by all rights know how to do this by now... but I just can't figure it out.
Here is a Function that I copied from Ktrasler in this thread:
Week Numbers
Public Function MyWeek(DateArg As Date) As Byte
Const BaseDate = "30/12/2001"
DateArg = CDate(DateArg) - (Weekday(DateArg) - 1)
MyWeek = Int(((DateArg - CDate(BaseDate)) / 7) Mod 52)
If MyWeek = 0 Then MyWeek = 52
End Function
I know how to pass variables to Functions, and tried this one out & it worked splendidly for my needs, but how do I take the value of "MyWeek" and use it in the subroutine that I am calling it from?
Pass TextBox To Custom Function (UDF)
Mar 10, 2008I have a simple function defined in one of my worksheets (Sheet1):
Function AddFuel(fuel As String)
MsgBox fuel
End Function
How would I be able to call this from a form button event?
Private Sub CommandButton1_Click()
Sheet1.AddFuel(TextBox1)
End Sub
Everytime I try running this code I receive the error: Run-time error '1004'; Application-defined or, Object-defined error. I've even tried Application.Run("Sheet1.AddFuel", TextBox1) but still no luck. I think this is a pretty common question but I couldn't find any answer to it on the forums.
Pass Variable To Evaluate Worksheet Function
Nov 15, 2013I understand that the SumProduct doesn't have the same capabilities in VBA as it does in worksheet formulas. I am trying to construct it in an evaluate statement in VBA but the problem is I am using it in a userform, so the conditionals are passed from two combo boxes. Here is the worksheet formula
Code:
=SUMPRODUCT((Table1[Home]=C3)*(Table1[Away]=M3)*(Table1[Goals (H)]>Table1[Goals (A)]))
The values C3 and M3 refer to a cell known as "Home" & "Away". It's comparing to the two teams selected to a table with game stats to show wins and losses. The user form has a combo box list with those teams. Combo Box 1 passes to a variable known as hTeam and Combo Box 2 passes to a variable known as aTeam. I need those values passed through to C3 and M3. Here's what I constructed but get a type mismatch
Code:
Evaluate("=SUMPRODUCT((Table1[Home]=" & chr(34) & hTeam & chr(34) & ")*(Table1[Away]
=" & chr(34) & aTeam & chr(34) & ")*(Table1[Goals(H)]>Table1[Goals(A)]))")
Amending VBA Sub / Function To Pass Results To Other Subs
Feb 5, 2014Basically someone has coded a VBA script to pull a users details from AD when a button is clicked, using the staff id as the search criteria.
I've amended the criteria so it uses Environ$(USERNAME) to find details of the current logged on user, but i am struggling to get the resulting information out of the Sub to be used in other Subs! It currently just fills a cell with the required info.
Pass Ranges To User Defined Function
Dec 28, 2006I am trying to write a User Defined Function in VBA to perform a simple two-dimensional table lookup. I have the temperature distribution in a solid given in a table in Sheet2. The first column of the table contains the time values, the first row contains the spacial values (radii), and the intersections contain temperature values. These ranges are named times, radii, and temperatures. In Excel, I can perform the lookup using: =index(temperatures, match(time, times, 1), match(radius, radii, 1))
But how do you do this in VBA? More specifically, how do you deal with passing ranges to the WorksheetFunctions?
Function temperature_ref(time_range As Range, radii_range As Range, temperatures_range As Range, time As Double, raduis As Double)
r = WorksheetFunction.Match(time, time_range, 1)
c = WorksheetFunction.Match(radius, radii_range, 1)
temperature = WorksheetFunction.Index(temperatures_range, r, c)
End Function
This function would be called (from any worksheet) as..................
Pass The Contents Of A Listbox To A Function To Sort It Into Alphabeticalorder
Mar 3, 2008I'm trying to do is pass the contents of a listbox to a function to sort it into alphabeticalorder. I get a error saying object required
Sub PopulateAll()
Dim lgLastRow As Long
Dim lgRowCounter As Long
Dim intCounter As Integer
Dim olb As MSForms.ListBox
For intCounter = 0 To UBound(vAllEnv)
Me.lstAll.AddItem (vAllEnv(intCounter))
Next intCounter
Set olb = frmOptions.lstAll.........................
Pass Multiple Tables Into Custom Interpolate Function
Jan 14, 2010I have 5 worksheets each with tables that are formatted the same on each sheet. I have named each table as a named range (ex:filter5tsd15BSF). I have a userdefined function that interpolates values for a single table in a vba module. I can make this function work for a single table by calling the function as =Linterp(filter5tsd15BSF,C12) so I know the function is OK. However, I really rather program this some way that all the tables get passed to the function and then a select case for the conditions for which filter and which TSD (15 or 25) will then select the correct table.
View 2 Replies View RelatedPass Multiple Non-contiguous Ranges Into A User Defined Function
Dec 23, 2009I am trying to call a function that calculates forecast error (wMAPE). This function needs to be able to handle passing in non-contiguous ranges. I can't seem to figure out how to do that.
Function wMAPE(Forecasts As Range, Actuals As Range, Weights As Range) As Variant
Dim Denominator As Double
Dim Numerator As Double
Dim i As Long
Dim Fcst As Variant
Dim Act As Variant
Dim Wt As Variant
If Forecasts.Cells.Count Actuals.Cells.Count Then MsgBox ("Error: Arrays not same size")
If Forecasts.Cells.Count Weights.Cells.Count Then MsgBox ("Error: Arrays not same size")
Denominator = 0............
Pass The Variable In The Range
Jul 25, 2008I want to work on a range
range("A1:B100")
The number 100 (in B100) comes from another varibale M
Now how do I write the range so that I don't have to write 100. I want to pass on the variable in the range.
the code should look somewhat like
Range("A1:BM").
I don't know how to pass this variable M onto the range.
Pass Value Of Range From Another Sheet
Nov 5, 2006I am using a spreadsheet with two sheets, basically, one sheet has a combo box control, you select a name from there and click on "Go". The function is supposed to go into the next sheet (Sheet2) and retrieve a value from there (in the same row as the selected name).
I am using the following code (on button click):
Dim strValue As String
Sheets("Sheet2").Select
strValue = Range("A1").Value
MsgBox (strValue)
I've simplified this, but essentially my problem is this - it isn't picking up any value at all - there is text entered in A1 so what is the problem?
Pass Range Through UDF By Matching Dates
Nov 11, 2013I'm trying to create a UDF that would be able to accept a column as an input range.
Match dates contained in two other cells and return the addreses so I can perform a function between the two addresses.
like Function multiplyrange(B2,B3,A:A)
So
B2= Start Date
B3= End Date
Column A is contains all the dates
sheet set up:
A
B
1
Start Date
1/3/2013
2
End Date
1/7/2013
[Code] ........
So it basically just multiplied all the values between the dates found.
Pass Array Values To Range
Oct 27, 2008i have two arrays that I want to use in a trend function. I don't think i can just use the array as is in the fucntion so my guess is that I need to pass the array into a range of data, and help on how I can do this? (also this is in VBA, fyi)
View 9 Replies View RelatedPass Values From An Array To A Range
Nov 21, 2008You have an array and a range of the same size and you have to put the array values into the range, something like this:
Dim i As Integer
Dim myCell As Range
Dim myArray(10) As Double
i=0
For Each myCell In Range("A")
myCell.Value = myArray(i)
i = i + 1
Next myCell
except that this code looks a bit awkward to me.
Loop Range & Increment Row To Pass
Jan 10, 2007I am using this code to create onsheet user form and populate a spreadsheet db
'Step 1 : store the information in every second row in DBsheet
Set rngDataOut = Worksheets("Database"). Range("A65536").End(xlUp).Offset(2, 0)
'Step 2 : Post the current results
rngDataOut.Range("A1") = Now()
rngDataOut.Range("B1") = Range("B1")
rngDataOut.Range("C1") = Range("B2")
rngDataOut.Range("D1") = Range("B3")
rngDataOut.Range("E1") = Range("B4")
'Step 3 : Clear current Selection
Range("B1") = ""
Range("B2") = ""
Range("B3") = ""
Range("B4") = ""
my question is how could I modify it with for-next loop because the form will be with more than 50 entries.
How To Count PASS / FAIL Cells In A Range
May 12, 2014I have a test log with PASS/FAIL. These results are logged in Excel under the column heading PASS/FAIL.
How do I count the number of PASS or FAIL using VBA macro?
So far I have tried:
[Code].....
This one did not work either:
[Code] .......
the error is at the name = Range("PASS/FAIL").Select line.
My thought process: once I had selected a range, I can now freely "look" at each string in a cell in that range. Not correct I guess.
All this is currently done in the active sheet.
Pass WB / WS / Range Info From Cell To VBA Procedure?
May 31, 2013I need to pass range information (eg. WorkSheets("abc").Range("A1") as text or ??) from a cell (ie the above text is in a cell on some worksheet, say "XYZ") to a VBA procedure or could directly use it in the procedure, similar to...
Sub MyProc(RngInfo as string [or?])
dim RRng as range
Set RRng= RngInfo ??? it is this part I'm really not sure how to do
where RngInfo is a worksheet that has a "named" cell that contains the above cell with the indicated range in it.
eg. RngInfo is worksheets("XYZ").Range("D1") where D1 contains the text (or ?)WorkSheets("abc").Range("A1")
I've only indicated these as text items because I'm not sure what/how you would do this. The end purpose of this is to pass variable Range information from a cell on a WS either into (or use within directly) a VBA Sub. I guess I could pass the WS and Range Address as a single string separated by a "Char" and use Split to separate them and then recombine using Worksheets(Parm(0)).Range(Parm(1)) but it seems like it should be easier than that.
Call Procedure And Pass Range Info
Mar 22, 2007Have a spreadsheet for creating employee schedules (any number of employees, 3 rows per employee, 5 columns for each day). I want to move all employees' info for one day (5 columns and XX rows) to another area and then clear the employees' data for that day. I have the code working for Monday but I don't want to duplicate it 6 more times for Tue -Sun. I have set up named ranges (i.e. MonFT, TueFT, etc.
I would like to call one subprocedure to do this and pass parameters for each day so the ranges can be changed to reflect the desired day?
I am doing this for holidays; i.e. if there is a Wed. holiday, store all of Wednesday's data and clear Wed. Then restore all Wed data when preparing the schedule for the following week.
Create Range Object & Pass To A Subroutine
Apr 10, 2007Create Range Object & Pass To A Subroutine
Sub Test(ByRef objRange As Range)
objRange.Value = "Hi"
End Sub
Sub TestTheTestMethod()
With ThisWorkbook. Sheets("Sheet1")
Set objRange = .Range(.Cells(1, 1), .Cells(i - 1, 3))
objRange.Value = "Hi" 'This works fine !
Test (objRange) 'But here... Getting ERROR 424 -- Object Required
End With
End Sub
Pass Random Numbers Between Numeric Range
Aug 29, 2007i modify the result but the problem is that when i try it the result alway have a higher number i cannot have a low number...
i want to have the reult from 1 to 1899
here is the
Private Sub CommandButton1_Click()
Randomize
TextBox1.Value = Int(Rnd * 1)
TextBox2.Value = Int(Rnd * 9) + 1
TextBox3.Value = Int(Rnd * 9) + 1
TextBox4.Value = Int(Rnd * 9) + 1
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub TextBox2_Change()
End Sub
Private Sub TextBox3_Change()
End Sub
Pass A Named Range From A Cell As An Argument To A Formula
Oct 20, 2009I have the following formula which works fine in this form:
View 4 Replies View RelatedExcel 2003 :: How To Pass A Parameter In The Input Range
Mar 21, 2014I have a combobox in a excell sheet. It is possible to pass a parameter in the input range instead of Parm!$B$1:$B$10
View 2 Replies View RelatedPass Row Number From Range Whos Address Is In Cell
Aug 18, 2007I am setting a variable to the value of an absolute cell reference. I want to set the value of another variable to just the row value of that absolute cell reference.
View 4 Replies View RelatedPass Each Cell Value In Range To Cell & Run Macro Code
Apr 28, 2008I have workbook template that I use to generate reports from a list of depts. This list is contained in a drop down cell that is a named range in a different worksheet. My current process is as follows:
-Select Dept Name from the list
-Click a command button which is assigned to code that calculates and saves to a file
-Repeat for next report until all reports are generated
I would like to automate this process by producing all reports with a single command with the following functionality:
-The Dept Name needs to be populated in the specified cell containing the current drop down because it drive various vlookups and other formulas
-If possible, I would like to retain the drop down functionality as I would like to have the option of running an individual report or running the “batch”.
Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function
Apr 13, 2007In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies View RelatedCalling Function That Acts On Matrix Range Within Another Function
Jul 23, 2013I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
SUMIFS Function In 2007 (range To Sum, Range To Evaluate, Evaluation Criterion)
Sep 8, 2009I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula:
{=SUMIFS(range to sum, range to evaluate, evaluation criterion)}
The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999.
So my formula looks like this:
{=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)}
This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?
Function To Sort A Range By The Values In A Specific Column And Return The Range
Mar 25, 2009I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
Function To A Range Of Cells Over A Range Of Worksheets
Mar 31, 2009I have a workbook with several worksheets in the same format. I would like to have a function to output the sheets that have rows in which collumn A = X AND collumn B = Y.
I've been working on this one for a couple days now and I'm not making much progress.