Answer Questions

• Nilesh Ingale Adding data in one column based on another column using VBA

  I have an Excel DB in which there are identical entries in some of the fileds. I need to identify those entries, delete one of them so I can keep the other one in the DB and sum their corresponding value in another column. EXAMPLE: A 23 B 12 A 10 C 20 I need to get the following result: A 33 B 12 C 20 This seemed easy, but I can't figure t out in VB. Thanks. Hi Below is not elegant, but it might give you an idea. The below assumes that data starts in A1. The arrays store the summary data then the data range is deleted and the array data written back. Try with some test data in a new sheet. Public Sub TEST() Dim X, lngLastRow, ...Show All

• Westonm Limitations of business scorecard manager

  As I am considering buying Business scorecard manager soon, and since it is an expensive piece of software I would appreciate someone either confirming the issues I am having... or correcting me if I am wrong. If i can get these issues sorted out then Microsoft, you defintely have a sale! Firstly, Am I right in thinking business scorecard manager does not work with more than one dimension attribute from a dimension For example, if I take the time hierarchy to be a year - month hierarchy from a dimension and put that in the column members of a dimensioned scorecard, and I add the branch attribute, from the same dimension, to the row members (so that I would have the data divided up into the different branches down the left hand side ...Show All

• AndrewBadera 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 does the variable MyPath contain If you have a file C:\MyFolder\book1.xls open it's name will be book1.xls. It will not include the path. buddy, the lookup is used in a different excel worksheet and the 'consolidated supplier.xls' is a different file. if i do not close the consolidated.xls there will be again a problem of active worksheet as the macro is not being written in the consolidated.xls... its just being used for the lookup range purp ...Show All

• Thomas Edstrom Please Help...When running a VB macro in Excel the date changes format to USA from UK !!

  Hi Below is the code from a macro I am running in Excel. The prolem is that once the file is saved the dates are converted into USA format from UK format and this is causing major problems.. Would appreciate any help please... Sub format() ' ' format Macro ' format for data load ' ' ActiveWorkbook.Save ActiveSheet.Unprotect Rows("1:11").Select Selection.Delete Shift:=xlUp Columns("B:B").Select Selection.ClearContents ActiveWorkbook.SaveAs Filename:="c:\payroll\Cleaners.csv", _ FileFormat:=6, CreateBackup:=False ActiveWorkbook.Close Savechanges:=False End Sub Thanks Paddy GoodMorning Padmaster, In order to recieve optimal help ...Show All

• pappascd Backup and Restore Data/Metadata for a Novel Backend.

  Hi, We have an application that behaves exactly like the NtBackup.exe and does some extra functionality based on our product. This has been working fine for almost all fileservers including EMC and NetAPP that support CIFS and windows based AD. We have a beta deployment in progress with a customer who has a Novel File Server and a Novel based AD. When we run our application to backup a Novel share from a windows client we observed that we could not backup the security stream. On further investigation we observed that even NTBackup cannot backup the security stream from a Novel Backend. We identified that tbackup.exe can be used to backup information from a Novel Backend but cannot restore security information on a windows backen ...Show All

• John Portnov To find Whether a value in a cell is pasted?

  Hi, Actually if we copy - paste a value of one cell into another, the format of the copied cell, like font,color, size etc, overrides that of the cell in which the value is pasted. Is there any way where we can prevent this. Thanks Is there any way of achieving this through VBA code i think there are a no. of ways of doing it... 1) right-click on the cell that you wanna paste, then select "Paste Special", and in the Paste Special dialogue box select "Values" or "Formula". 2) when you select the cell you wanna copy, copy from the Formular bar (ie. highlight the text in the formula bar then CTRL+C) instead of hitting CTRL+C ...Show All

