In a Macro replacing $x with ($x + i) gives x+1 not $x+1 as expe.

$x eg $10 can be replaced normally by $11 but when used with the counter i 
( i=i+1) 11 is returned and can be replaced seperately to $11.
This occurs whether the cell is formatted as number or general. The $ is 
being used to create an absolute cell adress not a currency.
0
Zorro1 (3)
3/3/2006 5:28:27 AM
excel.newusers 15348 articles. 2 followers. Follow

0 Replies
457 Views

Similar Articles

[PageSpeed] 36

Reply:

Similar Artilces:

Moving contacts from 1 computer to another
I am really struggling here, so any help would be greatly appreciated. I have put Outlook 2000 on my new computer and I want to transfer all my contacts, notes etc from my old computer to the new one. I can't seem to figure it out. Thanks Scott just export (File, Import and Export, Export to a file, PST) from the old one and then import onto your new one N "Scott" <wyrks@gto.net> wrote in message news:064001c36bdd$ed554a00$a001280a@phx.gbl... > I am really struggling here, so any help would be greatly > appreciated. I have put Outlook 2000 on my new computer &g...

Hotsyncing Entourage X with Palm Tungsten E
Does anyone know whether it's possible to hotsync Entourage X for Mac OS X.2 with the Palm Tungsten E, which runs Palm Desktop software OS 4.1? I'm currently using an older Palm handheld, which hotsyncs fine with Entourage X. The Microsoft website description of the MS Handheld Synchronization Software says that it works only with 4.0. I'm not sure whether Microsoft considers 4.1 the same version or an entirely different one. (Since all new Palms run either OS 4.1 or 5.0, I'm hoping that I'm OK.) Thanks! I'm eager to buy the Tungsten E to replace my old Palm, but I...

How do I print in Excel on 14 7/8 x 11 paper.
I have several old worksheets from Lotus 123 set up to print on 14 7/8 x 11 and want to transfer those to Excel. In print setup, I cannot find any paper size that corresponds to this size which is the standard wide fanfold paper. Any suggestions would be appreciated. That page stuff depends on what printer you are using. You will need to go into your printer properties and do a custom paper size. "David in Jxn" wrote: > I have several old worksheets from Lotus 123 set up to print on 14 7/8 x 11 > and want to transfer those to Excel. In print setup, I cannot fin...

Sum data in workbook 1 and write result in workbook 2
Hi, I need some help with excel macro! I have two workbooks. Now I need to sum all cells with some date criteria from first workook and result must be inserted in cell in second workbook (for example in cell A1) WorkBook1 data example Column B (dates) Column C (payroll) 12.05.2005 1200 12.05.2005 5000 14.05.2005 3100 14.05.2005 8800 17.05.2005 3550 .... For example: sum all values in column C where date in column B is 12.05.2005 and put result in workbook 2 in cell A1 Thanks for help. Martin You don'...

Find and Replace #2
Hello, I am facing a problem regarding finding and replacing, i want to change a value ":10:20" to "00:10:20". Or in other words just want to add a leading zero before the colon. I have used the wild card to locate the value but in replce it chages the value with wild card. Please Help me in this. Find > Replace > Repl;ace what: :1 Replace with: '00:1 > Replace all Vaya con Dios, Chuck, CABGx3 "Muhammad Hassan (New Excel User)" <Muhammad Hassan (New Excel User)@discussions.microsoft.com> wrote in message news:89F2F7DB-4DA9-4823-B572-E9D...

Run Macro only if certain form is open
I have programed a maco to open one of various forms depending on criteria on an open form. Is there a way to run a "follow up Macro" based on which form opens? In other words can I run a macro based on wheather a certain form is active? -- Thanks, Chuck Take a look at the RunMacro Method of the DoCmd object. You can have it as part of the OnLoad of the form in question. Chuck wrote: >I have programed a maco to open one of various forms depending on criteria on >an open form. Is there a way to run a "follow up Macro" based on which form >opens? In other w...

Autofilter in a macro
Hello, I have a workbook that uses several macros to filter data from the raw data on sheet1 and then create tables on subsequent sheets. I did all of this by using the macro recorder. The results I get are fine but the macros run quite slowly. Is there a more efficient syntax to produce the results quicker, currently the code looks like: Sheets("master data list").Select Selection.AutoFilter ActiveSheet.Range("$A$1:$AC$10000").AutoFilter Field:=3, Criteria1:="AVV" ActiveSheet.Range("$A$1:$AC$10000").AutoFilter Field:=23, C...

