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.
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.
A25346 ENG101 MAT115 PSY150
A35678 ENG111 MAT115 SOC101
A23456 ENG101 MAT116 HIS201
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
Set rMajor = wsMajor.Cells.Find(What:=Major, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rMajor Is Nothing Then
majorrow = rMajor.Cells.Offset(0, 1).Value............
heres the Coding.
Sub ADORUN_CSM_Reg1()
' Justin SQL Server Connection
'
' FOR THIS CODE TO WORK
' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
'
In the Server_name = "SRVREG1" in red above, i would like it to reference a cell...say A30. How is this done?? so that if im on the worksheet called Title on A30 i type in SRVREG1 and the VBA knows what to do from there.
Total amount in Column B should same as shown in Column F
=IF(E3<>"",SUM(OFFSET($C3,ISTEXT(E3),,,1):C3),"")Above formula is calculating always two rows.
I have problem with vlookup. I am attaching my file.
View 3 Replies View RelatedI have formatted cells to date but when I enter in a date of 020413 (Feb 04, 2013) my reslut shows as 11/20/55. I have a coworker who is having the same problem (but hers displays even a different date) so was wondering if there is a setting in excel that I don't know about.
View 1 Replies View RelatedI am including an attached file for reference.
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 have created a macro 'uid' and rename the module to 'profession'. In the list of macro 'alt + f8' the name displays 'profession.uid' which confuse me. I want to display the name 'uid' and not 'profession.uid'. I select the macro by pressing the first letter of it as in this case 'u' and reach that macro and click it to run.
View 4 Replies View RelatedI have a really strange problem i have two different hard directories o: & u:
i have the following code linked to a command button
chdir "U: foldername foldername foldername"
saveactiveworkbook.saveas range("c9")
I'm fiddling with a C# library, trying to learn about including homemade libraries in my VBA code. I've written a simple library, compiled it, and did the COM registration. When I open the VBE and start up a new module, I'm able to find the library in the Tools | References dialog box, and I check it to include it. When I write the code, however, autocomplete doesn't give me the option of any of the definitions I've written into the library, and when executed, I'm getting a "User-defined type not defined" error.
View 1 Replies View RelatedI have an excel file emailed to me everyday. It is a daily tracking of our fleet and I manipulate it before printing it and giving it to management. I dont know why when I try to record the macro and then use it it hides all columns.
The report has columns A-N. I hide B, F-G, I, M-N. When recording I hide multiple columns across.
I have done similar macros with other reports, no problem.
my macro filling down the data properly. I have a table wit headers set up. My macro selects two cells in the first row, and tries to copy it down into the other rows of the table. Instead what happens, the macro copies down the table headers into the two cells that are selected. Here is my code.
Range("I3").Select
ActiveCell.FormulaR1C1 = "Order Filled"
Range("J3").Select
ActiveCell.FormulaR1C1 = "Order FIlled"
Range("I3:J3").Select
Selection.FillDown
how do I use mround properly in this function?
I did this =(IF(ISNA(MROUND(A20*0.75),"$0.00",(A20*0.75))) and it isn't working. I want the numbers to the nearest 5 or 10 cents.
I try the following selection
Columns("B:AF").Select
Selection.ColumnWidth = 2.3
It is selecting columns a to af. Why would take column a if it is not part of select?
Sub Add_worksheet_month()
Dim Lst() As String
Dim I As Integer
Dim wname As Date
'Dim wmonth As Date
I have a user that is experiencing a strange filtering issue within MS Excel 2000. He is working with a database of about a dozen columns and nearly two thousand five hundred rows. Two columns that are worked on for filtering proposes are the Job # and Job Name columns. Initially, the sorting works between the both – meaning if we want to find a particular job by either the name or the number – it is accomplished. However, once we cut (about a couple of dozen rows) and paste a section of rows from the bottom to the very top of all the entries – we discover that a number of jobs do not appear within the filter (once it has been applied again). Is there some type of refreshing that is needed? I have saved the file after the changes.
View 2 Replies View RelatedI have some columns of data that won't get perfectly aligned. The numbers in all the cells are right aligned but the numbers in some of the cells are a few pixels to the left compared to some other cells that appear to be properly right aligned.
I have attached a screenshot if my explanation isn't clear.
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??
View 4 Replies View RelatedMy league table just stopped adding the scores up as of week 22. prior to that they worked fine. I input scores in the "Our Players" sheet, per player per week.... simple. but like i said, as of Week 22, it just inputs that specific weeks scores. See attached file.
View 4 Replies View Relatedsome Excel columns (see attachment). A1 is set to =C1 and the formula is dragged down, relatively referencing column C. Column B applies a formula to the values in Column A.
If I go into the formula bar for cells in A and push enter, the spreadsheet shows the correct value and I can then push enter in the adjacent Column B cell to solve. But, how can I get these data to display properly automatically?
This code calculates a value for column K when you punch in a number in column J (in any rows 17 to 116, separately). The first problem is that when you delete a value out of J, the value stays in K for some rows (where it should only contain a value if J does). I'm guessing it has something to do with the triggering event that I put in bold. Secondly, I need to be able to protect row K at all times from deletion or entering another value. It ONLY should calculate a value based on J. So I need to use the module (below the code) and I want to integrate it into the code or call it in a module. It's not working properly because it prevents ANY and ALL changes, even the ones caused by new values entered in J (which should be allowed). It should only prevent deletion or manually entering a value on the worksheet. So the bottom line is that when you enter a value into J, unless column E says "Annuity" it will calculate using the formula given. If you delete J, K should go away as well. And at no time can you delete or manually change K. And when you switch values in E, it must reloop to check if it says "Annuity" or not, and if not, it must clear J (which clears K).
And lastly, this thing is brutally slow, so maybe there's an easier way to write it. Sorry for all the detail, but I think it's an easy fix for someone who knows programming, but i don't know how to put it together! NML Inventory is the Activesheet.
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.
View 3 Replies View RelatedI 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 am setting up a spreadsheet to be able to calculate loads on spread footings. The thing is the footings may be as small as a 2 x 2 or as large as a 6 x 6 or any combination inbetween so I need a coordinate system (to the nearest inch with a max of 1800 inches) to be able to assign each spread footing to a certain spot in the system with which I can calculate the soil stresses.
I intend to make this as idiot proof as possible for anyone who uses this spreadsheet so I was going to set it up as in the attachment where a user can input each individual load on each footing and the distance between it and its neighbors. The numbers are just random values with in each footing and feet between each one. Naturally most people would probably begin data entry in the upper left footing first so I was going to make that the origin unless for coding purposes the lower left would be best.
In this case the upper left footing would be 0,0 and the footing to its right 20,0 and the one farther right 55,0 (20+35) and so forth.
It undoubtedly would, but don't hold your breath. Doing so would break
tens of thousands of existing applications.
It would also make exponentiation the only operator that worked
right-to-left. That alone would make things extremely confusing for
non-structural-engineers.
Operator precedence and direction of application is neither correct nor
incorrect - it's convention. And scientific convention is not the
convention that Excel was designed for. A frequent example is
-x^(2*n)
XL will always calculate this as a positive number (for integer n's)
since negation has a higher precedence than exponentiation. This seems
counterintuitive to most of those that have commented on it in these
groups. But it's consistent with XL's published specs.
In article <D8D7DD4E-D595-47A3-8770-AAFEF5C38048@microsoft.com>,
"expatrie" <expatrie@discussions.microsoft.com> wrote:
> It would also make using the program easier for structural engineers
> like me.
I'm trying to have a fixed date entered into column B when data is entered into column D. Here's the formula in column B:
=IF(D3="", IF(B3=";", FixedDate(),B3))
When I enter data into cell D, I get the following result in B:
0-Jan-00
Taking this a step further, even if I do a "control + ;" I still get a value of
0-Jan-00 in the cell but the formula bar shows me "=1/9/2007'. ??
I've gone through all of the options but cannot fix this error. I have two spreadsheets using this formula. The first one worked fine last year but now I have this error in it. I also started to create a new sheet and still have the same error.
Here is my dilemma, I have two formulas that reference another sheet, my example shows it more clearly...
...............Column E
Row 11......=sheet1!F16+sheet1!I16
Row 12......=sheet1!G16
Row 12......=sheet1!F17+sheet1!I17
Row 12......=sheet1!G17
etc.
Is there a macro or a formula I can use on the remaining 4000 rows that will keep this pattern while maintaining the formulas above?
I have a pivot table that I refresh using .refresh in the VBA code.
My problem is that when I loop through all the PivotItems in a filter list in order to make all visible, there are PivotItem names that aren't in the drop down menu and so the code breaks.
Do I need to refresh the pivot table slightly differently than just using .refresh?
I made the excel file with the VBA source code, saved it, opened it many times, and all at sudden I cannot open it anymore. When I try to open the file I get the following screen:
actually, Excel asks me if I want to enable macros or not, but this window is different than usual window:
when opened file with macros works fine.
So, in my case I click the Enable Macros button of the first picture and my file seems to open in some hidden instance, because the file name isn't writen in the title of the Excel window.
When I close the Excel window it asks me if I want to save changes to my file, and that proves that it was opened in some hidden instance of the Excel.
I have to say that I was not playing with the security options of hiding any part of the file that I want to open, and which doesn't open properly.
