Answer Questions

• Corey Furman IIF Statement

  I have the following statements and keep getting "YES" where I should be getting "WAIVED" or "NO". Does anyone know why, everything else evaluates correctly. IIf([admit_dec_code] Is Null Or [admit_dec_code]="R","",IIf([admit_dec_code] Is Not Null Or [admit_dec_code]<>"R" And [date_acc_fee_paid] Is Not Null,"YES",IIf([admit_dec_code] Is Not Null Or [admit_dec_code]<>"R" And [date_acc_fee_paid] Is Null And [accept_fee_amount]="1","WAIVED","NO"))) IIf(IsNull([admit_dec_code]) Or [admit_dec_code]="R","",IIf(Not IsNull([admit_dec_code]) Or [admit_dec_code]<>"R" And Not IsNull ...Show All

• AndyL run-time error '1004' Method 'Range' of object '_Global' failed wen using refedit

  I have looked around the web and cannot find a solution .. hope somebody can help.. I consistantly get run-time error '1004' Method 'Range' of object '_Global' failed when I hit the Range command. The refedit contanis text somthing like (select a range of cells with my mouse) 'Sheet1'!R5C2:R15C2 But I cannot turn that into useable information. My code is launched from a command button through a Form Public Sub CommandButton1_Click() Dim freqRange As Range Dim respRange As Range Dim psdRange As Range freqRange = Range(UserForm1.RefEdit1.Value) 'error occurs here! respRange = Range(UserForm1.RefEdit2.Value) psdRange = Range(UserForm1.RefEdit3.Value) TextBox1.Value = (Module1.sumpsd(freqRange, respRange, psdRa ...Show All

• coolkay Reversing a lists order in excel for my macro

  Hi there. I have a macro that im building within excel, but am stuck on an excel function. Does anyone know how to reverse the order of a list ini excel. I have a column set up of names. How can i select this list and have it reverse the list in a separate column. I dont want it reversed alphabetically, or numerically... my example would be this... Cell A1 - A5 Paul Barry John Steve Jim i would like to either select the 5 cells and have click a button to reverse this order, or have it return the reversed order in column B1 - B5... giving me the result of Jim Steve John Barry Paul The only way ive seen how to do it is with the offset function but ive had alot of trouble with that for some reason and hoped someone had anot ...Show All

• AndyL My mini Utility_Move class, please have some suggestions.

  Hello guys, I made a class called Utility_Move. Please give me some suggestions. If there is a better, cleaner, more performance way than mine, please tell me, thank you. 'Motive: Sometimes it is eaiser to debug when you use ActiveCell as current data record or parameter. '   When the macro stopped by exception or stop sign, you can determine the running progress by ActiveCell. '   And it is easier to say Up(5) instead of offset the row index, for me at least. 'Summary: The Utility_Move class introduce 3 sets of functions. 'First set, Up-Down-Right-Left simulate the key stroke of arrow keys. '   Additionally, Steps parameter allows you to repeat number of seps to that direction. '   Negative st ...Show All

• RobbieJ Code Snippet Manager for VBA

  I am, for the moment, still writing my VBA code in Excel (and possibly soon word). I find that I use some code repeatedly. Is there any free code snippet manager for the VBA IDE Nice one. I'd like to recommend mz-tools for vba editor. It support a lot of features including user's code template. plz refer to http://www.mztools.com/index.htm best regards, sjoo ...Show All

• LynnOoi vlookup

  Hello can anyone help me with how to write the excel vlookup function in vba the function in excel looks like this VLOOKUP(O2,'[Consolidated list of supplier.xls]Sheet3'!$A$5:$F$218,6,FALSE) how can i get something similar working in a macro. thanks namrata What happens if you wait until after the lookup before closing the workbook Sheet3.Cells(2, 16) = Application.WorksheetFunction.VLookup(LookupValue, TableArray, RangeLookup, ColIndex) ActiveWorkbook.Close SaveChanges:=False hi, i modified the code as below but it gives the following error .. run time error 1004 unable to get the vlookup property of the worksheet function class my new code looks something like this. **************** ...Show All

• Dan Lingman Cannot close all instances of excel after calling excel workbook in Access Code

  Somehow, another instance of excel is loading when I refer to elements in my code. Such that when I ask for another display of the excel sheet the first global range is exceeded. You should probably see the code. If you want I can send it to you for comment. Is there an ABSOLUTE way of shutting down ALL instances of excel at the end of code execution Thank you, supermsd hi i am also facing the same problem actually i dont have problem if the user of the system will be end up with all the application stopped when i dispose my excel object i just want to dispose my application's excel object. one more thing i would like to mention when i write something like if (objExcel != null) ...Show All

• Doug DeBug worksheet_change and data validation

  Hi there I have an Excel spreadsheet in which one column has data validation set. So the cells in this column have drop-down lists with the pre-set allowed values. Now I want the cell in the neighboring column to be set to some default value when the user selects an item from the drop-down list. This seemed like it would be easy, using the Worksheet_Change event handler. When a cell in the first column is changed, it initiates a function which sets the value in the neighboring column to some default value: Target.Offset(0,1).Value = "default". The problem is that it doesn't work when using the drop-down list. If the user types in the value manually, or if a cell in the first column is deleted, it DOES fil ...Show All

• Eric Wirch Using VBA macro to locate next free column on worksheet

  I need a macro that locates the next free column (i.e. has no data in cells) and then places the cursor in a cell on a designated row. Would like search to start from column B and to place the cursor on row 4 on the free column. <> My VBA code thus far: Function NewColNumber(Range1 As Range) As Integer Dim j As Integer For j = 1 To Range1.Columns.Count If Range1.Columns(j) = "" Then NewColNumber = Range1.Columns(j).Column Exit Function End If Next End Function Sub SaveCOLComments() On Error GoTo Err_Part Dim Range1 As Range Dim intNewColNumber As Integer ' ...Show All

• Kah Geh Automatic Fill-in Form in Word

  I would like to create a form in word with four questions on it with a field for answer under each question.  But I would like to have a macro that becomes active when the form is opened.  The macro should pop-up a mini window with question 1, 2, 3, 4 in order (but one question per window at a time) and should have a field for inputting the answer.  Up on entering each answer in the pop-up window, the macro should automatically populate the Form with the correct font etc.. Any and All help will be greatly appreciated.  I am sure it will be a piece of Cake for most of you.   JJ Hi JJ Try this for a solution ... Create your form, including a help ...Show All

• Richard Russell How to re-connect an Excel.Application variable to a disconnected Excel instance?

  Hi, Automating Excel is wonderfull. I program it quite offen and I quite enjoy it. I have a question: When a program with new a Excel.Application is broken and stopped, the created Excel instance will be deattached to any VBA variable. In this case, I need to be "End Process" manually in the window task manager. I do this more than 50 times in one day of programming. Without doing this, I once had found more than 20 Excel instances in the task manager. It is terrible. I tried following, the program broken at J=27 in my 1Gbyte RAM notebook. Dim J As Byte, mExcel(1 To 50) As Excel.Application For J = 1 To 50 Debug.Print J; Set mExcel(J) = New Excel.Application Next Stop My question is how can I scan all curre ...Show All

• MaliciousGeek How to retrieve worksheet name using ADO?

  I want to get worksheet name from Excel workbooks, that included some worksheets, how can i do it Thank you very much! ...Show All

• Fluxtah Data pickup Issue

  Hello Every body, I face a problem about data pickup from datasource. ok i try to explain it first. Some my ui have different combo box. comboboxes contain data like Panel, Reader, Badge etc devices. each combobox contain single object data. in left side of every combo, one check box item is there. if combo box will checked then combo box will be enabled and show its data other wise not. In that senario user may check all checkbox or not any singel check box. It totally depends on the user. In that senario what i did, I pickup all data from database and bind it to the combo box when form first time loaded. that means loading time of form i request to my business layer to give me all data. Is it correct approach. Thanks for advance help. ...Show All

• AndyC12345 key in date and filter the data in a week

  Hi all, I have question on VBA which is quite difficult for me. I need a VBA code that can filter out all data in a specific week after i type in a date, any date within that week in a textbox which i have previously created. For example i have a table of dates corresponding to number of week: Week 1 02-Jan-2006 09-Jan-2006 Week 2 09-Jan-2006 16-Jan-2006 Week 3 16-Jan-2006 23-Jan-2006 Week 4 23-Jan-2006 30-Jan-2006 Week 5 30-Jan-2006 06-Feb-2006 Week 6 06-Feb-2006 13-Feb-2006 Week 7 13-Feb-2006 20-Feb-2006 Week 8 20-Feb-2006 27-Feb-2006 Week 9 27-Feb-2006 06-Mar-2006 and ...Show All

• InfiniZac Converting text file to excel file macro - help needed

  I have a text file I need to convert to an excel spreadsheet. I wouldn't have a problem making an array code if it were a straight file but the trick is that each line could be different. For example: Line one is 01330948951000312 eeid jan boerenkamp line 2 is 023330948951000312 00370058274800350010112/01/2002 Line 3 is 05330948951000312 31065327123107533811 any time a line begins with 01 it will follow that format any time a line begins with 02 it will follow the format of line 2 and so on Is there a way to make an array to put into Excel so that if a line starts with 01 it splits it one way and if a line begins with 02 it splits another way and so on Thanks in advance ...Show All