I m trying to achieve is generating ref numbers automatically.
I have attached an example.
In Cell A if the user choose yes it gives a ref number. The trouble im having is that say in cell A5 a Yes is choosen at a later date, it will throw the numbers out of order and the ref numbers get mixed up, there any way of stopping this from happening or any function that prevents this from happening
I have multiple users that use a spreadsheet to enter data. I am trying to figure a way to assign a case number to each row of data that is entered. I cannot really do sequential numbers, because often people are entering data at the same time, and often these people are working offline.
So I was thinking if I could somehow have the case number be a few letters (to identify a specific person) and then a series of numbers (to signify which case number it is). That would allow multiple users to enter data offline simultaneously without having to worry that the case numbers are going to be the same. Maybe I could use the Application.UserName and somehow parse it to give me the letters (perhaps Intials or something), and then somehow apply sequential numbers to that...
for instance, JJH00001, JJH00002, etc
I get the following error whenever I try running a Monte Carlo simulation with iterations in excess of 64000 (The MC simulation calls the 'NormSInv' worksheet function at least once per iteration, and the argument to the 'NormSInv' function is always a standard normal RANDOM number also generated by Excel VBA): Run-time error '1004': Unable to get the NormSInv property of the WorksheetFunction class
Why do I inevitably encounter this error ONLY when the number of iterations exceeds 64000, for instance? Simulations with iterations < 64000 run smoothly without a glitch.
I understand how RAND and RANDBETWEEN work, but have a slightly more complicated random number generation problem that requires a number that isn't solely between two numbers.
I have one cell that is randomly generating a number between 0 and 350 (we'll call this value A) and another randomly generating a number between 0 and 15 (value B). Then, I have a third cell (value C) that equals 15 minus B. Where I am having trouble is with a fourth cell (value D). This cell I want to have randomly generate a number between 0 and 350 but it needs to be D less than the value in A or C more than the value in A. Is there an advanced random number generation technique that can solve this?
Been trying to think of a way to get random numbers 1 to 36 generated without repeating in the same column, however also getting it to perform the same opertaion in 9 more columns (B:J) without the same number appearing in the same row.
example
1 2 5 4
2 5 3 1
3 4 1 5
4 1 2 3
5 3 4 2
Unsure if this is possible, have created a basic script for random number generation but have no clue how to expand across columns.
it is possible to generate a completely random number generation in excel?
Other than using the rand() function and then sorting by top or bottom (as then have affected the sample by choosing which direction it is sorted).
And the randbetween() function changes everytime you modify a cell.
You must install "Analysis TookPak-VBA" before doing this; check in your Excel's Tools/Add-Ins menu. Paste this long function (see below) in an Excel's worksheet (any cell) and press F9 (calculate) until you see at least 2 identical numbers. Real life's lottery does not have repeating numbers. I can disable this repetition by using cells relation but I want to use just one (1) cell. Any expert wants to challenge? If not, you can use VBA. But it still requires one (1) line of codes.
=RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)&" "&RANDBETWEEN(1,50)
I have taken on a project that is would seem is far out of my league right now, but need to deliver.
I have attached the example spreadsheet what I am trying to achieve, which is basically a random automated number allocator.
Look forward to receiving your replies,
Example.xlsx
my requirement is attached here with.
in generating the unique no?
I have multiple files that I need to get data from. My dream is to push a button and gather the data from variable files. (I know how to make the button and get data from single file).
The file name is below:
Cell A1 contains this formula ='C:ReportsYear 2009[CityWaterReports Jan 2009.xls]PHFlows'!$B$44
Cell A2 contains this formula ='C:ReportsYear 2009[CityWaterReports Feb 2009.xls]PHFlows'!$B$44
and so on.
I would like the customer to be able to push the button and be asked to enter the year(eg: 2008) and have the formula update to the new year in each cell.
I have only two sheets..first sheet is the user sheet where the user gives the data...second is the sheet where in we get the result according to user given data..
there is a cell in User sheet where User gives a Number. for example " 8 "
so sheet2 : here we have only 9 rows. as user gave "8" the fisrt 8 cells in the first column should see like this
B 01/08 , B02/08 , B03/08 as so on till B08/08
when user gives 9 in user sheet
then the first sheet is finished with all the 9 rows ending with B09/08
when user gives 10...then sheet 3 should automatically appear with the first row and first colum saying B10/08.
when user gives 20 in user sheet...then sheet4 should appear with last number as B20/08
so each sheet is having only 9 rows..
first sheet ends with B09/08
second sheet ends with B18/08
third sheet ends with B27/08
I am currently working on a price configurator which has more than one sheet with parts and prices on it.
what i want to do is have a sheet where i can populate a parts list from the parts that i have values for.
i want it to only pull the lines out of the previous sheets with values next to them.
I have three columns A,B&C In the column A Iwill generate random
numbers between 40&50, In the Column B I will generate random numbers
between 35&45, The column C is the sum of Column A&B(i.e. C=A+B) but I
the sum should be between 83&88. I need acondition such that the random
numbers generated in Column A&B Should satisfy the Column C(A+B)
condition sdatisfying the A&B columns condition.
What I have got is a list of exams and subjects on the first sheet from cells A3 to A whatever. This list varies in length from a few exams to many dozens.
Under each exam is a list of subjects.
i.e.: Under the math exam is addition. multiplication, division etc
On columns B through ZZ and beyond. I have individuals names.
Under those individuals names (opposite the exam row) I have pull down boxes saying if the individual has passed failed or did not sit .. etc.
What I would like to be able to do is to automatically generate a second sheet with the individuals name on it with a report of what exams they took with what result. Of course it would be nice it was nicely formatted with the headings of all the different exams and the subjects taken arranged in order.
If an exam was not taken it should not appear in the generated sheet
I have a fairly good knowledge of Visual Basic though I have not had much to do with VB for scripting.
The following code works very well up to a point. See previous thread Automatic Report Generation.
As I use it to change my yearly summary sheet all at once. However as it stands I need to insert a seperate line of code for each month and cell that I need data from I will have over 2000 seperate lines when I am done. Also if the file does not exsist yet then the cell returns a #REF (error 2023). And none of the other formulas in spread sheet will work. Is there a way to have the cell return a zero if the file has not been created yet?
Is there a way to try and automate more of the code below? Cell references will be all over the place. All the months for the year will be needed.
The code below generates numbers in the range (A2: H2)
View 4 Replies View RelatedI'm trying to write a code that will generate random numbers and no number appears more than onece.
For I = 0 To 3
For J = 0 To I
Do
n = Int(4 * Rnd) + 1 'random number generated
Array1(I) = n 'random number settled in an array
If (I = I - J) Then 'checking for the same place in array
numsOK = True
Else
If Array1(I) = Array1(I - J) Then ' comparing two different places in an array
numsOK = False
Else
numsOK = True
End If
End If
Loop Until numsOK = True
Next J
Next I
want to generate automatic report from excel work sheet
View 1 Replies View RelatedI want to prepare a graph / chart for the column A and column B. Column A has headers and B has the data that I want to represent in the graphs. The real problem in this is that the two columns length is not fixed. For eg. for January Column A & B could have 5 rows of data but for February they could have 10 rows of data. I dont want to prepare the chart manually every month. I want that if we could apply any VBA coomands that could prepare the chart automatically irrespective of the rows count.
View 9 Replies View RelatedI'm using Excel to create a flowchart from a configuration sheet. I'm not manually entering the coordinates. Instead, I draw the start step in a set location. The next step(s) that's connected to the first step is placed directly below the start step. if there's more than one step connected to the start step, it's placed at the same y-coordinate but shifted along the x-axis.
The problem I'm running into here is this: say I have three steps connected to the start step and three further steps connected to the first of the three connected to the start step, how can I keep track of this and shift the other two steps connected to the start step along the x-axis so they're not positioned above the four steps below the first step (and as such, in the same location as other steps)? I'm not sure if my description is particularly clear or not so I've attached a drawing that will hopefully clear things up.
Is there any way of giving Excel shapes a "weight" so that other shapes won't line up on top of them?
I would like to generate a random value using a skewed normal distribution. For inputs I have the sample mean, standard deviation, and skewness. I would like to find a way to generate numbers randomly that would fit the skewed normal distribution described by the inputs.
Some information about skewed normal distributions can be found here: ...
I want to generate a random number based in a poisson and i use this
Application.Run "ATPVBAEN.XLA!Random", ActiveSheet. Range("$B$2"), 1, 1, 5 , , 35
But i want to save the number in a dim single variable called N. I try to put N where i have write ActiveSheet.Range("$B$2") and do N=Application.Run ...
But it doesn't work.
Without using VBA, how can i generate strings say
x1
x2
x3
x4
x5
assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.
Trying to add a macro to generate an approval email with certain data from the spreadsheet within the body of the email. I have the following in place currently to deploy the email:
Sub Sendemail()
Dim Email_Subject, Email_Send_To, _
Email_Cc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "New Account Approval"
[Code] ....
What do I need to add the following cell values into the body of the email, All Sheet 1?
B6
B4
B8
F14
E16 : F16
E17 : F17
A25 : B25
A26 : B26
A27 : B27
Is there something simple I can insert to make this work?
The following table/ code is something which I've been trying to tailor from a previous post so I'm not taking the credit for what I think is some very good code. Unfortunately I can't find the link to it - sorry!
Right, I have a number of columns containing a various amount of data entries in each with the first row being the header. I would like to generate all possible combinations of this data in one column, the entries separated by commas, that will eventually be exported as a csv file.
The number of columns and number of rows in each column will be changed regularly ...
I'm am running Excel 2003 SP2 on Windows XP SP2.
I have a macro which modifies cells in a spreadsheet. But for brevity sake, I have an empty spreadsheet which has an Auto_open macro and two subroutines in it. The subroutines initially consist of just the Sub and End Sub statements.
The Auto_open procedure reads in lines from two text files (generated by another application), and inserts the lines into each of the empty subroutines. Auto_open then runs the two subroutines.
I have been able to get this dynamic creation of the subroutines to work for one subroutine, but not for the second. I receive the compile error: "Only comments may appear after End Sub, End Function, or End Property". I don't see anything wrong with the inserted code.
The modified subroutines appear fine, and if I save the macros with the modified code, close excel, rename the text files so they are no longer read in, and re-open the spreadsheet, the auto_open procedure and the two subroutines run fine. This tells me the code itself is okay, and yet it won't work during the initial run.
Below is the macro code in its original state:
Sub Auto_open()
Dim fso, f
Dim VBCodeMod As Object
Dim LineNum As Long
Dim StrFileName As String
ShowVisualBasicEditor = True
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
End Sub
----------------------
The contents of the read in file, %TEMP%subA.txt, is just one line:
MsgBox("inside SubA")
The contents of the read in file, %TEMP%subB.txt, is just one line:
MsgBox("inside SubB")
------------------------
When I open the spreadsheet the first time, it gives me the first message box from Sub A, but then generates the error and highlights the Sub SubB() line.
To duplicate the problem:
1. Insert the macro into a spreadsheet. Save and exit it.
2. Create the files %TEMP%subA.txt and %TEMP%subB.txt containing the single MsgBox lines.
3. Open up the spreadsheet. SubA will run and a message box will appear. Then the compilation error will occur.
4. Save and exit the spreadsheet.
SubA will now contain:
Sub SubA()
MsgBox("inside SubA")
End Sub
SubB will now contain:
Sub SubB()
MsgBox("inside SubA")
End Sub
5. Rename the two text files, so that the next time you open the spreadsheet it won't try to insert the lines from the files into the subroutines.
6. Re-open the spreadsheet. Two message boxes will now appear, one from SubA and one from SubB.
I need to enter a revision string into a file.
Basically, I need to combine the filename and its generation time and then encode it into a string (i.e. a 6 digits HEX code or 6 digits using [0-9][a-z][A-Z]) so it would generate a pretty much unique revision number.
So, is there any function that you can think of that would do something similar?
If not, what is the best to tackle this?
Generate a CRC or md5sum?
What would be in your opinion a quick and efficient way to solve this tricky one?
I have a large excel sheet that will take a lot of time grouping it manually, so I was wondering if there was some way somebody could help me out with creating a code in VB to automate this grouping for me. All I would need is something to read through the rows of one column and when it finds a blank row, keep scanning until the next blank row and then group the data between the two blank rows. I have attached a small portion of the excel sheet I am working with if you need to get a visual of what I am needing.
View 9 Replies View RelatedI have a monthly report that I get with lab tests and amounts ordered. (I will copy a section for you to see). The Director wants it to be compiled into an easy to read, 1 test with totals. The way i get the data is broken down by site (we have 5). So, i may have between 1-5 rows of the same test with a total for each. I want to set up a Macro/Script that would go through the report and automatically add the same test codes together. My long term goal is to have it pull the Test Number, Test Name and Total Count into a separate worksheet, with the Total Count being the count of all the same tests.
see attached data: ...
I have a spreadsheet that deals with expiration dates. The whole sheet is conditional formatted so that when the current date is within 3days of the date showing in A2 then that cell changes colors. Ok is it possible that a script can be written so that when the cell changes colors... (Someone is going to think I am silly asking this but) excel sends an automated Email?
View 2 Replies View Related