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. 1 followers. Follow

3 Replies
676 Views

Similar Articles

[PageSpeed] 54

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:

MSCRMExRouter errors in event log
This error shows constantly in our our event logs on the Exchange Server: Event Type: Error Event Source: MSCRMExRouter Event Category: None Event ID: 0 Date: 5/22/2007 Time: 3:09:03 PM User: N/A Computer: TARARI-EXCH Description: System.NullReferenceException: Object reference not set to an instance of an object. at ADODB.ConnectionClass.get_State() at Microsoft.Crm.Tools.ExchangeConnectorService.ExchangeSinkServiceCore.ProcessMailbox(ExchangeSinkServiceMailbox mailbox) at Microsoft.Crm.Tools.ExchangeConnectorService.ExchangeSinkServiceCore.ExecuteService() Anyone see this er...

Event logs
How far back would I be able to access event logs? Do they just 'disappear' from the event log when the maximum size is reached? or are they then stored somewhere else? depending on your settings they are supposed to be re-written as needed if they have reached the maximum size of your log settings. they don't disappear neither are they stored elsewhere! If you had something in your log and it disappeared then you have a bug! Good luck, I saw that happen once but didn't find the solution. I had to reinstall! dwc wrote: > How far back would I be able to access event l...

how to trap the sort event?
Hello everybody Situation: the user press the option [A->Z] on a column of a datasheet form. Is there a way to trap the sort event before it's applied so let me CHANGE THE SORT CRITERIA on the fly and then continue the sorting? Thanks in advance robeito wrote: >Hello everybody > >Situation: the user press the option [A->Z] on a column of a datasheet form. > >Is there a way to trap the sort event before it's applied so let me CHANGE >THE SORT CRITERIA on the fly and then continue the sorting? > >Thanks in advance I don't think t...

Defining an Activecell
Each month I add new data to an existing sheet. I then want to run a macro to insert a new column and a formula. Trouble is this data increases rows each month and I need to be able to identify the final row each time so I can copy the formula down. ie in Jan the data finished at row 100. In Feb it finishes at row 200. I need the macro to be able to know to copy the formula down to row 200 in Feb for example. Hi Ant You can use this function fir finding the last row with data on thye worksheet Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:=...

Need an HTML replacement Macro
I need to perform a find/replace in a Word doc so that text encased in a specific html tag is replaced with a style mathing that tag. For instance, this: <em>some text here</em> <em>different text in this one</em> should be replaced with this: some text here <--(image that this is bold/italics) different text in this one <-- (same comment) I'm having trouble figuring this one out. Thanks in advance for any guidance. Please clarify. Is this HTML source code, or text in a Word document? In other words, is the TEXT <em> there, or not...

Macro problem: Abortion when trying to save on existing filename
Hey guys EXCEL-VBA Problem When using ActiveWorkbook.SaveAs Range("x1").Value ActiveWorkbook.Close - its ok when the file/filename specified in range X1 not exists, but if the file allready exist,a standard windows messageboard "Replace file? "YES" "NO" "EXIT appears. When I choose "YES" the macro works and the file is replaced. When I choose "NO" or "EXIT" my macro crashes. How can I make my macro by-pass this and just abort whithout crashing? Hopefully regards Snoopy Hi Snoopy If you always want to overwrite use App...

Saveas Macro
Hi, i have a code that prints a range as a jpg, opens outlook and puts it in. What i need it to do is name itself as the filename to which it is created in. Example: c/data/excel/1234 myclient.xls ( file it is to be created in) i would like it to name itself 1234 myclient.jpg it doesnt save anywhere, only as an attachement but it needs a name. i can alter the code and give it a standard name like Quote.jpg but when you send alot to the same person and they save the attachment, they all have the same name so this is why i need the change. Can this be acheived? REgards, Nigel If you don&...

2007 Macros converting from 2003
I created a simple macro in 2003 to convert text in the slide to a certain size font. I read that the original file (which I named standard.ppt) needs to be open to run. This worked for 2003 but I have been upgraded to 2007 and now I can not get the macro to run. I have already attempt to change the settings to 'enable' all macros, etc. and nothing seems to be working. How can I get this to run in my 2007? Here's teh original code: In article <OFKgLK74KHA.980@TK2MSFTNGP04.phx.gbl>, Annette wrote: > I created a simple macro in 2003 to convert text ...

