Repetitive Task - Looping With Variables
Jan 17, 2014
I have a repetitive task that needs to loop effectively. The only things that change each time are the start Columns ("C:C") next moves to ("D:D"), this then continues down the different columns, until it reaches the end of the columns.
The other thing that changes is the formula initially it starts at RC[-1]&"",""&RC[1]. Next time the numbers will be plus one, so RC[-2]&"",""&RC[2].
The last thing that moves is the "B1:B" & LR on the next step will move to "C1:C" & LR.
Basically a loop with variables in the loop. Is this possible? Below is the code I have begun to write but I could be writing this for ever, over and over again.
Code:
LR = Cells(Rows.Count, "A").End(xlUp).Row
Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]&"",""&RC[1]"
Selection.AutoFill Destination:=Range("B1:B" & LR)
[Code] .......
View 3 Replies
ADVERTISEMENT
Nov 6, 2013
I need a macro in a excel for the below scenario:
1)i have merged cells from A1:B3
2)if i click on a macro the output in the above cells should be as follows"
"Login to database"
3)if i run the same macro for the 2nd time the output should be:
"Are you done with the reporting?"
4)if i run for 3rd time the output should be:
"Logoff from database"
5)Say example if i run the same macro for 1o times i should get 10 different outputs....if i run for the 11th time the output should get repeat from 1st....
6)Output need not be in order....random also fine...
If possible gimme 2 different codes:
1)Output should be in the order wise from 1-10 and get started from1 again
2)Output should be random....
View 4 Replies
View Related
May 5, 2009
I was wondering if it is possible in Excel VBA to loop through variables as can be done in some other programming languages. What I want to do is something like the following:
PHP
For i = 0 To 5
x = rnd
value[i] = value[i] + x
Next i
In this simple example, we have 5 different variables that are incremented by a random number. This would return:
PHP
i = 0
x = 0.1232
value0 = value0 + 0.1232
i = 1
x = 0.3221
value1 = value1 + 0.3221
...
Eventually I will want to remove the absolute value '5' and replace it with a cells value, but I think I know how to do that.
PHP
intNumValues = Range("A1").Value
For i = 0 to intNumValues
...
This kind of code would be really helpful in designing a little application where I want the user to be able to specify the number of values they are looking at producing.
View 9 Replies
View Related
Oct 19, 2011
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.
View 1 Replies
View Related
Feb 6, 2009
Another VBA problem I am having to need help with...
Andy Pope and DonkeyOte graciously helped me with the first stage here:
http://www.excelforum.com/excel-prog...-a-time-2.html
where I was trying to loop through 1000 records at a time and retrieve data from my Oracle database based on a single Field ID.
This is the code that ended up working...
View 14 Replies
View Related
Mar 25, 2007
I'm an entry level Excel user who is looking for a spread sheet to print multiple pages for tagging pallets in a warehouse. The pallets will contain the address of the customer and the number the pallet is.
Example 30 pallets would be
1 of 30 then print
2 0f 30 then print
3 of 30 then print
how I can put in a varible pallet total and have it print the all sheets up to the given number?
View 9 Replies
View Related
Nov 26, 2007
script to replace consecutive data like this:
(RX 40.1.22) (RX 40.1.23)
with this:
(RX 40.1.22-23)
I'm trying to get rid of repetitive references in a Worksheet I have. These references have "" instead of "()" but the won't show up in the post.
View 9 Replies
View Related
Sep 3, 2008
i want the formula to be used in excel to make acounter in another column for the values the occured repetitively
like to have these two column.
ex:
Oper No
Work Center
10 Paintsh
20 Paintsh
30 Paintsh
40 Paintsh
50 Paintsh
60
Paintsh
70 Paintsh
80 Paintsh
90 Paintsh
here the operation no is the counter for the repeated work center whose name is paint sh
View 9 Replies
View Related
Sep 5, 2008
I want to highlight the names that are repeated over 3 x's in any group. Example is below.
http://tinyurl.com/6gu5v6
View 9 Replies
View Related
Mar 25, 2012
I want to find the row position in an array.
1 200 CAR 001
2 230 CAR 002
3 400 TRUCK 001
4 200 BUS 001
5 250 CAR 003
6 250 BUS 002
7 200 BUS 003
8 730 CAR 004
in 4th column it should say that this is "n"th position of 3rd column value.
ex.
in 4th row 3rd column 1st "BUS" appears. hence column 4 is 1
in 6th row 3rd column 2nd "BUS" appears. hence column 4 is 2
in 7th row 3rd column 3rd "BUS" appears. hence column 4 is 3
tell me how to automate this
View 1 Replies
View Related
Apr 12, 2013
I am going to convert different costs during life time of a product to Net Present Worth (NPW), consisting Maintenance and Rehabilitation costs (M&Rj) and Salvage Value (SV), according to the following formula: [URL] .......
SV occurs only once at the end of life time, it's NPW in Excel can be calculated as following:
=PV(i discount, AP,0, SV,1)
But M&Rj costs occurs J time during life cycle, and formulating manually their mathematical formula most times takes much time. Exactly what should I write in the cell to get their total NPW? In other words, how can I get the following formula by Excel functions: [URL] .......
Reminder: NPW= FV( 1/(1+i Discount))^n
FV: future value
i Discount: discount rate
n: numbers of years (periods)
View 1 Replies
View Related
Feb 5, 2007
in data validation i have taken a list & assigned it to defined named range say industry ( 50 - 100 industries which are repetitive in nature)
now the problem is the data is neither sorted nor unique
but i want unique sorted data (i can not make changes in original data because of its other various linkages nested formulas)
View 3 Replies
View Related
Apr 30, 2008
There is a list of interconnections of panels.
For example:
From To
..................
P1.........P4
P2.........P4
P1.........P5
P1.........P4
P3.........P5
P2.........P4
P2.........P4
P2.........P3
There are hundreds of sheets like this with more complex panel numbers (there are 70 to 80 panels). I want to count the number of times each panel is linked with another panel i.e the number of times its name is mentioned in front of it.
This may look like as follows.
............P3.........P4.........P5
P1..........0..........2...........1
P2.........1...........3...........0
P3..........0...........0...........1
View 4 Replies
View Related
May 13, 2009
I have a workbook populated with values in 3 columns. The values of the second column group rows into larger segments. I want to pull one row for each different value onto a different sheet.
example:
1. apple
2. apple
3. apple
4. apple
How do I only pull one row of apples onto another sheet without copying by hand? I have attached an live example. I want to pull one row for each classification (column 2) named on the sheet without copy/pasting each one.
View 2 Replies
View Related
Jul 30, 2014
how can I modify the formula below so that after every row (i+60) the letter D changes to E then F, then G..... and so on. I want the following with the formula below:
=MAX($D$2:$D$61)
=MAX($E$62:$E$121)
=MAX($F$122:$F$181)
and so on...
[Code] .....
View 8 Replies
View Related
Jan 27, 2014
I have attached an example spreadsheet with two tabs. The first tab (Matrix) shows raw data scores - there are 28 measures in Column A and Participants in Row 1. There are always 28 measures but participants can vary. What I need to happen is to sort each measure in a Lineup tab i.e. rank all the participants listed in the Matrix tab from lowest score to highest score for each of the 28 measures. Obviously this can be done manually but a VBA/macro solution will be so much faster and easier. Unfortunately I have to keep the data in this format so the macro will need to transpose the data to get into the formt in the Lineup tab.
View 9 Replies
View Related
Jun 23, 2006
I have a column of repetitive data:
BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL
I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL
I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?
View 9 Replies
View Related
May 9, 2008
I have a few columns referencing Crash numbers. The majority of each column is a null value, with about 10-20% of it actually containing the number of a Crash. I want to run one MODE each on a column to see what the most common Crash is.
The following
=MODE(Table_X[Crash Alpha])
Results in a #N/A Value Not Available error. What do I need to change to make it work on the column with some nulls and some numbers (no strings)?
As a secondary question, if I wanted to apply a date filter (the columns are part of a table, including a Date column) like the below:
If(Table_X[DateTime]>F9,If(Table_X[DateTime]<F9+1, etc.)
(F9 being the location where my filter Date is)
Where would I apply that to the Mode? Should I just change it to an If statement, with the Mode positioned like so?
=If(Table_X[DateTime]>F9,If(Table_X[DateTime]<F9+1, MODE(Table_X[Crash Alpha]))
View 9 Replies
View Related
Mar 5, 2009
I've got the following table (see attachment). In a column A there're the articles and some of them appear several times. In column B there's the quantity. The goal is to get in column C the sum of quantities in column B for the repetitive articles in column A and then delete the repetitive articles.
so the table should look like:
3RD125070442H 30
3RE071870493H 4
3RI100009624T 15
in excel 2007 I found only the function to remove the duplicate cells but I need to get the sums before removing them and have no clue how to that.
View 3 Replies
View Related
Jun 3, 2013
I have a macro that works but was wondering if there is an easier way to shorten up the part of the code that is very repetitive with an array or ? Nothing I have tried works except the code below.
Code:
Sub ClearAndReset()
Dim WS1 As Worksheet
Set WS1 = ActiveWorkbook.Worksheets("Stocks")
WS1.Unprotect
Application.EnableEvents = False
With WS1
[code]....
View 3 Replies
View Related
Dec 3, 2012
I'm not sure what I'm trying to do is even possible, but figured this is the place to ask the question. I'm trying to compare a list of companies (column a) to a list of employee email addresses (column b) and post results (column c) that display all associated email addresses that are unique to company name. Since multiple employee email addresses can be associated with numerous company names.
View 2 Replies
View Related
Dec 6, 2009
I have a work book with several (couple of hundred) buttons, which when clicked display the contents of a range of cells in a message box as shown below;
View 4 Replies
View Related
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
Feb 3, 2010
I am getting ready to create a spreadsheet to list vendor's names and emails along with other data. I would like to include at the bottom of the email addresses a quick copy and paste distribution list. I have achieved this in the past by using a similar formula:
View 4 Replies
View Related
Jul 12, 2013
I'm a fairly new user to using VBA and I'm currently running into a little snag. I'm trying tu create userform which will let me know if a person already exists in my sheet by looking up the last name in "A", then looking up his first name in "B", if such entry exists then display a message saying " Yes". If entry user has not been entered in the sheet then use a combox, to select the reason , then choose a date ( i have a working date picker ). Then entry should be entered in sheet (Name, Surname, Reason, date).
Whats working, I can use all of the above to enter all the info into the sheet, what I need to do is verify if user is alread in there, if not, then add it. The so on and so forth
View 3 Replies
View Related
Nov 26, 2008
Excel cannot complete this task with available resources. Choose less data or close other applications. My file is only about 3mb in size, made up of 17 worksheets. These sheets are calculated by referencing to another file that contains all of the background data. The data file is also about 3mb, made up of 13 worksheets.
There are probably about 2 - 3 thousand formulas in the file in total, ranging including vlookups, sumifs, sumproducts, etc. When the data was contained within the file there was no problem. I moved each data sheet into a new workbook to trim the size of my file and also stop the incessant calculation and this is when the problem started. Now, when I open the file and am prompted to update, it will update to about half way and then throw up the error message!
View 4 Replies
View Related
Jan 11, 2012
I have 2 sheets with a formula in sheet 2 looking to pick up information from sheet 1. the formula in sheet 2 is:
=HLOOKUP(C8,'Cost-Effort'!$B$7:$AH$42,26, FALSE)
But C8 appears in W7 and X7 in sheet 1 and will only pick up the first Occurrence and I need it to pick up the second Occurrence for this specific task.
View 6 Replies
View Related
May 6, 2013
I have this excel workbook with VBA code that starts when the workbook is opened.
Every day at 12:00 the windows task scheduler opens the file (hidden). The problem is that my computer is not always turned on at 12:00 hence it would be better if I could store the file in the cloud and run task scheduler from there.
In an optimal world there also has to exist a dropbox connection.
View 2 Replies
View Related
Jun 6, 2013
I have a PDF sheet with info laid out like this:
Code:
Peter Paul Mary
Text Corporation
One two three street
Mars, New York 90000 USA
phone: (111) 555-2222
email: tellmewhy@yahoo.com
There are 10 names and addresses like this on each PDF page and the entire PDF file has 50 pages so to manually copy and paste each bit of info into separate cells on the spreadsheet's appropriate cells "Name, Company, Address, State, Phone, Email" e.t.c is too difficult.
View 1 Replies
View Related
Jul 29, 2013
I have a large spreadsheet set up with multiple rows, I have the formula -
=a5&"repeated text"&c5
this brings back 2 results
test repeated text test
repeated text
In the instance this is the latter with no combined cell vale, how would I mass delete them?
View 2 Replies
View Related
