I am in the process of converting my programs over so as not to use these and I am already seeing a drastic difference in speed.
I have run into a problem that I need help with. I have a spreadsheet similar to the example below. The first column contains a list of college majors and after each major is a row of classes that are required by that major.
The first thing I need to do is search down the first column for the major. Once the major is found I need to search across the row for the class. I am having trouble searching across a single row for the class.
Here is the section of code where I experience the problem
The file is sorted A-Z, but if you scroll down towards the bottom, you will see that some values are out of order. Both instances of University of Central Florida and University of North Dakota are out of place.
University of Central Florida should be between University of California and University of Colorado.
University of North Dakota should be between University of Colorado and University of Southern Mississippi.
I am trying to work out an excel workbook for calculating family recipes. I have gotten most of it figured out but am having a problem with one thing. When the serving size of an item is 8 oz & the recipe calls for 14.5 oz the only way I can get it to work properly is to enter 1.75 in the serving size.
But I would like (actually need) to be able to enter the 14.5 and have it calculate the calories correctly - since sometimes the amount to be added to the recipe may be 15 ounces and the serving size is 8 ounces etc....
I have a rank formula that is seemingly erroring out on certain cells - indiscriminately. I have tried and tried to figure this out for myself but this is beyond my knowledge.
Only on some cells is the rank not working and returning a "-" (the error result).
I want the rank to use absolute numbers, so that it will rank according to variance or percentage (regardless of +/-). I am also using another cell (R1) to tell the rank what column to use.
As you can see there are a few entries, both + & -, that are not being included in the rank - for reasons beyond me.
I have 6 categories that need to be ranked 1-6 in order of highest number of occurances. My ranking formula is showing 1-7, missing number 3. I have attached a sample worksheet further showing what I am trying to explain. I need cell A3 to show a ranking of "3". Currently it shows "4". What am I doing wrong??
I have a form with 2 buttons and a frame. Inside the frame I have another button. I get a Type Error Message when I run the code and I don't understand why.
I have the following two codes on a userform, The first code performs two steps:
The first step is running the second code (MyDate), which is used for checking the entries in the userform textboxes and if there is any wrong entry a message box appears then it exits sub.
However, what happens when it finds any wrong entry is that it displays the msgbox and instead of exiting sub as it is requested in the (MyDate) code, it resumes running and moves to the second step and adds 1 to ComboBox1 ListIndex.
I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.
Dim cnt1 as integer, cnt2 as integer,cnt3 as integer
cnt1 = 2 cnt2 = 50 cnt3 = 2
dim myrange as range
set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))
I have no idea why it won't work. I'm basically searching a columner range of cells.
The code below is supposed to insert a column and rename it. However, when I debug, the code only renames the column, it does split or run the loop. I press debug again, and then code executes as it is intended.
I can't explain why I have to press debug twice for in order for the code to work properly.
Code: Sub renameColumns() With Sheets("byPosition")
I've been working on a particular spreadsheet and I've just noticed that double clicking the format painter will only format one cell. It won't retain the format to be put into the next selected cell which it should when its double clicked. The same goes for copy and paste. Normally once you've copied something you can paste it into a number of cells individually, one after the other, but after the first paste selection, the paste option is no longer available. I can't see any settings under options to fix this. What has happened, and how do I fix it? (I'm using Excel 2003 SP1 - is there a SP2?)
I thought I had been able to use array to have more than two criterias with Autofilter, but now I am unable to make the following code work. What I am trying to do is have all the records that does not contain either N/A, S/O or xx and also have a filter on column 125 for the value "OUI".
VB: Sub test() Set ws1 = ThisWorkbook.Sheets("SOMMAIRE_EN_ALL") Set ws2 = Workbooks("Fichier_central_2013_anglais_2_CLEAN").Sheets("DETAIL_CONCAT") Set r = ws2.Range("A1:du4783")
In the attached sheet, one macro is developed which is not working properly. My req is to convert date from dd-mon-yyyy to yyyy/mm/dd format. (23-jan-2010--> 2010/01/23)
If incomplete date is there, then hyphen should place the gap i.e.
For date like APR-2014------> Output should be 2014/04/-- For date like 1998 -----> Output should be 1998/--/--
The below macro works only for row number 11, 15 and 16.
I'm using the following code to try to ensure a value is entered into a text box, but when I click over to the next text box skipping the first one entirely, I don't receive any message indicating the previous box is empty. I'm trying to make it so certain fields are required and others are optional. Here's the code I'm currently using:
I have entered a custom sort order under 'first key sort order,' but the field is not sorting according to the list.
I'm sure I entered the list correctly, because I actually entered it a long time ago, and it was working for a while. However, today the underlying data was temporarily erased, and the pivot table, when refreshed, basically went blank. I restored the data and refreshed the table, but this field is back to sorting in the wrong order. Refreshing the pivot table and re-choosing the appropriate sort options do not work.
Had been using this formula for almost 3 years, recently the formula didn't work properly as the range goes down halfway only instead of to the last data in the column. I'm using Excel 2010 now.
Does anyone have any links to using .find object.find range.find properly? or can explain it?
Basically I have a column of unique numbers... and I have to offset to find what I'm looking for.. however it cannot be done with vlookup because i need it to also go down the column (variable number of entries) to retrieve whatever is under that entry.
The statement below was working perfectly until I added the last argument highlighted in red. Also, the statement in red works as expected when used sepeartely. It doesn’t work when combined with all the other proceeding IF statements.
Additionally, all the values that I’m testing with my IF statements are being populated using vlookups. Again, the statement works fine but I just can’t put the last statment (in red) with all the others and get it to work.
I have two worksheets named "Data" and "Product". I want to use the following macro to sort the records depending which sheet is active. If I am in the "Data" sheet, it does the sort in the "Product" sheet? I was expecting it to ignore the second IF statement when I am in the "Data" sheet?
I wrote this short program that parses out last name and zip code from one cell into two cells so I can map the sales data. I flagged each sale as either member, non-member, or neither, based on the prices of the items (members get 25% off). I used the case statement below.
the problem is that for one of the sale items it's getting flagged as "neither" despite being at the members price level. it works for that same item in other records. the quantity at error point is 3 for two of the errors and 6 for the third, but the strange thing is it works for a few other sale records in which the person also bought 3 of that item (the quantity is not the culprit, or sole culprit).
Here's the code. Obviously Price and Quant are the two variables involved. I declared Price as a double and Quant as an integer, even though both have two decimal places in the data file. That way the product of Quant and each items price for one and Price will both have only two decimal places even though it was rounding off that product to two decimal points without declaring either variable. I cut out some of the case statement so as not to exceed the character limit.
How should my multiple If And Or statement syntax read?
On first button click If "BUY" or "SELL" is in column 83 then copy as the code indicates to sheet "Orders" as the code indicates. This it does.
On second button click don't copy the same "BUY" or "SELL" that has already been copied to sheet "Orders".
So the Countif should set J to greater than 1 for the previous copies and be ignored and only copy from sheet "Main" the new "BUY" and "SELL" that show up.
I'm getting double and triple copies of items already copied that should set J to > 1.
Code: Option Explicit Sub BuyCells() Dim c As Range
I have the macro below that I want to use to conditionally delete all columns where the first row of each column does NOT have the following values but it seems to be deleting most of these columns as well all the others.
Fellow Excellor Sub Deletecolums_Conditional() Application.ScreenUpdating = False
creating a multiple if statement. I have 3 columns in Sheet 1 called "Raw Data". Column A is dates, Column B is Clients and Column C is Products. I want to count the number of products sold to a specific client on a specific date. In Column A, the are multiple dates repeated as there are different sales reps so i have the dates repeated several time.
the problem is that i keep getting 0 as a value. If i remove the Date as a variable, the formula works. I just cant seem to count multiple criterias if the date is one of them.
In the below code I am trying to Freeze the panes on "B3" and Zoom out of the page to 90%. I keep getting errors on these two lines and I believe I am not coding these lines properly. (Errors are occurring in the "With" statement with all the PasteSpecials)
Code:
Sub Export() Dim LastRow As Long Dim TabString1 As Variant Dim TabString2 As Variant Set NewBook = Workbooks.Add'Â Â Â Â With NewBook'Â Â Â Â Â
I have dates in column A (Source Dates) and Column C (Target Dates). All data is formatted as Dates. I want to find which dates in column A have a matching date in column C. When using the find statement within a For Each loop I can not find a date match unless I format the target dates as General. How can I use the Find Statement using dates without formating the target dates as General?
Option Explicit
Dim SourceDate As Range Dim TargetDate As Range
Sub FindTargetDate() Columns("C:C").Select Selection.NumberFormat = "General" For Each SourceDate In Range("A1:A32") 'MsgBox SourceDate Set TargetDate = Sheet1.Range("C1:C7").Find(SourceDate, LookIn:=xlValues)
