Answer Questions

• duoshock How do i reference a cell in an unopened workbook?

  I want to get information from a network drive that i have access to and all users will have access to.  Would it be something like workbooks("c:/folder/workbookname").worksheets("Sheet1").range("a5")   Also, is there a way to shorten things a bit so that I only have to declare the location once then use a variable to reference it after that You can make it opening the workbook. Dim wb As Workbook, ws As Worksheet, v As String Set wb = Workbooks.Open("c:\folder\workbookname.xls") Call Windows("workbookname").Activate Set ws = wb.Worksheets("Sheet1") v = ws.Range("a5").Value Call wb.Close MsgBox v Hi, You can make use of the fact that a cell formula can read from a closed ...Show All

• Ethan Hunt How to pass a value of a clicked cell to another cell in a different worksheet?

  Guys, How to pass a value of a clicked cell to another cell in a different worksheet Thank you! Hi Guys: I work it out. It is very simple. one line of code is enough Private Sub Worksheet_SelectionChange(ByVal Target As Range) Sheet2.Cells(2, 2) = Sheet1.Cells(Target.Row, Target.Column) End Sub simply copy the sub to the sheet you want to click and change the receiving sheet number and cell address. Hello Alexander, You need to eloborate on what you are trying to do to get a proper response. You could try the worksheet_Change event, and then use the properties of the active cell but without knowing what you want to achieve and when to achieve it, it is difficult to answer Chas ...Show All

• Earl Jon Daman VBA Excel - Using FindNext function

  Hi guys, I am trying to get the range of rows containing "313" in column Nr 21. I am trying using find and findnext, but getting error. Help will be appreciated. Thanks! Sub GetRange() 'Sort by Project Nr Call SortRange("A2:IV" & ActiveSheet.UsedRange.Rows.Count, "U2", xlAscending) StartLookingInRow = 1 StartLookingInCol = 21 Cells(StartLookingInRow, StartLookingInCol).Select FirstRowWithData = Cells.Find(What:="313", SearchDirection:=xlDown).Address LastRowWithData = Cells.FindNext().Address 'Here getting ERROR!!! End Sub Hello, I would just loop through column U2 and see if the cell matches your searcg string store the first address when search string is fo ...Show All

• Mark A. Richman VBA Excell Autofilter

  I've used these for some time and I must be forgetting something basic. Where can I get error checking information on building the autofilter function into an Excel Macro. Something must be wrong with one of my ranges or my data. Can't figure it out. Thanks much, Bob The code used for a macro autofilter is: Range("DataRange").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _ Range("CritRange"), CopyToRange:=Range("FiltRange"), Unique:=False I've used this several times. There must be something wrong with my data or my ranges. This code works for several applications I've built. Why it doesn't work for this one I don't know. I think there are some basic guidelines for the data and ...Show All

• scubabbl DAO can't connect to Access2007

  Hi all, I'm using DAO to connect to an Access2007 database file. But it reports such an error "unrecognized database format". Does anyone know if MS supports using DAO connect to Access2007 Is there a new release of MDAC along with its Office2007 Thanks in advance, - Haojun I am having the same Problem.. Can anyone Help Also - This looks like a seasoned forum of Users who are still using DAO. have any one you guys used the DAO in Client-Server Mode across the Networks Can I have multiple users ( from different Systems ) connecting to an MDB on the Server Any help will be appreciated. Venkat M desikan.venkat@gmail.com NOw I meet t ...Show All

• JIM.H. how create new entity in crm 3.0

  Dear all i am use ms crm 3.0 . i create new entity in crm 3.0. name of entity is bank. the bank entity have attribute like custid_id, name address .. ect . i need the cust_id auto increment like that c_000_01 ,c_000_02 .. pls give me advise. very urgent with regards sivam ...Show All

• tinny4u Runtime error '13' - type mismatch problem! Again...

  Hey all Thanks to the help of the forums here i thought i had most of my code working however, this infamous 'runtime error 13' keeps cropping its head up on only 2 of my 10 sub routines! :( Ive spent most of my work day searching through forums and google but i am no closer to finding a solution. The 2 functions that it is affecting are important and i need to get them sorted else i may as well scrap my two days work! Ive posted the code below and hopefully if anyone has a chance they could have a look and see if they notice anything that may be the cause. Ive highlighted the lines of code that are being thrown up in the debugger. '*********************************************** ' Calculate Award '************************** ...Show All

• mtm81 ActiveX, ATL, Object library invalid or contains references to object definitions that could not be found

  I had just discovered a critical prerequisite for when you want to create an ActiveX control using ATL that you want to use in an Excel document: The ActiveX control MUST support Connection Points. If you do not add Connection Points to your object, you may receive the following useless and misleading error message: "Object library invalid or contains references to object definitions that could not be found". Steps to reproduce: 1) Use Visual Studio 2005 to create a new ATL project. Use all default settings. 2) Use "Add Class" from the project's context menu to add an ATL Control to the project. Use all defaults for the object. 3) Compile the project. 4) Open Excel. 5) Add a Command Button. 6) Add the ActiveX c ...Show All

