I 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.
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 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 RelatedI 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 RelatedIs 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'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!
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 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 running Win XP and Excel 2003.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test()
Dim i As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete
Next i
End Sub
After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive.
Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub
Sub UpdateEntries()......................
I have a spreadsheet which is updated daily. Row A of the sheet has the date in it, and every day a new column is created for the that set of data. I have the below code which works at the moment:
[Code] .....
I want to use this same code on another spreadsheet to do the same process (I need to copy and paste 4 columns (A,B,C + D, into E, F, G & H, then tomorrow it will copy E, F, G & H into I, J, K &L etc etc)). The problem I'm having is that A1:C1 is a merged cell, then D isn't (used as a border to separate). So when it is copied I need to select the merged cell columns and column D (i.e. A:C & D on day 1) and paste it into E:H with E1:G1 merged.
a macro to convert this;
a
b
c
d
[Code]...
Into this;
x
a
9
x
b
[Code]...
So far I have the following, but this is not quite right!
[QUOTE][Sub ConvertRange()
Dim targetRowNumber As Long
targetRowNumber = Selection.Rows(Selection.Rows.Count).Row + 2
Dim col1 As Variant
[Code]...
/QUOTE]#
How would you set up a macro to delete numbers in a spreadsheet in one column that appear in another column or worksheet?
In other words, if I have the numbers 1,2,3,4,5,6,7,8,9, 10 in one column and in the same column, or another column or worksheet I have the numbers 1,2,3,4,5 - I need a way to delete 1,2,3,4,5. I know how to delete duplicates via the advanced filter but I want to have the duplicate and original deleted.
I am trying to find a macro that look for last non empty cell in column A and them paste a formula/comment in all cells of column B.
View 3 Replies View RelatedI have been working with a few people on here to setup a macro to copy text from a column of cells to another column and then print this in to a text document but it seams to have got stuck in a loop ....
View 9 Replies View RelatedI am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1.
A B C D E
1 X X X X
2 X X X X
3 X X X X X
4 X X X X
5 X X X X
This is for a template for teachers to analyze student testing data. On sheet4, wrong answers in each column are noted by a lack of a + in the corresponding cell. I want to paste the names of the students who missed each question into sheet 5.
I've done it by repeating a filter macro, but I manually copied the following separately for the 75 columns in the template.
Problem #1 - there must be a more efficient code, something that automatically loops to the next column
Problem #2 - the template has 75 columns, but many tests have fewer questions. I'm trying to find a way to stop the loop whenever it hits a blank cell in Row 10 on sheet 4. I've done it with an if/then in the last section on the above code, but where I'm at now, i would have to add that to the code section for each column. Which isn't that big a deal, but I figure there must be a better way.
The relevant portions of the workbook are attached here.
repeating macro until hits blank cell sample.xlsm