I need to repeat the following two actions one after another therefore I cannot use F4 key
The actions are :
1) select some cells in two rows and colour them yellow.
2) select the last two cells of the rows ans merge them.
these are one after another where F4 will repeat only the 2nd step but I need the 1st step of colouring.
Are there any short cut keys or method or recording to repeat the above steps when necessary.
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.
I need to assign two actions to a one button.
1. action: Copy Column A, paste to Column C
2. Write time&date into G5
I am only capable of doing one button for each action using the macro recorder and simple code:
[Code] .....
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:
[Code] ......
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?
View 5 Replies View RelatedI need to insert code into an existing macro that does each of these two similar actions:
If a specified word is in the heading (row 1 cell) of a sheet, DELETE that column
If a specified word is in the heading (row 1 cell) of a sheet, BOLD that column.
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?
View 2 Replies View RelatedI want know the formula for below issue. I have number of of actions done for each day for a month or more.
If I select a particular period for example 01jan14 to 10jan14 i want to get all actions done in this period.
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?
View 3 Replies View RelatedMacro: 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?
View 3 Replies View Relatedwhere 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 workbook that is used to track overtime hours worked through the year. I've received some help from users on this forum, and others, to create the necessary macros and formulas to allow a supervisor to make use of the form; however, the workbook is also used to track those that wish to volunteer to work overtime each weekend. The sheet has a list of names for the employees on each shift, then a column to allow a "Y" to be placed under the date - designating them as a volunteer for any overtime available. I've found this spreadsheet (attached - from this site) that will allow individual users to access the workbook and have unique passwords for each. I don't completely understand how it works, but I feel it may be a start to getting what I want.
Essentially, once the user selects their name and inputs their password, I'd like the workbook to be opened and then allow them to only manipulate the row associated with their name. That row will change from week to week, so specific numbers will not be able to be used, but their name will be the same as the name used in the logon.
I feel like I'm asking for a lot here, so just some initial guidance on whether this type of thing is possible would be an acceptable answer.
Is it possible to have a conditional cell based on the actions of another cell in a second database sheet.
For example: A single excel file contains two database sheets. If any characters are entered into cell A1 of sheet 2, then cell A1 of sheet 1 turns grey.
I have this formula - a vlookup. If the returned result is N/A how can I make it return 'No Actions' =VLOOKUP(A2,FRACA_Pivot_With_Actions!A:D,4,FALSE)
View 2 Replies View RelatedI was asked to parse and chart some data files. The data are obtained from a data acquisition system sampling a pressure transducer at about 300 samples per second. Since the start of the pressure event is created by a chemical reaction, there is no easy way to gauge when the the event will begin. As a consequence, the data acquisition system is left running continuously while waiting for the event.
Needless to say, at 300Hz, the data file grow very large, quickly. The data preceding the pressure event are close to zero and can be removed simply by using code which deletes rows with values less than a given threshold. An example of this code is:
Code:
Private Sub CommandButton1_Click()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("B20:B500000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) < 0.15 Then
[Code] ....
There are other operations that need to be performed as well. My question is whether if I have a workbook open with this and other code, will Excel allow operations on one or more open workbooks from this workbook? I would think that you can because you can move or copy a worksheet to another open workbook.
I am trying to repeat this macro until the last row of data present in column "D".
Sub inserttext()
'
' inserttext Macro
'
'
Range("D2").Select
ActiveCell = "Test Description:" & Chr(10) & "" & Chr(10) & "Expected Results: ERROR level fires stating " & ActiveCell
Range("D3").Select
End Sub
I have a workbook with 4 worksheets. I have a macro ( Cntrl + U ) that runs a series of steps and prints the results with it ending on the beginning page. This works well but I want it to repeat the macro until it reaches a blank cell on the starting page.
View 9 Replies View RelatedI had a spreadsheet with 45 fields, endind with a SUM in field 47.
I've inserted new fields, exactly 531. The SUM doesn't know that and still thinks that I have 45 fields:
SUM(F2;F5;F8;F11;F14;F17;F20;F23;F26;F29;F32;F35;F38;F41;F44)
How do I add automatically the value 3 to F44, continuing to add the value 3 to 531? So I want:
SUM(F2;F5;F8;F11;F14;F17;F20;F23;F26;F29;F32;F35;F38;F41;F44;F47:F49; F52; ETCETERA)
How do I keep repeating
If Range("E22").Value Range("G3").Value Then
Rows("22:22").Select
Selection.Delete Shift:=xlUp
Until E22 does = G3
Then I need it to go to Row 23 and do the same.
My goal is to leave only rows (between rows 22 and 50) that have the same value in its column E that match cell G3.
I'm trying to pick 10 random numbers from a list of 23, with no repeat numbers. I'm using the formula "=INDEX($A$1:$A$23,RANDBETWEEN(1,23))"
This will give me 10 random numbers with repeat numbers most of the time.
Is it possible to repeat rows on top of every page but one? Specifically, I have a seven page spreadsheet and I don't want the rows to repeat on the seventh page.
View 6 Replies View RelatedIn attached File, there is a table there is date in COLUMN "G" and column "H" contain Name....
I want to count value in column "L" by matching date from column " K " .....BUT THE SAME NAME SHOULD NOT BE REPEAT COUNT... i.e, HITENDRA and RITU name is repeat 2 times in date 1-Jul-2014 .. but it is a similar so it should count as a "1"..... the total count for the day is "6" but avoiding similar value the total count is "4"..... so I nead a "4" figure as a result......
I'm putting a presentation together for work regarding false alarms and I'd like to count how many alarms have gone off at certain locations and I'm wondering if there is a formula that will accomplish this. I've attached a pic of a spreadsheet containing the data to be used. On the left side, there is a table labeled "Alarms" with columns displaying the dates, street numbers and street names. On the right side of the spreadsheet, there is a table labeled "Activations", which will be used to display the amount of single, double, triple, etc. activations.
For example, using the data, the alarm at "2 Red Oak Row" has gone off 3 times and since there are no other addresses where this has happened, a "1" would be displayed under the box labeled "3" in "Activations". Since the alarm at "4 Winding Way" and the one at "23 Blackberry Lane" have both gone off twice, a "2" would be displayed under the box labeled "2" in "Activations". The same would hold for addresses where the alarm has gone off 3 times, 4 times, 5 times, so on and so forth. In addition, if an alarm has gone off 10 or more times at any residence, the first 10 activations and any subsequent activations would be counted as a "1".
For example, if there is one case where an alarm at a certain residence has gone off 10 times (or more), a "1" would be displayed under box 10. If another residence's alarm also went off 10 times (or more), a "2" would be displayed under box "10".
(1) In an excel i am about to create 1000 rows of records. The first 100 i set the column to display" Job Role 001 - Job Role 100. The formula i applied from row 2 onwards is: ="Job Role " & TEXT(ROW()-1,"000")
(2) Then now i need to repeat this Job Role 001 - Job Role 100 for the next 100 rows. But if i copy these Job Role 001 - Job Role 100 to the next 100 rows it will result in: Job Role 101 - Job Role 200.
Any solution to make it repeat exactly Job Role 001 - Job Role 100 for next 100 rows onwards??
