Pass WB / WS / Range Info From Cell To VBA Procedure?

May 31, 2013

I 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.

View 9 Replies


ADVERTISEMENT

Call Procedure And Pass Range Info

Mar 22, 2007

Have 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.

View 7 Replies View Related

Pass Cell In Loop To Procedure

Jan 1, 2008

I want to put some colour in the cells B2, B3, B4, B5, etc and a put black the edge of every box. For do that i make a subrutime that put some color in the range that i want (only if i write "B2" or "B2:B5"), but if i want to put a black edge in all the box i can't do "B2:B5". Then i tried to do something like:

For i=2 To 5 Step 1
Call Boxcute(ActiveSheet.Cells(i,2))
Next i

Sub Boxcute (Box As String)
Range(Box).Selection
etc.
End Sub

But Excel gives me a error with Box and Range.

View 2 Replies View Related

Pass Value From Function To Procedure

Aug 11, 2006

If 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.

View 4 Replies View Related

Pass More Than One Argument To Procedure

Dec 11, 2006

I want to be able to call a procedure and pas 2 variables that it will need to it.
The code looks like this:


Private Sub UpdateScreen(StartLine As Byte, EndLine As Byte)

End Sub
UpdateScreen(104 , 140)

For some odd reason I get an "Expected: = "error when I enter the last line. I works if I send only one variable, but not when I send 2.

View 2 Replies View Related

Pass Userform To Public Sub Procedure

Feb 15, 2010

I have a userform that has a number of cascading comboboxes, however i wish to use the same cascading feature in a number of other userforms. So what i am trying to achive is calling the cascading element from a public macro, to do this i need to pass the userform name to the procedure. The snippet of code i am using is:

Form:
Private Sub ComboBox1_Change()
If ComboBox1.Value <> "" Then
myform = Me.Name
Call Cascade1(myform)
End If
End Sub

Module:
ub Cascade1(myform)
myform.Label2.Visible = True
myform.ComboBox2.Visible = True
myform.ComboBox2.RowSource = Range(myform.ComboBox1.RowSource).Cells(myform. ComboBox1.ListIndex + 1, 1)
End Sub

View 2 Replies View Related

Pass Multiple Arrays To Procedure

Sep 10, 2007

I have a procedure that I want to pass two arrays to. When I pass a single array it works fine but as soon as I try to pass two arrays I get a syntax error. Both arrays are declared exactly the same way and are used in the same way. Am I limited to passing only one array per procedure?

View 8 Replies View Related

Calling Stored Procedure And Pass Through A Paramter?

Apr 27, 2007

Is it possible to call a stored proc from excel and pass through a paramter?

If it is would anyone have a clue how to do this?

View 11 Replies View Related

Pass A Named Range From A Cell As An Argument To A Formula

Oct 20, 2009

I have the following formula which works fine in this form:

View 4 Replies View Related

Pass Row Number From Range Whos Address Is In Cell

Aug 18, 2007

I 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 Related

Pass Each Cell Value In Range To Cell & Run Macro Code

Apr 28, 2008

I 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”.

View 2 Replies View Related

Find Empty Cell In Range Then Copy Info In Cell Above It?

Dec 15, 2011

I've got a range of data in Column D approx 50,000 rows long and I need to go down this range and when theres a blank cell copy the info from the cell above. I've got some code which loops through this but I need to make sure I put "EOF and the bottom of the info to stop the loop. Is there a slicker way of writing this code?

Code:
Sub TestBlankCell()
Range("D5").Select
Do

[Code].....

View 6 Replies View Related

Procedure To Return A Range

Jul 25, 2006

I want to write a procedure similar to MMULT. I find that

Public Function aa_ok(arg1 As Range)

works to get the data.

I have not been able to figure how to get the result back to the spreadsheet.

Documentation sez that args are passed by ref, but I am unable to modify args and see the change in the spreadsheet (thank god).

I find that statements like

aa_ok = Worksheets("Sheet1").Range("A1").Value

work just fine but statements like

Worksheets("Sheet1").Range("A5").Value = Worksheets("Sheet1").Range("A1").Value

don't, even though I copied that statement from the help system.
I tried

