About Experts Sitemap - Group 46 - Page 28
Excel: Transpose multiple rows data into columns, vba macro, catalog price
vba macro, catalog price, trunc: Assume Product is in A1, Catalog is in B1 and Price is in C1 Assume A is in A2, 25 is in B2, and 2 is in C2 and so forth. Make a copy of the worksheet. Delete row 1 in D1 put in this formula =OFFSET($A$1,TRUNC((ROW()-1)/3),MOD(ROW()-1,3))...
Excel: Transpose, dummy data, asrar
dummy data, asrar, address data: Saem Asrar I can automate if for you, but you need to send me one workbook/ sheet with the data as an example and one workbook/sheet with all the fields in one row (from your video). Also, the email address appears before the other address data (is that...
Excel: Transpose, great architect, blank row
great architect, blank row, column format: I could show you some formulas to do it on another sheet in the same workbook. (this won t do the formats, but I would guess that isn t a consideration) Send me a workbook with about 30 rows of data and I can put the formulas in the workbook for you. Then...
Excel: Transposing Data
It s not totally clear from the example given how the data is laid out - is it possible to see a sample file (my email is aidan.heritage@virgin.net) - I am thinking that it is either going to be a pivot table, or possibly VBA but cannot be certain from the...
Excel: Transposing Non-contiguous Cells, cell a1, ctrl key
cell a1, ctrl key, thanks in advance: Jeff: Follow these steps and this should work for you - if not, please let me know because it means I misinterpreted your question :). 1) Holding down the CTRL key on your keyboard select cell A1, C1, E1, G1, and I1 on worksheet 1. This allows you to...
Excel: Transposing a list with a blank line dividing contacts, aero club, club de campo
aero club, club de campo, autopista: If - A1 - AAG - Club Empleados A2 - Av. Corrientes 538 piso 11 A3- (1043) Capital Federal A4 - 4325-1113 A5 - 4325-8660 A6 - handicap@aag.org.ar A7 - A8 - ABRIL CLUB DE CAMPO A9 - autopista BS AS- La Plata km 33,5 A10- (1884) BERAZATEGUI...
Excel: Transposing a list with a blank line dividing contacts, aero club, club de campo
aero club, club de campo, autopista: try out the following macro- (Two consecutive blank lnes will terminate the macro) Sub transpos() i = 1 For j = 1 To 65536 If Not Cells(j, 1) = Then Sheets( Sheet2 ).Cells(i, 1) = Sheets( Sheet1 ).Cells(j + 1, 1) Sheets( Sheet2...
Excel: Transposing address lists to excel, address city state, blank row
address city state, blank row, rob thomas: Rob Thomas, So every address is 4 rows followed by one blank row. in B1 put in =Indirect( A &(row()-1)*5+1 in C1 put in =INDIRECT( A &(ROW()-1)*5+2) in D1 put in =LEFT(INDIRECT( A &(ROW()-1)*5+3),FIND( , ,INDIRECT( A &(ROW()-1)*5+3))-1)...
Excel: Transposing address lists to excel, address city state, blank row
address city state, blank row, column c: Rob, It looks like when I pasted in the formula for column B from my worksheet where I tested it (successfully), the last parenthesis was left off try changing =Indirect( A &(row()-1)*5+1 to =Indirect( A &(row()-1)*5+1) the formula in...
Excel: Transposing data in Excel, sheet1, time control
sheet1, time control, f7: Hi Learner_VB (or OnthewaytobeExpert_VB? How are you? Please do the following (I assume that your data is in A1:F7) 1.Select Sheet2 2. Select A1:G6) Type on the left-upper hand cell of this range =Transpose(Sheet1!A1:F7) Press...
Excel: Transposing, excel, transpose
excel, transpose: I am assuming that the original data is on sheet1, it is ordered by country and year, it has just two columns, and it has headers, so the values starts at A2. With that, on the second sheet, once you have the names of the countries on column A, you can use...
Excel: Transposing, voice of experience, population data
voice of experience, population data, macros: Try transposing each column of data separately. Concerning a possible macro soution then sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected...
Excel: Transposition Macro, suny albany, code column
suny albany, code column, female population: I SUSPECT that you would PROBABLY be able to do this with formulas (actually, I m certain you can - the offset function would do it) but the macro would basically need to follow this route dim looper dim counter dim FinalRow dim SourceSht set the source...
Excel: Troublesome multiple IF function., index text, sunday monday
index text, sunday monday, monday tuesday: You re getting #NAME? because if A8 were Monday, for example, your formula would return the name Tuesday, not the text Tuesday . You could change it to be =IF(A8= Monday , Tuesday ,IF(A8= Tuesday , Wednesday ,IF(A8= Wednesday , Thursday ,IF(A8= Thursday...
Excel: Trying to figure out total, profit company, aidan
profit company, aidan, poverty: Yes, but I m struggling with the 100 dollars bit? I would probably modify the table slightly so that it only had the up to value, but thats minor (and can probably be worked around) ~Can you clarify the rules for the 100 dollars - and if it helps to email...
Excel: Trying to filter for 15 minute data, minute interval, detailed explanations
minute interval, detailed explanations, dummy data: Sarah, I have more questions than answers. I m SURE there is an easier way than doing it manually! So if you get data every 5 minutes in the ideal case, what do you do with each set of data? Do you take the average over the 15-minute interval?...
Excel: Trying to filter for 15 minute data, minute interval, detailed explanations
minute interval, detailed explanations, dummy data: Sarah, There is a solution to this but it is going to be very complicated to get it worked out. I have a great solution to that issue: see the last several paragraphs of this answer. Without actually seeing this data, I can only give you a general...
Excel: Trying to get a head count.., jane doe 1, pivot table
jane doe 1, pivot table, headcount: I m sure I m missing something here, but wouldn t the pivot table be the way to go - this would group on names and bill dates, so you would automatically get the unique counts you need? If I ve misunderstood, please let me know - and if it helps to email...
Excel: Trying to get a head count.., jane doe 1, pivot table
jane doe 1, pivot table, headcount: You may possibly be thinking along the lines of the pivot table you want to use for your final output, rather than a pivot table to combine the values - you may well need two files, but a pivot table would by definition group on whatever you want - so putting...
Excel: Turning a lengthy one-column sheet into a format suitable for a database, consecutive rows, access database
consecutive rows, access database, brief description: Candidly and politely, if you stop to think about this for a minute I think you will realize that ANY useful advice I would give to get you started or to proceed would require a tremendous amount of typing. Simply saying just create an ACCESS db is something...
Excel: Turning a lengthy one-column sheet into a format suitable for a database, consecutive rows, consecutive lines
consecutive rows, consecutive lines, access database: You will need to write a macro to do that and sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short,...
Excel: Turning letters into numbers, vlookup, seperate sheet
vlookup, seperate sheet, equivalant: Veer You can create a seperate table for the numerical equivalant and then use a vlookup to access the corresponding numbers ie in a seperate sheet you would have A in one cell and then next would be 10 then using a vlookup on the main sheet when there...
Excel: Turning number into grade, conditional test, countif
conditional test, countif, breakpoints: Jeremy, What kind of grade? A letter grade, or a percent? A percent, of course, is easy. Just format the result as a percent. To create a letter grade, you need a nested IF statement with each conditional test examining the breakpoints,...
Excel: Tweak to macro that sends attachment, dim objmail, outlook application
dim objmail, outlook application, variable string: Don t remember writing this one but regardless I don t follow fully. Is the list in another wb? If so, are there formulas in the current wb that contains the code that references those cell so that the current wb shows the names? If the names are just...
Excel: Two Question, vba, aidan
vba, aidan, thanks in advance: I will certainly do my best to try to help you, but I don t follow the questions I m afraid - it doesn t help that this site is text only - if it helps to email me with sample files you can get me at aidan.heritage@virgin.net - I m fairly hopeful I will be...
Excel: Two different conditional formats, conditional formatting, checkbook register
conditional formatting, checkbook register, cell shading: Hi Juergen How are you? What about joining the 2 conditions into one =And(Iserror(B:B)=True, Mod(Row(),2)=1 ) and Format the cells on this composed condition? When there is no error just use the condition =And(Iserror(B:B)=false, Mod(Row(),2)=0...
Excel: Two formulae problems, info sum, negative numbers
info sum, negative numbers, cell address: Frank: Sorry for the late answers, I was sick the last few days. send me the spreadsheet and I ll put the answers on it for you. From what I gather from your current info: =sum(a1:d1) where a1 is the beginning address and D1 is the end address of the...
Excel: Two formulaes in one cell?, dear frank, hotmail
dear frank, hotmail, two pieces: I believe I understand. You want to evaluate the expression (BB20-BC20-BF20), and if the result is greater than 0, show PAID , otherwise show UNPAID . Your formula would therefore be this: =IF((BB20-BC20-BF20) 0, PAID , UNPAID ) Again, please...
Excel: Two formulaes in one cell?, two pieces, bf
two pieces, bf, hotmail: Hi Sarfraz, I think I see more or less what you are trying to do. Let s assume that you have the following values in your cells: BB20: 250 BC20: 25 BF20: 30 It looks like you want to put the result of BB20-BC20-BF20 together in the same cell...
Excel: Two line graph, scatter graph, default graph
scatter graph, default graph, graph types: Mike: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/timegraph.xls Graphing with time isn t something Excel has a standard graph for; however, it certainly can be done. I have made several changes to a default graph...
Excel: Two lines of text in one cell, alignment tab, format cells
alignment tab, format cells, arrow keys: Christine, Question 1: two get two lines of text in a cell, with one over the other you would type as below: this is line 1 Alt+Enter this is line 2 so when you reach the end of the first line, do Alt+enter, then continue typing. You then need...
Excel: Two logic, array formulas, array formula
array formulas, array formula, example image: If you understand the concept behind my previous 2 answers, you can adapt it to any situation, adding additional items by imbedding still more IF functions within the array formula. If you have trouble understanding the complex array formulas I ve given...
Excel: Two logic, sumif, sals
sumif, sals, three persons: Following the example file you attached, with the data in B3:D20, and results in the table G3:H4, do as follows. In cell G3, enter the formula: =SUM(IF($B$3:$B$20=$F3,IF($C$3:$C$20=G$2,$D$3:$D$20,0),0)) as an ARRAY, which means after typing in the...
Excel: Two worksheets, countif, occurrence
countif, occurrence, match: Ariano, Your problem statement is a bit confusing because you repeatedly refer to ROW E when you probably mean COLUMN E. There is no such thing as ROW E. So you have two workbooks, ABLE & BETA. You want to look for MATCHING (not duplicate...
Excel: UDF in Excel, UDF Excel VBA
UDF Excel VBA: Kiran, So you want to write a User Defined function in VBA? So what you show in column B is the results of the funciton being entered in column B1 and drag filled down the column. In column A, the numbers 1, 2, and 5 are using strike through in the...
Excel: UDF in Excel, exit function, cnt
exit function, cnt, ogilvy: Before I posted the function, I installed it as I described in a workbook and put the formula I showed in B1, then drag filled it down to row 7. It produced this result: A B 1 2 3 1 4 2 5 6 3 7 4 identical to what you describe....
Excel: UPPER CASE, case feature, cell d1
case feature, cell d1, change case: Mary: If you are looking for a solution to change the entire sheet with just one click of a button, it doesn t exist; however, here are some options: 1) Excel has an UPPER() function. If you have text in cell A1, then you can use this, =UPPER(A1), to...
Excel: Un-protect Textbox / Protect sheet, french answer, format option
french answer, format option, textboxes: I suspected you might be Canadian as the large majority of questions come from USA/Canada - I m not 100% sure what you mean by textboxes, as there ARE textboxes that can be included from the developer ribbon, but these would allow inputs. I m WONDERING if...
Excel: Un-protect Textbox / Protect sheet, mother lives, textboxes
mother lives, textboxes, aidan: I m slightly puzzled in that any textboxes I would enter on the sheet WOULD be open to data entry without unprotecting the sheet - which version of Excel are you using, and what form of textboxes are you referring to? If it helps to mail me a sample file...
Excel: Unable to Click on my Macro Buttons, format cells, macro buttons
format cells, macro buttons, macro code: ...Is there a parameter missing from the AutoFilter command?... No, there are none Try NOT selecting the cells before doing the filter since you RARELY EVER have to select a cell or range before doing anything with that cell and/or range as I think...
Excel: Undo is disables, excel shortcuts, cell b2
excel shortcuts, cell b2, target: It seems you have created shortcuts to your macros and you have used shortcut keys EXCEL uses for some or all of your macros and when that is done those over ride the EXCEL shortcuts. You will have to remove the shortcuts to be able to use the EXCEL shortcuts...
Excel: Unique Identifier, text entries, ogilvy
text entries, ogilvy, a2: John, Assume the identifier is to go in column B starting in B2 and the text entries start in A2 =IF(COUNTIF($A$2:A2,A2)=1,MAX($B$1:B1,0)+1,INDEX($B$1:B1,MATCH(A2,$A$1:A1,0),1)) So I put the above in B2, then drag filled it down the column. It worked...
Excel: Unique Item Counter?, confusion, literature
confusion, literature: Alen, Better try your explanation again because I don t have any idea what you are asking for. If you are handing out books on first-come-first served, what difference does it make what order they were ordered in? The guy is there for his book:...
Excel: Unique Values, collection application, instr function
collection application, instr function, cl: Not sure I follow the requirement - are you asking to only count cells in the range that have a in them? if so Function CountUniqueValues(InputRange As Range) As Long Dim cl As Range, UniqueValues As New Collection Application.Volatile On Error...
Excel: Unique Values, collection application, instr function
collection application, instr function, arguements: My previous answer was wrong anyway as I got the arguements the wrong way round for the INSTR function - this version will fit your requirements unction CountUniqueValues(InputRange As Range) As Long Dim cl As Range, UniqueValues As New Collection ...
Excel: Unique overlaping times, array formula, overlaping
array formula, overlaping, countif: I am assuming that the data will be ordered by Start Time, so we can use formulas for the solution. One way of finding the overlaps is with an additional column, with a formula like: =IF(B3 0,C2 B3) This should go in D2, and then drag the formula across...
Excel: Unique row combined, filter data, jonathan
filter data, jonathan, appearance: Jonathan You could use the filter data option under Data menu. You can use the Auto filter if you want to filter in place or the Advanced Filter to filter in place a create a new copy at another location. http://www.contextures.com/xladvfilter01.html...
Excel: Unique row combined, follow ups, aidan
follow ups, aidan, jonathan: This one sounds tricky - can I check - is the name in one column and the rest of the text in another, or do we have multiple columns? If it helps to email me (eg sample files etc) I m at aidan.heritage@virgin.net, or please feel free to post follow ups...
Excel: Unknown Macro statement, excel vba, internal conversion
excel vba, internal conversion, ogilvy: Ian, It is zero. You can check out the purpose in the immediate window in the Visual Basic Editor: ? typename(0) Integer ? typename(0#) Double so you can see that the # casts the zero constant as a double. This might be appropriate if...
Excel: Unlinking Sheets using Excel 2007 in Vista OS, excel spreadsheet, cells
excel spreadsheet, cells: M You would have linked the files together somehow. If you copied information from one workbook to another then you may have inserted a link instead. You can find the link using the this link http://www.contextures.com/xlfaqApp.html#HasLinks depending...
Excel: Unwanted Shaded Pattern on my Spreadsheet, selection move, cursor position
selection move, cursor position, spreadsheet: Excel remembers settings, so the cursor position and active sheet (for example) are saved with the sheet - when you send the sheet, you are effectively saving it. Having a range of cells selected will result in those cells being shaded, and that is something...
Excel: Update a time field based on a certain condition, target validation, target value
target validation, target value, value case: This was interesting research - I first found a suggestion of having a very hidden sheet which stored all the old values (http://www.tanguay.info/web/codeExample.php?id=872 ) but I think I ve managed a neater solution, which is to use two events Dim oldVal...
Excel: Update data from rows to columns, transpose
transpose: Brad: Let s assume your date is in cells A1 through B3. I am assuming your months are in A column and the numbers are in B column. 1) Select cells A1:B3. 2) Select edit- copy 3) Select the cell you want to copy to...say D1 4) Select edit- paste special...
Excel: Update data without links, necessary thanks, running office
necessary thanks, running office, hyphen: Seth I have been looking at the files and have forwarded it onto a VBA expert that I know. If it is possible he will find a solution. The only problem we have is that not all the items have service or install and some don t have the hyphen in them which...
Excel: Update the values in the current month, consolidation worksheet, parent cells
consolidation worksheet, parent cells, cell a2: You show PAY as being in row 1, but I assume it is in row 2 (actually cell A2) and the word April is in B1 I will guess that you want the labels in column A and the monthly data copied to the next blank row in consolidation sub CopyData() set...
Excel: Updated calculations, calculation tools, a8
calculation tools, a8, aidan: I SUSPECT that the reason is simply that the sheet in question is set to manual calculation (tools, options, calculation tab - if I m right, change it to automatic and it will be resolved). If not, I will need to do more research - possibly seeing the file...
Excel: Updating Pivot Table, set rw, pivot table
set rw, pivot table, table source: Rafael, Dim rw as Long with worksheets( Data May 08 - ... ) set rw = .cells(rows.count,1).end(xlup).row end with ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:= Data May 08 - ... !R1C1:R _ rw & C32 ).CreatePivotTable...
Excel: Updating Pivot Table, set rw, pivot table
set rw, pivot table, table source: Rafael, There were a couple of typos on my part. This compiled for me: Dim rw As Long With Worksheets( Data May 08 - ... ) rw = .Cells(Rows.Count, 1).End(xlUp).Row End With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=...
Excel: Updating data using macros, datadate, datalib
datadate, datalib, br4: You didn t tell me what the code you are using is, but I assume you have the file name in a string somehow - use the FORMAT function within VBA to handle this Dim MyName as string MyName= www.reports.com/reports/late_tasks.csv=0&DataType=Daily&DataDate=...
Excel: Updating info in several forms comboboxes, macro code, excel workbook
macro code, excel workbook, input range: I think you basically need a supply list that continues to grow, but never re-orders - the issue (as I see it) at the moment is that the list is being re-ordered. I would PROBABLY see Visual Basic as the solution, where a stock list is kept up to date but...
Excel: Updating powerpoint with excell, www google co uk, powerpoint object model
www google co uk, powerpoint object model, power point presentation: I m not at all familiar with powerpoint - there is probably an object model available for it that would mean it would update - but there is probably also a setting to automatically update the links in powerpoint? The google search that will give you the...
Excel: Updating spreadsheet, filter commands, excel function
filter commands, excel function, relevant data: Phil, There is no excel function or equation that I can think of which will delete a row from a database, or which will erase data from a row. I can think of several ways to HIDE such data, but the only non-macro way to delete it is to manually, not...
Excel: Updating spreadsheets using a Macro, column headings, sheet tab
column headings, sheet tab, target: Still -- are you only changing column B? If so: right-click the sheet tab, select View Code, paste this in: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then Cells(Target.Row, 1).Resize(1, 8).Copy With Workbooks(...
Excel: Updating spreadsheets using a Macro, column headings, spreadsheets
column headings, spreadsheets: There s one basic problem which needs to be addressed: If an entire row in New is being updated, from col A thru H, then when col B is changed and the macro kicks in , the rest of the information is not yet present to copy to the Master. Same when a row is...
Excel: Updating with new information into different sheet, vlookup, input sheet
vlookup, input sheet, joby: Hi Joby How are you? Please try the following example 1. On Sheet1, A1, type 100 2. On sheet2, type in C3:C6 50, 100, 150, 200 3. On sheet2, type in D3:d6 Ab, Abc, Aca, Ada 4. on sheet3 type in A1 =Vlookup(Sheet1!A1;Sheet2!C3:D6;2) I hope this...
Excel: Updating worksheets names from a name list, sheet names, work sheets
sheet names, work sheets, doable: The only way you will be able to do it is via VBA, and (as far as I know) this would have to be through the change event of the worksheet, and you would have to change the name on the list itself. You didn t specify WHY you wanted to do this however, so there...
Excel: Urgent : regarding the formulae, correct manner, excel sheet
correct manner, excel sheet, formulae: If I understand the question correctly, the cell will contain text which MIGHT contain one of the values you want to search for - if this is a correct understanding, then this formula will do it - this example assumes the value you are checking is in A1 ...
Excel: Use Complex Array to count Combinations of text values in two columns, isnumber, text values
isnumber, text values, search functions: =sumproduct(--(A1:A1000= TBD ),--(B1:B1000= Track 1 )) I assume whole word matches in the cells If you wanted to count substring matches such as A3: JohnTBD his house B3: abd Track 1efg containing TBD and Track 1, post back. -- Regards,...
Excel: Use Dates\Product No to split qty's between cells, column headings, hello bill
column headings, hello bill, code numbers: Anthony, Let me parrot back your question to you, with my comments interjected. My comments will be enclosed in ### s. ================================================================= There is a 1-1 relationship between BECSTOCKCODE and VENDORCODE(they...
Excel: Use Hyperlinks, but NOT validation when Protected, data validation, external documents
data validation, external documents, time thing: In order for mgmt to bw able to select items from drop downs from Data Validation then those cells will have to be unprotected for them to make choices. Then AFTER that those cells will need to be protected based on what you say. And mgmt, in the early phase,...
Excel: Use Hyperlinks, but NOT validation when Protected, external sources, hyperlinks
external sources, hyperlinks, hyperlink: Just protect the cells that you don t want the other users to be able to change. But you will have to leave them UNprotected so mgmt can add what they need to add. You will need to have a macro run AFTER mgmt gets thru with what they before you then let...
Excel: Use VB to sort a table from a dropdown menu, range sort, running in circles
range sort, running in circles, myrg: Private Sub worksheet_change(ByVal target As Range) Dim sortBy As String If Not Intersect(target, Me.Range( Sort )) Is Nothing Then sortBy = target.Value Me.AutoFilterMode = False If sortBy = All Names Then ...
Excel: Use a specific data value if another cell has a certain text value question, va4, value question
va4, value question, b5: Keith, Assume your table of VA values and corresponding O values are in the range of cells M1:N3 as shown: M1: VA3 N1: O6 M2: VA4 N2: 010 M3: VA7 N3: O14 in K19 put in =if(B5= , ,Vlookup(B5,M1:N3,2,False)) If these values of equivalencies...
Excel: User Defined Function, udf
udf: You can reverse the function with something like: Function NumGrades(Grade As Integer) As String Select Case Grade Case Is = 15 NumGrades = A+ Case Is = 14 NumGrades = A Case Is = 13 NumGrades = A- Case Is = 12 ...
Excel: User Form, column headings, input number
column headings, input number, listboxes: Zsoft, Not sure what you mean here. Do you mean you want three listboxes or you want to make a selection between approved, unapproved, superseded. Anyway, a listbox can only show 9 columns unless it is bound to a worksheet, so I have chosen to filter...
Excel: User Input Validation, input validation, commandbutton
input validation, commandbutton, numeric fields: Hi David, There are a lot of ways of doing this, but the following illustrates a method I recommend. Here is an example that should prove helpful. In this example, there is one combobox (ComboBox1) and three textboxes (TextBox1,...,TextBox3) on the...
Excel: User Input, vba help, c users
vba help, c users, snippet: Dave, The GetOpenFilename method will do exactly what you need. It uses the open file dialog box to let the user browse to a file and select it. The short snippet of code below gives an example. There are several options that can be used to set filters...
Excel: User file selection, getopenfilename, spath
getopenfilename, spath, ubound: Something like this will ask for a filename (the True argument in GetOpenFilename enables the user to select more than one file): Sub GetOpenFileNameExample3() Dim lCount As Long Dim vFilename As Variant Dim sPath As String Dim lFilecount...
Excel: User form textbox default values, optimum depth, input place
optimum depth, input place, vba code: Example: you have TextBox1 in UserForm1. When UserForm1 opens, you want this textbox to default to the value that s in A1 of Sheet1. Open the UserForm in Visual Basic Editor. Double-click on the UserForm to get to the vba code attached to the form. Then...
Excel: User limit in Excel, sheet names, hog
sheet names, hog, time out: The snapshot of the code was too small to read so I don t know what that person was doing and therefore don t how to put a limit on what the user does. But code can be written to do that sort of thing. I suggest asking the person who answered your question....
Excel: Userform help, userform textbox, valve type
userform textbox, valve type, drop down list: There are many ways to do this. You can just set the ControlSource property of the text to A1. You can also set the Rowsource of a listbox to be something like B1:B5. Whatever is in A1 will appear in the textbox and then whatever is typed in the textbox...
Excel: Userforms, userform initialize, relevant department
userform initialize, relevant department, fruit vegetable: Suppose in A1:A2 you have Fruit Vegetable. The Userform Initialization code would use: Private Sub UserForm_Initialize() Me.ComboBox1.List = Application.Transpose(Range( A1:A2 )) End Sub Now you also have ranges named Fruit and Vegetable, so...
Excel: Using "contains" with the IF function, vlookup function, input cell
vlookup function, input cell, word city: Owen, My version of Excel (2003) doesn t have a contains function, so I don t know what that is. Perhaps you meant FIND or SEARCH? I would propose the following method: 1) use simple text processing to take out the numbers from the city...
Excel: Using Autofilter Results, header row, rng
header row, rng, loop through: I would use something like this (this particular code is untested and may contain typos but should show the direction): Dim StrtRow as Long, LstRow as Long Dim i as Long Dim rng as Range, cell as Range get the range of data that is filtered, then ...
Excel: Using Autofilter Results, rng, header row
rng, header row, loop through: Dim rng as Range, cell as Range get the range of data that is filtered, then reference only the first column of the range set rng = Activesheet.Autofilter.Range.Columns(1).cells adjust the range so only the data is included, not the header row...
Excel: Using Basic Macros - Checkboxes, checkboxes, checkbox
checkboxes, checkbox, ogilvy: Select the range where you want checkboxes and run the macro: Sub AddCheckboxes() Dim cb As CheckBox Dim cell As Range delete existing boxes For Each cb In ActiveSheet.CheckBoxes cb.Delete Next Selection.EntireRow.RowHeight = 22.5 For Each cell...
Excel: Using Cell References in GetPivotData Formulas, getpivotdata function, pivot table
getpivotdata function, pivot table, cell references: When you reference a date in a cell, like = Today is &A1 and A1 contains 10/1/2008, you d see Today is 39722. this is a Serial# and you can get what you want this way (assuming the dats is in E3: =GETPIVOTDATA(BMPT!A3, sum of amount 651 10 &TEXT(E3, m/d/yyyy...
Excel: Using Count in Excel, d 500, ascending order
d 500, ascending order, craig: Hi Fred, You actually don t need to have column D sorted in any order. Assuming your column is 500 numbers long, D1 to D500: You ll actually want to use the COUNTIF function. For each of your numbers, you ll set up a formula like this: =COUNTIF($D$1:$D$500,4)...
Excel: Using Excel IF command for odd or even numbers, ISODD ISEVEN
ISODD ISEVEN: Justin: You would want to use a formula similar to this: =IF(ISODD(A1), ODD , EVEN ) This formla will display the word ODD if the value in cell A1 is odd. It will display EVEN if the value is even. Please let me know if you have any additional...
Excel: Using Excel as Database, customer statement, statement of account
customer statement, statement of account, customer name: Osama The following is an article from the microsoft website--I think it will help you. Richard ====================================================================== How to look up a value in a list and return multiple corresponding values Applies...
Excel: Using Excel, excel follow up
excel follow up: Hi Debbie, There are several ways to do this in Excel. The most slick way to do it would be using VBA and userforms. But, that can get complicated. So, a simpler method would be to do as follows... In A1, put the header (something to describe what you...
Excel: Using IF statement, finish time, e10
finish time, e10, ogilvy: I am not sure what is going on here. I thought I just answered this, but I am not sure what you mean by it doesn t put an x in the start time. If I make BZ9 match the start time, I get an X in BZ10. If BZ9 is calculated, there could be slight differences...
Excel: Using IF to find a product, cell d2, cell a2
cell d2, cell a2, cell b2: Tim I am a little confused with the question. You don t mention product inthe E2 cell, you are looking for a multiplication option using the if statement. The product in F2 will automatically use the figure generated by the if function in E2. Have...
Excel: Using INDEX/MATCH to look up multiple values, microsoft office support, formula index
microsoft office support, formula index, question thanks: Dale, ROW(326:326) tells it to return the 326th largest number. It looks like you should drag it down 1 more row to get it to go to 327 (so if you started with row(1:1), you only have the formula in 326 cells. -- Regards, Tom Ogilvy ...
Excel: Using ISERROR, array formula, zero errors
array formula, zero errors, divide by zero: I m puzzled slightly in that you refer to an array, but then multiple values - which implies that the formula as given isn t quite what you are using - I would be INCLINED to try =if(value(B)=0,0,H/B) this assumes you are using one row per row, rather...
Excel: Using Individual Sheets and importing to a master list., target value, master cells
target value, master cells, thanks in advance: This requires the master sheet to be named MASTER: Right-click the Excel LOGO (Near the file menu), select View Code, paste this in: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Sh.Name = Master Then Exit Sub ...
Excel: Using Lookup Functions, lookup functions, c300
lookup functions, c300, error value: Assuming the range A1:C300 contains the ID, name & price, then if you enter the ID in a cell in the sales form, say K5, then in L5 enter =IF(ISERROR(VLOOKUP(K5,A1:C300,2,FALSE)), ,VLOOKUP(K5,A1:C300,2,FALSE)) and in M5 enter =IF(ISERROR(VLOOKUP(K5,A1:C300,3,FALSE)),...
Excel: Using Lotus slash commands in Excel, lotus 123, transition keys
lotus 123, transition keys, menu choices: You can use the / commands in EXCEL 2003 but at some point I suggest that you learn EXCEL and remove yourself from an old and obsolete product. And since not ALL of the menu choices are available by using the / key then this is another reason to wean yourself...
Excel: Using Named Ranges, seperate cells, cell a2
seperate cells, cell a2, excel formulas: EXCEL can create dynamic ranges which means the area the range name refers to will change based on the amount of data. So, if you have data in A1:A10, for example, and have a range named TEST then TEST will select A1:A10. Add data to A11 and to A12 and...
Excel: Using Nested If's in Macros
You are using R1C1 BUT the formula DOESN T use this method - amend it to activecell.formula. Having said that, I wouldn t put a formula in place if it is to be replaced with values, rather use the code to work out what the value should be and set it directl...
Excel: Using OR statement with a list, consecutive columns, curly braces
consecutive columns, curly braces, stutts: Sounds like you neglected to hold ctrl+shift when pressing enter. If you did it correctly, then when you click in the formula bar you ll see {=IF(OR(....1,0)} (with the curly braces around the formula -- something YOU don t enter, Excel does. Try again...
Excel: Using SUMPRODUCT to calculate a SUM, datevalue, sumproduct
datevalue, sumproduct, sheet1: Hello Glyn, It looks like you just have a typo in your formula. You cover rows 3 to 13726 in column C and rows 3 to 15001 in column G. The should cover the same range: =SUMPRODUCT(--( Sheet1 !$C$3:$C$13726 =DATEVALUE( 01/01/2006 )),--( Sheet1 !$C$3:$C$13726...
Excel: Using VB to modify drawings objects, excel vba, text boxes
excel vba, text boxes, mehta: I will assume you know something about VBA to begin with. If so, then rather than having to type or retype a lot of text, the site below will help. http://www.tushar-mehta.com/excel/vba/vba-embedded%20or%20linked%20objects.htm If you don t know anything...
Excel: Using Vlookup find multiple result, excel tips tricks, hardeep
excel tips tricks, hardeep, 599cd: You re going to need a seprate VLOOKUP for each item you wish to look up on the other sheet. See: http://599cd.com/tips/excel/vlookup?key=AllExperts Let me know if you have any other questions or comments. Also, be sure to visit my FREE Excel Tips...
Excel: Using a formula to filter, mango mango, banana mango
mango mango, banana mango, banana banana: Hi Carlos, Yes, we can create a fairly simple formula to identify unique fruit in Column B. Making the following assumptions: -your original list is in Column A, starting with Cell A2 -your filtered list is in Column B, starting with Cell B2 Put...
Excel: Using a macro to get data from a file within a folder, scripting runtime, apple prices
scripting runtime, apple prices, fruit prices: Below is some sample code that checks all files in the folder C:\temp\sample and opens the first one found that has the string summary within its file name. You should be able to adapt the code to whatever specific path and key words you need. Sub openFileNamed()...
Excel: Using a spreadsheet like a graph, upper left quadrant, range target
upper left quadrant, range target, point coordinates: George, What a unique, cool, and really interesting Excel project this is! So I assume you have a data table that has the shot number, x co-ord, and y co-ord. Your target has only two dimensions, since the quadrants are access via negative values...
Excel: Using a standard windows call to find a file to load into excel, csv file, csv files
csv file, csv files, vb examples: Try this: Dim File_Type as String Dim CSVFile as String File_Type = Open the CSV file you need, etc CSVFile = Application.GetOpenFilename(Title:=File_Type, fileFilter:=( CSV Files Files (*.CSV), *.csv )) This will not actually open the file....
Excel: Using a word or phrase to symbolize a number, comparison spreadsheet, array formula
comparison spreadsheet, array formula, number 0: I m having problems visualising this- it half sounds as though you could use the SUMPRODUCT function (which returns the values of all x multiplied by y values in a column and would treat a non numeric as a zero). Otherwise, it s PROBABLY an array formula...
Excel: Using alignment in IF statement, horizontal alignment, true dates
horizontal alignment, true dates, yyyy: There is no function built in to EXCEL to do this unless you write your own. After the report has been generated why not just reformat that column manually? IF the data is left aligned where the dates are then those are probably not dates. All dates...
Excel: Using alignment in IF statement, horizontal alignment, true dates
horizontal alignment, true dates, yyyy: ...I ll give it a try.... OK A function that does not exist in EXCEL and thus would be one that you would have to write yourself is really a macro. And since you will be writing a macro then I would suggest NOT writing a custom function. If you do...
Excel: Using alignment in IF statement, horizontal alignment, true dates
horizontal alignment, true dates, yyyy: Typically I don t write macros for people on this site and this is the standard answer/reason I provide ----------------------------------------------------------------- Sorry, as politely as possible, I don t write macros for people on this site who...
Excel: Using arrays in if statement to rearrange a table, straightforward answer, pivot table
straightforward answer, pivot table, column c: Jeff, Have you tried making a PIVOT TABLE of your database? You could actually make two of these, but it is so easy to dynamically change a pivot table to display information in whatever way you wish, one P-T might be sufficient. A Pivot Table...
Excel: Using cell reference as part of another cell reference, cell reference, number 0
cell reference, number 0, text string: Stuart, There are various ways to do what you want. You can create a text string that looks like an address using the CONCATENATE operator (the & symbol). = B$ &TEXT(cell-containing-number,0) You can also use the ADDRESS function...
Excel: Using countif function to count specific date range, dates of interest, added entries
dates of interest, added entries, data values: Since you count the 9 companies and you have a countif of all added entries, I would use the Countif you already have, then subtract the total/Sum of the 9 companies. This would be the easiest. a pseudo formula representation: =Countif(arguments)-Sum(count...
Excel: Using countif function to count specific date range, dates of interest, data values
dates of interest, data values, ogilvy: If you had the dates of entry in column B =Sumproduct(--($A$1:$A$10000= Company A ),--($B$1:$B$10000 =DateValue( 12/10/2007 )),--($B$1:$B$10000 =DateValue( 12/16/2007 ))) This formula worked for me. clearly you will have to adjust this formula to...
Excel: Using excel Macros - Checkbox feature, excel macros, arrow tool
excel macros, arrow tool, checkbox: ...Do you know of a way to a) insert the checkbox into particular chunks of cells, and b) ensure that all checkboxes line up at the same point in each cell?... There is no quick way to add a lot of checkboxes all at one time. You can DOUBLE click the...
Excel: Using filter with data validation., data validation, excel sheet
data validation, excel sheet, number 9: It s hard to picture how it can hide rows in the layer column without hiding the info in all the rows. Can you send me the file or a copy for me to examine? Include what you want to do (again), and I ll see what I can do. bobumlas@yahoo.com Please use AllExpertsQ...
Excel: Using forms to fill cells in Excel, microsoft excel 97, insertion point
microsoft excel 97, insertion point, contextures: Shawn, Obviously an explanation would require more information that would be appropriate here - so let me refer you to some links where this question has been answered in detail. then if you have questions specific to your situation which you can t figure...
Excel: Using formula answer in anouther formula, vlookup formula, cell reference
vlookup formula, cell reference, exact error: The VLOOKUP formula should work with a formula, value or cell reference in the first parameters with no problems, if the final value make sense for the range being searched for. Probably the error is due to the third parameter, the one selecting the column...
Excel: Using images as chart backgrounds; avoid portability limitations, chart worksheets, copy worksheets
chart worksheets, copy worksheets, chart background: Hi Steve, Unfortunately, I do not believe that this is possible for chart or chartobject backgrounds. The UserPicture method is rather inflexible in this regard, and there is no Picture property for charts or chartobjects as there is for controls and userforms,...
Excel: Using range names in vba, range names, indirect method
range names, indirect method, vba: you shouldn t need the indirect function in VBA sub test() dim MyVar MyVar= A1 msgbox range(myvar).value msgbox range( A1 ).value end sub as an example shows you that both methods return the same value- that contained in A1. so countvar=1...
Excel: Using rank, cell b2, priority code
cell b2, priority code, random number: Noren, We ll need to add another column, and Concatenate the Priority and the Random Number together, and then convert it back to a number. -Assume that your Priority is in Column A, starting with Cell A2 -Assume that your Random Number is in Column...
Excel: Using references, code column, discrepancy
code column, discrepancy, aidan: I m not 100% clear what is required here - is this a one off, or is it to be ongoing - if ongoing, do you just want to see the data that matches the given code? And presumably you are missing the code on the first sheet IF it remains the same so we d have...
Excel: Using the FIND function in a macro, devil is in the details, macros
devil is in the details, macros, occurrence: AHHH!. That s not what you asked and I try not to assume. As with ALL macros the devil is in the details and you do not provide much detail. Do you need to be prompted to type what the code needs to fond? Do you need it to find the first occurrence or...
Excel: Using the If function with two conditions set to a variable, vba
vba: In this case, concatenating the value to compare and the comparison won t work, as the line is comparing the numerical value with the text =50 , and that will never be a match. You could add other options to the Or statement, something like: If IsNumeric(cel.Value)...
Excel: Using trendline equations in cells, linest, scatter plot
linest, scatter plot, hth: The coefficients from the LINEST function can create up to a 16th-degree best fit curve! For 3rd degree, select 4 columns, ctrl+shift+enter this: =LINEST(A1:A100,B1:B100^COLUMN(A:C),TRUE,TRUE) and you ll have your coordinates (assumes y-values in A, x-values...
Excel: Using two different files in one formula
Hi Andrew, It looks to me like it does like the $D$4:$D$5 part of your formula. My guess is you want to add those two cells, so putting a SUM statement in the formula will fix it: =((E14-SUM( [TILL RETAIL REPORTS 2007.xls]TOTALS !$D$4:$D$5))/E14) ...
Excel: Using vin numbers, vlookup function, vin numbers
vlookup function, vin numbers, cell references: Chris, What you want to do is quite possible, but it requires a certain amount of expertise to get it to work properly. For the first part of your question: Use the VLOOKUP function, it will do what you want. You can use it to tell if each...
Excel: V-Look UP, vlookup, seperate tables
vlookup, seperate tables, spreadsheet: Michelle Using the Vlookup should be automatic. I am a little confused with the question though. Can you send me a copy of the file so I can look at it and see what you mean. My email is (answer@pcexperthelp.comp) remove the p from the comp. If...
Excel: V-Lookup, array formula, delivery number
array formula, delivery number, input sheet: Assume in SheetA (the output sheet), you have Delivery # in Column A, Quantity in Column B and you want the result in Column C In sheetB( the input sheet), you have Delivery # in Column A, Quantity in Column B, and Value in Column C there are atleast...
Excel: V-Lookup, delivery number, addition formula
delivery number, addition formula, cell b1: Tanaz The simplest way to handle this is to create a unique number out of the delivery number. You can do this by combining a series of numbers or letters with the existing delivery number for example In col A is delivery number insert a new...
Excel: VB +, cl column, filter auto
cl column, filter auto, ords: I d probably just do this manually with data, filter, auto filter but something like Sub helper() Open C:\AE_output.txt For Output As #1 For Each sh In ActiveWorkbook.Worksheets For Each cl In sh.UsedRange If cl.Value 0 Then ...
Excel: VB - attempting to change 100's of range names..., application workbook, memory design
application workbook, memory design, range names: Alex, You say that all the names are already in the workbooks, but you have changed workbook names and now they can t find the workbook. As far as I know, named ranges in other workbooks always include the fullly qualified path/name. when the other workbook...
Excel: VB - attempting to change 100's of range names..., application workbook, memory design
application workbook, memory design, range names: Alex, When dealing with links it is always best to open all workbooks in excel, then make the changes using Save As and so forth. Then the links will be updated. If closed, if you maintain the relative locations of workbooks, then if you move the workbooks...
Excel: VB - use of range names, range names, vba code
range names, vba code, cells: Do all range names in the workbook that holds your VBA code: Dim oName as Name For Each oName in ThisWorkbook.Names Now use oName, e.g. : Msgbox oName.ReferstoRange.Cells(1,1).Value Next Or: Set oName = ThisWorkbook.Names( TheName ) Now use...
Excel: VB -subscript out of range err when chgng NamedRanges using VB..., application workbook, donating your time
application workbook, donating your time, excel application: Firstly, my apologies if this answer is too brief - I forgot to set vacation dates and am actually away from base at the moment - IF you need to contact me you can get me in about a weeks time here, or email me at aidan.heritage@virgin.net and I will pick...
Excel: VB Coding, data validation, subcontractor
data validation, subcontractor, drop down list: In cell C1, enter =IF($E1= subcontractor , external , ) In D1, enter =IF($E1= subcontractor , internal , ) Cols C & D may be hidden, or formatted white font to hide from user. Select G1, and from main menu Data, Validation, Allow: List, Source:...
Excel: VB Excel, loop statements, blank cell
loop statements, blank cell, simplest possible example: Here s a simple example to give you an idea. Say your workbook has Sheet1 and Sheet2. You want to start with A1 on Sheet1, and then transfer the value of each cell in Col A to the same range on Sheet2, until the first blank cell in Col A is encountered. The...
Excel: VB Script for drawing charts in Excel, xy scatter chart, grico
xy scatter chart, grico, line style: Hi Ammar How are you? Please have a look at Sub ColouringChartLine() Dim S As ChartObject, Se As Series Set S = ActiveSheet.ChartObjects( Gr?fico 1 ) S.Activate Set Se = ActiveChart.SeriesCollection(1) Se.Format.Line.ForeColor.RGB = RGB(0, 255,...
Excel: VB Script to Save Active Worksheet, ogilvy, project managers
ogilvy, project managers, constants: Philip what I would do would be ActiveSheet.copy creates a one sheet workbook Activeworkbook.SaveAs C:\MyFolder\Myfiles\ & Activesheet.Name & .xls , Fileformat:=xlWorkbookNormal Activeworkbook.Close Savechanges:=false now you can clear...
Excel: VB code change, target range, time cells
target range, time cells, tahir: To send the email every time the workbook is saved, used the code: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook.SendMail mohsintahir7@gmail.com End Sub The code should be entered in your Visual Basic...
Excel: VB code, receipt printer, printer object
receipt printer, printer object, command keys: You will need to find the printer manual to determine what command the printer expects to see. In the old days of programming, you could use LPrint to send the command keys directly to the printer. I ve not managed to find a VBA equivalent (though I m fairly...
Excel: VB help please, prompt text, follow ups
prompt text, follow ups, msgbox: Target.Column seems to refer to the other code that was using the WorkSheet_Change event. In that routine it refers to the actual cell (not the contents of the cell) the cursor is on. You need to determine what cell Target refers to and then use Set Target...
Excel: VB to Sum Values in Excel, vb program, total values
vb program, total values, message box: The for instance causes a problem - do you want to sum the first 10 values, or the total values on the sheet, or a number input by the user. dim myvar Workbooks.Open c:\temp\sample.xls myvar=worksheetfunction.sum(range( A1:A10 )) activeworkbook.close...
Excel: VB, target address, title abbreviation
target address, title abbreviation, sheet1: 1) You would use the change event on sheet1. You can get to this by right clicking on the sheet1 tab and selecting view code. In the left down at the top of the resulting module, select worksheet and in the right dropdown select Change (not selectionchange)....
Excel: VBA & Printing, setup dialogs, setup button
setup dialogs, setup button, command buttons: Question 1: I am not aware of a way. Question 2: application.Dialogs(xlDialogPrinterSetup).Show this has a setup button, but it is the setup for the print driver itself rather than the page setup dialogs of Excel. -- Regards, Tom Ogilvy ...
Excel: VBA - Excel 2003 in XP, pastes, good afternoon
pastes, good afternoon, ogilvy: Omar, I believe this is what you want: Sub copydata() with activesheet .Range( R:R ).copy .Range( S:S ) .Range( R:R ).ClearContents End with End sub Since your copying all the data, it is easier just to copy the whole column. That said,...
Excel: VBA - Excel Outlook Appointment, outlook, excel
outlook, excel: Sorry for the late response, I was out for the weekend. Regarding the code, this may give you some ideas: Sub CreateAppointment() Dim myOlApp As Outlook.Application Dim myItem As Outlook.AppointmentItem Set myOlApp = GetObject(, Outlook.Application...
Excel: VBA - Formatting, chris answer, vba procedure
chris answer, vba procedure, false application: In my previous answer... the cell A1 must be unlocked. That way, when G3 changes, the vba procedure runs and the formats from A1 get copied into G3; this insures that G3 remains unlocked. FYI, if you needed to unlock the cell using vba, the line of code...
Excel: VBA - Formatting, false application, wkb
false application, wkb, target: Choose a different cell in your wkb (I ll use A1 in this sample), and format it exactly as you d like G3 to be formatted. Then, use vba code that executes whenever G3 is changes, and have the code copy in the correct formats only (i.e., in this sample, copy...
Excel: VBA - Show MsgBox once formula result is negative, target range, data validation
target range, data validation, cell b1: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range( a1 ).EntireColumn) Is Nothing Then Exit Sub Else Range( b1 ).Calculate If Range( b1 ) 0 Then MsgBox B1 is less than zero ...
Excel: VBA - importing several TXT file at once, folder files, folder c
folder files, folder c, temp file: The sample vba procedure below will check each file in the folder C:\TEMP. For any .txt file in this folder, it will copy all data to Sheet 1 of this workbook. You should be able to adjust the code for your specific needs. Sub copy_txt_files() Dim...
Excel: VBA -code to go to last blank cell, empty cells, blank cell
empty cells, blank cell, thanks in advance: Hi Ish How are you? Is it to locate the data that follows an empty cell? Please see the following: Sub searchingdata() Dim R As Range, i As Integer Set R = ActiveSheet.Range( A1 ) i = 1 Do While IsEmpty(R.Cells(i, 1)) = False i = i + 1 Loop...
Excel: VBA Arrays, aa aa, business day
aa aa, business day, aj: Here is one way of many ways of doing this and used your approach of using Goto s Untested Dim sh as Worksheet Dim Sh_Found as Boolean For Each sh in ActiveWorkbook If sh.Name = PriorDate Then Sh_Found = True Exit For End If Next...
Excel: VBA Arrays, aa aa, business day
aa aa, business day, aj: Should be: For Each sh In ActiveWorkbook.Sheets forgot to add .Sheets And you need to put Sheets(PriorDate).Select after sh_found = True because the loop will exit when the sheet is found and skip right past Sheets(PriorDate).Select where you have...
Excel: VBA Cell References and Loops, loop macro, cell references
loop macro, cell references, dear tom: Michael S. Sub ClearDeck() Dim i As Long, cell As Range For i = 28 To 100 Step 19 Set cell = Cells(i, C ) cell.Resize(3, 6).Copy cell.Offset(1, -1).Select ActiveSheet.PasteSpecial Format:=3, Link:=1, _ DisplayAsIcon:=False,...
Excel: VBA Cell Value change help, target value, moto
target value, moto: You can try with a code like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim varValue, rCheck As Range Set rCheck = Application.Intersect(Range( O4:O191 ), Target) If Not rCheck Is Nothing Then varValue = InputBox(...
Excel: VBA Code Replace Command, vba code, macro in excel
vba code, macro in excel, c120: Hi Shane, I have not fully understood your requirement.. Check if I am right.. In Cell I4, you will have some alphabet. (e.g. T) You have some text in cells C1:C120 In every cell, you want F in every cell (or any cell that has F) to be replaced with...
Excel: VBA Code in Excel, cathy baker, gail brown
cathy baker, gail brown, sally green: Ironman, as you might imagine, this is a rather common need. Ron de Bruin has written macros to perform this type of task - one is exactly this task. The code can be found at Autofilter http://www.rondebruin.nl/copy5.htm -- Regards, Tom Ogilvy...
Excel: VBA Code in Excel, gnat, clipboard
gnat, clipboard, notepad: It is somewhat awkward to have the code behind your button prompt you to select a range and then continue with the rest of the code. I suggest just selecting the range PRIOR to then clicking the button if you choose to use a macro. And if you still choose...
Excel: VBA Command Question, false selection, macro execution
false selection, macro execution, screen appearance: ...Please let me know if something can be changed to get the macro to work so that the data values get refreshed without changing the screen appearance. ... Well, how does the screen s appearance change and what did it look like before the code runs and...
Excel: VBA Command Question, false selection, macro execution
false selection, macro execution, screen appearance: This will help you even more and I should have mentioned it before. You RARELY ever have to select anything to something with that anything . For example, to do a simple copy (and you really don t need to copy anyhow - see the last example) then use this...
Excel: VBA Command Question, false selection, macro execution
false selection, macro execution, screen appearance: You re welcome. It SHOULD work faster because it has to do less and fewer things are selected (which takes time). And you can remove myCell.Select. It is not needed since the cursor never moves during the code s execution. Take it out and see...
Excel: VBA Conditional Formatting in Excel, conditional formatting in excel, colorindex
conditional formatting in excel, colorindex, cell b1: The functionality of the code/rules are still defined with the old names, but if you put in the new equivalent names as found in row 3 in the array, then it should work with the new names. Sub ColorCells() Dim sh As Worksheet, r As Range, cell As...
Excel: VBA Counting, integer size, true range
integer size, true range, address range: JJ, this does what I think you want your code to do: Size_Post = Range( F1 ,Cells(rows.count,6).End(xlup)).Count If you want to select Range( F1 ,Cells(rows.count,6).End(xlup)).Select If that doesn t give you the correct answer, then why...
Excel: VBA Custom Filter, case senstive, irow
case senstive, irow, range selection: Sub deleteRows() Dim cellToCheck As Range Dim iCheck As Integer, iRow As Integer, iCount As Integer Dim strCheck As String Dim blnDeleteRow As Boolean iCount = Range( g1 ).CurrentRegion.Rows.Count - 1 For iRow = iCount To 0 Step...
Excel: VBA Custom Filter, case senstive, lcol
case senstive, lcol, range selection: this worked for me: [it assumes your data starts in A2 with headers in row 1 and that the column to filter on is column G]. It adds an adjacent column to the right side with the criteria and actually filters on that column. It then removes that new column...
Excel: VBA EXCEL Selection.copy, excel, vba
excel, vba: I am not sure on what you need to do, I can see that you used the Ctrl+Arrow to move around the range, but sometimes is still overwritten by the static selections. One thing that you can try is to record the macro using Relative references. To do that,...
Excel: VBA Excel Creating dynamic buttons, misaligned, target width, target height
target width, target height, dynamic buttons: Rather than creating the buttons with VBA, why not create the buttons manually on the sheet, and then have VBA make them visible or invisible as desired? This is far simpler than creating buttons on the fly. If this doesn t meet your needs... then perhaps...
Excel: VBA Excel Listbox, vba excel, rowcount
vba excel, rowcount, excel worksheet: For a two column list box, here is an example Dim X As Byte Dim RowCount As Byte With Me RowCount = .ListBox1.ListCount For X = 1 To RowCount MsgBox Row & X & First column = & .ListBox1.List(X - 1, 0) _ & vbCr & Row & X & Second column...
Excel: VBA Excel, blank copy, paste data
blank copy, paste data, sh1: Grant, This worked for me as I understood you to describe it. Sub CopyData() Dim r As Range, cell As Range Dim rw As Long, r1 As Range Dim sh As Worksheet, sh1 As Worksheet Set sh = ActiveSheet Worksheets.Add after:=Worksheets(Worksheets.Count)...
Excel: VBA Find Replace, vba replace function, irow
vba replace function, irow, necessary text: Hi Ryan, Here is a simple and efficient VBA macro that I believe does what you want: Sub ReplaceTEMP() Replaces the text TEMP in Col A with whatever is in the corresponding row in column B Dim iRow As Long iRow = 2 start...
Excel: VBA Form with excel, header row, excel worksheet
header row, excel worksheet, situtation: Hi Frank, I did have to make a few assumptions, and hope they are right. Here are the assumptions I made: 1. Your combobox and textboxes are on a Userform, but the form that you refer to is a table on an Excel worksheet. 2. Just for the sake of...
Excel: VBA Form writing to multipe spreadsheets in the same workbook, simplest form, new question
simplest form, new question, drop down list: My suggestion would be to link the drop down list with the names of the worksheets, and then use the result of the combo to select the worksheet. Something like: Sheets(ComboBox1.Value).Range( XX ).Value = XX This is the simplest form, you can adjust it...
Excel: VBA Help More multi columns code, target value, code subject
target value, code subject, moto: On this line you can add columns: Set rCheck = Application.Intersect(Range( O4:O191 ), Target) like this: Set rCheck = Application.Intersect(Range( O4:O191 , Z1:Z5 ), Target) Make sure the column you want to check is not one in which you ask...
Excel: VBA Help, excel, vba
excel, vba: The range C7 is hard coded, you can avoid it with something like: Selection.End(xlDown).Offset(1,0).Select ActiveCell.FormulaR1C1 = x This will move the selection to the first empty cell, and write the formula there. Hope this helps, Migue...
Excel: VBA Help, target address, xlw
target address, xlw, b70: Yes, you can make it skip the calculation if the cell is not B70 .. This will do the trick: Public Function SumByTeamNumber(Cell, TeamNumber) If (Application.Intersect(Cell, Range( B70 )) Is Nothing) Then Exit Function End If Dim...
Excel: VBA Last numeric entry, mycell, blank cells
mycell, blank cells, numeric entry: I hope this code can give you some ideas: For Each myCell In ActiveSheet.Range( A:A ) If IsNumeric(myCell.Value) Then If myCell.Value 0 Then curRow = myCell.Row End If End If Next...
Excel: VBA Last numeric entry, numeric entry, mycell
numeric entry, mycell, blank cells: Thanks for the information, but there are still some details that I would need to craft the VBA code. The first one is about the tables, what I have understood is that they have been created in advance, and the users will need to fill them; if so, I would...
Excel: VBA Macro - need it to run automatically, target address, dropdown boxes
target address, dropdown boxes, b97: Lynn, Yes, there is a typo in the code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $B$83 or Target.Address = $B$97 then If Range( B83 ).Value = 1 AND Range( B97 ).Value = 1 Then Range( B97 ).Value = 12 End if End...
Excel: VBA Macro Question, mycell, cell location
mycell, cell location, location range: Yes. MyCell refers to an object, the cell itself. In your code you are saying select the a cell that has a range name of mycell. All you need is mycell.Select And in case you might try it then Range(myCell).Select would, or should, not work either...
Excel: VBA Macro Saving a file to a directory through macro, cell reference, vba macro
cell reference, vba macro, na2: Assuming you have already created the macro and need this final piece then here is one way of many: Dim FN as String FN = CStr(Format(Range( C2 ), MMDD )) Use the FN variable in your code that does the actually saving. Since I have not seen that then...
Excel: VBA Macro, blank row, vba macro
blank row, vba macro, excel macro: Well, the macro can not run forever and it will stop eventually after it runs 16,776,960 times because it will run out of cells to look in if no limits are placed on it. Therefore SOMETHING needs to be in place for the code to look for and therefore for the...
Excel: VBA Macro, blank row, vba macro
blank row, vba macro, excel macro: I don t understand why you do a Copy and Paste Special as Values when you find HSGetValue in a cell. Since that is text then doing a copy and paste special as values really does nothing. I must be missing something or you have not told me everything. Are...
Excel: VBA ON PROTECTED WORKSHEETS & ALLOWABLE ACTIONS, inputbox, answers to questions
inputbox, answers to questions, mypass: Hello Roger, Thanks for the positive comment. If you only want to protect the cells and leave the drawing layer/objects alone then change DrawingObject to be False and you might as well make Scenarios false as shown below. Sub PROTECT() If InputBox(...
Excel: VBA Search, Match, Copy then Paste, c cells, srow
c cells, srow, b cells: Private Sub cmdSearch_Click() Dim DestSheet As Worksheet Set DestSheet = Worksheets( Sheet3 ) Dim r1 As Range, r2 As Range Dim r3 As Range, r4 As Range Dim cell As Range Dim sRow As Long source Row Dim dRow As Long destination row ...
Excel: VBA VLookup and cell formats, vlookup formula, layout location
vlookup formula, layout location, voice of experience: Sorry, as politely as possible, I don t write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. Perhaps someone else on this...
Excel: VBA Variable Range, heatrow, question pool
heatrow, question pool, concatenation operator: Karma, added: Karma, if you had used an & as the concatenation operator originally as I showed you, it wouldn t have made any difference whether the variables were dimensioned as a specific type. Note that in my sample code, my variables are variants,...
Excel: VBA Web Query - Overwrite Existing Data, cell a2, web query
cell a2, web query, joe joe: Joe, Each time the sheet is activated a new query is added. For whatever reason, the default action is to not overwrite a previous query. So there are two possible solutions. First, if it is the same query everytime then just use the line: ActiveSheet.QueryTables(1).Refresh...
Excel: VBA and email, de bruin, body cells
de bruin, body cells, basic excel: You can find the last filled row with lastrow = cells(rows.count,1).end(xlup).row You don t show your code or what your email software is, so I can t say for that .body = cells(lastrow, A ).Value & & _ cells(lastrow, B ).value...
Excel: VBA code for extracting unique Values, irow, search range
irow, search range, control button: Hi Benjamin, Here is some code I came up with: Sub ListUniqueValues() lists the unique values found in a user-defined range into a user-defined columnar range Dim SearchRng As Range Dim ResultRng As Range Dim Cel ...
Excel: VBA code for extracting unique Values, irow, search range
irow, search range, control button: Hi Benjamin, I just made a slight modification to my previous code, and I think that now it does what you want. Instead of testing whether there are multiple instances in the source range, I checked the destination range, and if COUNTIF indicates it is...
Excel: VBA code to record legnth of characters, character format, decimal format
character format, decimal format, a3: Sub checkLen() Dim checkCell As Range, alertCell As Range Set alertCell = Sheets( Review ).Range( d1 ) alertCell = PASS For Each checkCell In Sheets( Asset ).Range( a1:a300 ).Cells If Len(checkCell) 6 Then alertCell...
Excel: VBA code to run a nested loop row.delete, dummy data, call id
dummy data, call id, e phone: Kevin, I would like to search through the list and find a row with Pending Appointment or They will call us and Get the ClID for that row. I am not sure of exactly what the function process is here. Do you want to delete all call ID s for every call...
Excel: VBA code, pattern recognition systems, pattern recognition software
pattern recognition systems, pattern recognition software, seperate cells: Give me some time for this, might take a couple of days before I get round to it. ...Later... This macro gets close, as close as I can afford to spend time on it: Sub GetPerimeter() Dim oPerim As Range Dim oCol As Range Dim oRow As...
Excel: VBA command button to duplicate row and formulas, vba script, row selection
vba script, row selection, command button: Sub AddRow_Click() Dim insertBelow As Range Dim wks As Worksheet Set insertBelow = ActiveCell Set wks = insertBelow.Worksheet wks.Range( A6 ).Select insertBelow.Offset(1, 0).EntireRow.Insert insertBelow.EntireRow.Copy wks.Paste...
Excel: VBA copying multiple worksheet and naming them from a list, assumptions, kerry
assumptions, kerry, cells: Hi Kerry, Assumptions: You have entered the names in Sheet1 Column A Sub Macro1() m = Application.WorksheetFunction.CountA(Range( A:A )) For i = 1 To m Sheets.Add ActiveSheet.Name = Worksheets( Sheet1 ).Cells(i, 1).Text Next i End Sub...
Excel: VBA decision loop, c cells, decision loop
c cells, decision loop, b cells: Hi Dave, I m having trouble understanding what the problem is that you are trying to solve with this code. Part of this is my lack of familiarity with the concept of component spacing . But also I don t know how the data are organized on the two worksheets,...
Excel: VBA for Headers, sright, rev range
sright, rev range: Just make the range reference more specific , like sLeft =UCase(Sheets( Sheet1 ).Range( T71 )) & Chr$(10) & Sheets( Sheet1 ).Range( T77 ) & Chr$(10) & Sheets( Sheet1 ).Range( U74 ) or With Sheets( Sheet1 ) sLeft =UCase(.Range( T71 )) & Chr$(10)...