• Hooper Assign an array to .values and .Xvalues in an excel chart

  Hi, I'm having trouble with assigning arrays to a chart. Below is how I done. Anyone nowing how to make this work     Set MyDispl = Application.InputBox(Prompt:="Select displ.", Type:=8)     Set MyForce = Application.InputBox(Prompt:="Select force.", Type:=8)     Do '''Collects Displ data             plot_disp(i) = MyDispl.Offset(i, 0)             i = i + 1     Loop Until IsEmpty(plot_disp(i))           Do '''Collects force data                ...Show All

• droujav Newbie problem: storing in an array

  Hello, I'm new to VBA. I've some questions regarding to array. Here's the code linked with excel spreadsheet: Function Function1(DailyClose, EFBillsYield) TradingDays = Application.WorksheetFunction.Count(DailyClose) For i_cnt = 1 To TradingDays - 1 DailyReturn = Application.WorksheetFunction.Ln(DailyClose(i_cnt) / DailyClose(i_cnt + 1)) Next i_cnt AnnualReturn = Application.WorksheetFunction.Average(DailyReturn) * TradingDays AnnualVolatility = Application.WorksheetFunction.StDev(DailyReturn) * Sqr(TradingDays) RiskFreeRate = Application.WorksheetFunction.Ln(1 + EFBillsYield) Function1 = (AnnualReturn - RiskFreeRate) / AnnualVolatility End Function When I enter the parameters DailyClose (which is an array of n ...Show All

• Mirko Messori Error handling problem

  Hi - i have an end of month routine that will run - however it brings up error 3021: no current record.  this occurs if the user presses the command button where they have not entered any activity that is greater than the 'datelastsubmitted' date because the form loads with the following code: Private Sub Form_Load() Me!officer.SetFocus officer.Value = loginname Me.submit.Enabled = False Dim rsdbase As Database Dim rstemp As Recordset Set rsdbase = CurrentDb Set rstemp = rsdbase.OpenRecordset("SELECT SUM(activityhours)AS [availablehours] FROM [qryavailablehours]WHERE [loginname] ='" & CStr(Me.officer.Value) & "'") With rstemp     .MoveFirst     Me.availablehours = Format(Nz(!availablehours, ...Show All

• TrevorW Visual Studio Tools for Applications

  Following are the question related VSTA that we have sent to Microsoft: When is VSTA getting Released Can we write the code/script in VSTA, store it in files/database; load the script and run it on the fly through a windows application Can I store the Code/Function in the database and load that function dynamically, debug it, make changes, compile and save it back to database and deploy the changes to all the client machines on fly Can we load the script (on-the-fly) in a VS.Net IDE and debug through it This should be done on a button click in a windows application. If the script has to be debuggable, what are specific settings Can we selectively run a script based on config settings that ...Show All

• steveareno Word: How to you determine the current cursor position

  For one of my macros to run, I have to ensure that the cursor position is not in a table or in a frame when the user activates the macro. How can I find our the current cursor position Once I can determine that the cursor is positioned in a table or a frame, I can then code a message box to instruct the user where to put their cursor to run a certain routine. Hi, Cursor position Selection.Range.End This will check whether currently in a table or frame. Dim lngTableCount As Long Dim lngFrameCount As Long For lngTableCount = 1 To ActiveDocument.Tables.Count If Selection.Range.InRange(ActiveDocument.Tables(lngTableCount).Range) Then MsgBox "In Table" Exit For End If ...Show All

• stormside Trying to get just the file name

  I'm using VBA to read a text file made of files paths (path & name). I'm opening them in word and trying to resave them into .txt format for notepad/wordpad. There is no easy way to be able to find the last "\" and then use that to take all the text after it. example: "C:\my docs\this file.doc" i would want the "this file" portion of the text I found what seems to be a useful method (sourcename) but have no idea how to implement it If f1 = fso.FileExists(file) Then Documents.Open filename:=line, ConfirmConversions:=False, ReadOnly _ :=False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate _ :="", Revert:=False, WritePasswordDocument:="& ...Show All

• Angry Coder Office XP VBA can't find reference after saving by Office 2003

  Situation is: 1) I created VBA in Excel XP, and added reference to "Word" object -- version 10.0 2) I open that Excel in Excel 2003, and now the reference automatically changed to -- version 11.0 Word object 3) I saved file, then open it again in Excel XP Now the reference to "Word" object shows "missing" in VBA project, and it still refer to version 11.0. Any idea how to fix that Instead of modifying the reference in VBA project. Can we stop auto update of dll reference in Excel Regards, Robin <<Situation is: 1) I created VBA in Excel XP, and added reference to "Word" object -- version 10.0 2) I open that Excel in Excel 2003, and now the reference automatically chang ...Show All

• RickGaribay.NET .Net Version in the future?

  Hello, I am wondering are we going to have a .Net Version of Office in the near future. The most important function I want to get out of it, is the multiple library version control. This probably not going to help my current version issues, but I hope we start to using .Net multi-library-version-control as early as possible for the future. Thanks. Hello, Thanks for the reply. So far COM has this version limit that only one version of DLL can be referenced, and I don't have any way to reference a DLL conditionally. For my current problem, I am using Outlook.COM in my Excel file using version Office2003. But once I run the Excel file on Office2000 platform,I failed to reference Outlook.COM because the ve ...Show All

• Leon Mayne personalized command bar disappears when minimizing excel

  This is my first message in this forum, so please be patient. I have programmed a makro for an excel workbook. one part of this makro creates a command bar. when the excel workbook is started i make all the regular command bars disappear, only mine is left. Works wonderful. Well, i have put a button into my command bar to minimize the application. However, when i restore the workbook again from the windows taskbar, my commandbar is vanished, and the normal command bars (like standard and format and also the formula bar) are suddenly there. The command bar is still there but not enabled/visible. The problem exists on many computers and also on differnet excel versions i tried (97, 2000, xp) I do not know if that is a bug or not. Now is it p ...Show All