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...
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 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.
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.
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 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.
I'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
I 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.
I'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: ...
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.
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?
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 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.
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 would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.
I have 2 worksheet A for data capture & is looking for a VBA to create a report in worksheet B using worksheet A. How the VBA should look like?
Worksheet A Container Type Type of Seafood Weight (kg) Ave price $/kg Price ($)
Container A
Fish
1
5
5
[Code] ........
Worksheet B -contains X number of table, one for each container in worksheet A. -there should be a sub total at the end of each table. - one final grand total at the end of report.
Container A Type of seafood Weight(kg) Ave price ($/kg) Price ($)
What im trying to do is this..I read this number and check for the first number with the 2nd number. If its greater then i will swap it. Im trying to do this so that my number can be rearranged as 456789.
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost Col B = The number of the month when the costs are starting (1=Jan etc) Col C = The duration or the number of months for which the costs are to spread Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
