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"
I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.
The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.
Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?
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 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.
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:
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.
I'm using a macro to drag down a formula across a worksheet then again further down the worksheet which is working ok but I know there has to be a better way of doing it. At the moment every time I add a new column I have to edit the macro over and over and its getting out of hand. I know there must be a way of rewriting the macro into a loop but my skills are obviously still new.
Here's the macro:
[Code] ......
This continues over to column AO so far then I drop down a few rows and do it again:
[Code] .....
I think I need to set variables that set the row and column each time. maybe a "drag formula one column at a time from row x to row y until column header is empty then move to the new row and repeat"?
Trying to code my first custom VBA function. The function is supposed to translate the words in a cell by repeatedly looping through the string and looking through a list of provided translation words.
Problem is the variable name changes with each loop, and I can't figure out how to program the name change.
Function TestTranslate(TranslateCell, ProductCell) As String
Dim t As Long Dim TranslateCeiling As Long Dim SS1 As String Dim SS As String Dim Q, R As String Dim W As String Dim LLRString, MLRString, SLRString As String Dim LRString, MRString, SRString As String Dim TArray() As String
Looks like the loop keeps defining Q as "SS" & t instead of doing the select case formula. I tried removing the Q variable altogether and making it read just "SS" & t = Replace(R, LLRString, LRString) for example and keep getting a compile error.
I want to write a For loop so that it checks the range G2:R2 of Sheet1 for the first non-zero cell in that range. When it comes to the first non-zero cell, it will change the formula of A2 on Sheet2 to have the SUM of that non-zero cell and the next two cells to the right. Example:
I am trying to populate many arrays with the same code using something like this. For this test, assume the following data in A1.
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
Code: Sub populate() Dim firstArr(5), secondArr(5), thirdArr(5), fourthArr(5), fifthArr(5) As Integer Dim r, c, num As Integer
[Code]....
The above code does not work of course and falls over. I am unsure whether I should try and concatenate with something like this eg "" & arrName(i) = Cells (r,c) or go down a different route.
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.
This may have been answered on here but can not seem to find it. My situation is I have values in A1,A2 & A3 that are like counter reading so the value is always changing. What I am looking to do is change the cell color if one of the values is over 500 from the other two values. Say A1 is 3000, A2 is 3250 and A3 is 3500. I would like the cell for A3 to change color.
Moving between different sheets changes the procedure window, which is a good thing, but can also can be error prone and annoying to search through several windows searching for the procedure or module currently working on. Is there a setting somewhere to prevent VBE from changing the current procedure window?
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
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,
I 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
I am working on some code that loops through a column of number values. Whenever it encounters a number value and a blank cell in an offset column, it places that number value into an offset cell (forming a separate column to be compared to another column in a separate sheet). I would like to take all the values in that new column and begin placing them in a new column in a separate sheet adjacent to another table. Most of the time, these values should match the adjacent values in the separate worksheet. However, if they don't match, I would like a new row to be created for that mismatched value.
For example. This is the first worksheet. So far, my macro loops through the column with rows 1-5. It looks in the offset cell(0,2) for Isempty value and then places that value into the offset cell(0,6)
For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,
Sub Filterout() Dim c As Range Dim rng As Range Dim i As Long Dim lrow As Long Dim counter As Integer lrow = Cells(Rows.Count, 3).End(xlUp).Row Set rng = Range("c2:c36")
For Each c In rng If Left(c.Value, 1) "~~" Then c.EntireRow.Delete End If Next c
it seems like the first instance of the code is running the way i want it to run, but when it starts with the second instance, it does the first search and copy, but it seems like the nested loop is being ignored.
am i doing something wrong?
dan ========================================================== Thanks to Aaron Blood for the find_range function. i also poached the lastrow function from somewhere on ozgrid, but I cant remember the name of the poster. ==========================================================
Sub new2()
Dim Org_Area As Variant Dim Item As Variant Dim Copy_To1 As Variant Dim Cell_Ref As Variant