• Bradrover Help with highlighting employees nominating each other for awards...

  Hey This is more a mathematical problem I guess rather than a how to do it in VBA. I'm trying to write a simple macro that will allow me to highlight people who are nominating each other for small awards. Basically I get a copy of the datafeed from the server and run it in Excel on a monthly basis. I have a Nominator ID and a Recipient ID along with numerous other information and I want to be able to find out if anyone is continuously nominating each other Been playing about with IF statements and loops but having no joy at all :( Just not mathematically minded enough! Nominator Recip Amount Date Reason etc... Scott Louise $20 12/01/06 ....... Louise Scott $20 1/01/06 Jack Jones ...Show All

• Can-Ann 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 hello,   the whole code that i have written is as follows   Public LookupValue As Range Public TableArray As Range Public MyPath As String Sub VlookUp_Click() MyPath = "C:\Documents and Settings\np22331\Desktop\Consolidated list of supplier.xls" Set LookupValue = Sheet3.Range("O2") Set TableArray = Workbooks(MyPath).Worksheets("Sheet3").Range("$A$5:$F$281") End Sub   namrata buddy, the lookup is used in a ...Show All

• XNA Rockstar Change a formula in vba code, How do you

  Hi, I have a spreadsheet that adds each days purchases and each days sales et sums them, until the inventory is posted. The formula in each cell going down the sheet is like:=bi9+bf10, with bi9 having the current sum and bf10 having the current days sales or purchases. Lets say the next days formula is in cell bf11. I need to change the formula in bf11 to =bi11. Note: the store can post the inventory every day if they wanted to, so I have no way of knowing what line the current inventory is posted on, nor what day of the month it corresponds to. Below is the code for the Deli department inventory sheet with the lines in yellow that need to change the formula. All these lines of code do at the moment is erase the formula becaus ...Show All

• Arkady Frenkel 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

• Science_1 Application.FileSearch in Excel 2007

  There is an issue which I am facing after I installed Office 2007. I had written a small VBA which would search for a file and if found will rename the file. I used Application.FileSearch then. Now I am tying to do it again but unfortunately there is an error prompted stating that 'the object does not exist' Set fSearch = Application.FileSearch defPath = "H:\SourceSafe_1_Feb-28_Feb_2006\SecondSet" Set rg = Range("MyFiles") fSearch.LookIn = defPath I have been frantically looking for changes in the VBA object model especially for Excel but cant seem to find them. Thought I'd just put a quick reply to this as I was having trouble with this file searching process. I used it in Access and then tried to use i ...Show All

• Zero_ Service won't use default printer

  I am developing a program that is intended to run as a service on an XP Pro machine. It processes files and prints them to a printer automatically in the background without any user interaction. The printer that I use is configured to be the system's default printer. When the program runs it works fine when it is not running as a service. However, when it is running as a service, instead of printing to the default printer, it tries to print to the Microsoft Office Document Image Writer. I have not been able to find any information on configuring the system so that I can get my service to use the designated default printer. Any ideas would be appreciated. Thanks in advance. Packrat, I escalated your issue ...Show All

• YeeBoon Access 2003: Connect to Word Document.dot and fill in DocVariables dynamically

  Hi, i hope i find a answer on this way. Let me describe my little ( i hope so...) problem: I would like to have my code figure out which fields are on the document and populate only those. I realized this with the following code: Sub sendToWord(DokPath, DokName) Dim strPfad As String strPfad = DokPath On Error Resume Next Dim objWord As Word.Application 'search instance of Word Set objWord = GetObject(, "Word.Application") If Err.Number = 429 Then 'no instance found Err.Clear Set objWord = CreateObject("Word.Application") 'create instance On Error GoTo Fehler ElseIf Err.Number = 0 Then ' nothing Else On Error GoTo Fehler End If With objWord .Visible = True . ...Show All