Calendar events
Hi, may be a very simple question: How can I mark a calendar event as done or canceled? I like to reconstruct whether a scheduled meeting happened some months ago or not... Thanks Steffen Calendar items can't be marked as complete - task can, tho. Steffen Loringer wrote: > Hi, > > may be a very simple question: How can I mark a calendar event as done > or canceled? I like to reconstruct whether a scheduled meeting > happened some months ago or not... > > Thanks > Steffen ...

Running VBA Macros in Outlook 2003
I have an application which runs perfectly in my (single user) Outlook 2003. I run a macro which copies all the attacments and the body of the email to a disc folder. At the client's networked workstations, I get a message that macros won't run (in Outlook 2003). I've looked at the Microsoft's explanations - phew!!! I guess that there is some sort of system ban. Can anyone suggest how I might approach the System Admin. to allow this single macro to run on some of the network workstations? I've seen something about "signatures"? Is this a way of getting round ...

Bringing to a cell the last date an event occurred
This is a long worksheet and need a formula, but don't know if possible. A COLUMN B COLUMN C COLUMN Doe, John Jan 05 a Doe, Jane Jan 16 a Presley, Elvis Jan 22 Doe, Jane Jan 30 a All the previous data will be in SHEET 1 Now, in SHEET 2 : COLUMN A = all possible names in COLUMN A SHEET 1, written by me. The formula will be used in COLUMN B, SHEET 2 and will tell me which was the last date for example DOE,JANE had a letter "a" (Jan 30 should be the result of the formula) I'm thinking in VLOOKUP formula but don...

Event to click into TaskBar of app
Hi all, I have a problem about catching/hooking mini/maxi events when user click into an app on taskbar? WM_NC messages. For example, do a WM_NCLBUTTONDBLCLK handler and if you don't call the superclass you won't get the maximize-on-double-click. For some of the events, WM_SYSCOMMAND messages. In particular, the special cases of SC_MINIMIZE and SC_MAXIMIZE are what are called from the system menu, which is what is available from the taskbar. joe On Wed, 24 Sep 2008 09:07:56 +0700, "James Duy Trinh \(VietDoor\)" <vietdoor@gmail.com> wrote: >Hi all, >...

Activecell problem #2
I have a really basic question I am trying to print a certain area which starts at a certain cell which may differ every week. I have the code to get to the bottom right of the area I want to print but then I want to select the area, from the active cell to A3: 'go to the bottom cell in colum L Range("L100").End(xlUp).Select ???????? what goes in here?????? 'Print the selected area ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Any help appreciated. remove your select line and add range(Range("A3"),Range("L100").End(xlUp)).Select -...

VBA? Activecell formatting
Looking for a simple VBA macro for formatting three cells starting wit the active cell. Want to use ctrl-d to select activecell, then next two cells to th right and then fill all three cells with color red -- Message posted from http://www.ExcelForum.com Just a single line of code will do it: Range(ActiveCell, ActiveCell.Offset(0, 2)).Interior.Color = vbRed HTH, Nikos "click4mrh >" <<click4mrh.1bmwdk@excelforum-nospam.com> wrote in message news:click4mrh.1bmwdk@excelforum-nospam.com... > Looking for a simple VBA macro for formatting three cells starting with >...

macros on a form
hi we have a workbook containing serveral worksheets each worksheet contains a lot of data we have split each worksheet into three pages for print purposes what we want to do is have a form containing drop down list of names we need each name to select and run a macro can this be done? thanks in advance kevin Kevin Carter Sigma Machining XL and VBA are capable of a lot. From the generic description of the problem, I would say yes. -- Regards, Tushar Mehta www.tushar-mehta.com Business solutions leveraging technology Microsoft Most Valuable Professional (MVP) 2000-2004 In art...

ActiveCell
I am trying to achieve If ActiveCell In Range ("C22:K22,C24:K24") Then but I require assistance with the correct syntax. Thanks Sandy Try something like this: IF Not Intersect (ActiveCell, Range("C22:K22,C24:K24")) is Nothing THEN (Not really intuitive, is it?) Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Sandy" <sandy_stephen@DELETEhotmail.com> wrote in message news:5B6BB6C6-B6CB-46A8-B998-C8BA24EA9FFB@microsoft.com... >I am trying to achieve > > If ActiveCell In Range ("C22...

