Reset Variables For Each Pass Through For Loop

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


Reset All Numeric Variables During A Procedure?

Mar 19, 2009

Is there a way to reset all numeric variables to zero during a procedure (e.g. at the end of a loop), without having to manually set them all to zero within the code?

View 3 Replies View Related

Does Clicking A Button Reset Variables?

Jul 3, 2009

I've included the code I'm using to calculate the net weight depending on which option button is selected. I use a flag ("VLAG") to know whether it's the first time the button is pressed. All variables are DIMmed at initialization (different sub).

By using other flags, I can see the code runs perfectly, but the tare weight ("TARRA") is not incremented each time the button is pressed. The formula I'm using to increment is

View 4 Replies View Related

Reset All Public Variables In A Module

Aug 6, 2009

Is tere a way to reset all public variables in a module. I have a number of sub routines withing the same module and need to carry a variable from one to another so I have decalared tham as public variables.

This works fine except for an instance when I expect the variable to be empty (because nothing has yet been assigned to it) when in fact it contains the value that was assigned to last time another sub was run. So I suppose what I need is a way to reset all the variables in the module when a particular sub ends

View 3 Replies View Related

Pass And Return Variables?

Mar 21, 2012

Today's question concerns one sub calling another, having the called sub do some stuff that then effects what the calling sub does.


public sub sub1

dim x as integer
dim countCall as boolean

x = activesheet.cells(1,1).value
countCall = False

call sub2(x, countCall)


Obviously the above is a very simplified version of what I'm trying to do, since everything there could of course be handled in a single sub. It is for illustration purposes only. My actual main sub needs to call the secondary sub repeatedly in some stacked loops and its in-feasible to but the second sub's code into the main thread.

The problem remains though, I know how to call a sub and pass variables to it, i just have no inkling on how to pass results back to the calling sub. I've tried a few things I've found around the web but I seem to be missing something important.

View 1 Replies View Related

Pass Variables Between Userforms

Jan 26, 2007

I have a userform with a button that wil run macro "sub1". If "sub1" encounters an error on a worksheet it wil show a custom
error messagebox(a userform) that contains the option "Don't ask me again". If true it sets a global variable. Do I need to use global variables to pass to the original procedure "sub1" for the ok/cancel buttons too or is there an other method?

View 3 Replies View Related

Pass Array Variables Between Modules

Jun 8, 2007

I am trying to pass a public variable to another module in the same workbook. On Module1 I declare at the top

Public stores
Sub Main()
Dim stores(1 To 100, 1 To 10, 1 To 10)


...which then calls the procedure Reader in Module2

Sub Reader()
let x=1
let y=1
let z=1
let stores (x,y,z)=activecell.value

I've left out the portions of code that seem irrelevant. When the macro runs, I get a type mismatch error on the "let stores" line. If I move the code from Reader into the procedure Main, it works, so it seems to be an issue with passing the variable. I haven't used multiple modules very often so this is probably a very basic issue.

View 3 Replies View Related

Pass Variables Between UserForm & Macro

Sep 2, 2007

I am looking at using forms, as in the Userform... Not used them before, but would like to know, if you create a text box on it, is the value global, so any module can use the var? or how can I, so that value entered can be used on the whole book ?

View 3 Replies View Related

Pass Count By Condition To Variables

Jan 22, 2008

Here in the code wbResults is a Workbook present in my present folder. In the Sheet1 and Column "I", the result of test cases is entered. The Result of the TEST CASES can be PASS or FAIL. So i am trying to get the total number of PASS test cases and Total FAILED test cases. BUT with the code i have written, i am not able to fetch the right data. Is the method of comparing wrong? or what else? i have tried to debug but nothing comes out.

Do While Not i = 1008
If (wbResults.Worksheets("Sheet1"). Cells(i, 8) = "PASS") Then
End If
i = i +1

View 4 Replies View Related

Linking Macros & Pass Values/Variables

Oct 18, 2006

I have a worksheet which I've set to read-only, I am trying to get it to pull in information from external worksheets. I've created a Macro that will automatically bring in the data however it will only go into a pre-defined cell/row. I have another Macro which looks up the next available row (which is where I want the data to go) but can't get it to link into the macro's. In short, Macro1 brings in data, Macro2 finds next available row, Macro3 brings in another worksheet, Macro4 finds next available row etc etc.

