I am currently making a spreadsheet that will calculate the costs of products that my company makes. I have created the code that will add a line for each component of the product and fill in the details, but i want to set it so that it run's when i change the part number field in the sheet.
The only way i know to do this is using the worksheet_change event, but i will have more than one of these 'calculators' on one worksheet and the part number field will not always be the same cell.
I thought that an IF statement would work, as the cell next to the part number will always be the title "Parent". This is the code that i have tried but i keep getting run time error 1004 application-defined or object-defined error when the IF statement would return TRUE, when the IF statement returns FALSE (the cell to the left is NOT "Parent") the code exits, which is correct.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Offset(0, -1).Value = "Parent" Then
Code.....
End If
I would like to have a vba code that changes the cell color so that if there is number 1 (just number 1) in some cell the background of the cell changes in to shade of grey and cell that has number 2 changes into darker shade of grey and so on. I have numbers 1-10 in random order in my sheet. I mean there is many cells that has the same number and the sheet is quite large so vba code would be ideal choise to do it quickly.
I'm trying to write a macro that will watch to see if there is change in a range of cells, and if the values of the cells change to preform a given action. I need to do it this way because I can't put a formula in some cells.
2 Questions here...(Entire macro is at the bottom)
QUESTION 1 - In line 4 of the code below, in Column B, how can I get this code to auto-fill ONLY equal to the bottom-most row with any data in column A? Note that it's never exactly "B254"
QUESTION 2 - How can I change these 3 lines of code...
I have cells in range L12:BN1000 with formulas that will output a 1 or a 2.
If the output is 1, I would like the cell to color yellow if the output is 2, I would like the cell to color orange
the reason I dont want to use conditional formatting is because the use needs to be able to copy and insert rows and by doing so the use would need to manually add the new cells into the conditional format range. I would like to come up with a macro that applies this condition to a large range.
also, were do I add this macro? under sheet1? this workbook? or as a module?
I have looked at a series of Change Event topics and code but can't see what I need. I simply want a macro to run automatically when a cell ....which contains the Maximum time from a range.... changes. I assume I use .... Private Sub Worksheet_Change(ByVal Target As Excel.Range) ...but I have no idea what code to use...
I have the code below set to change the background color based on certain conditions. What I need to do is change it so that if none of the conditions are met, it does not change the color at all. As it is written now, if anything is entered in a cell that has a background already set, but does not meet these conditions, the cell turns white.
I need to be able to hide and unhide a given set of rows based on the value in a particular cell. My current code allows me to successfully do this, ONLY, when I select the cell being 'watched' for changes and press enter. It does not execute the code automatically.
The cell is changed by a set of filters that modifies the data. The watched cell is then a summation of the filter modified cells. If the filters change to all "0" or "-" then my 'watched' cell sum becomes "0" and thus should execute the code automatically.
Current Code:
Code: Private Sub Worksheet_Change(ByVal Target As Range) ActiveSheet.Outline.ShowLevels RowLevels:=3 If Range("I62").Value = "0" Then Rows("63:87").Hidden = True End If End Sub
So, right now if I use the filter and the cells change then the sum in I62 becomes "0", I have to manually select cell I62 and press enter and which point the above code executes exactly as I intend.
I need the execution part to be automatic and not have to manual click into cell I62.
When ever I update my external CSV file and hit refresh all data, I get #REF! Errors. The CSV file has a table that changes daily. Thus the "A" Column has a different number of rows depending on the day. The problem is that Monday the CSV file will contain 700 Rows, while on Friday it will have 200 Rows. I end up with a lot of #REF! Errors friday. How do I write the code so that (A561 for example) does not change regardless if it can reference the target cell or not? I even thought of making a macro that just copied the formula into all of the cells after each refresh, but there must be a better way.
Example code =IF(A561>0,IF(Start!$H$2="Monday",'calculations-mon-sat'!O561,calculation!O561),"")
working this formula out for use in conditional formatting;
Make E9 show E9+H12 if H9>0
I've got the following formula to conditionally format E9 to red fill when H9>0, but it won't change the contents of E9 to E9+H12 under the same condition;
I'm currently trying to compare all of my companies data sources for staff, ie helpdesk, telephony, ldap etc. and I have made quite a bit of progress, mostly thanks to this forum, however one issue I am running into, is the fact that when I use the text to columns funtion (in a macro) it's splitting up names which are double barrelled into 3 columns. The names are in the format of...
The loop is putting the correct formula into the cell - but THEN... - I wish to (within the loop and before it moves on) check if the value is equal to OR greater than 1 - if it is, make it a value of 1 (this '1' is then used at the bottom of the column of data to give a total). I need to do this before it moves onto the next cell. What am I doing wrong? Is it because once it puts formula in the cell, it then moves down - do I need to select the cell again first? As I had originally posted on this site - link: url]http://www.excelforum.com/showthread.php?t=641970[/url]
I have a peice of code that i know is inefficient and it is in danger of becoming too large. I have a spreadsheet that has circles aligned to each cell. There are around 100 in total. The code changes the colour of the shape based on the cell value in which it sits. However, the code needs changing and also it does not automatically update the colour shape even though the cell value changes. I have to manually select a cell and then the formula bar and then press return for it to update.
I am using excel 2010.
This is the code i am using for each shape.
Code: If Range("n12").Value = text Then ActiveSheet.Shapes("Oval 250").Fill.ForeColor.RGB = RGB(255, 255, 255) End If
I am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)
The strings I will be looking for vary depending on data entered so I will need to cell reference them
The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)
I need some strings red, some green, and some blue.
These changes should also apply to the whole workbook not just one sheet.
I have problem to change text code into numerical code using macro. i have data contain text code and i would like to convert it into numeric . each text code has dedicated numerical code for example I have 4 fluids with text code text code:
FW = Fresh water SW = Saline eater CW = connate water MW = Meteoric water
numeric code as follow
FW=1 SW=2 CW=3 MW=4
I would like to convert the text code into numeric code, it is easy if using excell , but it is routine job for me , i need to create macro to be more simple .
I have recorded the code below that does what I need, problem is I will use the code on files with various amounts of rows. What can I change in it so it will work on all different size files.
Is there any way, in which i can make my code dynamic?? For example, in a code written in VBA, there is a reference to particular sheet, say "ABC". If i change the name from "ABC" to "ABCD" from excel, the underlying code should change automatically from "ABC" to "ABCD".
Trying to create a VBA code that will enter a formula such as: SUM(Q2:Qt) in a range of cells for instance A2:A10, and if there is information in B11, B12, B13 etc., it will consider that variable change and enter the SUM formula in cells A11, A12, A13.
I read that Dim LR As Long is the solution but not sure how to apply.
This code is coulnm specific, can this code be adapted to look through a range for a match i write this code up to 25 time changing the column it looks at each time, the row always stays the same the location it paste to also always stays the same if it could be made to look through a range in reducing the amount of code to write.
the range it needs to look through would be, row 6 from column B to column Z.
the line i am hoping to adjust for this is below it reads: "WST.Cells(6, 2)Then"
HTML Code: If WS1.Cells(8, 2) = WST.Cells(6, 2) Then WST.Cells(7, 2).Resize(30, 1).Copy Worksheets("torr1").Cells(9, 5).PasteSpecial Paste:=xlPasteValues nextrow = nextrow + 1 End If
Is there a way that I can add a statement or change my code to automatically have the checkbox checked if a certain value in a cell is greater than zero?
This is my If CheckBox1.Value = True Then Range("RANGE_WATER_AND_SEWER").PrintOut Copies:=1
If CheckBox2.Value = True Then Range("RANGE_ELECTRICAL_SERVICE").PrintOut Copies:=1
I have part of the code below and need to modify it so that others who only have C: instead of D: to use it
Filename = "D:TempBudgetMonthly.xls"
FileExists = (Dir(Filename) "") If FileExists = False Then msg = "The data file Monthly.xls does not exist !! Load the required file and run the report again !"
If MsgBox(msg, vbExclamation + vbOKCancel) = vbOK Then End If Exit Sub End If
I need code to replace all D: to C: for the entire PROJECT not MODULE.
I know that there has got to be a post on here somewhere on this, but anytime I enter in "Import VBA Code" I get back loads of stuff that really isn't relevant.
In post [url] I had to change a line of code so that the formatting macros I made would be accessable to everyone.
Now that I have the fix in place I must import the fix in the one line of code into 100+ workbooks. I suppose I could do this manually, but I imagine that there is a much faster way to do this using VBA.
I was flipping through the "VBA and Macros for Excel" book by Mr. Excel and on page 334-335 I came across something that looked like it might work. However, it seems that this code is switching modules and my code is pasted in "ThisWorkbook".
All of the files are located in the same directory, so it would involve opening all the files in the specified directory, making the VBA code change, saving the file and closing.
I have a workbook with several sheets that get info from sheet"Main BOM". I use a =Main BOM(a6) type formula on the top row of other sheets where info is needed and I drag that down to the end of info on the Main BOM to get the info into other shhets of the workbook. How would I alter this code to find the last row of data on the Main BOM tab and only drag down that far? Or would I need a completly user written macro? This is a recorded macro.
Sub DragDown() ' ' DragDown Macro ' Macro recorded 2/25/2009 by David D ' Sheets("David,Jimmy--To Proto").Select Range("A16:E16").Select Selection.AutoFill Destination:=Range("A16:E47"), Type:=xlFillDefault Range("A16:E47").Select End Sub
I am trying to do is look at the first two digits of the number in column c (Range c5:c5000) if the number starts with 01 then I need the formula in column g = f5*12 if the number is 03 then I want the formula to be g=f5*24 I have several of these "cases" or "if" to put in can someone get me going a little further
Here is what someone gave me to work with but I'm having troubles getting it to work...I get a name error in the cell and I cant use this they way it is because the code needs to go into the sheet mod with a bunch of other code.Also when I use the Option Explicit the rest of my code wont work.
Option Explicit Function re(Cl As Range, Src As Range) Dim i As Long Select Case Left(Cl, 2) Case "01": i = 12 Case "03": i = 24 End Select re = Src * i End Function =re(C5,F5)
Can Attached code be changed to alter font colour,type. rather than just fill colour.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer If Not Intersect(Target, Range("e:Az")) Is Nothing Then Select Case Target Case "Ph" icolor = 6 Case "Li" icolor = 12 Case "O" icolor = 7 Case "An" icolor = 53
This piece of code successfully replaces a module in a range of workbooks -
Sub CopyOneModule01() Const strModName As String = "Module1" Dim FName As String Dim vWbkNames As Variant Dim lLoop As Long vWbkNames = Array("CFADS01", "CFADS02", "CFADS03", "CFADS04") With Workbooks("CF266") 'copies from here FName = .Path & "code.txt" .VBProject.VBComponents(strModName).Export FName End With...........................
i have some existing code which is trigerred when anything is input into column c. The code then adds various information in another three columns. One of which pastes a vlookup formulae, and i would like this forumlae pasted into the column c cell which i initialy edited, in order to remove the requirement for one additional column.
The existing code i have is:
Code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim MyText As String MyText = Environ("username") If Target.Cells.Column = 3 Then With Target If .Value "" Then .Offset(0, 2).Formula = "=VLOOKUP(D:D,'P:TAOffshoreTAOffshoreTreasuryRecsGeneralCommit ID''s for control Sheet - Do not move or delete[commit ids - DO NOT DELETE OR MOVE.xls]Sheet1'!$A$1:$B$65536,2,0)"
[code].....
I have tried changing the offset to (0,0) or changing the offset to 'target = ', which does add in the vlookup but then the macro debugs at the 'If .Value "" Then' code?