Slow Macro
I have a fairly simple macro that is runnning slow for some reason. Here is the code Application.ScreenUpdating = False ActiveCell.Range("A1:DS1").Select Selection.EntireColumn.Hidden = False ActiveCell.Select Application.Calculation = xlCalculationManual For a = 3 To 200 If Range("D3").Offset(0, a).Value = "Hide Column" Then Range("D3").Offset(0, a).EntireColumn.Hidden = True Next a Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Any ideas? -- Thanks for your he...

RMS version 1.2 Database not connecting on register 2
I have a RMS system using version 1.2 that I purchased used. I have been successful in setting up the datebase and getting my date imported from my old system. The system has a total of 3 computers, 2 for registers and then a file server. I have POS1/register 1 computer working fine. My problem is with Register 2. I can access the administration program on register 2 computer and assign the database. I can also use manager and see my list of items in the database. My problem comes when I try to launch store operations. There is a delay and then I get an error message that says &...

ON_EVENT_RANGE gives compilation errors.
Hi, I am trying to use ON_EVENT_RANGE for handling events from dynamically created instances of a single ActiveX control. I have followed the steps given in MSDN. However I get following compilation errors, F:\OneHiClient\ClientAppDlg.cpp(93) : error C2059: syntax error : 'constant' F:\OneHiClient\ClientAppDlg.cpp(93) : error C2143: syntax error : missing ';' before '}' F:\OneHiClient\ClientAppDlg.cpp(93) : error C2143: syntax error : missing ';' before '}' F:\OneHiClient\ClientAppDlg.cpp(93) : error C2143: syntax error : missing ';' before ...

Getting value from 2 cols right, 1 row down
Hi, I guess this is a simple question but I couldn't figure out how to ge around it. Code ------------------- A B C D E F 1 Worker 100 100 100 100 2 110 110 3 Worker 120 120 120 4 130 130 13 ------------------- Sheet above is a work schedule and 100, 110, 120 and 130 are jus predefined work shifts. Row 1 is a morning shift row and Row 2 is night shift row for the first worker. Now what I want to do is to create another sheet with workers names t m...

XmlValidatingReader gives errors from inline schema
Hi there, I do have a dataset which I use to write a XML file with inline schema. A second application should use a XmlvalidatingReader to read the xml file again. Now I get errors like: The 'http://tempuri.org/LicenceFile.xsd:LicenceData' element is not declared. I'm a bit surprised since it is the inline schema and xml that was generated by the WriteXml method of the dataset and I didn't change anything there. I would have expected that schema and xml data do match when they are created by the dataset method. Did I miss something ? below is the xml file with the inline sc...

Large txt file import macro
All, I asked a longtime ago for a macro to allow large text files to be imported over several sheets within one work book, this worked great, however my machine has been rebuilt and I failed to keep the macro, can some nice person please supply a link again . Many thx Larry. Larry, The usual code is below. HTH, Bernie MS Excel MVP Sub LargeFileImport() 'Dimension Variables Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double 'Ask User for File's Name FileName = Application.GetOpenFilename 'Check for no entry If FileName = "...

set up a macro to delete characters in each cell of a column
I wish to delete 3 characters in each cell of a row. A macro qould be much faster but how i do it? Hi Rick, Try: '===================>> Public SubDeleteLast3Chars() Dim rng As Range Dim rcell As Range Dim WB As Workbook Dim SH As Worksheet Dim iLen As Long Set WB = ActiveWorkbook '<<========== CHANGE Set SH = WB.Sheets("Sheet4") '<<========== CHANGE Set rng = SH.Range("A2:M2") '<<========== CHANGE For Each rcell In rng.Cells With rcell If Not .HasFormula Then ...

