I got a cell value and I want to check if it exists in another table. Problem is the second table splits the value and moves the last 2 chars on the right into the next column like this :
4112JJPV
4112JJ PV
Ive tried using vlookup with a LEFT(cell1,6)
and a RIGHT(cell1,2) in place of other values but im having no luck.
I need to check for the first 6 characters then if the next column the value is equal to the last 2 character on the right I would like a True value returned.
I eve tried IF(AND(COUNTIF( but im just getting errors ect
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code: Sub Tester()
Dim WB As Workbook Dim WS As Worksheets Dim modCounter As Long Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm") Set WS = WB.Sheets(BM18)
I have a table in excel range B5:M32 with the top two rows containing merged cells (B5:M6). I need to write a macro to copy data from cells B38:C38 into the above table.
I need the macro to first check to see if there is any data already entered i.e. check if the table is empty. If it is empty then paste the cells in the first cell (B7). If the table already has data entered i need to move to the last entry then move down one cell and paste the copied cells.
I have figures out the copying/pasting functions but am struggling with the part to check if the table is empty and moving down the table without using cell references.
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
I have a piece of code that duplicates all of the values in the page fields of one pivot table onto a series of other pivots tables. These can each have multiple selections
The only trouble that I am having is that one of the page fields has about 800 items in it, so looping through all of the values to make sure that they are checked, and then copy those selections into the other pivot tables is more than a little resource heavy.
I know that you can use a piece of code to see if a particular pivot item has been selected...
Code: sheets("Dashboard").pivottables("ptcontrol").pivotfields("Year").pivotitems("2013").visible .. but I want to know if "(ALL)" has been selected (without looping through every value). When the option to select multiple items is selected, the "currentpage" also doesn't work - it will always return with (All):
Code: sheets("Dashboard").pivottables("ptcontrol").pivotfields("Year").currentpage so... any takers?
This is my whole piece of code - I have changed the font to red for the bit that loops the pivot item values.
Code: Sub pt_db_filter() Dim wsMain As Worksheet Dim ws As Worksheet
In Column C I need a "yes" or "no" answer to if the country code in B is listed in column A. I have 700 orders from country codes and need to understand which ones I need to charge VAT.
What I have is a table of employees names, which site they work at and a comment.
I have a table on another sheet with the addresses of all our sites.
What I need is a macro which will: >look at the active selection >copy their name(sheet 1, row B) & comment (sheet 1, row G) >look up the site they work at (sheet 1, row D) >copy the relevant address (sheet 3) >paste the name and addresses into different sheet >paste the comments into another sheet
This will be used to make printable labels for letters that we post out to employees. They will need to be split up so each name and address is printed on a different label.
The comments will be printed onto letters which will have a headers which is the same for everyone, but would be nice if they could be personalised with the comments of their managers and perhaps their names (something like "Hi John, you have been selected by your manager for your great work, please find enclosed your badge. Jeff's comments: "Comment 1""). But this part is a nice extra, the important part is the address labels.
The idea is that the pivot table always displays yesterdays data.
However, there may not be any data for that day, so when i tell the macro to filter for it, I get an error.
My question is, how could I formulate some sort of iferror statement so that if yesterdays date isnt in the filter list, then it does nothing (by default leaving the filter on last populated date)?
I am building a table for staffing purposes, and I basically only need to controls to "validate"/approve the table.
1. The number of staff put in must equal the required amount of staff (E column) 2. The same staff cannot be put on twice for the same time period (which I have done by the OR(F7=G7,F7=H7,G7=H7) as part of the validation)
My problem is that sometimes I need 0 staff (when its closed), but when I leave it blank obviously it wont approve the table because of rule #2 above, since it thinks the blanks are the same staff member.
I have tried with some ISBLANK statements, but it wont work.
Please see attached the Workbook. I need to check the policy Numbers in Column A to be present in Access Table. If yes then write the corresponding ScanDate and BatchNo in columns I and J.
I am trying to automate creation of pivot table, where the first three fields will go into the RowLabel field, and the rest of them will go into the values as "Sum of ____".
Is it possible? Because I could do it one by one; however, those fields are different every time (there are different number of those fields, and their values are also different).
I have problem with function, I have array with random numbers in cell G1 and columns with X in some of the cells, now I want to check the cells with numbers from cell G1 in column F to see is there X or not if yes count if not do nothing. In attached example result should be 3.
how to explain this except by showing it, so I explained it as best I could in the attachment. Anyone think they have a solution for this? Let me know if you need more information. Thanks!
the macro will look at the value I entered in cell A1, then find all instances of it in Column A in every row that A1 matches, if the value in column E is "0", I need the macro to change the value in column E of that row from "0" to (that row's column B * $B$1)
My company uses 4 types of barcodes 8, 12, 13, & 14 number barcodes for our products my problem is that I can't figure out how to force the barcode to format properly no matter how somebody enters it if they don't use spaces or put them in the wrong spots, I can't use custom formats because there is 4 different layouts
8 digit should be "#### ####" 12 digit should be "###### ######" 13 digit should be "# ###### ######" 14 digit should be "# ## ##### ######"
these barcodes are in columns L, M, & N also right now 'm using a formula in another cell to verify the barcodes by calculating the check digit and comparing it to the check digit typed the formula i'm using is
sheet 1 contains student data, name dob, actual age, raw score and an empty column standardized score
sheet 2 contains a conversion table, using the actual age from sheet one you locate the age in the top row of table in sheet 2, and using the raw score from sheet 1 you locate the matching raw score from the first column of the table in sheet 2, going down and acrosss until you meet this gives you a standardised score
i want the standardized column in sheet one to fill by using a formula which looks at the table in sheet two locates the two values and returns the result.
If you have a data in two dimensional table. and you need to fill each cell with the value returned from another table.
Example : this the my table
Company A Compay B Company C Product 1 Product 2 Product 3 Product 4
and this my data :
Products Companies Price Product 1 Company 8 Product 1 Company A 7 Product 1 Company B 5 Product 1 Company C 6 Product 2 Company B 6 Product 2 Company C 9 Product 3 Company A 10 Product 3 Company B 8.8
I want to check the contents of all the cells in S Column to see if it equals "Process Recover/Rebill"
If True, then Change corresponding cell in C Column to "Manual Correction"
I have this VBA and it doesn't look like anything is processing. It appears that I must have something missing in the If line..message about missing.
(tot_new is the number of rows of data) I begin in the 3rd row.
For Counter = 3 To tot_new If Range("S" & Counter & ").Value = "Process Recovery/Rebill" Then Range("T" & Counter & ").Value = "Manual Correction" End If Next Counter
I'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G) I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong.
Sub RatingsFix1SP() Dim FindWhat, rngCell As Range, i As Integer FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+") For i = 0 To 3 For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp)) If InStr(rngCell, FindWhat(i)) <> 0 Then rngCell.Offset(0, 6) = 0.15 End If Next rngCell Next i End Sub
To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%. It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.
I am using a menu button to run a macro. Before running a particular Macro, I would like excel to check if the cell A1 of a worksheet named 'MC_1921' contains the phrase 'Master Curve' ...if it does then the macro runs otherwise gives an error message.
I'm trying to write a vba code that will do this actions: the code will check if a text in cell b2 and cell c2 identical , if it's true, insert a new column d, and the value of d2 is the sum of cells b2+c2. if false - do nothing. I need to run the check, from row 2 to the last row with values in this sheet.
I am using the forms toolbar. I am just trying to place a simple check box in a cell (assigned to a cell). After I place the check box in the cell, if I resize the cell, the check box doesn't follow or stay assigned to the cell. I go into format control options and try to select "move and size wtih cells" but that option is greyed out.
a code that can check the values of a row, and if it is equal to zero, or has no value in the cell, then delete the entire row. Let me know if you need more info or anything.
