Modify VBA Code Written By Tom Urtis
I am using the following code, slightly modified, written by Tom Urtis.
I would like to enhance the code such that it will filter on two criteria to enable more specific results to obtained e.g. from the possibilities below I would like to get "Jones Manchester".
Data list:-
Jones Blackburn
Jones Liverpool
Jones York
Jones Manchester
Jones London EC1
Jones London N12
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address "$D$1" Or .Cells.Count > 1 Then Exit Sub
ActiveSheet.AutoFilterMode = False
If Len(.Text) > 0 Then
Dim myVal$, myVal2$
myVal = .Value
myVal = "*" & myVal & "*"
If WorksheetFunction.CountIf(Columns(1), myVal) > 0 Then
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Modify The Code
I have a code here, which thanks to Jindon helps on yesterday, But now, I want to change the code to count by column instead of rows, to continuous copy & paste my date, How can it to modify: Sub test() Dim home As Worksheet Dim Filename As String, myDir As String, fn As String Set home = ThisWorkbook.ActiveSheet With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = False If .Show = -1 Then Filename = .SelectedItems(1) myDir = Left$(Filename, InStrRev(Filename, "")) fn = Mid$(Filename, InStrRev(Filename, "") + 1) With home.Cells(Rows.Count, "E").End(xlUp)(2).Resize(2) .Formula = "='" & myDir & "[" & fn & "]MAN_SUM'!k6" .Value = .Value End With End If End With End Sub
View Replies!
View Related
Modify (PDF Code )
Find below a vba code that i attached to a button that converts my print area in excel to PDF via Cute PDF writer. Sub PDF_Sheet() Dim Filename As String With ActiveSheet Filename = .Range("A1") .PrintOut Copies:=1, ActivePrinter:= _ "CutePDF Writer on CPW2:", Collate:=True SendKeys Filename & "{ENTER}", False End With End Sub I need a VBA code for the Excel sheet that i am cuttently working in that will do the following: i have text and pictures in range O86:W97 that must be automaticaly romoved & replaced by another range after the above mentioned code hase complete the range that must be replaced with the following, O101:w112 Pdf code must run then O113:w124 pdf code must run then O125:w136 pdf code must run then O137:w148 pdf code must run then O149:w160 pdf code must run then note that the ranges run in a sequence 101to112 then 113to124 then 125to136 and so on Now this needs to be repeated 190 time starting from range O101:w112. The range O86:w97 forms part of my print area of A1:W97, this means i will have 190 PDF saved pdf sheets when i the vba code is complete.
View Replies!
View Related
Modify Code To Create Worksheets
I have this code that will create 3 worksheets with the name "New Name" and the # ( 1/2/3 ) .. How would I change the code if I want 3 worksheets with different name? Like Sheet1 then Sheet 2 then Sheet3 or any other name? 'To add worksheets and change name with one code For i = 1 To 3 'Creates 3 worksheets Set ws = Worksheets.Add ws.Name = "New Name" & i Next i
View Replies!
View Related
Modify Code To Copy Only Value Not Formulas
Thanks everyone that has helped me with this so far. I am very new to VB so I definitely appreciate all the help. I have the following code to select rows that meet 2 different criteria and copy them to a new sheet. The problem is I only want to copy the values not all the formulas - the formulas don't work in the new location as they are referencing cells that are no longer there.
View Replies!
View Related
Modify Code To Always Centre The Picture
I got this code from someone who is not currently available, I modified it a little so that when I pick a picture it always lands on j96, but I need it to line up exactly on top of the picture underneath it I have also attached a sample. The pictures will always be exactly the same size
View Replies!
View Related
Trying To Modify Code To Track Changes In Two Workbooks
I was trying to see if this code would work for my application where I want to compare two forms. Basically the master is filled out and then sent to a vendor. When it comes back from them rather than going through then entire thing and trying to determine if they changed or added anything manually I was hoping to use something like this code to do it.. I tried this code using a similar setup, basically a master file and an update file but it wouldn't actually populate the changes in the changes sheet on the master form..
View Replies!
View Related
Can You Modify This Code To Search Only One Colomn?
Private Sub CommandButton1_Click() Dim strFirstAddress As String With Sheet1.UsedRange Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues) If Not rngFind Is Nothing Then strFirstAddress = rngFind.Address Do rngFind.EntireRow.Copy sheet3. Range("a" & sheet3.Rows.Count).End(xlUp).Offset(1, 0) Set rngFind = .FindNext(rngFind) Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress End If End With Unload UserForm2 UserForm4.Show End Sub The problem is it searches the whole sheet, what I want it to do is only search one colomn (colomn A). I know its prob an esay thing to change but I can't for the life of me do it!
View Replies!
View Related
Code To Create/modify/save Templates..
I have quite a few Excel templates that need to be modified by changing/adding information or sheets to them. Instead of working through the lot manually, it would be good if I could do the same through code. With my little knowledge I can do this for workbooks. Need to typical code snippets or suggestions? e.g. to open a template file, save it with the original name, etc. The usual Dim srcWB as workbook, Set srcWB= ABC.xls and Workbooks(ABC.xls). open do not seem to work with template files (ABC.xlt)
View Replies!
View Related
Modify This Code To Add-up Numbers In COL-D
It's a linked post from: [url] I managed to do the combine the row if column B matches. However column D(quantity, number value), i want the quantity to add-up if column B matches. Any idea how do I modify the code below to do that? for example: TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30 TDG-**002 Tuna Cheese Pizza Bar (MG) MG 30 TDG-**002 Tuna Cheese Pizza Bar (KG) KG 30 will combine to become TDG-**002 Tuna Cheese Pizza Bar (KG) KG 60
View Replies!
View Related
Modify Code To Have Popup Box For Input
I wanted to change a column text to call intial Caps. I found this macro code on the microsoft web site Sub Proper_Case() ' Loop to cycle through each cell in the specified range. For Each x In Range("C1:C5") ' There is not a Proper function in Visual Basic for Applications. ' So, you must use the worksheet function in the following form: x.Value = Application.Proper(x.Value) Next End Sub I need to change the macro to have a input box (asking what column to convert text to initial CAPS)
View Replies!
View Related
Protect Sheet But Allow Macro Code To Modify
I am trying to design a tool which dynamically results in different cells depending on users inputs. Yet, I am willing to protect most of the cells to avoid any user involuntarily (or voluntarily!) modifying the formulas/contents of the calculated (and locked) cells. Is there any way to protect my sheets, allowing only a certain macro to modify the locked and protected cells?
View Replies!
View Related
Modify Code To Change Cell Background Color
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.
View Replies!
View Related
Modify The Formula Entries In The Following Code To Cycle Or Loop Through All Checks Required
I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.
View Replies!
View Related
Modify ComandButton VBA In Multiple Sheets
I've been successfully using a for loop method to make minor changes to formulas or formatting in multiple sheets within the same workbook. I would like to update a standard button I have in each sheet. This button jumps the user to the 'dashboard' sheet. Now I want to add a functionality to the button which makes it input the refrence sheet name into a cell in the 'dashboard' sheet. Is there a way I can make a VBA macro to edit the code in multiple sheets? I'd love to not do this manually for 100 sheets! Example of my for loop:
View Replies!
View Related
Use IF To Avoid Getting Anything Written After
I'm havin' a chart for interest, instalments and so on; Interest [X%] Loan [Z crowns/dollars] Loantime [Y years] Instalment [formula, which is "=Z/Y/12"]for each month. Year, Month, Loan(left), Instalment, Total payment 1, Januari, =Z, =Instalment (constant), =Interest+Instalment Month, Loan(left), Interest Februari, =above-Instalment, =X%*Loan(left)/12 (rent is divided per year), Instalment, Total payment Constant, =rent+Instalment and so on... as long as you want ^^ (it's quite messy, but try :D) The question is as follows "What would a loan of 900'000kr with 9% interest rate totally cost over 30 years?" [use "IF" to avoid getting anything written after the loan expires] The question itself can be easily solved by just dragging the formulas down to 30 years and fill in the % and loan etc. But what I do not understand is the "IF" question. I'm supposed to use the IF function to instantly show the cost of the loan (total rent payment) after the 30 years? That's how I understood it. In that case... how do I do that? ^^
View Replies!
View Related
Written Number()
This is a a formula entered in a checkbook program for excel that I downloaded. But I want to use my current accounting spreadsheet to print checks. The problem is I don't know how to get excel to write out a number like $23.40 as: Twenty Three & 40/100 Does anyone know how this is done? Is it something simple that I am overlooking - i can't find anything like this in the number formatting section of excel.
View Replies!
View Related
Formula Written In Word
I wrote out a long IF statement in Word, but when I pasted in the formula bar, I got an error. I've determined that the quote marks are causing the error. Can anyone advice how to fix this going through and changing the quote marks one-by-one?
View Replies!
View Related
Calculate Written Numbers
Whilst I was searching through the channels on telly the other night I came across a 'Quiz Call' channel where you had to add ALL the number within a certain sum (see sample) Sixteen minus five times seven multiply by nine add twelve You not only have to do the sum you also have to add in all the less obvious numbers like the Six within Sixteen, plus also all the Roman numbers in each word like the I, X, & IX within the sixteen. I've done a small example to show what I mean, but I don't have any idea how to create a formula that will add up all of all of the permitations of roman numerals within each word.
View Replies!
View Related
Creating Written Summary In 2002
I am creating a financial summary on a worksheet and I want to be able to write up a summary about each category. Is there a way that I could write text on my worksheet and create a parameter where my text could go to the next line such as a word document?
View Replies!
View Related
Comparing Spreadsheet To Written Data
Is there anything in Excel 2007, the spreadsheet and written data are tangled lists of numbers and its hard work sorting it out? So something like highlight current row and darken others and tick each correct row with one click would help a lot.
View Replies!
View Related
Macro Open The Worksheet In Which It Was Written
I've written a macro and i've added a button to the toolbar so that it's easy for users of the spreadsheet to run the macro. the problem i'm running into is that the sheet that the macro's written in is a template. the macro formats some downloaded junk. soooo, every time someone downloads stuff, they copy-paste into the template, push the button, and it formats it. my problem is that when they Save As, the button's assigned macro gets reset to the name of the file they saved as. therefore, when someone else comes along and press the button, it doesn't run the macro out of the original template sheet which they have open, instead it tries to find the last sheet that was saved and run the macro through it.
View Replies!
View Related
Formula's Value Written To ClipBoard
I have a formula that will give me a phrase that I need to be able to paste into other applications - the value and not the "mid"s and the "search"s, etc. So, can anyone tell me how to take that formula's value and write it to the clipboard?
View Replies!
View Related
Reminder Statements Written In The Message Box
creating a MSG box to be placed at the start of the code, i need the following reminder statements written in the message box. If the answers to all statements is Yes then proceed running the code. 1. Is the value date in Sheet Summary Correct? 2. Is the workbook free of errors, check sheet "Errors" 3. Are all the graphs up to date? Sub Save_File() Application.ScreenUpdating = False Sheets(Array("SUMMARY")).Copy With ActiveWorkbook .Sheets("SUMMARY").Cells.Copy .Sheets("SUMMARY").Cells(1).PasteSpecial xlPasteValues .SaveAs Filename:="S:Recsdata ReportingRec StatsSummary Stats" & Format(Date, "yyyy\MMM") & _ "Stat Summary_" & Format(Date, "dd-mm-yy") & ".xls" .Close False End With Application.ScreenUpdating = True End Sub
View Replies!
View Related
VBA Macro Code Needed To Run The Code Contained Within A Cell
I have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains. Cell B5, for example, would contain the text: Range("B13").Formula = "SUM(D12:D14)" I need a macro to "execute this code", as if it were in the macro itself. I have assigned the above to a variable, but am not sure how to execute it. EG. Dim the_calc the_calc = Range("B5").value Now, how do I run the_calc ?
View Replies!
View Related
VB String Written Cross Refers Tab2 On A Spreadsheet
I had the following VB string written for me recently, that cross refers Tab2 on a spreadsheet to tab1 with any duplicated fields in Tab1 being moved to Tab3. Column B is the column I am comparing over the 2 sheets... Sub DIVIDEND() Dim Master As Worksheet 'Tab 1 Dim RefTab As Worksheet 'Tab 2 Dim NewTab As Worksheet 'Tab 3 Dim Cell As Range Dim iRow As Long Dim jRow As Long Dim ff As String The addition I would like to add is after this first action has taken place, I need all all of the data that is in Tab2 and not Tab1 moved to Tab 3. To summarise, I need any info that appears in Tabs1 & 2 moved to Tab 3 and any remaining lines that are on Tab 2 and NOT Tab1 moved from Tab2 to Tab3.
View Replies!
View Related
Efficient VBA Code Vs Macro Recorder Code
What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code. 1) Macro Recorder Code to Copy One Sheet to Another Sheets("Data Apple").Select Cells.Select Selection.Copy Sheets("Banana").Select Cells.Select ActiveSheet.Paste 2) Macro Recorder Code for Replacement Purposes......................
View Replies!
View Related
Display All VBA Code In Code Window (VBE)
When I started using VBA, all the code for each module was shown in the code window. This was a bit cumbersome when looking for one sub in particular. Now when I load VBA, all the Subs and Functions have their own page, and can be selected from the right hand drop down list. This is much easier for entering code, and finding subs. However, it is sometimes useful to see 'the big picture', so how do I swap between the two types of display?
View Replies!
View Related
Multiple IF Statements Check If Words Are Written In Certian Boxes
I am trying to write a formula that will do multiple IF statements checking to see if words are written in certian boxes and if so to do certian things and I need help, never done multiple IF statements in one formula before and can't find anything in the books I have or on the web. = IF(D30 = "Oceanic", IF(D35 = "Yes", Both True (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + (D31 + 4500)), Oceanic = "Oceanic", D35 = "No" (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) Oceanic = False, D35 = "Yes" ((d32 * 4) + (d33 * 5) + (d34 * 9) + (4500 + d31)) Both False ((d32 * 4) + (d33 * 5) + (d34 * 9)) ======================================================= I got: = IF(D30 = "Oceanic", IF(D35 = "Yes", (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + (D31 + 4500)), (((D32 * 4) + (D33 * 5) + (D34 * 9) + D31) * 0.15) + ((D32 * 4) + (D33 * 5) + (D34 * 9) + D31), ((d32 * 4) + (d33 * 5) + (d34 * 9) + (4500 + d31)), ((d32 * 4) + (d33 * 5) + (d34 * 9))
View Replies!
View Related
VBA Code To INSERT CODE - GURUS
I have a worksheet named mylist, that I delete, then recreate everytime I run a specific macro. However, I have code in this worksheet that I require, so is there a way for me to insert code to "insert code" on the "re-creation" of the worksheet?
View Replies!
View Related
VBA Code- How Do You Get The Code Boxes To Appear In Threads
I have code that inserts columns, inserts formulas, and then copies the formulas to the last row of data. It all works good but for some reason, the columns that are being copied, are being copied past the last row of data. It can always be determined how far down it will be copied. Examples: if the last row of data was row 4, then the formulas would be copied down to row 24, if the last row of data was row 54, then the formulas would be copied down to 254, if the last row of data was row 284, then the formulas would be copied down to 2284. I can not figure out how and wh this is happening, but whatever the last row of actual data is on the spreadsheet, there is a 2 being placed in front of the last row of data and the formulas are being copied down to whatever the last row is with the addition of a 2 in front. Here is the code for the columns additions, and formulas: Columns("O:S").Insert Shift:=xlToRight Columns("W:Z").Insert Shift:=xlToRight Columns("AB:AF").Insert Shift:=xlToRight Columns("AH:Al").Insert Shift:=xlToRight Range("O2").FormulaR1C1 = "=CONCATENATE(RC[1],""/"",RC[2],""/"",RC[3])" Range("P2").FormulaR1C1 = "=IF((RC[3]=7),LEFT(RC[-2],1),LEFT(RC[-2],2))" Range("Q2").FormulaR1C1 = "=IF((RC[2]=7),MID(RC[-3],2,2),MID(RC[-3],3,2))" Range("R2").FormulaR1C1 = "=RIGHT(RC[-4],4)" Lets take this first one for example: Range("O2:S2").Copy Range("O2:S2", Range("O2:S2" & Range("A" & Rows.Count).End(xlUp).Row)) If the last row of data was row 54, then columns O:S would have the formulas copied down to row 254. How can this code be modified so the formulas will not be copied past the last row of data? How do you get the code boxes to appear in these threads. I do not know how to do this. As you can see, all I did was copy and paste my code in this thread.
View Replies!
View Related
Create Macro Code With VBA Code
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!) The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code... The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work). The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments. 'this code calls the method which creates the buttons in specific cells with specific names Private Sub CommandButton3_Click() 'the first call always works! AddCmdbuttonWithCode "Edit_111_111_114", 23 'the second one crashes excel AddCmdbuttonWithCode "Edit_111_111_115", 27 End Sub
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
Distributing VBA Code Changes Via VBA
I have a number of identical workbooks used for data capture within the organisation, and need to continue development as time goes on. Whenever I make a change to the core Excel functions of the workbooks I simply use a VBA procedure to conduct this change within each of the production workbooks, however I am stumped as to how I can achieve this for changes to the VBA code. I have made some changes to the code held within one of the modules, and have also modified the form slightly. What I want to be able to do is run a VBA procedure that either updates the production workbooks from a central source, or by importing previously exported code / forms.
View Replies!
View Related
Insert Vba Code With Vba
I have a code that needs to be placed on any new worksheets created. All new worksheets are made using VBA code already so they just need to be told to add the code into each new sheet. How do I do this?
View Replies!
View Related
Modify Multiple Spreadsheets At Once
i have admit forms for multiple patients, about 200 or so already done, and its set up to make an upload sheet, which we then add all of them to one big file to import to access. i am in the process of cleaning things up, but we need to add a formula to a cell to determine length of stay, and several similar things, but i'd like to not have to go and do it file by file. is there any way to update all the files at once? some kind of automation? then changes would be in the same cell for each file. or maybe some way to do a batch modify or something, so it can make the change, and run the two macros, one to make upload for file, one to dump it in the file that goes into access.
View Replies!
View Related
Modify A Lookup Function
i use the formula below in a wb. When it runs its searches col A for what ever is in H36. Is there a way to make it search col A & B for what ever is in H36? =IF(ISNA(VLOOKUP(H36,'PRICE-GAS'!$A$149:$N$6000,5,FALSE)),0,VLOOKUP(H36,'PRICE-GAS'!$A$149:$N$6000,5,FALSE))
View Replies!
View Related
Modify Last_Cell_Value Module
I use the code below to automatically update formulas with the most recent data added daily to my spreadsheets. Can I copy this module, edit it so that instead of finding the last_cell_value it finds the value one_week_ago, and apply it to the same spreadsheet that the Last_cell_value module is used?
View Replies!
View Related
Modify Series Formula
I'm constructing a dynamic chart and I'm having difficulty naming the series formula. I wish the Y values to pick up values in a different sheet and have entered the following formula : =Rnd4Projects.xls!,Timeline!ChtYLeics Workbook is called Rnd4Projects and the data is contained in a different sheet, called Timeline. However, I keep getting an error message to say there is a problem with the formula. how this formula needs to be entered in Series Values box?
View Replies!
View Related
Modify To Calculate Numbers Only
I'm using sum to calculate numbers can I modify it not to calculate numbers? like it'll return blank or something else. Because it calculates alphabets in a row and returns a 0. tSumthing = "=SUM(RC4:RC[-1])" then based on the 0's I got above, it'll delete the row. I got diffculty modifying the formula below. Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
View Replies!
View Related
|
TRACKER
