I have a report with details for a large number of countries. Each month, I need to copy the country details and move them to a sheet specific to that country,
e.g. UK details to a UK folder, Germany details to a DE folder. The level of details available is different for each country. So there may be 10 lines for the UK but only 5 for Germany. However, the country details always start with (e.g.) 'COUNTRY: UK' and end with ' TOTAL FOR: UK.'
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies View RelatedI am trying to send emails using a table in Xl2002 via outlook2002.
The code below is from Walkenbach's VBA Bible for Excel 2003 and I am getting "Uesr-type not defined" error with the 1st Dim statement.
Do I need a different statement such as set OutObj = blahblah or similar?? I have searche dthe net hi & lo for a solution to this problem & I do have JW's excel VBA bible.
Sub SendEmail()
'Uses early binding
'Requires a reference to the Outlook Object Library
Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Dim Bonus As String
Dim Msg As String
I am using the following formula to find out the No. of "HH" in the range given and I am getting the results simply with numerical value as 2, 3 etc. I want the result should be displayed as 2HH, 3HH depending upon the No. of "HH" available in the given range. The result should be displayed as (the No. added with the text "HH").
=IF(AND(DJ10="HH"),COUNTIF(DF10:DJ10,"HH"),"-")
I use excel 2002.
I am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is
Dim XColumn As Range ‘the cell containing the text “X”
Cells.Select
Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set XColumn = ActiveCell
I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros
I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
Software: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
Is the Hlookup funktion not included in Excel 2002? I've been trying to run basic exemples from microsoft, and ones made by myself, and I just can't get it to work. Excel just keeps telling me that's it wrong in the formula. I really need hlookup work.
View 13 Replies View RelatedI hate this toolbar.
I have no need for it but would like it to remain in the list of available toolbars (eg when i go View>Toolbars).
When i uncheck it from the list it disappears (hooray!) but the little b*stard is always back on my screen the next time I open Excel (this is in contrast to any other toolbars I hide/make visible which are always hidden/visible as expected - hence I don't think it's an .xlb problem).
has anyone got an explanation as to why and, better yet, a way to get the problem child off of my Excel (without removing it from the toolbar menu list)? Because I'm at work I can't modify Registry settings by the way.
Is it true that the sheet protection in excel is practicaly none ?
Not even excel 2002 (XP) prevents simple cracking software from removing sheet protection.
Are there any master keys that cause that ?
Is there a way to protect a sheet with strong encryption ?
I have a workbook in xl2002 that has a data sheet with approx 50 columns and 500 rows which is populated from a querytable querying another workbook. There are no formulas on this data sheet.
I have another 5 sheets, only one of which actually has formulas on it and these refer to the Data sheet above (some fairly beefy Sumproduct formulas in the main, but only 1056 in total (only about 650 of these are Sumproducts - the remainder are simple = A1 type or Match formulas with the third argument as True (ie quite efficient)).
There are also 3 dynamic named ranges in the workbook, which are utilised by the Sumproducts/matches. An example Sumproduc is as follows:....
I have excel 2002 XP Professional, and the problem I am having is that Excel keeps saying I am using 65536 cells but I am only using 1406, I have tried pressing clear contents on them and delete, but this only makes it lag for 15 minutes and still says the same.
Code:
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?
I am using excel 2002 and I have found that when the workbook is shared and getting 2 or more users to open the workbook around the same time brings up a read only type prompt box for the 2nd ( and 3rd) user when the book hasn't fully opened for the 1st user/is still fully opening. Is this a limitation within excel or is there a way around this for example via vb code.
I know the more data a workbook has can cause it open a lot slower but is this the same for shared mode.
In a worksheet the data (text or numerical) from five columns are compared using the following formula.
=IF($A10$G10,"1ERROR",IF($B10$H10,"2ERROR",IF($C10$I10,"3ERROR",IF($D10$J10,"4ERROR",IF($E10$K10,"5ERROR","-")))))
The formula is working well. The problem is I get the result for the first column of error even if the error is in more than one column. I want the result should display all the errors with the column number 1, 2, 3 (or with column name A, B, C etc). The total number of error is not required. The exact columns which have the error should be displayed. For example if the error exists in 3, 4 and 5 columns, I should get the result as "345Error" or "CDEError".I use excel 2002.
I've got Excel 2002. I am devising a sheet for checking work (sheet A) which feeds onto a separate sheet (sheet B). If I wanted to delete info from sheet A can I retain the info on sheet B? I want sheet A to be used repeatedly for each case but the info for every case to be recorded row by row on sheet B.
View 4 Replies View RelatedI'm currently trying to set up a spreadsheet on excel, when certain documents need updating. Basically I have a column with the dates in that each document was last updated, I want the cells to turn red (fill colour) if the document has not been updated within the last 6 months and orange (fill colour) if the document has not been updated in the last 4 months. How do I do this?
I am currently using Excel 2002 and am on a Windows XP computer.
We're gradually moving over to Excel 2007 (from 2002) at my workplace.
My problem is that I need to run macros on both versions but I'm having trouble with the SaveAs method.
I have Excel 2007 set to save automatically in Excel 97-2003 File Format but this doesn't seem to apply to files saved using VBA.
My original 2002 code is as follows:
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:ReportsNewFile.xls"
and 2007
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="C:ReportsNewFile.xls", FileFormat:=xlExcel8
How can I get it to switch depending on the version it's run on? Unfortunately Excel 2002 doesn't recognise '=xlExcel8' so I don't think I can use an If/Else statement on the version number.
Need the formula to turn a date into a quarter and year
1/15/2003 would be 1Q2003
5/15/2005 would be 2Q2005
11/10/2007 would be 4Q2007
I am using the following code which works in microsoft 2002, but not in 2000. When I run it in 2000 it says " Named argument not found." The debugger highlights the line Set C through delete.
Sub DeleteRows()
Dim c As Range
With Worksheets("Sheet1").Range("A1", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
Do
Set c = . Find(What:=" ", After:=[A1], LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then c.Resize(12).EntireRow.Delete
Loop While Not c Is Nothing
End With
End Sub
Excel 2002.I have data in columns A and B on sheet 3, starting at row 100. The total number of rows of data is variable (max is 50 rows). I want to take the values in A100:B100 and copy them from Sheet3 and paste values transposed to SheetTL Range C1:C2. I have started to write the following code but as you can see I will have to repeat the code upwards of 50 times. It seems I should be able to do a COUNT function to see how many rows of data exist and then somehow loop the code to copy the data from each subsequent row that many times, but how to put that together.
Sheets("Sheet3").Range("A100:B100").Copy
Sheets("TL").Range("C1").PasteSpecial Paste:=xlPasteValues, Transpose:=True
ActiveSheet.PrintOut
If Sheets("Sheet3").Range("A101") = "" Then
[Code] .........
I have a workbook in which there is a sheet containing a table.
Excel 2002ABC123Specific Gravity6 Volt ValuesSOC40.0000.000Dead
51.1345.97010%61.1485.99020%71.1626.02030%81.1766.06040%91.1906.10050%101.1976.12055%
111.2046.14060%121.2116.16065%131.2186.18070%141.2256.20075%151.2336.23080%161.2416.26085%
171.2496.31290%181.2576.32095%191.2656.350100%SOC Chart
From another sheet I query this table using the LOOKUP function to obtain values from column C such as 25% 60% and so on. A typical query formula looks like this:
=LOOKUP(B2,'SOC Chart'!B4:'SOC Chart'!C19)
It would be nice if the fill color came along with the value.
I found in an old file that i triggered the macro by clicking a button! I would post a pic but i don't know how. How do I activate a macro by clicking a shape on the excel 2002 page?
View 7 Replies View RelatedBasically, I have an excel spreadsheet with different valued items for example,
TV Essential l £15
TV Essential extra l £20
Broadband Essential l £ 8
Broadband Extra l £12
I have options for TV, Broadband and Phone. At the bottom of the spreadsheet I want three drop down lists to pick what TV, Broadband and Phone package I want. For example: TV Essential, Broadband Extra and Phone Weekend. I can create the lists but I was wondering if there was anyway where, as you select the option on each of the lists, a running total will be created. So when TV Essential is selected £15 is added to a cell, say F20. Then when Broadband Extra is selected another £12 is added to cell F20. And as you select different ones in each list the total in F20 changes.
I'm using Excel 2002.
what format is used when you save an excel file as "unicode." I am using excel 2002 on XP.
I have been asked to provide a UTF-8 formatted unicode file for use by another program but I am not sure which formatting excel uses.
I've got some code which some one posted me a link to on here which set up a pivot table on more that 1 sheets (using excel 2002).
I inserted this code into a rountine I do daily which works fine.
The problem Im having is that Im getting more and more info which I need to pivot over more than one sheet. So I was going to amend the rountine so It picked what ever sheets are in the work book to pivot.
At the moment I have to go into the code and name the sheets to pivot. Is there some code which can work out how many sheets that need pivoting and just do it with out naming them? and also just add a new sheet with the pivot on rather than having to name the destination?
I've posted the code i got given below and have highlighted the bit where I have to name the sheets.
Code:
'---------------------------------------------------------------------------------------
' Procedure : CreateConnection
' Author : KL (Kirill Lapin)
' Date : 18/08/2009
[Code]....
I have columns of figures with times like 5:52:54 and next to them cells with values that i wish to format based on the time.
that is if the time on the adjacent cell is before 8AM then make the value cell have a red border it does not have to be conditional formatting - Excel 2002 in win7/64
I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
I have problem to change text code into numerical code using macro. i have data contain text code and i would like to convert it into numeric . each text code has dedicated numerical code for example I have 4 fluids with text code text code:
FW = Fresh water
SW = Saline eater
CW = connate water
MW = Meteoric water
numeric code as follow
FW=1
SW=2
CW=3
MW=4
I would like to convert the text code into numeric code, it is easy if using excell , but it is routine job for me , i need to create macro to be more simple .
Excel 2002
I have a simple spreadsheet and am trying to get some Conditional Formatting to work.
Cell1 = Name
Cell2 = 1
Cell3 = 2
Cell4 = 3
If cells 2, 3, and 4 all match I want the background in cell 1 to change to green.
I've tried (Formula Is =B1=C1=D1 green) with no luck.
I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
[Code].....