Loop Find With Variables
Nov 22, 2011I 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?
ADVERTISEMENT
Loop Through Variables
May 31, 2007This 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]?
Array Name Loop Through Variables
Jun 17, 2013I 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 & "")
Define Variables In For Loop?
Jul 8, 2014I 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] ......
Loop With Changing Variables
Oct 16, 2007i 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
Loop Sequential Variables
Sep 29, 2007I 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
Cumulative Sum In Loop Using Variables
Apr 6, 2008I'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..................
Reset Variables For Each Pass Through For Loop
Jun 27, 2014I 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.
Loop Range Using Variables For Rows
Dec 16, 2006I 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
Loop Through Range & Use Cell Values In Variables
Aug 30, 2006I 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”
'
'******************************
Locating Important Columns In A Table Of Raw Data - Setting Variables Using A Loop
Feb 22, 2012I 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.
Find (Using Find) Using Variables With Or Without Quotes
Sep 2, 2009In the code below Dim "x" is a string, the string may or may not contain quotes ( "" ). I might search for a string like this:
The big brown fox
or like this:
The "big" brown fox
I just found out the string can not be found when it contains quotes as in my second example.
Find Format: Find Method And Combine It With A Countif Or Loop
Feb 20, 2007I'm trying to use this Find Method and combine it with a countif or loop. Something that will count a number of occurences of a unique type of character. I'm looking to find all "F" characters in Bold, Italic and Size 16. Here's my find code that I'm trying to use. I can get it to work by itself but not along with a countif or loop.
Sub count_4()
Dim r As Range
Set r = Range("A1:A6")
With Application.FindFormat.Font
.Bold = True
.Italic = True
.Size = 16
End With
r.Find(What:="F", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, searchformat:=True, MatchCase:=True).Activate
End Sub
To Find The Power ( I.e. ^ N) When You Have Other Variables
Mar 12, 2009To find the power ( i.e. ^ n) when you have other variables.
Mathematical Perspective as well as through EXCEL..
Ex: 5^n = 125
We know that 5^3 = 125,
Now if I have been given the value 5 and 125 , then how do i get the answer 3 ( i.e ^ n) value...
I need to solve a slightly complex issue and therefore need help for the above requirement as well as to get the "n" value..
Find First Value Greater Than X In Given Range Using Variables
Mar 3, 2014My data has a bunch of near 0 figures followed by values I actually need followed by more irrelevant 0's than once again followed by values I need and again irrelevant 0's. Looks like
0.1
0.12
3.2
3.4
0.1
0.3
4.5
4.2
0.3
0.11
but with many more rows and numbers. I'm graphing the start and end values before/after zero's.
The first block of numbers I figured out using
=MATCH(TRUE,INDEX($C8:$C150>1,0),0)+7 I then index from that given row.
The span of relevant values I'm looking for constantly changes, so once my numbers deviate too much I stop, using another index formula.
Now for finding my beginning and end points for the next block of data I've combined using VBA with excel formulas. I can't quite figure this part out. I've created Variables for ranges and then used them to find max and min Values.
Public Sub RunCurrent()
'
' RunCurrent Macro
Dim upEnd As Integer
Dim dnStart As Integer
Dim dnEnd As Integer
[Code] ........
Find Value With Multiple Variables And Change Value In Other Column
Jul 3, 2014Please see attached example book.
In it I want to change name "G" in column A to "X" in column C.
And I want to do it for month of May in column B.
So a match won't do the trick because Name G is in 3 times so it should match on G and May from columns A and B.
How to catch this in a simple VBA line?
Find Correct Data Based On 2 Variables
Oct 19, 2006I have a project that looks at a table of train schedules for a range of dates. The data is formated so that arrivals and departures are on the same row but differnent columns. The dates are to the right and the grid is completed with the number of arrivals for a paticular day. I have created a formula that looks at the data and returns the arrival time for that date. It worked fine until the schedule was altered so that the same train could arrive at 2 different times on 2 different dates. So train 976 arrives at 4a on some days and 5a on others. In the example Ihave posted train 976 has 2 passengers arriving at 5a but the formula shows 4a. Is there a way I can have the formula return the correct time based on the day of the month? I should point out that the same problem could exist for departures.
View 8 Replies View RelatedCombining HLOOKUP AND VLOOKUP To Find Solutions In A Matrix Using 2 Variables
Oct 28, 2009This was a snap in Lotus. Unfortunately, the VLOOKUP and HLOOKUP formulas don't translate to Excel on conversion. I need to estimate the weight of reinforcing steel in concrete based on 7 different sizes of rebar and spacing of the bar between 1" and 24".
I have created a matrix(lookup table) with the bar spacing listed in decimal equivilents in feet on the top row.Inthe row below, I have numbered the columns from 1 to 24. the next row is blank. Along the left side of the matrix, I have listed the bar sizes 3 thru 10. Within the table I have listed all the bar weights/SF of concrete. IE if the slab has 1 mat of #5 rebar spaced 6" oc. If you look in row 7, under column 6, the weight of weight of the bar in that sf of concrete is found. In the part of the worksheet where the caculations are performed set up as follows:.....
Find Row Value With For Loop?
Jun 5, 2014i have a data sheet and i am trying to store the row value of a user input time. for the most part my code works but for certain times it returns a 0. it seems like times which end with a 3 are not returning any value.
my code looks kind of like this:
Dim StartA As Date
StartA = InputBox("First start time (ex: 2:30pm)")
For i = 1 To 9000
[Code]....
Find Within A Loop
Nov 21, 2006i have a piece of code which i have cobbles together from many different sources, to many to list, but i know I have had some help from some of the people in this forum.
first of, i know some of the code might be a bit shaky, but please bear in mind that i am very, very new to VBA.
basically the code is reading the first usable line of the "Mapping" sheet to get information, then it searches for that information in the "GLEX" sheet. If it finds the information that was searched for in the "GLEX" sheet, it will copy it to the "Calc" sheet. the "Calc" sheet contains a formula that does a sum on everything in the P column. It then takes the value in the P column on the Calc sheet and pasts it into a cell that it reads from the "Mapping" sheet.
If i run the code, it seems to be doing what I want it to do for the first line in the mapping sheet, but how do i make it continue?
If must read the value of the first usable line in the "Mapping" sheet, then search for those values in the GLEX sheet, if it find a match it must copy the whole line to a next sheet and then CONTINUE SEARCHING. the mapping sheet. Once it has search the whole GLEX sheet and copied all the matches to the calc sheet, it must perform the rest of the functions. after everything has been completed for the first line in the Mapping sheet, it must do the same for the second line.
That means that there must be a loop in a loop.
I have also included some sample data.
Sub Accrule()
Sheets("Mapping").Select
Dim Org_Area As Variant
Org_Area = Worksheets("Mapping").Range("A3").Value
Dim Item As Variant
Item = Worksheets("Mapping").Range("B3").Value
Find FindNext In Loop
Dec 19, 2006I am using Range. Find() and Range.FindNext() Method. As per my knowledge FindNext() advances in the text and searches for same string found using Find(). I have used Find() for two different searches in Sheet. (Eg. Find() text "Risk" and Find text "ACL", Now when I use FindNext() for the second time (to search "ACL" text), next time when I use FindNext() to search first text ("Risk" it searches prev text - "ACL"). I have used them in a loop. Below is the code i have used.
Set SrcCell = SourceBook.Worksheets(1).Columns("A").Find("Risk", After:=Sheets("SubArea").Range("A21"), LookIn:=xlValues, lookat:=xlWhole)
'Set srcCell = SourceBook.Worksheets(1).Columns("A").FindNext
Set firstSrcCell = SrcCell
Do While Not (SrcCell Is Nothing)
tgtCell.Offset(tgtRow, 0) = SrcCell.Offset(-1, 255)
tgtCell.Offset(tgtRow, 1) = subAreaId
tgtCell.Offset(tgtRow, 2) = SrcCell.Offset(0, 1)
tgtCell.Offset(tgtRow, 3) = SrcCell.Offset(1, 1)
tgtCell.Offset(tgtRow, 5) = SrcCell.Offset(3, 1)
tgtCell.Offset(tgtRow, 6) = SrcCell.Offset(2, 1)
RiskId = SrcCell.Offset(-1, 255)
'tgtCell.Offset(tgtRow, 0) = SrcCell.Offset(0, 255)...............
Find And Replace With Loop
Jan 25, 2007I have a sheet with data on it. In column 5 I have adate range and I would like to delete and values which have "01/01/1900". Currently I have created a loop but for some reason it does nothing. I can use the find option and it picks up the cells with the ones on. For some reason it seems that it cant find them cells.
The code is as follows
Sub jdate()
rowcn = 8
target_sheet = "Status Report"
Do
'status_flag = 0
If Sheets(target_sheet).Cells(rowcn, 5) = "01/01/1900" Then
Sheets(target_sheet).Cells(rowcn, 5) = ""
End If
rowcn = rowcn + 1
Loop While Sheets(target_sheet).Cells(rowcn, 1) > 0
End Sub
For Loop Not Running As Intended, Can't Find
Feb 17, 2010I thought my code was working properly, but then I tried different numbers for myNum and I realize it's not. Here's the
View 5 Replies View RelatedLoop Through Range Find Value Input Box?
Jun 20, 2012I have a range (C3:C56). I have a set number in each of these cells. I also have a range out to the side that calculates a number after input a % in cell BI2. The formula reads as "=AE3-(AE3*$BI$2)". Basically it is reducing the values in my range. If a number is in my range is 8 and i input 25% in cell BI2, then my result would be 6.
I have a few steps I want to happen with this range.
1 Display an input box asking for a percentage
2 I want to loop through my range and use that percentage to reduce the numbers
3 (optional but would be amazing to have) after the reduced value is determined, round down to the nearest multiple of 4.
Loop Through Range Find Values?
Dec 6, 2013I'm working with reports where I am given a list of used RFID tags which contain 13 alpha-numeric characters, and need to compare it against another list, in order to determine if any are matching. I'd like to be able to loop through one column of values to compare against the other, but unfortunately my VBA skills aren't that great..
View 5 Replies View RelatedLoop Rows To Find A String
Aug 21, 2009I have an Excel workbook that i need to loop through all rows searching for a certain string we can call "Totals". There are more than one, and i need to grab the values from each at an offset in the same row, different column, to total at the bottom. I have tried a few things unsuccessfully. The code below is just my first step to try to find the row, i know there are problems as i am unfamiliar with the different properties of a given variable.
Function FindRow(szName) As Integer
nFoundRow = 0
For nRow = 0 To Worksheets("Totals").Columns(nCol).Rows.Count
If StrComp(Worksheets("Totals").Cells(1, nRow), szName) = 0 Then
nFoundRow = nRow
End If
Next nRow
FindRow = nFoundRow
End Function
Sub Totals()
nCol = FindRow("Totals")
ActiveCell.Select = nCol
MsgBox (nCol.Value)
End Sub
Advanced Loop In Macro Find Maximum Value
Feb 5, 2009I've an excelsheet with quite advanced formulas that area really nested and using quite a bit of iteration.
What I would like to do is finding the maximum of
A while Amin<A<Amax
By changing B
There is a Variable C than should be constant unless it becomes larger than Cmax ( Cmax dependas on A,B ,C, D and all kinds of things)
If C is > than Cmax there is a variable D that can be changed has to be less or equal to Dmax
So the question is how to find the maximum of A while fulfilling all these variables?
What I've come up with so far is:
Find Value, Loop Through 2 Sheets And Copy Headers
Jan 22, 2010I am putting in search items and running a macro to find the items on 'physical servers' WS, copying the header in that WS and the entire line the match appears on, though I cannot get it to do this.... it is really causing me stress
Then next part that is working is the items that return false are showing up on the results page - this is expected and what i want it to continue to do.
What I cant seem to work out also is how to run the search on the 'Virtual Server' WS also and return the results to the results WS as just like the 'physical servers' WS.
I have included some dummy data + code + the expected result on the 'Server Results' WS.
Hope you all can work out a way to make it work.
More Efficient Alternative To Find-Replace Loop?
Feb 1, 2010I was tasked with looking through a 10,000 row by 20 column spreadsheet for 628 different ID Codes and replacing them with their Descriptions. The ID Codes could appear individually in any of these cells (200,000 cells!).
The list of ID's and Descriptions hardly ever changes, so I decided to create the two-dimensional array as part of the find-replace macro shown below (only a few lines of each dimension are shown).
My question isn't related to that (although if you can suggest a better alternative please do). The main "work" of the macro is the loop at the end of the array declarations, which essentially loops through all 628 ID codes in array dimension 1 and does a Find-ReplaceAll with its corresponding Description from array dimesion 2.
This task, manually, could take days. My macro has whittled the task down to 2.5 minutes on a 5-year-old laptop, but I was hoping one of the gurus might suggest an even better method than 628 loop iterations. If not, so be it, the end users will appreciate what I've done and then have to find something to do with their "free time."
With Loop + Find Method And Sum The Negative Values
Feb 23, 2010I have a very large worksheet (row count maxed in 2007, and then some), for which I need to do the following: search column A for a string that will occur many times, and then check the 10 cells that follow in its row for negative values, dropping some sort of indicator in the 11th (shading it red or something would be fine). An additional bonus would be if the 10 cells that possibly contain a negative could be summed (the sum could serve as the indicator?). If no negative is found, nothing need be done, and the macro should chug along searching A for the next reference to this string.
My hope was to do a sort of "With Range("A:A"), .Find("MyString")", save position as StartPos, do the 10-cell row checking in a nested IF or For (though the For would take a long while, checking each cell individually), then doing a .FindNext after StartPos until = StartPos (does .FindNext loop back to the top?). The formatting of the indicator cell in the 12th cell in each relevant row doesn't really matter, it's more just for jumping to critical rows.