i am using VBA to handle a pivottable in excel. i am using pt.PivotFields("Position Status").Orientation = xlHidden to hide a field. but what if this field is not there in the first place? how should i put a "if" clause so that if this field is not there, i can skip this and go to next action?
I have a worksheet with a several pivot tables on it and this number can vary. I want to do, is check if a table with a given name exists, and if it doesn't create it, but if it does, to just .RefreshTable. I tried to use
On the user input page, there will be two fields to enter information in... one for an exact address (ex: 600 n. nowhere) and one for a casual address (ex: On Nowhere street south of Anywhere Blvd). On the records page, I'm going to keep all the addresses and their corresponding casual addresses so that in the future, I don't have to keep inputting both.
What I'm looking to do is have a formula/function/vba code check the exact address against my records for it already existing -- and if so, pulls the corresponding casual address. For example, if last week I entered 600 n. nowhere and then transferred it into my records and then this week, I again entered 600 n. nowhere, the workbook would find the record and place the casual address for me.
I am trying to check for the existence of a record in an access table and do one of the following:
1. If the record exists, overwrite it in the table. 2. If the record doesn't exist, add it to the table.
I'm having real difficulty with the VBA code to check an Access table for the existence of a specific record, and then overwrite. I can do it with dates, but I'm looking for a unique record ID "300112_1_II" and it's trowing up a "Type mismatch" error.
For testing purposes, I'm using the following parameters:
Field = Field_1 Table = TBL_1 Record ID = Test_Record
Basically, I want to check Field_1 for the existence of Test_Record and then either edit the other fields in the record or add a new record, depending upon what it finds.
I use the code below to check and see if a sheet exists or not when a workbook is opened. If it does not, continue. If it does I need to run a different sub on it. Twist, I need to check for a hidden sheet. How would I chg the code to do this?
Private Sub Workbook_Open() 'Also need to check the code in the userform. Has On.Time command. 'SplashForm.Show Sub Sheet_Test_1() Dim sh As Worksheet On Error Resume Next Set sh = ActiveWorkbook.Sheets(" total") If Err.Number <> 0 Then MsgBox "The sheet doesn't exist" Err.Clear On Error Goto 0 Else MsgBox "The sheet exist" End If End Sub
I receive thousands of raw data every week as shown in the excel sheet {raw data}
I would like to use a macro that could automatically sort the raw data like the format in the excel sheet {template}
For example:
Looking at the raw data and compare with the template, i would have to delete the whole columns that have (BU_CODE , EAN_CODE, SUPPLIER_CODE, SUPPLIER_NAME , BRAND_NAME, and SUB_CATEGORY_CODE) and add in columns and name it as Principal, Cateogry , Brand and range).
I have a worksheet for a mailing list with name, address and other information. I need to search for the text "board" in the name column and if it is found, add a code to a different column.
I have a worksheet that has over 5000 rows - what i need is to find all rows that contain " Total - content targeting" in column 3 & add the number on each corresponding row (column 3 from text match). Below is what i have managed to create,
I have two worksheets. On one worksheet will be a place to enter a five digit number. In the adjacent cell, I want to search on the second worksheet in a three-column area for that same five digit number. The five-digit numbers on the second worksheet are located in the third column and the text I want returned from the second column. I cannot change the order of the columns as the first worksheet will be an "add-on" worksheet to a widely used spreadsheet within our company (this spreadsheet already has the second worksheet formatted).
I want to create a column where the user can input ANY data (number, symbol, letter, etc.) and create a sum. Basically, I want to be able to add "checkmarks" where the checks have a predetermined value based on the column the check is placed.
if there's a way I can verify that a custom add-in is active? My company uses an add-in to allow end users to construct several types of API calls. My program depends on that add-in to be installed. I'd like to try to detect the add-in and halt the program if it's not there.
i have two column of dates A & B as shown below.Among these two columns i want to check the existence of a date range 21/08/2009 to 25/08/2009.I want to check where-2 this date range is existing in the given column ....
I have a Formula in Cell A1 and I am entering a Vlookup in D1, I get a #N/A in D1. If I enter for example Monday in Cell A1 instead of the formula, Vlookup works. My question is, will it not work in a Cell with a formula?
I have to look up a specific SS# on three different sheets of the same workbook, because each of the sheets has more than 65536 rows (it is really one list but has to be divided up due to the size). How to make a vlookup formula to look on each of the three sheets? The sheets are named 2-16, 17-33, 34-63.
Have spent ages on this to no avail. Need the attached sheet to verfiy the values in the "Reconciliation" are correct when referencing the "Prices" sheet. I need column F (Reconciliation sheet) to lookup the "Code" in column B in the "Prices" sheet and then further look up the "Payment Frequency" (column D in the Reconciliation sheet) in the "Prices" sheet to verify that the price is correct.
Due to slight decimal point issues, if the value has a greater than 0.02 difference then display "ERROR" otherwise 0.
I have a sheet in which some of the cells have two strings separated by a linefeed. I have come up with a cumbersome formula which will let me check if either of the two strings is a member of a list stored on another sheet. However, it fails if there is only one string in the cell, presumably as there is no linefeed for the formula to find. How can I modify the formula to cope with this situation?
There are also on occasions, three strings in the cell, but I can't seem to access the middle string with the formula. Simplified spreadsheet attached to show the problem. This must be formula-based, as we have a no VBA policy. If you think there is better way of doing this, please let me know.
I have a formula that counts if a date range is present. However I need to change it to count another column only if that date range is present. For example a17 a50000 the user will enter the date of the order. and in column B has the order number. I want the formula to count the order numbers for a data range in column A. Here is what I have but it is counting the dates in col A not the order numbers in B?
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
I have 3 columns, C1, C2, C3. I have to return a value for the following conditions
1. Check for the presence of a particular text in the C 1 (duplicate values can be present) 2. Check for the maximum value in C2, if the second for the particular text from the first column (duplicate values can be present) 3. If the text is found, I need to return the Maximum value from column 3. Else I need to return 0.
My formula has been returning zero despite the fact that I have a larger valid value in column C3.
The formula(s) I have been running is 1. IF((AND(C1 = "E", Max(C2))), (C3),0) 2. IF((AND(C1 = "E", Max(C2))),MAX (C3),0) (In case of duplicated in C3) 3. IF((AND(C1 = "E", Max(C2))),MAXA(C3),0)
For all bank transactions I've downloaded, I would like to add a subject. The list contains many hundreds of rows, so I tried to make a macro for it. Unfortunately I cannot manage to search if a cell contains a certain text string. I came up to:
Can the below be achieved with any formulae or macro -
I have 2 worksheets (sheet1 & sheet2). In sheet2, i have some colors in column A (e.g. "red" in A1, "blue" in A2, "green" in A3.
In sheet1, i have some text strings in column A, e.g.
A1: I love red color A2: my pen in blue A3: Green grass A4: Orange juice A5: I like red and blue
What i want in corresponding cells in column B, is the name of the color which is in column A. so my output should be
B1: red B2: blue B3: green B4: "NIL" (any error message) B5: red (even though it contains 2 color, i am happy to show any one of the colors it contains)
I want to write a formular to initiate an action if a particular text is not in a list For example, I have a list in column D1:D50, and I want the following to happen in say cell A1
If "apple" is not in list D1:D50, then in put "N" in cell A1, else input "Y".
