ActiveCell.Offset for event macro

I need to move from the active cell in any row to the cell in column 6 of 
the same row.
I know how to use ActiveCell.Offset to move a fixed number of rows and 
columns. But I can't figure out how to handle a relative row and absolute 
column situtation.
What I need is... ActiveCell.Offset(0,"however many cells there are between 
the column I'm in and column 6)
Do I need to create a variable to calculate the number of cells, or is there 
a simpler way?
Thank you!



0
Jeremy
1/12/2010 4:06:02 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1089 Views

Similar Articles

[PageSpeed] 16

ActiveCell.Offset(0,6 - ActiveCell.Column)
0
joe1946
1/12/2010 4:55:30 AM
ActiveCell.Offset(0,6 - ActiveCell.Column)
0
joe1946 (1)
1/12/2010 4:59:54 AM
activesheet.cells(activecell.row,6).select

is another way.


Jeremy wrote:
> 
> I need to move from the active cell in any row to the cell in column 6 of
> the same row.
> I know how to use ActiveCell.Offset to move a fixed number of rows and
> columns. But I can't figure out how to handle a relative row and absolute
> column situtation.
> What I need is... ActiveCell.Offset(0,"however many cells there are between
> the column I'm in and column 6)
> Do I need to create a variable to calculate the number of cells, or is there
> a simpler way?
> Thank you!

-- 

Dave Peterson
0
Dave
1/12/2010 1:45:32 PM
Reply:

Similar Artilces:

Outlook 2003 Duplicate Events
I have noticed, that when I receive a meeting request and accept it - two events are placed into my calendar for the same meeting (One as tentative the other accepted). What is causing this, and how can I stop it from happening? Thanks! Jeff ...

Macro Subroutines
Hi I want to build a new macro to run a sequence of macros already built, would like these subroutines to run after a delay of 4 seconds so as t not overload my computer. So when i hit the macro button on the toolbar i would like macro one t run straight away,then macro two four seconds after pressing th button,then macro three four seconds later(8 seconds after pressing th toolbar button),etc. Here are two of the macro subroutines that i want to build into the ne macro:- Sub DAILYSOLVER() ' ' DAILYSOLVER Macro ' Macro recorded 25/01/2004 by ADE ' ' SolverReset Solver...

CONDITION IN MACRO #2
This is an update to an earlier question I posed. I am using Access 2002. I'm trying to use a Condition in a Macro to determine whether or not to SetValue. It appears that the Condition will not allow me to use a "wildcard" (asterisk). The Field I'm checking is filled with entries like CRUZ SA or CRUZ SB or CRUZ SC. I simply want to set the Condition to look for CRUZ*...meaning any entry that begins with CRUZ. The Condition I set up is [CLASS]="CRUZ*". It doesn't work. But, when I set the Condition to [CLASS]="CRUZ SA" it works. Access Help seem...

Macros Disabled #2
Hello, I have a user running Excel 2000 on a Win 2000 sp3 machine. She has a file with several macros. The file is a form with data fields for names, numbers, etc. As soon as she enters any data and clicks the tab button to go to the next field, she gets an error message that the Macros have been disabled and the Tab key won't go to the next field. She emailed me the file and when I open it on my machine (same setup), I first get asked if I want to enable the macros. I say Yes - and everything works fine. I emailed the file back to the user, but it's still broken on her...

Macro that deletes values with condition
Hi, I need a macro that deletes duplicates of numbers that appear an odd number of times and that deletes duplicates and the value duplicated an even number of times. Example: Original data A 1 2 3 1 2 1 2 3 4 Result: A 1 2 4 Values 1 and 2 must remain and only delete duplicates because they appear an odd number of times (3), 3 must be deleted because it appears an even number of times (2) and 4 appears because it has no duplicates. Hope this can be done! Thank you so much Hi Insert a heading in row 1 and try this macro: Sub aaa() Dim f As ...

Record Macro Relative does not work?
Can someone help me with this macro. I want to go to any cell of my choice and press f2 to edit, then go to the fifth character of that cell, then select the next 6 characters and change them to a smaller font size (8), then "enter". I tried to make this macro using the record macro function, but it did not work how I want it to. It ends up pasting the contents of the cell which I used to record the macro initially. Can this kind of macro be done? To get a relative recorded macro, make sure the stopr recording toolbar is visible, it has 2 icons, one is to s...

Deleted Entourage calendar events not deleting in iCal
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Email Client: imap Hi, <br><br>Here's yet another sync oddity regarding Entourage and iCal/Mobileme. When I delete a calendar item in my Entourage calendar, it is not getting deleted in iCal and MobileMe and, hence, my iPhone. Even when I sync the iPhone manually via iTunes, the deleted item remains. Any thoughts on how to correct this would be appreciated. <br><br>Thank you. ...

Macro HELP!
I am trying to build a macro that will check for the day of the week in cell A1. "Mon", "Tue etc.., if true, it will copy the contents of another cell and paste to another cell. Hope this is discriptive enough. Help the newbie ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements Cycle, I think you should ask a more specific question. Maybe this is a first step...

Yet another Lotus macro question
I have for the most part converted to Excel but have a simple macro that on the surface cannot be converted. The Lotus macro used a database, template and list of branch names. The macro itself started with the name of the first branch, copied it to the template, sucked in the data for that branch and then printed the report. Then it went down to next name on the list and repeated the process until it reached the bottom of the list. In Excel it seems to indicate I have to repeat the steps for each and every branch on the list and not loop back through a 8 line macro that refers to the ...

Some macro help with formatting object please
I have a macro that turns on the calculate command function while the user works in it. I was asked if I could have the color of the text box that launches the macro turned RED when the calculate feature has been turned off. Is that possible? TIA! activesheet.textbox1.backcolor=&h0000ff -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Alan" <me@here.now> wrote in message news:oYeGc.8684$WM5.492396@news20.bellglobal.com... > I have a macro that turns on the calculate command fu...

importing text file into excel file using macro
Hi, How to import text file into excel file using Macro? Can this proces run on form menu button? Please help and give me some ideas of doin it. I have attached a text file for your reference. Thanks, An Attachment filename: rdata.txt Download attachment: http://www.excelforum.com/attachment.php?postid=66130 -- Message posted from http://www.ExcelForum.com >Hi, >How to import text file into excel file using Macro? Can this process >run on form menu button? Please help and give me some ideas of doing >it. I have attached a text file for your r...

Excel 2007 Macro: Open a file from any folder
Hello. How do you code a macro so that you can pop up a file dialog screen to open a worksheet from any folder of your choice? Thanks in advance. Same as always: Dim myName As String myName = Application.GetOpenFilename If myName = False Then MsgBox "You pressed cancel" Exit Sub End If Workbooks.Open myName HTH, Bernie MS Excel MVP "C C" <someone@atsbcglobal.net> wrote in message news:eS8FI0UeIHA.3940@TK2MSFTNGP05.phx.gbl... > Hello. How do you code a macro so that you can pop up a file dialog screen to open a worksheet > from any folder of y...

Using offset in series values of a chart
I have 1 named range created with the offset function on sheet(1): Month=offset($A$1,0,0,counta($A:$A),1) Simple to use as the x-as categories in a chart. For the y-axis I want to use data which can be offset 1, 2, up to 20 columns. Yes I can create 20 named ranges but to do that for 20 sheets in my workbook gets cumbersome. Therefor my question: Can I use the offset function in the series values, like: =offset(Month,0,7) In VBA it it can be done with: ..SeriesCollection(2).Values = Month.Offset(0, 17) But then I have to create one macro for each graph...again cumbersome. Please help. T...

Help Please- How to offset Date to correct column for Ageing
Hi All, I have a stock ageing sheet for finished goods, which uses a vlookup table, on entry of the Product code (last 3 Digits), this populates the next three columns with description, FG cost and Quantity of product. the quantity of product is entered again manually into the appropriate column, there are five columns these are banded <3 Months 0%, 3 to 6 Months 25%, 6 to 9 months 50%, 9 to 12 Months 75% and > 1 Year 100%, the percentage is liability provision. What I would like to do is on entry of the product code as above the quantity is automatically inserted into the correc...

can send but can receive- event log shows loop within Exchange
Hi all, When I try to send email to my exchange 2003 server from an external email account I never get the email. I get the following error message in my event log. ------------------------ A non-delivery report with a status code of 5.3.5 was generated for recipient rfc822;adrown@[192.168.2.249] (Message-ID <200411090532.iA95WemV015823@gatekeeper.com>). Causes: A looping condition was detected. (The server is configured to route mail back to itself). If you have multiple SMTP Virtual Servers configured on your Exchange server, make sure they are defined by a unique incoming port and t...

Event ID 9554
Hello, I"m getting the following error: Event Type: Warning Event Source: MSExchangeIS Event Category: General Event ID: 9554 Date: 10/11/2005 Time: 1:33:15 PM User: N/A Computer: !@#$!# Description: Unable to update Mailbox SD in the DS. Mailbox Guid: 3b5b90b3-9fcd-4540-9858-d13d6498e9e1. Error Code 0x8004010f For more information, click http://www.microsoft.com/contentredirect.asp. ------------------------------------------------------------------------------------------------ 3b 5b 90 b3 - 9fcd - 4540 -9858 - d13d6498e9e1 \b3\90\5b\3b\cd\9f\40\45\98\58\d1\3d\64\98\e9\e1 (msE...

macro #5
i want to make a macro record saving a file but it has to be differnet name file on the sheet. example if i change the name on the cell the save got to be the same name too when i click on save the macro would be asign to a picture. try activeworkbook.SaveAs Filename:=range("a1") "guest" <anonymous@discussions.microsoft.com> wrote in message news:047f01c39a78$28ce7d40$a301280a@phx.gbl... > i want to make a macro record saving a file but it has to > be differnet name file on the sheet. example if i change > the name on the cell the save got to be the s...

Enable Macro Message
How can you disable the message upon opening a file without setting the security to lowest? I do not want to give the user options to disable or enable the macro. Is that possible? Thanks. This is a user's security setting--not a developer's security setting. If you could change it, it wouldn't be of much use to the user, huh? Rook wrote: > > How can you disable the message upon opening a file without setting the > security to lowest? I do not want to give the user options to disable or > enable the macro. > > Is that possible? > > Thanks. -- Da...

Named Ranges
Hi! I created several worksheets that contained huge lists, so I created a dynamic named range. Now that the name has been inserted in my array formulas, my Macro runs very slowly. The Macro unprotects one of my sheets, sorts the data in a table, hides the blank rows and the protects the sheet again. There are no related named range formulas in that worksheet, so I'm not sure why it would be affected. The Macro ran quickly when I had a defined range of data. Would giving a named range like 'Date' and having text in my workbook with 'Date' create conflicts? Could this...

why getting event 9551 warnings?
I thought that when you switched to native mode (which we have) that these problems with zombie ACL's went away. Why are we still getting these errors? (running Exch 2003 SP1) Thx, J. ...

Creating Event procedures from a macro
Hi, I'm using Jon Peltier's workaround (to Excel's inability to create hyperlinks to chart sheets) to create a table of contents. The following event procedure is placed in the code module for the "contents" sheet. It activates the chart sheet when the user selects the linked cell: ' Thank you Jon! Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B2")) Is Nothing Then Charts("Chart1").Activate End If End Sub This works fine, except that I need to create it on the fly from within a ma...

Excel macro for copying range to another worksheet
On a monthly basis, I would like to copy the completed range (varies from month to month) of a database (Sheet1) to a master list (Sheet3). Once the data has been copied I intend to manually delete the entries of Sheet1 and start anew for the new month =96 for eventual transfer to Sheet3. The idea is to copy each month=92s data at the bottom of the previous months=92 (Sheet3). I followed Excel=92s record macro command but the macro I ended up with is not capable of placing the new data at the bottom of the existing one; it simply keeps overwriting the previous entry. Unfortunately, I don=92t...

What event does a hotkey generate?
All, I have a date field in a form that has vba code attached to the 'after update' event. Then I setup a hotkey to auto the current date in the same field. The problem I am having is that when I hit the hotkey sequence the field is populated with the current date - however my vba code does not work. Any idea what event the population of the field by a hotkey produces? Thanks for you help. On Tue, 15 Jan 2008 11:30:00 -0800, Tom <Tom@discussions.microsoft.com> wrote: None. And for good reason. Programmatic filling of fields typically does not trigger any event. So you rewrite...

Event Handling
Hello, I have a project that creates 18 CBitmapButtons. As you know CBitmapButtons cannot be created (or at least to my knowledge) from adding the componet to the form. So I decided to create 18 CBitmapButtons in my code to create the buttons. Here is the problem... I need to write an event handler for these 18 buttons, and since there isn't the convenice of the event handling that you will find when you are adding componets to the form, I cannot figure out how to manually add event handlers.. Here is some of the declarations I made: CBitmapButton* Button1 = new CBitmapButton(); Button...

Macro won't delete? Any ideas?
Hi, I inserted an Excel 5.0 dialog worksheet into my spreadsheet. Excel seemed to insert also a macro, which I then located (Tools -> Macro -> Macros, then deleted all the macros in the document). I also deleted the worksheet. However, since then, Excel asks me every time I open it if I would like to enable macros. This is very annoying seeing as I thought I deleted them. Can anyone direct me where I might find the remaining macro? thanks. G Gabbon You have to delete the module that held the code too. Check here http://www.nickhodge.co.uk/tipstrick.htm#macrowarningremoval ...