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.
Code:
Sub Macro3()
'
' Macro3 Macro
[Code]....
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
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.
Is there a way to do this with the VBA code.
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 a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).
I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.
Demo1.xlsm
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?
an event macro to change the font colour of a cell whose value changes as a result of a calculation.
View 9 Replies View RelatedIs 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".
I want to make my code dynamic..
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.
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
etc. Note: I have about 80 checkboxes on my form.
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 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 want to change value if i change products from drop down list.....
For more information please find attached file: Book.xlsx
I want to drag a formula however i want one of the values of the formula to change and the other not to change. for example:
c1=a1+b1
c2=a1+b2
c3= a1+b3
: : :
how can i make it do this when i drag? a1 is not just a constant and it depends on other parameters.
Attached is book in which, when a choice is selected from Drop Down list in ColumnF the macro has to do the need.
When the macro was written it was working well. But when I tried to change it as a Worksheet_SelectionChange event nothing is happenning even though a choice is selected from drop down list.
Basically the situation I have is Sheet2 has many references to cells in Sheet1. Sheet2 is for all intents and purposes a kind of nicely formatted report form, and Sheet1 is the input form.
My ultimate goal is to automatically resize row heights on Sheet2 when cell contents change on Sheet2.
Using a worksheet_change event isn't working I presume because it doesn't see the formula output change as a worksheet change, the worksheet_change is firing only when the input is changed in Sheet1.
how can I capture these formula output changes on Sheet2 (triggered from input on Sheet1) OR is there a way of making a particular sheets rows always adjust in height to best fit?
I'm coding a userform where there are some comboxes which are popolated by values coming from Sheet2. Up to now I work it out (maybe its not elegant but it works).
Now I would like that when the user selects one combobox the values of the other comboboxes are set accordingly to the grid in Sheet2.
Please download the XLS file at: [URL]....
Im trying to use an event change to change the sheet name based on a cell value, but my issue is how can I error trap if the sheet name is a duplicate? Here is what I have so far
Sub ChangeName()
On Error GoTo errhandler
Sheets(1).Name = Sheets(1).range("d10")
Exit Sub
errhandler:
MsgBox "sheet name is already exists"
End Sub
I have used the below code to insert a new row when the value in coulmn A change. I now need to evolve it so that the new row will contain a specific value depending on the changing value:
Before:
Column A Column B
one test
one test
two test
two test
three test
three test
After:
Column A Column B
one test
one test
Coz two............................
How would I add this formula as a worksheet function with VBA. I can't see INT, MOD or Year in VBA. Also want to change C2 to change to activecell column + row 2.
=INT(((C2-1461)- SUM(MOD( DATE(YEAR(C2-MOD(C2,7)+3),1,2)-1461,{1E+99,7})*{1,-1})+5)/7)
I have an Excel workbook which operates as a template. When it is opened, it runs an OnOpen routine. When it is saved under a different name, is it possible to ignore the OnOpen routine if that file is re-opened? I suspect it could be done by testing the name of the file, but I can't figure out the code.
View 2 Replies View RelatedI have just noticed that there is no defult event for the running code after updating a label caption. I would like to have like a textbox does (Change,AfterUpdate). to run code on the label caption change. could it be done with activex?? can it be done at all.??
View 2 Replies View RelatedI have found the code below that is very good and changes the case size to Upper, Proper or Lower. The problem is that when I select a cell, range of cells, column or row it changes the entire sheet instead of the selected range.
Code:
Sub CaseSize()
Dim myCase, rng As Range, r As Range
myCase = Application.InputBox("Enter" & vbLf & "1 for Upper Case" & vbLf & _
"2 for Lower Case" & vbLf & "3 for Proper Case", Type:=1)
If (myCase = False) + (Not myCase Like "[1-3]") Then Exit Sub
On Error Resume Next
[code]....
I have a macro that runs through a list and stores elements to an array. If the element is not found in my list, it adds to the next available row and adds the value from the array to that cell. Right now, I am searching in column d but am storing the value of column a at that location. When I switch the reference to column D, the value is not inserted into my new list.
Here is my current code:
Code:
Sub Arraytest()
Dim arr As Variant, lastrow As Long, i As Long, f As Long, l As Long, lastrow2 As Long, insertrow As Long
[Code].....
Why does the code not work properly when I switch to D? The value is storing correctly in the array. I test this by the last line of the code and it executes properly.
Please check this sample file. Say for example; I am sending this file via mail to different agencies. See in this file sheet named base & column title Agency , filters concerned agency wise and send via outlook to the concerned agencies mail id from mailinfo sheet. What my concern is since these are different agencies, I wanted to bring the agency name in the mail subject.
Link below:- [URL] ............