Id like to run a Sub Macro a certain number of times, dependant on the amount of data provided. It could be that I need to run it say 290 times today, but 500 tomorrow.
Is there a bit of code to say, run the macro '500' times?
At the moment, if I set it to run more than it would need to it debugs and gives a compile error, procedure to large.g
I have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.
However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.
I'm trying to repeat a macro a set number of times depending on the number a person inputs into cell B3.
Here it is currently:
Columns("C:D").Select
Range("C4").Activate
Selection.Copy
Columns("E:E").Select
Range("E4").Activate
Selection.Insert Shift:=xlToRight
Range("C3:D3").Select
I have never built or used a macro before and need very clear step by step instructions. I am just assuming that a macro is the answer to what I am looking for but if there is a simplier solution that would be fantastic.
I need to be able to repeat a row of data a specified amount of times.
Example:
3 Yellow James
2 Green Mark
5 Purple Sue
1 Orange David
Needs to look like:
3 Yellow James
3 Yellow James
3 Yellow James
2 Green Mark
2 Green Mark
5 Purple Sue
5 Purple Sue
5 Purple Sue
5 Purple Sue
5 Purple Sue
1 Orange David
I need a macro to do the following
Assume A1 has 100 & B1 has 20
The macro should divide 100 by 20 & then fill 20 for number of times I get as the answer starting from B3 9 in this case its 5 times
E.g.: B3 , B4 , B5 , B6 , B7 should be filled with 20
If A1 has 200 & B1 has 50
B3, B4 , B5, B6 should be filled with 50
Just curios if this is the most efficient way to copy a workbook x number of times.
I tried copying 77 workbooks and not sure exactly how long it took, but about 2 mintues. The original workbook is 300 KB.
In my workbook I have about 20 sheets (less in the attached sample), and on most sheets I’d like to query data for each day and find an average value based on the time and then copy that result into another sheet. I’m not sure if I’ll need 2 macro’s for this or if one can be used.
Here’s a quick description:
Assuming that I am currently in the sheet I want to run the macro, then I will either input “1:45” or “0:45” into a popup box, or I could always simply input the 1:45 or 0:45 in the code itself. I’m flexible
1:45:
If I input 1:45, then I’d like the macro to find the first 1:45 in Column B and the value in the same row in Column F will be used in the calculation. Once 1:45 is located, then the macro moves up to find 22:45 in Column B and the value in the same row in Column F will be used in the calculation.....
I have a large amount of data and I'm trying to count how many unique values I have in one column. I also want to know how many times each duplicate appears. I tried using a pivot table but it's not working for me.
I also tried the following formula: =SUM(IF(FREQUENCY(H:H,H:H)>0,1)) but it's not quite working.
Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:
id
event_name
C83-858
sample_1_arm_1
[Code].....
I am trying to come up with a macro that selects values from one sheet and inserts them into another sheet. The number of values will change each time based on the user's entry, as well as the number of times that each entry should be pasted.
For example:
Entry: X | Y | Z
Number of Times to be Inserted to New Sheet 3 | 2 | 1
Result:
X X X Y Y Z
I have spent a while trying to figure it out, however the best I can come up with is using an array, but I can only get one value from the array to paste multiple times:
(*Note: In my testing, I didn't insert into new sheet or set up the array to handle different values, I was just trying to get the basic idea to work)
Dim A(1, 3) As Variant
A(1, 1) = Range("C3").Value
A(1, 2) = Range("D3").Value
A(1, 3) = Range("E3").Value
[Code].....
I've tried everything I know (which isnt that much to be honest. lol). Ive tried the frequency formula but that doesn't work the way I want it - I think its probably the wrong formula to use. I've also tried a pivot table but they always vex me. If a pivot table IS the way to go, could someone talk me through it step by step? (*the wizard is just as confusing as doing it yourself I find) ....
View 9 Replies View RelatedI have time start and time end and the difference between the two times. In a third column I have profit and want to calculate the profit per hour. When I divide the profit with the time spent I get a very very high number. If I for instance type in 1 hour of work and profit 100, then I get profit per hour 2400. (profit/hours spent)
On a side note I am trying to get the numbers as 24 hours and not use am or pm and in the spread sheet I get these numbers but in the box at the top where you see the formulas etc. it shows as am and pm. I have uploaded an example.
I want a cell to tell me how many times the number 12 is in a group of cells. Is there a formula or something that will.
View 4 Replies View RelatedI'm trying to modify this line:
Range("A2").AutoFill Destination:=Range("A2:A10"), Type:=xlFillSeries
I trying to copy down a formula a set number of times based on a cell value or a declared value.
Attempting to do a countif as follows
Count the number of times that F2:F65536=Q1 where E2:E65536 also = N2.
So for a given row, the cell in col F needs to equal Q1 and the cell in col E needs to equal N2 for it to count.
Is it possible to determine how many times a formula will be used? Here’s my problem.
I have items that are bought, and then altered. So I need a cell to determine how any times my ‘IF’ formula is used. My current formula is:
Formula entered into D3.
=IF(D2>0,D2,””)
Row 2 is the amount and Row 1 are dates.
If I drag the formula to the right, E2 will be equal to 0 so will enter 0. But I need it to say what ever is in D2 3 times. (As this particular item takes 3 weeks to do) So D2 & E2 & F3 will all have whatever was entered in D2.
I have a start time in one column, end time in another and a column with the difference between the two. In another column I have profit. In the last column I have profit per hour where I divide the profit by the hours, but since one is hours and the other is a number it gives me an error.
View 4 Replies View RelatedI have this list with articles at work. It looks like this:
1
1
1
2
2
2
3
3
3
etc
I wish to find out if any number in a column appear more then 3 times, and if it does, return that number to be. Is this possible with a formula? I've tried, but didn't succeed.
I have a user form that opens when you pick (team1) sheet, I made a chart after week 8.
is there anyway using the combobox's to do a count on who was who's partner? so at the end of the league I know which 2 played together the most?
I have a problem with LAN function. I have following formula to count how many times appears number 2 in a box: LEN(I5)-LEN(SUBSTITUTE(I5,"2",""))
in I5 I have 1,2,3,12,34,22,21 . Outcome is 5 which is not what I need. What I wanted to do is to get output of how many times appears number 2, not how many times appears expression 2 (it counts also 22, 21, 12, 2) . The output that I need should be 1 since number 2 appears just once in the box.
I want to count every time a certain cell changes. For example, if C2 is currently set at August 5, 2007 and I change it to August 12, 2007, then add 1 to cell B2.
View 9 Replies View RelatedIs there a way to calculate the number of hours between two times specified like this:
Start
End
Hours
Comments
Mon 6:00p
Mon 9:00p
3.00
Same day
[Code]..
I think I can write a UDF and that may be the easiest. Is there a straightforward way without a UDF?
I have created a userform with 4 textboxes and "ok" button. whenever I press OK I want it to copy TB1(text),TB2(number) and TB3(number) in the first empty row a number of times based on what is in TB4(number). however, each time it has to add 1 to TB2 and whenever (TB2+1) exceeds 5 then 1 is added to TB3 and TB2 start over from 1 again.
View 3 Replies View RelatedI have a task which I am sure can be done quite simply. I have a list of names in column A and a number in column B. I want a Macro to copy the name in column c, d, e etc for the number in column b.
View 9 Replies View Related(the html is messing up the out put for a2 and a3)
I am trying to get the total amout that is in the set in individual cells. I have the size type done just having trouble timesing the numbers in the cell
Example.
A1 A2 A3
10/2LB/CASE 20 Pound
12 16 OZ CASE 192 OZ
12/750ML/CASE 9000 ML
5 Gal/Box 5 Gallon
(i have placed the columns to match formatting normal=A1 Bold=A2 Underline=A3)
I am not good at writing codes and so would really appreciate if someone could help me.
I have a range suppose A1:D50 which i want to copy it certain no.of times, say around 50 times below the original range or in another sheet.
Can a code be created where I have a useform , where I will be entering the number for eg.50, which will copy the range 50 times below the original range or in new sheet.
I'm trying to do a simple calculation of the # of hours between 2 times, rounded up to the nearest hours. Somehow along the way I've ended up with this massive formula below, which is doing what it's supposed to do except for certain cases (like the one in the attached file...where it says the difference between 2am and 3am is 2 hours) =ROUNDUP(IF(B2="",0,IF(A2="",0,(IF(B2<A2,((((B2+1)-A2)*1440)/60),(((B2-A2)*1440)/60))))),0)
View 4 Replies View RelatedI have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies View RelatedI have a few spreadsheets with a few land transactions. I want to see if the parties involved are male or female, or both (in case of joint titles). And how many. I've tried to use ISNUMBER formulas and COUNTIF formulas but I can't seem to make them work. I've attached an example of what I need to do, the original has many more column with more info, and the names are in a different language which makes it easier to identify as female or not (like 'phany' in english female names etc).
View 8 Replies View RelatedI have a list of names all in one column, separated by row...
Example...
John Doe
John Doe
Jane Doe
Jane Doe
Jane Doe
James Jones
James Jones
I want to count how many times each name appears. Like this:
John Doe | 2
Jane Doe | 3
James Jones | 2
This is a very large list of names and I prefer not to have to type each single name into a formula because there are hundreds of separate names.