Replace data in a column
Okay, I have a column that has text in it. I want to change all these to the Identifying number that is found in a different table. So how do I do this? Example: Current table has: A 1 First name 2 New first name 3 Another first name Need to change the A column in first table to the correspoding column in this table: A B 1 12 First name 2 15 New first name 3 22 Another first name So after the switch either I need a new column in the current table so it look like the second column (there might be more then one match in the current t...

Degrees � as an imput in a x-y scatter plot
For two columns of data: column A where I have integers ranging from 0 to 90 representing degrees; and, a column B where I have integer values randomly selected representing distance, I would like to create an x-y graph of points representing degrees and distance from 0,0 intersect of the x and y axes. I suppose the end result should look like 1/4 of a circle, that is 90� of a full circle. Is this possible in a x-y scatter plot, or am I forced to use another chart type? My thanks in advance for any suggestions on handling the integers representing degrees. Brad Hi, Assuming dat...

Authentication between asp.net 2.0 and asp.net 1.1
I am running CRM 3.0 which is running in its own app pool for asp.net 1.1. I've added a virtual directory under the CRM website which runs under a different app pool for asp.net 2.0. This is where I put my custom web pages. Both app pools are running under the NETWORK SERVICE account. Both the CRM website and the virtual directory has windows authentication. My question is why I am prompted to enter my credential when I access the custom page through CRM the first time I access the page? Have I not already been authenticated? Hmm not sure whether this can help. Are you sure you put...

Remove macro is greyed out
I want to remove a sort macro that I made which is in a workbook that I made. When I go Tools|Macro|Macros I see the macro but the delete button is greyed. How can I remove the macro? Beemer Sometimes, the project is protected by the developer. But if you created the macro, then you know if you protected that project. Did you? Do you get prompted for a password when you try to access the project (inside the VBE)? Beemer wrote: > > I want to remove a sort macro that I made which is in a workbook that I > made. When I go Tools|Macro|Macros I see the macro but the delete ...

adding macro to right mouse click menu
I know how to add macros to toolbars and menus, but I would like to add a macro to the right mouse click menu in Excel. Can anyone tell me how to accomplish this? Thanks and regards This can be done only with code. With Application.CommandBars("Cell").Controls.Add(temporary:=True) .Caption = "Click Me" .OnAction = "'" & ThisWorkbook.Name & "'!MyMacroName" End With -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "JimB" <JimB@discussions.microsoft.com>...

stop SR-1 messages
does anyone have a way to stop SR-1 messages - program trying to access e-mail when another program gets address from outlook to send mail. On 2000 server using Outlook 2000 SR-1. Does upgrade to SP3 give more customizing to block these popups? Not really. See if the information on the following page helps: http://www.slipstick.com/outlook/esecup.htm#autosec -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:01f301c3d556$f42d7770$a401280a@phx.gbl, Gary Louis...

Macro help to shorten "Save As" for emails
In a perfect world I would be able to create a macro that looks at an email (like a rule does) and "Save As" to a file folder on our server (not an exchange folder). I've done a search in this newsgroup to no avail. One person asked but they were using Express. I have Outlook 2003. Any suggestions, code you can share or links you can recommend? Thanks to all. scrowley@littleonline.com At its most basic, a VBA procedure you could run as a rule action in Outlook 2002 or 2003 to save the message as an .msg file could be: Public Sub SaveAsSomething(MyMail as MailItem) ...

2007 EEO-1 Report Requirements
Does anyone know if the new requirements for the 2007 EEO-1 report will be available for download at year-end? I just replied to a post from 7/19/06 by jnason on this same topic in the hopes that it would generate a response from MS. I haven't been able to find anything on CustomerSource about it and my HR dept is starting to get antsy. Doug "Elaine" wrote: > Does anyone know if the new requirements for the 2007 EEO-1 report will be > available for download at year-end? ...

Delete Macro #2
I have a file with Macro4, Macro5, Macro6 and Macro7. When I select Tools >>> Macro >>> Macros, these four macros are displayed yet the delete button is greyed out. In the VB editor there are no modules with these macros. I'd really appreciate anyone who can advise how I can remove these macros. Thanks Need to Protect/Hide Macro/Modules Posted by Dan on October 15, 2001 10:18 AM From the Microsoft Web Site, HTH: You can protect the modules from being viewed by locking the project for viewing, providing a password, and then saving the file normally. NOTE: By doing th...

Difficult Time/ Deleting Macro/ Tool Bar
In deleting a macro my steps are: Tools--Macro--Macro-- select Personal.XLS in Macros Dialog Box. Then Unhide Personal Workbook by choosing Windows on the tool bar, then selecting unhide on the drop down and clicking O.K. to the dialog box. Then Tools---Macros--Macros again. Highlight the Macro click delete. During this process Unhide does not appear highlighted on the Windows drop down. Some how trouble shoot this situation? You could delete the macro from the VBA editor. Simply press Alt+F11, then in the navigation window select your personal workbook. Find the macro you ...

I type a date (1/05) in a cell, press enter, it reads ####.Why?
I'm entering data inMicrosoft Excel and am fairly new to Excel. I'm entering dates in cells in a worksheet and typred in a date and pressed enter and all it will do is read #####. Why does it do this and how do I get the computer to recognise the actual numbers of the date I entered? erg Probably the cell is not wide enough. -- Kind regards, Niek Otten Microsoft MVP - Excel "KIWI" <KIWI@discussions.microsoft.com> wrote in message news:D1F07478-F035-4DDF-8F77-5FC5A070155F@microsoft.com... | I'm entering data inMicrosoft Excel and am fairly new to Excel. I...