I have a list of petroleum accumulations with three types of data: Reservoir depth, Net Pay and Gas-oil ratio. for each accumulation, the number of reservoirs within each can vary. So I may have a accumulation with only one set of those stats, or an accumulation with 20.
I need the program to go down an alphabetized list (8000 entries long) and after every unique accumulation name average the values of the stats(which are in three separate columns) then put it in one row perhaps beginning in Column M or another sheet. So that I have a list of accumulations with the average of those stats, one entry per accumulation name.
This Coding is correct for CheckBox49. This code Changes the Color of one Cell from Black to white and the cell below it from white to black...(its a dipswitch) when the check box is checked... When its unchecked it does the opposite....
Code: ' ACM 1 Switch 1 Private Sub CheckBox49_Click() If CheckBox49.Value = True Then Range("AC24").Select With Selection.Interior .Pattern = xlSolid [Code] ........
Now what needs to happen is this.... I'd Like to have this Happen if checkx49 is checked...
Code: Range("A1,K50,K51,R50,R51").Select Range("R51").Activate With Selection.Interior .Pattern = xlSolid
[Code] .......
If Checkbox49 is unchecked do this... Put in D26 Reader Name? in Italics.
Code: Range("K50,K51,R50,R51").Select Range("R51").Activate With Selection.Interior .Pattern = xlNone
[Code] .........
Also...what ever information is entered in D23. Put that in F50 with RDR at the end... Whats in D23 Put in F51 Whats in D23 Put in M50 and concatenate with a space from cell U49 Whats in D23 Put in M51 and concatenate with a space from cell Y49
Lastly code to select K50, K51, R50, and R51...=Len whats in F50, F51, M50, and M51 when the box is checked...and dont len when the box is unchecked.
I am currently using a product formula in a cell and was wondering if I could add a round up/down action to numbers containing decimals? For example, let's say that my cell values are 300, 327.6 and 355.2 after using the product formula. Is it possible for those values to reflect 300, 328 and 355, respectively?
I have a collection of about 200 .xls files in a folder, each contain the same macro. I now need to run this macro in all 200 files and save them but to do this manually will take hours. is there anyway to set excel to batch process a macro in multiple files?? Or maybe i can write a new macro to open up each workbook, run, then save and exit??
Im trying to get my macro to open allow me to select multiple workbooks, then have each workbook open up and copy A2:N2 and down and paste these into my active workbook, under each other, ie on the next available row (basically combining all the workbook data together to create a big list.
The below code works to open a single workbook, copy the data and paste it in at the next available row, but I have to keep running the macro for each workbook whos data I need to import!
How can I modify this code so I can select more than one file? I need something like for each wb .....at the end next wb...until the macro has done the below for all selected workbooks.
VB: Sub openandcopy()
Dim wb As Workbook Dim ws As Worksheet Set ws = ActiveSheet Set wb = Workbooks.Open(Application.GetOpenFilename)
I am working on trying to make a macro I wrote run faster, and I understand that one way to do this is to stop certain actions from occurring. However, I am having a hard time understanding what stops what, specifically among these:
Is there a structure inside of Excel which keeps track of all the actions taken by a user (something that would presumably be used to allow for an undo sequence)? If so, is there a way to get access to it?
I have a series of macros altering various workbooks and sheets. They're numbered Step1, Step2, etc. In my Step5 I have a series of Case statements in a macro, and I don't know how to get it to do what I want next. I have NOT tried running this code yet, and I'm sure I have something(s) wrong in it.
1) If the selection in DstWbk, sheet "Steps", is "01DSP" through "11DSP" the macro needs to delete specific columns in the SrcWbk, and then move to the next step (6).
2) If the selection in DstWbk, sheet "Steps", is "*DSP" the macro needs to go directly to the next step (6)
3) If the selection in DstWbk, sheet "Steps", is anything else the macro needs to flash a generic "No Data found" message and move on to Step7.
The part of the code that's throwing me begins at 'Select only the specific regional data' and ends at the "Case Else MsgBox"
I'm using a Private Sub Worksheet_Change(ByVal target As Range) macro on Worksheets(2) to perform certain actions when a selection from Worksheets(1) is pasted to Worksheets(2). Before ending the Worksheet_Change sub I'd like it to deselect the copied selection on Worksheets(1). I've tried to do this by selecting Worksheets(1) and moving the cursor, but VB ignores the "With Application.Worksheets(1)" instruction. Removing "Private" from the sub heading makes no difference.
Is it possible do this with a Worksheet_Change routine? If so, how?
I am trying to use following codes for a add-in utility so that user can enable or disable logging activity for a given workbook.
My problem is to how to add the following code to a new workbook so that "Loginfo" function could be activated. I have tried some application... based commands but it do not seem to be working.
I have made an class and created a file to log when user change value or insert formula in a sheet get recorded in a file C:Logme.csv. The problem is it is recording the value and formula at random i.e in some instances it records the value and in some instances it is not recording the value and formula in logme.csv . I am unable to figure out why this is happening?
Macro: the system monitors changes to the spreadsheet and then performs actions accordingly. Example: If the value of column c = Bug, then it sets the value of G to Celeste and Column H to Kewill.
Could this macro be changed so that instead of having to enter "Celeste" in this code, it could set the value of column G to the value found in H5 on the sheet?
I tried replacing "Celeste" in the code with H5 but it did not work.
Private Sub Worksheet_Change(ByVal Target As Range) 'Declare variable Dim iCol As Integer 'Look at the column number of the changed cell (Target) Select Case Target.Column 'Column C Case 3 Select Case Target.Value 'if "Bug" set iCol to 3 (Red)and change value of column G to "Celeste" and Column H to "Kewill" Case "Bug": iCol = 3 Target.Offset(, 4).Value = H5 Target.Offset(, 5).Value = "KWL"................
I wish to perform different actions depending on whether certain errors are present in a cell and I can't figure out how to do it.
Attempt (within some For loop):
othCell = ActiveCell.Offset(-95 - counter, 0) If othCell = CVErr(xlErrNum) Then conditioncount = 0
This generates a "type mismatch" error. I also tried othCell.value in place of othCell on the second line; I don't really follow the distinction in usage for "value". I've also attempted this with Error 2036 in place of CVErr(xlErrNum). I don't know much at all about VBA!
I have written a macro which references to several different sheets and cells therein. The macro is assigned to a command button on 'Sheet 1'. When I click the command button, the screen flickers and the user can see the macro running all the commands I have written. Is there a way of stopping this - maybe replacing it with a static screen view whilst the macro runs?
where exactly to add the code under Tools > Macro > VB editor.
My version: Office XP Pro ("2002")
The first code I need should be simple. I want Excel to copy cut delete files from the hardware, using data from a workbook.
There are 3 sheets: 1. "Names" - contains picture file names (without the JPG extence) in one column, 2. "From" - contains a cell with the original path to copycutdelete from 3. "Target" - has a cell with the target folder path (in case of copying/moving).
I made it in 3 sheets for making it user-friendly, since unskilled users will use the method.
I need to have 3 hyperlinks in the first sheet: "Move (cut)", "Copy", and "Delete", and by pressing the matching VB code eill be activated.
The second code should output (and print, if possible) data from a line to a pre-defind sheet.
Let's say there are 4 columns: picture file name, name of photographer, description of the photo, and the date it was taken. There will be in the end of every line a cell with a hyperlink called "Output photo details" to output the data to specfic cells in the Print sheet (e.g. the date goes to D7, the name goes to A3, etc.). An important thing I wanna output is the picture itself (like from "Add > Picture" ment, in a location I define in the code.
I have a worksheet ("Sheet1") with an activex checkbox (CheckBox1) in cell B1
what code do I need to tell if the checkbox is ticked or not. (it probably should be some true / false type test but I keep not getting the syntax of the code correct.
what do I need to declare or set or ? to access the state of the checkbox
I need to print a worksheet. The sheet has rows of a specific height (45) and data in one cell grows - adding comments/notes with date attached so that last one entered is displayed, the rest get shoved down.
I need to sometimes print out the sheet. However, I want to have all the notes shown so would like to (1) autofit all rows, (2) print the sheet and then (3) put all rows back to row height of 45. All this I would LIKE to have done by "trapping" the print button on the normal Print area
I have attached some code, but the sheet (1) does not print at all, (2) the code is sometimes executed twice (in debug mode followed it) and (3) sometimes does not set the row height properly either at autofit or static height.
Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.
Example: A1 @ 31/01/07 = 50 therefore cell C1 = 50 A1 @ 28/02/07 = 45 therefore cell C2 = 45 and so on for 12 months
what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.
I need to know if it's possible to log the process of a series of macros either to txt file or to a Sheet, (either way won't matter but txt file would be perfect)
For example:
Sub MyMacro1 () code End Sub
Sub MyMacro2 () code End Sub
If this process was logged, then the log file would look something like:
Date Time : Sub MyMacro1 Date Time : End Sub Date Time : Sub MyMacro2 Date Time : End Sub
But it would be a in text just like a log file. Is this possible with Excel ?
The reason is because my Workbook is huge and I am forever tweaking and adjusting or fixing and I require reference points.
Is there a way I can interupt the save process depending upon certain conditions. For example if a particular cell contains a specific value and the user attepts to save the worksheet I would like to interupt the process asking "Do you really want to save this sheet now?" with the options to continue or abort. I think I can manage the message box or userform but don't know how to initiate it when save is selected
I wanted to know If there a way to hide the process of executing macrob (vba)? I have a long macro that runs on a file and I want to hide the process. I know there is an option to it, I just don't know it.
I have a list of rows with Yes and No check boxes. Columns "T" and "U" contain the output of these checkboxes (i.e. "TRUE" or "FALSE"). At the end I have a button which I'd like to run this code. Essentially, I want to code to go through row by row and do the following:
1. Check to see if both boxes are blank or if both boxes are checked - and if so set the background color to yellow 2. Otherwise set the background color to clear
I have written the following code which accomplishes this task for Row 9. Is there an easy way to repeat this process for rows 10-15 without copying all the text and changing the row numbers?
Then, any way, say "If all the rows (i.e. 9-15) have clear background colors (which would mean that they all 'passed' the test of having exactly one and only one box checked in the row), then run another macro which I have written"?
Rem Check to see if neither box is checked or if both boxes are checked and sets background color to yellow, otherwise sets background color to clear.
If (Range("T9").Text = "") And (Range("U9").Text = "") Or (Range("T9").Text = "TRUE") And (Range("U9").Text = "TRUE") Or (Range("T9").Text = "FALSE") And (Range("U9").Text = "FALSE") Then Range("B9", ("G9")).Interior.ColorIndex = 6 Else: Range("B9:G9").Interior.ColorIndex = 0 End If
what excel can process without having the program crash. Our company is using an old dos program to store payroll information (don't ask -they won't change it). Anyway - I have the ability to use pervasive software to pull the information into excel.
The problem here - is that the information is stored line by line. And in order for me to get useful information out of it, I need to sort those lines by date and a task code.
I remoted most of my number crunching stuff to a dll.
It work fine except for a few strange things that I observed like from time to time a strange message "Error in loading DLL" even though everything works fine. This message appears only when entering the main formula in the formula, and is no problem at all in usual work.
When I investigated, I noticed in the "windows task manager" that one instance of excel remains open even after I have closed excel. This phantom excel process is maybe related to the other problem I explained above.
Instead of calling a VBA function, it creates a VB6 object from this dll and uses the methods of this object. In this way I have remoted the number crunching functionality.
The main parameter passe in this process is a reference to the original workbook. I need it because the number crunching functions need to read data from the workbook.
I made it sure to terminate the life of the object properly on both sides by ad hoc obj=Nothing statements.
Still I think that the "garbage collection" or the "terminating" is the cause of the problem.
I currently have a great VBA option to append records to the appropriate Access table. One problem I'm running into though is getting by the unique record number. Currently a user submits a record to a DataStorage tab which is then appended to the database. However, we would like to keep some of the old records (maybe a weeks worth: approx 100 records) on that tab in case any problems arise in the append transition. But we don't want duplicate records in the table.
Is there a way to modify the below code to kick out the duplicates and only bring in new records to the table? I will assign the table the requirement to only bring in unique new records but the code errors in Excel.
Sub RunAccessQueries_ADO() Dim cn As ADODB.Connection Dim cm As ADODB.Command
dbPath = "C:Documents and SettingsUSERDesktop" dbName = "MyAppendTest.mdb"
Set cn = New ADODB.Connection Set cm = New ADODB.Command
With cn .CommandTimeout = 0 .Provider = "Microsoft.Jet.OLEDB.4.0;" .ConnectionString = "Data Source=" & dbPath & dbName .Open End With
With cm .CommandText = "MyApp" .CommandType = adCmdStoredProc .ActiveConnection = cn .Execute End With cn.Close ActiveWorkbook.RefreshAll MsgBox ("Append Update is Complete") End Sub