what are macros?
Please can somebody tell me what macros are? and of what use can I make of them? thanks, I use excel 2003 Please post in only one newsgroup, you might also take a look at http://www.cpearson.com/excel/newposte.htm to get more out of newsgroups. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm Was this question ever answered? I would really like to know the answer myself. I looked on the other posting sites, ans I did not se...

=OFFSET question
Hi All, My first posting so thanks in advance for any help I have the following formula in J12 ='Summary-March2006'!$C$16 I tried the following but no go =OFFSET(J12,-7,2) Yet when I translate the formula into a literal string then it works well as follows =OFFSET('Summary-March2006'!$C$16,-7,2) What function can i use to convert the reference argument of the OFFSET function into text or string ? Thanking you kindly ! Regards I think the function you want to use is =indirect(). But that expects to see something that looks like an address--and you're bringing back ...

MACRO BUTTONS
Hello, If you have seveal buttons to activate different macros, can you put all of the buttons into a drop-down? As always, any help greatly appreciated. Hi, Well maybe, sort of. First, where is the drop down - is it in the spreadsheet, on a toolbar, on a menu, or on a user form? What version of Excel are you using? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "JOHN" wrote: > Hello, > If you have seveal buttons to activate different macros, can you put > all of the buttons into a drop-down? > As always, any...

Save Error when protecting XL macros
If I try protecting macros using the standard function in the VBA editor, I get an error whenever I try to save the associated excel document. Usually this says "document saved, but cannot reopen due to low memory" (there is no memory problem though); at this point Excel often dies. If I take off the protection it works fine, which is bizarre. Anybody come across a similar problem / any suggestions? ...

Exchange 2003 test environment issues event ID 8026
Hi all, this is my first post here with you fine folks but i have garnered much help from everyones issues before mine, however, i have come up with a nice doozy here. I have seen a few posts with issues similar to this but nothing has been answered. First the Error -------------------------------------------------------- Date: 3/4/2004 Source: MSADC (active directory connector i belive) Time: 6:19:03 PM Catagory: LDAP Operations Type: Error Event ID: 8026 User: N/A Computer: <My Exchange Server> LDAP Bind was unsuccessful on directory <Exch...

Changing the ActiveCell
I want to Select and copy several block of data based on where the initial ActiveCell is locate. The following copys the first block. Dim StartingCell As String Dim EndingCell As String Dim RowOffset As Integer Dim ColOffset As Integer RowOffset = 2 ColOffset = 3 StartingCell = ActiveCell.Address EndingCell = ActiveCell.Offset(RowOffset, ColOffset).Address Worksheets("Sheet1").Range(StartingCell & ":" & EndingCell).Copy _ Destination:=Worksheets("Sheet2").Range("Jeff") Now, "Logically" I ...

an outlook2000 macro programming question.....
an outlook2000 macro programming question..... I created a user-defind field "price" in outlook2000 "task", I want to count the all item of "price" field, and I know Excel can do this perfect, but when I export outlook "task" to an Excel file, the "price" field can't be converted , Outlook2000 can't convert user-defined field, so I try to find another way to do this statistic work, anybody know if Outlook2000's macro programming can do this? ...

Event ID 481
I'm receiving Event ID 481 during backup on both the priv1.stm and pub1.stm files. These same errors are appearing on all 3 of my Exchange 2000 SP2 servers! Please help! Event Type: Error Event Source: ESE Event Category: General Event ID: 481 Date: 11/1/2004 Time: 12:55:02 PM User: N/A Computer: server name Description: Information Store (2604) An attempt to read from the file "E:\Exchsrvr\mdbdata\priv1.stm" at offset 0 (0x0000000000000000) for 65536 (0x00010000) bytes failed with system error 87 (0x00000057): "The parameter is incorrect. ". The read operatio...

Why unhide personal.xls to modify a macro
Many tutorials state that to create or modify a macro in the personal.xls workbook it needs to be unhidden. I seem to be able to view and modify such a macro when it is hidden. Why the apparant discrepancy? I am using Excel 2002. I an confused. Brian Tozer There would be no reason to unhide the workbook. -- Regards, Tom Ogilvy Brian Tozer <briantoz@ihug.co.nz> wrote in message news:bs24th$nc1$1@lust.ihug.co.nz... > Many tutorials state that to create or modify a macro in the personal.xls > workbook it needs to be unhidden. > I seem to be able to view and modify such a macr...