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
ADVERTISEMENT
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
View Related
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
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
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
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
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
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
Jun 10, 2007
I am learning about Case Select-
I want to loop through every cell in col. V the range of
i = 6 to LRow
and find any value that equals 13, if it equals 13 then the value in (i,"W") will be "True"
View 9 Replies
View Related
Feb 4, 2009
I'm trying to write a VBA procedure that will loop through all the worksheets within my Excel workbook one by one (the number of worksheets in the workbook may vary from month to month) and count all the non-blank cells in Row 12.
If the number of non-blank cells is anything other than 24, I want the procedure to display an error message. (Each individual sheet in the workbook is supposed to contain 24 column headings, and all the column headings are in Row 12).
View 3 Replies
View Related
Nov 21, 2006
I'm creating a user form that will have 10 checkboxes on it. Depending upon certain conditions being met elsewhere in the workbook, I would like to populate the checkboxes' captions with data from the workbook.
My question is, can I loop the procedure with the variable number included in the checkbox name (well, more to the point, HOW can I loop the procedure...)?
I would like to do something like this:
Dim a As Integer
For a = 1 To 10
With Worksheets("Hi-Tech")
If .Cells(a + 1, 2).Value "" Then
chkHiTech & a.Enabled = True
chkHiTech & a.Caption = .Cells(a+1, 2).Value
lblHiTech & a.Enabled = True
lblHiTech & a.Caption = .Cells(a+1, 2).Value
etc.
View 6 Replies
View Related
Jun 27, 2014
I am having trouble defining a variable in a For loop. When I run the loop the first time everything seems to work fine, but when the loop goes back through the second time it carries its previous value with it. Here in lies the problem if the variable is supposed to be empty (not assigned a value). So, for example: 'Variable' in the code below may or may not be given a value depending on the conditions in the For i loop. If it is given a value then 'Variable' will still have that value on the next pass through the For j loop. And if none of the conditions are met for the If statement in the For i loop the second time through the For j loop, it should be an empty variable, but instead the code is reading it as having a value (from the previous pass through the For j loop).
[Code] .....
I have tried using:
[Code] .....
After the For j line, and instead of using:
[Code] ....
I tried using:
[Code] ....
and I've tried:
[Code] .....
I get an Object error with either line I use. I just want to be able to reset the variables each time through the For j loop so in the case of an empty variable, it doesn't try to calculate based on the previous value.
View 6 Replies
View Related
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
Feb 27, 2008
I've created a variable number (i) of text boxes at run time (i also named them, "txt"&i, at the same time), i'm now trying to get the values entered in these to fit into a column that has been inserted during the same sub routine. I'm having a bit of trouble refering to the text boxes though, this is the patch of code i'm struggling with:
Dim TextBox As String
For i = 0 To 2 * NumVar - 1
TextBox = "txt" & i
ActiveCell.Offset(i, 0).Value = TextBox.Value
Next i
I also tried this:
Dim TextBox As Object
For i = 0 To 2 * NumVar - 1
TextBox.Name = "txt" & i
ActiveCell.Offset(i, 0).Value = TextBox.Value
Next i
View 8 Replies
View Related
Oct 1, 2006
If I have cells that are formatted to a certain decimal width, say, 2 digits, cells that have the value of 3.599 will appear as 3.60. When I reference this cell in VBA, how can I have it pickup the 3.60? Currently, something like Range("A1") or Range("A1").Value will pickup the 3.599.
I feel like theres something thats like Range("A1").XXXX that will get me there, but I can't seem to guess it.
View 6 Replies
View Related
Oct 30, 2006
I want to use the value of the last cell as a starting value and add a number to it and assign it to the next cell.
i have this
Sub a()
For i = 15 To 100
Cells(i, 2).Value = Cells(i - 1, 2).Value + 6
Next i
End Sub
how do i incorporate this
Sheets("Sheet1").Range("A").End(xlUp).Offset(0,0).value
into the code above so that the value of the last cell can be the starting value.
View 3 Replies
View Related
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
Aug 8, 2012
The following code expands a cell width to size 42 when it is clicked on, then minimises it to size 9.29 when clicked elsewhere. Just what I need.
For instance, this would work for column 4.
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns(4).ColumnWidth = IIf(Target.Column = 4, 42, 9.29)
End Sub
However, I need this to cover the first 1000 columns. More if possible. I can easily copy this 1000 times, changing the column number to then look like this (First 10 shown for example)
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Columns(4).ColumnWidth = IIf(Target.Column = 4, 42, 9.29)
Columns(5).ColumnWidth = IIf(Target.Column = 5, 42, 9.29)
Columns(6).ColumnWidth = IIf(Target.Column = 6, 42, 9.29)
[Code] .....
The problem is if I do this 1000 times i get a Compile error that the procedure is too large. Is it possible to make the code more compact to basically cover all columns from 4 onward?
View 6 Replies
View Related
Jul 22, 2009
I am doing a simple filter list copy and paste into another worksheet and I have a challenge trying to pass the value in Cell B1 (which contains the city) as a filter parameter in the filtered list.
View 2 Replies
View Related
Dec 31, 2013
I have the below code which creates a new template
Code:
Private Sub NewTemplate_Click()
Dim Tsh As Worksheet
Set Tsh = Sheets("TEMPLATE")
Tsh.Copy After:=Sheets(Sheets.Count)
shName = InputBox("Please enter new sheet name:")
ActiveSheet.Name = shName
Tsh.Visible = False
End Sub
What I would like to add to the above code is two more inputbox prompts, the first should prompt the user for a Name (text and length) to go into the new sheet cell A6. The the second input box should as for code, (number any length), to go into the new sheet cell b6.
View 3 Replies
View Related
Sep 18, 2009
i'm trying to do my homework which requires me to pass values from an array in excel worksheet to VBA and print it in a msg box
i've tried
dim arr as variant
arr = range("A1:A6").value
msgbox arr
but it didn't work.
that's the first step of the homework the second is i have 2 list of array
year:1999 - 2002
profit:10,20,30,40
i have to find the max profit and get the year when it occurs
View 9 Replies
View Related
Aug 15, 2008
Following statement works for me:
bdcTerm1 = ThisWorkbook. Sheets("ws2"). Range("A1").Value
But instead I want to parse through 50 rows and dynamically get the value instead of using a static Range("A1"). So I am trying to do the following:
For Row = 1 To bdc_rows
bdcTerm1 = ThisWorkbook.Sheets("ws2").Cell(Row, 1).Value
bdcTerm2 = ThisWorkbook.Sheets("ws2").Cell(Row, 2).Value
Next Row
But I get errror.
View 5 Replies
View Related
Jun 11, 2007
I have a macro that nicely select the named range that the active cell is in. I want to chain on to that macro a macro that has a parameter a range with that active selection. I dont see how to "take" the active selection on the worksheet from within the macro and pass it to another. I assume I could change the cellInRange macro to return a range, however I dont yet see how to do that.
View 2 Replies
View Related
Jun 3, 2008
I am using this program in one workbook to capture the datevalue in integer from another workbook which i opened. But the program as it reaches the line x2=Datevalue( Cells(2,14).Value) gives a Type Mismatch error.
Public Sub find_date()
Dim x2 As Long
'I am trying to activate the last opened file by using workbooks.count
Workbooks(Workbooks.Count).Activate
Worksheets("Sheet1").Cells(1, 1).Select
x2 = DateValue(Cells(2, 14).Value)
End Sub
Auto Merged Post Until 24 Hrs Passes;btw..the cells(2,14) has a date, formatted in the type of mm/dd/yyyy.
View 4 Replies
View Related
Jun 3, 2008
Why when I want to use a varaible with a value like that 2.1, 0.9, 3.5 in a code to create a formula gives me an error? How to get it work?
Sub Code1()
Dim k As Double
k = 2.1
Range("h11").Formula = "=" & k
End Sub
Strange, but it works well if k is an integer with no Decimal Fraction, like 1, 2, 5, 11 ..
View 4 Replies
View Related
Dec 22, 2006
I am using excel to stop and start a service on the network and have that part done when using a txtfield to enter in the PC ID. What i want to do is use a list of PCIDs and pass them to my service object to stop and start the service.
Private Sub CommandButton1_Click()
Worksheets("PCIDs"). Range("B2").Select 'my issue is here
Range("B2").Activate 'and here
Do Until IsEmpty(ActiveCell)
Call StopService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop
Range("B1").Activate
Do Until IsEmpty(ActiveCell)
Call StartService("ServiceName")
ActiveCell.Offset(1, 0).Select
Loop
Worksheets("ServiceName").Select
End Sub
and with this function i need it to pass as a string to txtDeviceID. I have tried just simply setting txtDeviceID as ActiveCell but it didn't like that.
Public Function StopService(ServiceName As String) As Boolean
Dim oSysInfo As New ActiveDs.WinNTSystemInfo
Dim oComp As ActiveDs.IADsComputer
Dim oSvcOp As ActiveDs.IADsServiceOperations
Dim sCompName As String
Dim sSvc As String
Dim lRet As Boolean
View 3 Replies
View Related
Mar 23, 2007
I can't use code for this problem as I need to give this to someone who doesn't know VBA. They will need to use it in several different reports, so I can't produce something in VBA very easily. I have three worksheets, Summary, Year1 and Year2.
I want to display either Year1 or Year2 data in the Summary sheet depending on what the user enters in a cell in the Summary sheet. For example, if the user enters "Year1" in cell E2 then I want to point to a cell in the Year1 spreadsheet.
I tried using formula: =E2 & "!B3" but this doesn't work. Is there another solution? I have attached an example.
View 6 Replies
View Related
Aug 4, 2007
I am trying to create a macro (please look the attached excel what I have done), I have already crwated a macro that after 5 seconds is gonna change the background color of the of the cell en function of another cell.
That means if F3:F4 change the background color F8:is going to change the backgorung color also, every 5 seconds. The macro I wrote is also include in the excel attached file.
Now this is my problem... I have a variable cell that is give for D6 which could be A,B,C,D,E,F (Range F2:K2).
If the variable cell D6 is A the background color of F8:F9 should be the background color F3:F4 .
If the variable cell D6 is B the background color of F8:F9 should be the background color G3:G4 .
If the variable cell D6 is C the background color of F8:F9 should be the background color H3:H4 ......and etcetera.
View 5 Replies
View Related
Sep 28, 2007
I'll get straight to the point:
where ( Date > 9/20/2007)
The above Date si used in a sql select statement where I'm hard coding the date (9/20/2007). This date is actually located in sheet1, cell E1. How can I get it from that cell and use it in my Select statement instead of entering the date manually every day in my code?
View 5 Replies
View Related
Jan 1, 2008
I want to generate a random number based in a poisson and i use this
Application.Run "ATPVBAEN.XLA!Random", ActiveSheet. Range("$B$2"), 1, 1, 5 , , 35
But i want to save the number in a dim single variable called N. I try to put N where i have write ActiveSheet.Range("$B$2") and do N=Application.Run ...
But it doesn't work.
View 5 Replies
View Related