Sub Macro3()
With ActiveSheet.QueryTables.Add(Connection:= Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsjspencerDesktopExcelAMMH.xl" _
, _
"s;Mode=Share Deny Write;Extended Properties=""HDR=NO;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking................

View 2 Replies View Related

Pass Variables From Userform To Public Procedures

Nov 17, 2007

When working in an userform, and you call another sub routine within the userform, will all variables passed automatically be byref (no way to do byval?)?

View 3 Replies View Related

Reset For Loop On Each Iteration?

Jul 2, 2014

I want to iterate a For Loop changing one variable each iteration until the Total from the For Loop equals zero. Each iteration of the Do Loop keeps the previous Total from the For Loop and adds it. How do I reset the For Loop to start from Total = 0 at the beginning of the For Loop?

Sub PV_IRR()
Discount_Rate = Range("A2")
Total_Investment = Range("B2")
Annual_Return = Range("C2")


View 3 Replies View Related

Good Way To Reset Variable Each Loop To Empty?

Apr 15, 2012

I'm using a variable to loop down a column of data. Each time it loops the variable may or may not perform an operation - it will if there is something in the cell but won't if the cell is empty. It writes the answer of the operation to an ajoining cell, before looping again.

The issue is that if the row cell is empty, what's written to the ajoining cell is the pre-existing value of the variable, because its value hasn't changed.

how best to 'empty' the variable each loop to ensure either that the correct value or nothing at all is written to the ajoining cell?

View 5 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

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)
End Sub

But Excel gives me a error with Box and Range.

View 2 Replies View Related

Pass Multiple TextBox Values To Cells In Loop

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

Loop Through Variables

May 31, 2007

This has sort of been asked before

Improving a vba function argument check loop

but is there a way to loop through a series of variables like var0...var9? You can obviously loop through and make a set of strings with the right names,

Dim I As Integer
Dim Var As String
For I = 1 To 5
Var = "Var" & I
Next I

Is there a way to use the contents of a string variable to call a variable with the same name as contents of the string? or somehow concatenate the a string with a number like

View 9 Replies View Related

Array Name Loop Through Variables

Jun 17, 2013

I am trying to loop through different SlicerCaches but it doesn't seem to work.

Here is the code, I am trying,

Test_Name = Array("[Test - Test Allocation]", "[Test 2]")
For i = LBound(Test_Name) To UBound(Test_Name)

ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")

The code returns a mismatch 13 error.

When I try it without an array, it works fine.

Test_Name = "[Test - Test Allocation]"
ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")

View 3 Replies View Related

Define Variables In For Loop?

Jul 8, 2014

I am trying to define my variables with a for loop and if I run the code to the line after the first variable is defined, it shows that the variable is equal to the appropriate value, but after the for loop is done all of the variables are empty.

[Code] ......

View 5 Replies View Related

Loop Find With Variables

Nov 22, 2011

I have a process where I need to search for multiple customer numbers and delete line associated with them.

My question is how do I create this process to run in a loop going through all 10 numbers?

View 1 Replies View Related

Loop With Changing Variables

Oct 16, 2007

i have set up some test script below!

i dont know if it is possible but can you concatinate two variables i.e in the example below can i move from Sum1 to Sum6 using the intiger stored in i as the end of the sum variable

Sub Sum()
Dim i As Integer
Dim Sum1, Sum2, Sum3, Sum4, Sum5, Sum6 As Integer
Dim Ltr As String
Dim Sum As String
Sum = "Sum"

Sum1 = Range("B5").Value
Sum2 = Range("C5").Value
Sum3 = Range("D5").Value
Sum4 = Range("E5").Value
Sum5 = Range("F5").Value
Sum6 = Range("G5").Value

For i = 1 To 6
MsgBox "The value in cell " & i "is " & Sum & i
Next i

End Sub

View 9 Replies View Related

Loop Sequential Variables

Sep 29, 2007

I have variables 1-6 like the following:

dDDDD1 = Format(d1, "dddd")
dDDDD2 = Format(d1, "dddd")

They correspond to a date from a cell and I just format one for "Monday" through "Saturday". In another module I'm opening a file that has worksheets named "Monday" through "Saturday" as well. I need to do the same thing to each of these sheets so I'm trying to setup a For/Next loop. I'm assuming you can't put a variable to a variable as I keep getting 'variable not defined' when I try to insert 'i'? Or am I just going about this all wrong?

