Followup On Automatic Report Generation
Jun 24, 2009
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.
View 6 Replies
ADVERTISEMENT
Jun 14, 2007
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.
View 11 Replies
View Related
Apr 24, 2014
want to generate automatic report from excel work sheet
View 1 Replies
View Related
Jun 17, 2009
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.
View 4 Replies
View Related
Nov 3, 2008
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
View 13 Replies
View Related
Jun 19, 2007
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 ...
View 9 Replies
View Related
Dec 24, 2012
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 ($)
Fish
10
50/10 = 5
10*5 = 50
Prawn
6
30/10 = 3
6*3 = 18
SubTotal
$68
Container B...
Container X....
View 9 Replies
View Related
Nov 12, 2009
The attached file contains the brief but in short I'm looking for an automatic report to be generated from a given dataset. Conditional Lookup functions could be useful, but I'm getting lost in the middle of the large data. I've tried to summarise the requirements on the file. Based on the given data the output table should show which product, which SKU have zero value in which Class by Area?
View 5 Replies
View Related
Sep 19, 2013
I am trying to analyse, in a set of data that how many followup made by a salesman between a particular set of dates. I've attached a sample. In this example I differentiate between people if the name and age are same. I know that it is possible with formula 'countifs' but how?
View 3 Replies
View Related
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Jul 2, 2008
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies
View Related
Apr 5, 2007
my requirement is attached here with.
in generating the unique no?
View 14 Replies
View Related
Apr 26, 2007
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
View 9 Replies
View Related
Oct 7, 2009
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
View 14 Replies
View Related
May 4, 2006
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.
View 2 Replies
View Related
Jul 29, 2009
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?
View 12 Replies
View Related
Aug 10, 2014
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.
View 2 Replies
View Related
Oct 4, 2005
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.
View 11 Replies
View Related
Oct 24, 2012
The code below generates numbers in the range (A2: H2)
View 4 Replies
View Related
May 8, 2006
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
Loop Until numsOK = True
Next J
Next I
View 9 Replies
View Related
Apr 12, 2009
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies
View Related
Nov 24, 2011
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.
View 9 Replies
View Related
Sep 5, 2012
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.
View 9 Replies
View Related
Sep 5, 2013
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?
View 3 Replies
View Related
Oct 1, 2008
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.
View 9 Replies
View Related
Sep 20, 2008
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: ...
View 5 Replies
View Related
Jan 1, 2008
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.
View 5 Replies
View Related
Jun 3, 2009
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.
View 3 Replies
View Related
May 18, 2014
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?
View 2 Replies
View Related
Oct 4, 2007
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.
View 9 Replies
View Related
