I have a question on the vba syntax. I have a column of data whose values decrease by 1 or so as I move down the cells. My data look like:
A B C D
999.1 111
1000 110
1001 109
And here is a code that I have written:
Code:
Sub ppm_sort()
Dim strrow, endrow, i, j As Integer
strrow = 1
endrow = 5000
For i = strrow To endrow
[Code]...
What this code does it, it first looks at Column B and pulls the values from Column A to C until the value in B hits 1. Then it calculates the average of the pulled values, after which it deletes the values in column A to C. If values in column B are greater less than 100, it does not calculate the average and just deletes the columns, moving the cells up. However, I ran into this problem:
A B C D
999.1 80
1000 110
1001 109
I still need to pull the values and calculate the average but since the value in B1 is less than 100, the code does not do so. So I tried to change the code colored red above to:
Code:
If (Range("B1:" & "B" & i).Value < 100) Then but excel gave me an error of 13 run time error. Basically, I need to figure out how to compare the values in a range to a number and combine if statement.
I want to compare cells and use a range of values to calculate totals (i.e. count if cell A1=boy and cell A2 equals either "fat" or "skinny")? Also, I tried using wildcard character "*" in the SUMPRODUCT formula, which didn't work (i.e. if cell A1="boy*" would return at TRUE if the cell contained "boy", "boys", "boy and girl", etc.). Is there a way to include wild card characters in a SUMPRODUCT formula like the ones described above?
In Worksheet 1, Cell B63 I would like to create a drop down menu, with two options for the user to select - 0.05 and 0.01. I would like each selection to then control the formula in the cells C63:L63, for example;
I was looking to use the VBA conditional formatting script posted on OzGrid and was curious if it would be easy to make some slight modifications.
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 ..........................
I can see that Case is the number range referenced to change the background...However, would it be possible to have it read a cells value as the criteria for the color change? Currently, I would like it to reference values in range I2:M2 and anything matching those cells in range A4:E28 change background to icolor = 30. I'm just not sure what to replace Case with to make it refence cells I2:M2
I have two sets of data to compare. One is real world data, the other is forecasted data. I have real daily data for every day over about 3 months. The predictions though, there may be 4-6 predictions for ever one real daily value.
What I'd like to do is have a function that looks at Column A's date (real daily value) and Column C's date (predicted data) and if Column C equals Column A, then divide corresponding data in Column B by value in Column D.
In the attached excel file I have two macros that create hyperlinks:
1. BalloonToNumber - Creates hyperlinks from "Oval" shapes on sheet "Op60_1" to sheet1 (column D).
2. NumberToBalloon - Creates hyperlink scheme from sheet1 (column D) to sheet "Op60_1" "Oval" shapes.
In Q #1 my macro seems to skip some shapes on sheet "OP60_1" and I simply can not figure out why.
In Q #2 my macro creates hyperlinks to shapes that do not exist and hyperlinks to numbers that are not a "100%" match, IE: 182 and 82 would share the same hyperlink?
If you open the workbook and use the hyperlinks on sheet1 they will take you to sheet "Op60_1" and shape hyperlinks from sheet "Op60_1" take you to sheet1. On sheet "Op60_1" there are some red arrows indicating the shapes that get skipped when running the "BalloonToNumber" macro. Shape color changes as the hyperlinks are selected from sheet1.....
So I have a sheet with 600 or so rows of data on a sheet called "Agent_Summary". On another sheet in the same workbook I have a list 20 peoples names. I am trying to find a way to look at the 600 or so lines on the Agent_Summary in column A and see if the name in that cell matches any of the 20 names from the "Team" Sheet. If the name on Agent_Summary is not found on the "Team" sheet then it clears the contents of that cell and moves on. Then it comes back and Deletes the blank rows. I can make it look for any 1 of the 20 names and the code works fine. How do I turn it into something that will look for any of the 20 names?
Here is my code to look for 1 of the 20 names that works great.
Code: Sub Clean_up_Agent_Summary() Dim Cell As Range Dim R As Long Dim RE As Object Dim Rng As Range Set Rng = Worksheets("Agent_Summary").Range("A5:A600") Set RE = CreateObject("VBScript.RegExp")
[Code]...
"Worksheets("Team").Range("A5")" is where my 1 name is and the rest are just below that. I am thinking some kind of an Array but I am stuck on getting that set up and working right.
i've put this previously on here with no results. There is a formula already in there though does not work because what I want to achieve is to have Column A & Column C to Match Column E & Column F, to Return the Value YES or NO. I’ve Highlighted in ROW 947 one that should Say NO yet Says YES.
I am trying to compare one range to another. If an item in one list is not in another i am adding this item to an array called addarray. The problem is when i try to access the array to see if one of its entries is "" i get a type mismatch error. My code is shown below:
I am working on a project where I need to remove records based on certain criteria.
The key pieces of information here are the Company ID, the Contact ID, and the Product Codes. Each Company ID can have multiple Contact IDs, and each Contact ID and have multiple Product Codes. (To be more clear, imagine one company with a Tax department and an Audit department - with each department having a separate contact person. And, each contact person purchasing multiple products.)
I have already summarized the data by identifying the unique Contact IDs and combining the corresponding Product Codes into a string.
Now, I need to determine if any Product Code within the summarized string matches a Product Code in a separate list (a Range of cells). If so, each instance of the Company ID needs to be moved into a different tab. Also, to understand the magnitude of my records, I am working with 25,000+ unique records in the first tab, 8,000+ summarized records in the second tab, and 50+ product codes in the separate range in the third tab.
My first thought was to use a For loop to determine if any cell in the Range is found in each string and, if so, add the related Company ID to a dictionary object. Then, run another For loop against the Company IDs in the summarized tab (8,000+ records) to determine if the ID exists in the dictionary and, if so, moving the row to another tab.
My second thought was using the original data (unique records) - I would load all the Product Codes in the separate Range into one dictionary and run that dictionary against the individual Product Codes in the unique records tab (25,000+ records). If the Product Code in the unique records is found in the dictionary, I would add the related Company ID to another dictionary. Then, I would run a For loop comparing the second dictionary to the Contacts IDs in the summarized tab and move the ones that exist.
In summary, the two ways I have come up with to accomplish what I need includes:
Using VBA, I want to import data from a text file into a worksheet, but only import a row of text if the three-letter code matches one I have in a named range. The data looks similar to this:
i'm trying to do some analyzing on an excel sheet and was seeing if there was a function that I could use to speed this up.
So in the file... I want to Group what's in Column A So the 1's together and the 2's together... Once that is selected I want to know what the largest number in Column C is...
So if the function can select 1 in column A then tell me that 10 is the biggest number in Column C....
i have a range of values and want to compare the value of a single cell to all those in the range, returning TRUE if a the value matches any value in the range.
I need making a macro which compares a particular range of columns in one sheet to other sheet in same workbook.
It is like i have two worksheets named "Working Copy" and "Cleaned Data - (Working)". In this I have to compare Columns (A:AG) from Working Copy to columns (B:BH) of Cleaned Data - (Working). and if there is any difference between any cell then it should be highlighted with a colour in both the worksheets.
I have a list of dates in Column A, prices in Column B in Excel. I would like to write a VBA code that compares Price #31 in the list to the previous 30 prices to see if the price has moved more than 8%. If the price has moved more than 8%, I would like the date(s) that it moved above the 8% level written in Column C. Then, I'd like the routine to move on to Price #32 and have it look at its previous 30 prices and do the same.
i have 2 workbooks attached, in the "work order masterlist 2006.xls" workbook, i'm trying to compare column F which is the R date to see if it matches the dates in the "2006 Calendar.xls". if it's the same date, i would like to copy from "work order masterlist 2006.xls", column C, which is the Client's name to "2006 Calendar.xls" under its appropriate date, assuming that:Dates of the months will not change column/row in the "2006 Calendar.xls"it will be an added bonus if i can copy and paste the description as a comment...
I am trying to get rid of some duff data by running a comparison to a fixed value on a defined range. However, the macro tries to compare the cell value (00:05:00) as a decimal value.
I think I need to use the format function to get round this.
For Each timecheckcell In range("g3:g60") timecheckcell.Value = Format(MyTime, "h:m:s") If timecheckcell.Value <> "00:05:00" Then timecheckcell.Offset(0, 1) = "" Next
I am having no success using either: 1) COUNTIF with wildcard (*) or 2) Case Statements to find text strings in a cell. I've even tried using Copy/Paste Special values to change the way Excel 2007 interprets the field.
The Do While loop looks at target cells in Col. 18 which contains one or more specific last names. I want to put a text string (also a name) in Col. 19 IF the target cell contains the last name in the COUNTIF or Case Statements. The COUNTIF normally puts a TRUE or FALSE based on the condition, but I'd like to put the the last name based on which condition is met and then continue looping to Row 3. It's a small number of rows, less than 100.
I am trying to find out how many weeks our current inventory will support our sales. I am trying to research formulas that will do this, and coming back with things like OFFSET, MATCH, INDEX but am not certain the best way to put them together to get what I need. I use excel daily, but this is a bit mroe advanced than I am used to
Mini.xlsx
Starting in cell B4, I would like to count how many weeks of Demand can be covered by the specific Available On Hands in row 3 for that week, put the number of "Weeks Covered" into cell B2, and then fill over to the right in row 2. Right now, the values in row 2 are from my own manual calculations, but I would like a dynamic formula that will sum up the values in row 4 up to (but not greater than) the value in B3, give the count of cells that reached that sum (or even better with decimals to show the percentage covered), which I will copy over into B3:B13. Not sure if that makes sense, or if I can explain in a better way. The yellow cells are what I am trying to create a formula for and am currently stuck.
I'm doing a study of wind turbines, which are spread over a test field. There are 6 turbines, each checked once a day and produces a value. I look up what the weather man says it should be, "Reported" field.
The "Compare" field values must match all values for each turbine. The speeds identify the location with the best match speed against output of power generated. As each value in "Compared" is the best speed for that type of turbine.
I am trying to compare vertical range "A" in sheet1 to vertical range "K" in sheet2 if they match to copy both rows and place in sheet3. I have tried the following but it did not work.
Sub Searching() Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet ' create short references to sheets ' inside the Sheets() use either the tab number or name Set sh1 = Sheets("Sheet1"): Set sh2 = Sheets(2): Set sh3 = Sheets(3)
I am trying to figure out a formula that will allow me to compare the contents of cells within a range and let me know if there is a value that is not the same as the others. For example: Columns a,b,c,d,e and f and say "Joe"- this result is acceptable Columns a,b & c all say "joe" and d,e,&f all say "Mary"- this result is unacceptable
I want a formula or function that will tell me this wihout me having to visually scan the data to determine this.
I would like to rotate a Line Chart 90 degrees. I tried using an XY scatter chart but my Y-axis would be time values which are somewhat random but increasing. I would like to keep the spacing between plot points consistent (Y-axis spacing). I could simply use a number list for my Y-values to get consistent spacing but then I lose the time information. I don’t necessarily need the time value on the Y-axis if I could get the information to display when mousing over the plot point. Any ideas on how I could accomplish this?
I have an unknown number of rows that represent parts and 6 columns that represent part dimensions. I need to find and output matching pairs of parts that are within certain dimensional tolerance ranges.
For example: Dimension 1 Dimension 2 to Dimension 6 Part 1 Part 2 to Part X
-I would need the code to check if Dimension 1 of Parts 1 & 2 were within a certain range and if Dimension 2 of Parts 1 & 2 were within a range etc. then (if all of them were true) output them as a pair. Otherwise, it would continue to search for paired parts. Once a pair is found, those parts can't be used again. How can I do this?
I have two cells which contain text, and I'd like to use IF function to compare between them. Something like: =(IF(A1=B1),"TRUE",FALSE"). But it seems like the function cannot do it with text..
I would like to compare 2 cells, Sheet1!B2 and Sheet1!B3 ( If sheet1!b2 is between the values of "D101" AND "D105", OR EQUAL TO "D201", OR EQUAL TO "D203", OR EQUAL TO "E110", OR EQUAL TO "E111", OR between the values of "H107" AND "H110" OR between the values of "H107" AND "H110" OR between the values of "H203" AND H205 ) AND ( if sheet!b3 is EQUAL TO "E" )
then output a 1, if not, output a zero
The formula, which I deleted, excel reports back as being too long
I want to compare the value of D8 to the value of the last cell in Column D with data. I have the following code but am stuck! (This is code that I was writing from scratch and may not actually work)
Code: Dim myRng As Range Dim C As Range Dim lr As Long
On Error Resume Next
[Code] .........
I am stuck on the "Else" statement...If they don't match then change the color of cell D8 if they do match I want to do nothing.