For i = 1 To 6
Workbooks.Open ("J:AcctMgtITrepADIinf-v2-WE0922.xls")
Sheets(dDDDD & i).Unprotect Password:="hownowbrowncow"
Sheets(dDDDD1).Columns("C:E").Insert Shift:=xlToRight
Sheets(dDDDD1).Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
With Sheets(dDDDD1)
Set RngCol = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
End With
ConcName = "=B1&"", ""&C1"
LastRow = RngCol.Rows.Count
Sheets(dDDDD1).Range("$E$1:$E$" & LastRow).Formula = ConcName
Next i

View 2 Replies View Related

Cumulative Sum In Loop Using Variables

Apr 6, 2008

I'm having difficulty with concatenation and the use of a string variable. On my attached spreadsheet I have a row called Must Fund Tasks. The binary variable in the cell is entered by a selection on a user form. If the value of the cell is "1" then that particular task must be funded. The code I developed forces solver to keep the binary value of 1 in that particular cell when looking for the optimal solution. This same row of binary variables is the row of values that I have solver change to find my optimal solution.

My problem is that I have another set of contraints based on mutually exclusive tasks. If there is a binary value of "1" in at least two of those cells then only one of the tasks may be funded and they are said to be mutually exclusive. In other words, if the user form indicated that tasks 1 and 5 were mutually exclusive then the binary values are Task1=1 and Task2=1 and the constraint would be Task1+Task2<=1. The code I've written looks for a value of 1 in the Mutually Exclusive row and if it finds one it adds the name of the corresponding "Must Fund" cell 4 rows above to a set called ExclusiveSet. ExclusiveSet is defined as a string and I'm having trouble with the concatenation, I keep geting a "+" out in front because of the intial loop when ExclusiveSet="" ( it returns "+$F$29+$H$29"). I'm also not sure if Range("CalcsStartcell").Offset(I + 15, 3).Formula = "=ExclusiveSet" is the appropriate way for me to return the value of the ExclusiveSet in the desired cell.

Sub constraint()
Dim p As Integer, CalcsStartcell As String, I As Integer, T As Integer
T = 7
I = 5
CalcsStartcell = "C16"

'setting Mutually Exclusive constraints
With Worksheets("Sheet1").Range(CalcsStartcell)
For p = 0 To T - 1
If .Offset(I + 12, p + 1).Formula = "1" Then..................

View 7 Replies View Related

Loop Range Using Variables For Rows

Dec 16, 2006

I need to write a FOR... Next loop that will loop through a range of rows, select the same cell range on each row, perform a task, then advance to the next row. I can't seem to figure out how to substitute the loop counter for the row index. For example:

For counter = 1 To 100

ActiveCell.FormulaR1C1 = "1"

Next counter

View 3 Replies View Related

Loop Through Range & Use Cell Values In Variables

Aug 30, 2006

I have 2 worksheets. The first sheet has data of which some needs to be copied to a second worksheet. The trigger is a value found in column E. If a match is found, then a copy statement needs to be built. The values in F, G & H are the values to be used in the copy statement. The content of cell H may be numbers or letters or both.

What is the correct format of the copy statement?

Dimension all variables
Dim RowPointer As Long
Dim wbContrib As Workbook
Dim wbMaster As Workbook
Dim SheetName As String
Dim target_sheet As String
Dim target_cell As String
Dim Target_value As String
Dim CellAddr
' Initialize variables
Set RowPointer = 1
Set wbMaster = “Master.xls”

View 9 Replies View Related

Locating Important Columns In A Table Of Raw Data - Setting Variables Using A Loop

Feb 22, 2012

I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).

I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).

In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.


Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here
Sub FindFields()
Dim List1(3), List2(3)
Dim Count As Long
'Public variables (declared above)

[Code] ......

So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.

View 2 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Loop Within A Loop (repeat The Loop Values)

Mar 31, 2008

With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).

I have a named range called 'territories' that contains the list of X101 -> X152.

I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!

View 9 Replies View Related

Function Back Variables: Function Give Two Or More Output Variables

Jul 27, 2006

Can a Function give two or more output variables. e.g.

Sub a()
x = 5
result = Y(x)
End Sub

Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.

View 2 Replies View Related

Defining Variables Using Variables

Apr 27, 2006

I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...

Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................

View 3 Replies View Related

Copyrights 2005-15, All rights reserved