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] ......
i,d like to define variables,
a=2,7
i<j<k,m<n
5*a+10=i+j+k+m+n
i,j,k is defined in the process (i,j,k are three elements of a,a+1,a+2,a+3,a+4)
how do i define m,n.(two elements of a,a+1,a+2,a+3,a+4)
I have 3 variables right now...
Dim celadr As Range
Dim cLetter As String
Dim avgRow As Integer
and one line of code reads:
celadr = Range(ColumnLetter(LastScoreColumn) & avgRow)
When I mouseover columnletter it shows "H" and when i mouseover avgRow it shows "2"... this info is right but I think the problem may be trying to combine a string and an integer value as a range. How can I do this so that celadr is the range H2?
Code to loop through worksheets 1 to 3 only and perform a certain task, and how to define the variables i.e. worksheets 1 to 3 when I am not using actual sheet numbers, but tab names. (worksheet 1 to 3 is just the positionof each tab).
What i need the code to do is perform a task on the first sheet, loop to the next sheet, perform the same task, until it has done the 3rd sheet.
I can't quite get this formula to give me the correct value.
imgur: the simple image sharer
I tried defining as a Range and I can't get it to work right either way. When I hover over my r1 and r2 values the range is correct but it does have "$A$227:$A$447" quotes around the range so I think that is messing me up.
I need it to cycle through whole numbers for three variables whose range I can define. I need this macro to test the result of all possible combinations of A, B and C, as is shown in the attached file. The initial range boundaries for each variable are as follows:
VariableABC
Minimum1408
Maximum510012
So the macro should start with the following combination (1,40,8) then (2,40,8) then (3,40,8) etc until it reaches (5,100,12). I need the results for each combination to be posted at a separate sheet next to the corresponding combination.
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
array[i]=var.[i]?
I am trying to loop through different SlicerCaches but it doesn't seem to work.
Here is the code, I am trying,
VB:
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.
VB:
Test_Name = "[Test - Test Allocation]"
ActiveWorkbook.SlicerCaches("Slicer_Exec_Function_Summary1").VisibleSlicerItemsList = Array("[Mercury].[Exec Function Summary].&" & Test_Name & "")
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?
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
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
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..................
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.
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
Range("B(counter)","F(counter)").Select
ActiveCell.FormulaR1C1 = "1"
Next counter
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”
'
'******************************
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.
Code:
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.
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
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!
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.
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.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
View 3 Replies View Related1. I am entering the prices of a hotel. And the hotel has different prices in different periods.
For ex: 03/07/212 - 04/06/21 128$
I don't want to define it as an array in Sheet1. I want to enter start date A1 and end date to A2 and the price A3. And to the next column next period definition and so on...
2. After I define all dates like this when I will create a date array at Sheet2 in A column, I need a formula to make excel call the values to column B automatically to the proper dates.
I created three large formula that together give me the nested if limit if I try to put it all in one cell. Therefore I defined a name for each formula and then created another formula using the names =IF
(Facil_Move,less_25_Miles,Greater_25_Miles) my problem is the cell with this formula will only display answers from the last portion of the formula. I want it to display each answer as the various conditions are met. So it should be an answer for "Facil_Move" or "Less_25_Miles" etc.
I have a different set numbers each day I need to average if they fit a certain criteria. These are actually times (in Military time) and in the 3 column spread sheet I have in column D the difference between column B and C. What I need to do now is find all the data from column A that is in between 1100 and 1500 and average all their column C results together.
I need to do this for all 6 4 hour time frames (from the small table).
I am using this array: =AVERAGE(IF(B2:B14>1100,IFB2:B14
I want to define a Range() in VBA. have cell1 and Cell2 in the format of rows and cols. ie. Cell1 = Row 1, Col 2. Cell2 is dynamic, can be sometimes row100, Col200, or Row23, Col 1000. May i know how to define it in VBA?
View 2 Replies View RelatedI have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
I have dictionary defined as series of keys and let's say two values:
name1,val1A,val1B
name2,val2A,val2B
...and so on
I would like to define named range from "name" column allowing user to select desired name from combo on another sheet. This is easy
But after that I would like to get val1 and val2 for selected name and show them with some calculation; For example to construct two columns like this: <nameX_selected_from_combo>, (<val1X>+<val2X>)/2
All the problem is how to select values from the same row as name selected in range.