Function calculate(ByRef X As Range, ByRef Y As Range)
calculate = X * Y
End Function

which I got here - and it doesn't work on my system.

I get a block of cells with repeats of #VALUE! where I expect my result to be displayed.

I have Excel 2000 9.0.2720 and VBA Retail 6.0.8714

I have looked at security and it is set low.

View 6 Replies View Related

Execute Procedure Whenever A Range Is Copied

Apr 22, 2008

how I can have a routine execute whenever a cell range is copied in excel. Doesn't matter whether by selecting the appropriate menu item or when CTRL+C is pressed (I mean it must work anyway).

I need this cause I would like to store in a public variable the address of the range which is being copied in order to use it later for undoing purposes.

I first tried to find a way of accessing what has been copied into the clipboard but it doesn't seem possible without using external DLLs.

View 3 Replies View Related

Get Range To Pass Into Function

Jun 11, 2009

I 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 View Related

Pass The Variable In The Range

Jul 25, 2008

I 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.

View 30 Replies View Related

Pass Value Of Range From Another Sheet

Nov 5, 2006

I 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?

View 4 Replies View Related

Pass Range Through UDF By Matching Dates

Nov 11, 2013

I'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.

View 7 Replies View Related

Pass Array Values To Range

Oct 27, 2008

i 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 Related

Pass Values From An Array To A Range

Nov 21, 2008

You 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.

View 2 Replies View Related

Loop Range & Increment Row To Pass

Jan 10, 2007

I 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.

View 2 Replies View Related

Excel 2010 :: Procedure To Write Values To Range Using Array

May 23, 2012

Im using excel 2010 As it's 60 times quicker I was trying to speed up my code and replace all loops by putting the value into an array, and then transfer the array to the worksheet

It seems to be straightforward for math calculations like in this example:

[URL]

But no luck with the one below. I was trying to test it on a simple loop which replaces two types of string into the 3rd one:

Code:

Dim lastrow, lastrow2, i As Long
With Worksheets("KPI5")
lastrow2 = .Range("N" & Rows.Count).End(xlUp).Row
.Range("T7:T" & lastrow2).Value = .Range("F7:F" & lastrow2).Value
For i = 8 To lastrow2
If .Range("T" & i).Value = "Modification" Then

[code]....

View 4 Replies View Related

How To Count PASS / FAIL Cells In A Range

May 12, 2014

I 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.

View 3 Replies View Related

Create Range Object & Pass To A Subroutine

Apr 10, 2007

Create 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

View 2 Replies View Related

Pass Random Numbers Between Numeric Range

Aug 29, 2007

i 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

View 5 Replies View Related

Excel 2003 :: How To Pass A Parameter In The Input Range

Mar 21, 2014

I 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 Related

Copy Info Of Name Range?

Jun 24, 2013

I want to copy all the information from a name range from a different workbook and paste it in my activeworkbook , but the trouble that I'm having is that it doesn't copy all the cells of the name range, just the first cell of the name range Here is the command that I'm using, not sure if it's the best approach

Code:
ThisWorkbook.Worksheets("US Input").Range("usInput").Value = Workbooks("US Input File.xlsm").Worksheets("US Input").Range("usInput").Value

Forgot to clarify that the name range is the same for both workbooks

View 2 Replies View Related

Fill Info In Sheet One And Have Same Info Appear In All Sheets That Follow

May 12, 2014

I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?

View 3 Replies View Related

How To Fill Vertical Columns With Info From Horizontal Info

Aug 22, 2014

I have attached a spreadsheet and I am trying to capture the info in lines 2,7,12,17 and return the info into column d,e,f,g

The info in these columns at present has been manually entered but I am sure it could be automated.

OOL Roster Final 18-31Aug14.xlsx

View 1 Replies View Related

Input Box Info Pasted Into Range Of Cells

May 29, 2009

I have this code going for a sheet i've been working on, but thers only one thing I can't figure out how to do.

I want it to take the text that was entered in the "Grid Date" input box and stick it in the bottom 10 cells (plus one space). IE - if A14 is the last used cell, paste it into A16 through A26.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved