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 
0
3/13/2008 7:19:57 PM
excel 39879 articles. 2 followers. Follow

3 Replies
581 Views

Similar Articles

[PageSpeed] 24

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:K22,C24:K24") Then
>
> but I require assistance with the correct syntax.
>
> Thanks
> Sandy 


0
3/13/2008 7:28:40 PM
Certainly is not.
Many thanks though Ron.

Sandy

"Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message 
news:#drA0CUhIHA.200@TK2MSFTNGP02.phx.gbl...
> 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:K22,C24:K24") Then
>>
>> but I require assistance with the correct syntax.
>>
>> Thanks
>> Sandy
>
> 
0
3/13/2008 7:35:38 PM
Glad to help.

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Sandy" <sandy_stephen@DELETEhotmail.com> wrote in message 
news:eOIduFUhIHA.1408@TK2MSFTNGP03.phx.gbl...
> Certainly is not.
> Many thanks though Ron.
>
> Sandy
>
> "Ron Coderre" <ronREMOVETHIScoderre@bigfoot.com> wrote in message 
> news:#drA0CUhIHA.200@TK2MSFTNGP02.phx.gbl...
>> 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:K22,C24:K24") Then
>>>
>>> but I require assistance with the correct syntax.
>>>
>>> Thanks
>>> Sandy
>>
>> 


0
3/13/2008 7:49:22 PM
Reply:

Similar Artilces:

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:=...

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 >...

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...

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 ...

Selecting a range relative to ActiveCell, copy it to second Workshett within Workbook
This works through "ActiveCell.Offset(0, -8).Activate" but then doesn't. Any help appreciated. Sub SortforMember() Worksheets("Sheet1").Activate Worksheets("Sheet1").Range("I1").Select Worksheets("Sheet2").Activate Worksheets("Sheet2").Range("a2").Select Do Until Cell = "Null" Sheet1.Activate ActiveCell.Offset(1, 0).Activate If ActiveCell = "Yes" Then ActiveCell.Offset(0, -8).Activate Range(Cells(1, 1), End(x1ToRight)).Copy _ desti...

Select range from ActiveCell do to Lastcell in ColumnA
-- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware. Sub rangefinder() Dim rng As Range Set rng = Range(ActiveCell, Cells(Rows.Count, 1).End(xlUp)) MsgBox rng.Address 'do something with rng End Sub Gord Dibben MS Excel MVP On Mon, 15 Mar 2010 15:23:02 -0700, Aussie Bob C <AussieBobC@discussions.microsoft.com> wrote: Hi Gordon Sorry about double post. I require the range to include across to Column M. My second post states that. -- Thank you Aussie Bob C Little cost to carry...

ActiveCell.Text
I need a macro to delete any row in a spreadsheet that contains a certain word. I know how to do this for a number or formula, but how do I find a cell that contains a specific word? Could you not just just Find-Replace on the whole sheet (Ctrl A) and use an empty string "" as the replace string? Gromit ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Just use the word as the search string (the "FindWhat:=" argument). It should work just fine....

ActiveCell.Offset Question
Hi, Spreadsheet with many columns: In All column, Row4 I have dates: What I like to do is: "IF" I am in Column y "Any row" I would like to go up to Y4 and copy that value down to another cell . "IF" I am in Column de "Any row" I would like to go up to de4 and copy that value down to another cell . I wrote this thinking it may work, but I keep getting mismatch Type: Sub Look4DATE() Dim mycell As Range Set mycell = ActiveCell.Offset(0, 0) Dim myrange As Range Set myrange = Range(ActiveCell.Offset(0, 0), mycell) myc...

Select range from ActiveCell do to Lastcell
From Leith Ross 2/7/2006 Dim EndCell As Range Set EndCell = ActiveSheet.Cells(Rows.Count, "A").End(xlUp) ActiveSheet.Range(ActiveCell, EndCell).ClearContents If I change the A to an N it selects a range from ActiveCell down to last entry in Column N. My ActiveCell is in Column A but Column N may not have data down to the last cell as in Column A How do I select a range of cells from an ActiveCell in column A across to Column N and down to last data cell in column A -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007...

ActiveCell.FormulaR1C1
I need help.... The following statement is being used in a do loop. ActiveCell.FormulaR1C1 = "=IF(" & TagNmeMe & "!R[" & Cnt1 & "]C[-1]="""",""""," & TagNmeMe & "!R[" & Cnt1 & "]C[-1])" What I need it to do is this: if(sheet1!A5="","",sheet1!a5) then 9 rows down paste this: if(sheet2!A5="","",sheet2!a5) Problem is when recording a macro the cnt1 changes from nothing to 9 then to 18 but the formula does not mimic what...

ActiveCell.Formula
Hi How on earth do I make the following formula to function ? TempNumber=15 ActiveCell.FormulaR1C1 = "= TempNumber*R[-2]C" I have tried val() and different combinations of & and " Hobe somebody will reply. Kind regards Leif Rasmussen One way: TempNumber=15 ActiveCell.FormulaR1C1 = "= " & TempNumber & "*R[-2]C" In article <0c3e01c394c1$40a24080$a401280a@phx.gbl>, "Leif Ramussen" <anonymous@discussions.microsoft.com> wrote: > Hi > How on earth do I make the following formula to function ? > > Temp...

ActiveCell Problem
I can indepently get Trace to work, and Copyrange to print one line at a time. However, I can not both to work in conjunction with each other. wht happens, is the a1..b1 rows, pause for 3 sec and then goes down one row to a2..b2, pauses for two seconds, and then copies to e1..f1. Here is the problem. I now want to copy from e1..f1 to e5..f5 and loop until there is a blank line in column A,B --------------------------------------------------- Public NextTime As Date Sub Trace() Sheet1.Range("A1").Select newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 3 wai...

VBA ActiveCell Range
Hi All, Can't do this simple bit of VBA. Basically, due to various previous macros, you end up at a cell in th worksheet. This cell varies, it could be any cell. I want my macro to remember which cell this is, for instance, A20, suc that I can then refer back to this cell later in the macro. I cant do it. I feel such a fool! Please help df -- Message posted from http://www.ExcelForum.com When you have ended up at that cell, save a variable to it Set oldCell = Activecell Later on you can then refer back to oldCell. -- HTH Bob Phillips ... looking out across Poole Har...

activecell & Vlookup
Is there a way to rewrite a vlookup based on activecell? =VLOOKUP("activecell",Sheet1!A:B,2,FALSE) where in it picks up value from the active cell? Right click on your worksheet and put something like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A1") = ActiveCell.Value End Sub change A1 to any blank cell and refer to that cell in your formula. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261 View thi...

Excel 2003 - VBA
Simple, I'm sure, but not obvious to me. I am trying to put a conditional format on a cell that when the cell is true, it shades the cell dark gray. I have the cell selected and link to the shade routine. When I record the procedure and try this with cell B14 this is what I get. Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=B14=TRUE" Obviously this will not work because the ActiveCell will not always be B14 and I need to replace the "B14" with a variable. I've tried a number of things but with no sucess. Any help will be appreciated. Craig ...

? offset('activecell',,-2,1,1) in Named Formula ?
I have a cell formula as part of a WorkSheet for explaining stats =prob_T^(A9) * (1-prob_T)^(n-A9) I would prefer to use a Name, k, in place of the A9 relative reference, as this would align the WorkSheet with standard stats names & make explanations easier to understand. This name needs return a reference to the cell at at columnoffset(-2) relative to the cell the Name is used in ... the syntax I've used in the subject line is as close as I've got so far ... ?? is this something that can be made to work? input gratefully received Mat You can define k as: ...

Activecell.activate dont work
hi everyone!! i need a urgent help on this one. i recieve a excell sheet with macro that is running perfectly in japan. when i recieved it, it halt on the line with activecell.activate which is in the first line of the program. the error says: run-time error '-2147319784(80028018)': Method 'Activate' of the 'Range' failed does this error has to do with my operating system which is win xp prof? or with my office 2000? thank you very much!! and more power to you!!! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ ...

Checkbox1.LinkedCel = ActiveCell -- "Why wont it work?"
Have little bit of code that dumps Activex Checkbox on page, sizes it to fit in nicely to the ActiveCell. Works beautifully, except I want to make the linked cel the active cel as well, and I really don't want to have to go into design mode and do it manually for every checkbox I create. For some reason this code does not work as I expect. I'm sure theres a fundamental principle I am unaware of. Checkbox1.LinkedCel = ActiveCell Also another question: When dumping the checkboxes on the page, they are incremented by 1..eg Checkbox1, Checkbox2 etc. Seems to me there must be an index so...

Show Activecell Date in MsgBox
Can the date in the ActiveCell be shown in a MsgBox, allowing the user to check whether to continue or Cancel the macro running. All data on and below the ActiveCell is cut and pasted into second sheet. -- Thank you Aussie Bob C Little cost to carry knowledge with you. Win XP P3 Office 2007 on Mini Mac using VMware. dim resp as long resp = msgbox(Prompt:="Value is: " & format(activecell.value, "mmm dd, yyyy") _ & vblf & "Continue?", buttons:=vbyesno) if resp = vbno then exit sub '??? end if 'do the re...

select a specific ActiveCell by VBA
In any cell in column "F", a user can select a code number from a drop down list. These numbers go up to over 100, so, if the user isn't sure of which number to select, cell F6 can be rightclicked and VBA will take the user to a different sheet where all the code numbers (and their associated meanings) are listed. A command button on that sheet will take the user back to the ActiveCell on the first sheet. Even if the user was working in cell F2000, the active cell is now always F6 To get around this, how can I set the active row to the bottom most row with data i...

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! ActiveCell.Offset(0,6 - ActiveCell.Column) ActiveCell.Offset(0,6 - ActiveCell.Column) activesheet.cell...

ActiveCell giving blank value
I am having the following code: OSHEET.CELLS(2,3).SELECT MSGBOX OSHEET.CELLS(2,3).VALUE MSGBOX ACTIVECELL.VALU The cell is having value, but still, its giving the following error: ERROR DESCRIPTION: OBJECT REQUIRED 'ACTIVECELL i did the following also: MSGBOX ACTIVECEL but its giving me a blank messagebox Please someone help me out in this :confused: . Thanks in advanc -- itstom ----------------------------------------------------------------------- itstome's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2678 View this thread: http://www.excelforum.com...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

need help with seeting an activecell in VBA
this is the code i have so far...... Dim rng As Range Application.DisplayAlerts = False Application.ScreenUpdating = False Set wb = Workbooks.Open("f:\Bidditjobs.xls") Set rng = wb.Worksheets("jobs").Columns(1).find(cobOpenPro.Value) ActiveCell.Select Biddit.txtJobNumber.Value = ActiveCell.Value Biddit.cobRep.Value = ActiveCell.Offset(0, 1).Value Biddit.txtMainPrice.Value = ActiveCell.Offset(0, 2).Value Biddit.txtMainPercent.Value = ActiveCell.Offset(0, 3).Value ..... How could I have the ActiveCell.Select begin in the row it finds the cobOpenPro.value? right not is is on...