Generate Test Data From Range Of Values Using Vba?
Apr 15, 2014
I want to randomize a range of values stored in sheet 1 and insert them in another cell.
I am having values for sales consisting of product names and its price. there are 10 product names and its corresponding prices and is stored in cells E1:F10. I want to generate test data containing product names and its corresponding price. The generated data needs to be saved in the cells A1:B50
the product names needs to be randomized. This needs to be done in vba. Below is the screenshot of the final result that is needed. As you can see, I have randomized my 10 products and its prices into the test data column. This was done using vlookup formula and I need the same to be done in vba.
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL If B1 = 0 then C1 = NGL If A1 >= 1 and B1 > 0 the C1 = LTG. If A1 >= 1 and B1 < 0 the C1 = LTL. If A1 < 1 and B1 > 0 then C1 = STG If A1 < 1 and B1 < 0 then C1 = STL
I am creating a spreadsheet to monitor horse race betting but my formulas aren't coming out right.
The problem is to do with adding winnings to a running balance or minusing each bet's stake money from the running balance if the bet loses.
I have two formulas that are giving me a problem, both are in the P column in the attached spreadsheet. The first one, located in row 2, being slightly different as it is the first data row and so contains the starting balance in the formula.
=IF(OR(J2="W",J2="P",300+S2),IF(J2="L",300-O2,"")). This produces a blank or error result.
=IF(OR(J3="W",J3="P",P2+S3),IF(J3="L",P2-O3,"")) This produces an error whatever is in the J column
I want to reference 2 cells to open a msgbox. Currently I use this formula in a cell "=IF(E4="C",IF(A4>30,"OT","")) I am trying to write a macro that will open a msgbox instead. This is what I've tried so far
Private Sub Worksheet_Change(ByVal Target As Range) If Target(1, 1).Address = "$E$4" Then If Target(1, 2).Address = "$A$4" Then If Target(1, 1) = "C" Then If Target(1, 2) > "30" Then MsgBox("1") End If If Target(1, 1).Address = "$E$4" Then If Target(1, 2).Address = "$A$4" Then Target(1, 1) = "F" Then Target(1,2) > "38" Then MsgBox("2") End If
I have a workbook with several tabs. the first tab is a data input sheet: subtotals from this sheet get posted to cells on the other sheets. at the top of the Data sheet is a drop down validation list that the user will select the next month from. On each of the other sheets, is a grid with calendar months down the left hand side and years across the top:
I am trying to figure out a simple method to check whether there is data on any of the four other sheets in the spot corresponding to the month selected by the user on the Data tab. the format of the validation list dates are dd/mm/yyyy in cell B2 on the data tab.
The user will select a range (example, B4-Z4). The macro needs to test each cell to see if the number is in a certain range (example, is the number in the cell between 21-40, 41-60, 61-80, etc? - these ranges will not always be the same on each worksheet). If the cell is in that certain range, that cell is a 1 for that range. Example,
B4 = 23 21-40 = 1
C4 = 30 21-40 = 2
D4 = 45 41-60 = 1
After all ranges are tested, it will be graphed on a separate worksheet with the x-axis being the ranges (21-40, 41-60) and the y-axis will be the total number of cells that fit in the range. The above would be....................
What I am trying to do is cause (where the formula is) to:
If A2 is empty, put nothing, If C2 is greater than today put nothing, otherwise put C2 If E2 is empty then if b2 is "1st" add 4 workdays to A2, otherwise add 5 workdays.
I seem to have a problem with getting the formula to put the value of C2 if it is not greater than today In otherwords if the date in C2 is 6th Jan, i want 6th Jan as the result of the formula.
I need to test whether an optional Range has been passed to a UDF. IsMissing(RangeName) always returns False regardless of a range being given or not. RangeName exists as an Object that shows a Value of Nothing and Type of Range. I have not found any test that will indicate if the Range was passed in or not.
I have a macro that takes a value of one cell ("Cells(iFoundPass, 5")) and makes another cell (Admin_Level) equal it (the valules can be wither 'Administartor' or 'Standard'). When someone runs a macro it checks to see if "Admin_Level" equals "Administrator". The problem is that when that first macro runs, even if ("Cells(iFoundPass, 5")= "Administartor" and then "Admin_Level" will be made "Administrator" , my IF/THEN statements do not work. I think this is because the value of "Admin_Level"("Administrator" in the example above) is not text. I can't seem to figure out why. I do know, though, if I manually enter in 'Administrator' in the cell name "Admin_Level" then my IF/THEN works.
How to create a sheet to generate Random numbers from 2 specific list of a main list range of 36 Numbers. (List A = Specific 15 numbers, List B = The remaining Numbers from these 36)
Example :
-The Main List Range is from Number 01 to 36 -The Specific random list will be 15 Numbers of these 36
List A-. The Specific 15 Numbers are: 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33 List B-. Remaining Numbers : 04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36
So
I need to generate ONE Unique Random Numbers of List A ( 01,02,03,10,11,12,13,20,21,22,23,30,31,32,33) In Cell A1 In Cell A2 In Cell A3
And from List B, (04,05,06,07,08,09,14,15,16,17,18,19,24,25,26,27,28,29,34,35,36)
I need to do the same thing In Cell A4 Cell A5 Cell A6
It's possible to have a 6 cells with random but Unique Digits? (not repeated numbers between the 6 cell ??)
I am trying to build a macro that test for the value of each cell of a range (in a column), and if found, then the value of the cell of the same row (another column) will be set to 1. If not, then the macro writes a formula to get some data from BBG (this part is ok). this is what I have done so far but I have an error message, telling me "Not Else with out if" .
Code: Sub Fx() With Worksheets("DivRelease") Dim LastLig As Long
Is there a way to check if a named range exists before I run a piece of code? I created a new file that has need for all the old file's ranges plus a couple more, and I want to use the same macro for both. So on the first file I just want to say, if these other named ranges are there, go ahead and do his other thing.
I have a range of data that contains can contain either a pass or fail. what I require at the bottom of this range is a formula that says 'if any one of the range = fail then "test fail", else "test pass"'
I need to do stats for each month within a set of data. Ie take the min for all data in the month of Jan only and repeat for all other months etc. Is there some way I can set up a Min function to only consider the data belonging to jan for example and have it change and only consider Feb, then march.. etc Seems like it would be easy to just do this manually but there is a large timeframe over which data was collected so would take forever.
How do you write an If then statement using a range of cells? I want to construct a logic test using the range of cells A7:A19. I want the logic test to see if the any of the values =1. If the test is true then I want it to display the value in B7:19 (which ever cell corrulates to the cell in column A that has the value of 1) and display the number in the B column.
find the attached, I've some formulas on cells m,n & o and they related to each other, i just need n & o should be zero if m is zero.but the same formulas should exist also.
I have a user form into which data can be entered and is populated on the next sheet. The order ID is unique, is there way I can make the Order ID auto generated each time a record is entered instead of manually keying the number?
On Sheet1, Row 2 I have my columns named January-December, which correspond to the names of the other 12 sheets in my workbook. I want to test and sum the same range of cells in each sheet by simply coping and pasting the formula. I am using INDIRECT and SUMPRODUCT, but is there a better way? It seems to have caused the sheet to run slowly. Perhaps it's just the amount of data. Here is an example of the formulas I am using:
Monthly I am sent a spreadsheet detailing cost centre approval ranges for our management team. Unfortunately, the data provided shows a low range and high range in two different columns. Is their a formula to automatically generate the values for the missing numbers
For example columns A 1001 & columns B 1007
Can excel automatically generate the 1002, 1003, 1004, 1005 & 1006
I'm trying to use Advanced Filter to generate all the unique values within a list, only at times I want to Autofilter the data, then use the advanced filter to determine all the values of the slimmed list. For some reason, this works fine on column A, only returning the values that begin with a 1 when the column is filtered for all values beginning with 1. However, when I run the advanced filter on column 2 with column 1 filtered to all beginning with 1, all values all returned regardless of whether column 1 criteria is met. Either way, after the advanced filter, the autofilter has been removed.
The two boxes up top will be my start and stop numbers. I want to generate all the numbers between (and including) the 'start' and 'stop' numbers in the top two boxes above using VBA and list them in the larger text box below (I've changed the background of the textbox to appear as it was a label, fyi).
Hey everyone, I'm new to the boards and only moderately familiar with Excel, so I'm looking for a little help. My question might not be completely clear, so I'll try to give you a visualization.
I want to give users the ability to choose different kinds of expenses from a drop down menu (i.e., Salary, Postage, Supplies). However, some of the expenses have general costs associated with them on top of other costs, and I want it to be made clear by having the proper costs come up with the proper expense. For example: ...
I have a list in excel with ten values, but I need to get all combinations possible, picking 4 of 10 each combination. This must be as a combination and no permutation or variations either, I mean, values can't be repeated in a single combination.
I'm struggling to do this in a Macro but I don't get it.
I have a spreadsheet of website stats showing the number of visitors to all the domains and aliases we use for company websites. Each domain or alias has its own unique row of data. The data is in the order of most visitors. I have attached a simplified and anonymised example of the data in worksheet "stats". In real life this sheet runs to several hundred rows.
As you can see if look at the worksheet "domain key", each of our websites has more than one domain or alias pointing at it - these are reported separately by our stats package.
What I want to do is find an easily sustainable way of generating a summary report each month, such as you can see on the worksheet summary, which will give a total number of vistors for each site calculated from the visitors to the various adresses each site uses.
What I have done so far is use a very long SUMIF function, e.g. to find all visitors to the FR site the function reads:
This looks OK in the example above but in the real data we have in some cases over a dozen domains pointing to one site and its very messy and hard to maintain.
What I would prefer to do is something that would use a range of data for the criteria rather than a specified string e.g.:
=SUMIF(stats!A2:A16, domain_key!C2:C16, B2:B16)
Obviously the straight SUMIF function won't do this. The advantage to this approach is that it would make the ongoing management of which domains are counted for each country a lot simpler as I could just edit the data in the domain_key sheet rather than having update the functions.
Some issues to be aware of are: The order of data will change each month so youcan't guarantee that each address will be in the same row every monthThere isn't a pattern to the addresses that would allow you to use any kind of wildcard, e.g. you can't say all addresses containing "companyname" are the UK site and all addresses using othername are the French site. Similarly, you can't say all the french site addresses end in .fr - some countries use .com
