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 somewhere that keeps track of these. How can I refer these checkboxes by their ordinal number programmatically at runtime so that I can do operations with the checkbox at that time, using the index or the ordinal part of the string. I could write something to strip the ordinal part out of the name, but that seems like a major kluge.
nowhere9650
excel
39879 articles. 
2 followers.
1 Replies
643 Views
This worked ok for me:
Worksheets("sheet1").CheckBox1.LinkedCell = ActiveCell.Address(external:=True)
From a previous post that may help you:
If I knew how many checkboxes there were and they were named nicely, I'd do
something like:
Option Explicit
Sub testme()
Dim iCtr As Long
For iCtr = 1 To 4
Sheet1.OLEObjects("checkbox" & iCtr).Object.Value = False
Next iCtr
End Sub
If I wanted to get them all, but didn't know how many, I could get them this
way:
Sub testme2()
Dim OLEobj As OLEObject
For Each OLEobj In Sheet1.OLEObjects
If TypeOf OLEobj.Object Is MSForms.CheckBox Then
OLEobj.Object.Value = False
End If
Next OLEobj
End Sub
If you knew the names of just the checkboxes you wanted to change:
Sub testme3()
Dim iCtr As Long
Dim myCBXNames As Variant
myCBXNames = Array("checkbox1", "checkbox2")
For iCtr = LBound(myCBXNames) To UBound(myCBXNames)
Sheet1.OLEObjects(myCBXNames(iCtr)).Object.Value = False
Next iCtr
End Sub
Time Traveller wrote:
>
> 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 somewhere that keeps track of these. How can I refer these checkboxes
> by their ordinal number programmatically at runtime so that I can do
> operations with the checkbox at that time, using the index or the ordinal
> part of the string. I could write something to strip the ordinal part out of
> the name, but that seems like a major kluge.
--
Dave Peterson
ec35720@msn.com
ec35720
Similar Artilces:
Select contiguous cells by ActiveCell.Interior.ColorIndex and apply borders
I've got some code that loops through a list of start/end dates and fills in cells in each row based on the number of days between those dates (kind of like a Gantt chart) This works fine, but I'd like a way to add borders to the cell range so they stand out a bit better. The difficulty I have is that the borders are currently being added to each individual cell, rather than one border for the whole selection: Each set of selections must stay within the specific row, so I can't have borders applied across multiple rows (hope that makes sense!) Here is the code:Sub Ga...
inserting rows duplicates combobox's linkedCell
I have the a combo box that calls a macro when changed. Its linked cel is positioned directly to its left. Far above the combo box, I wil need to frequently insert and delete rows. My problem is the following: whenever I insert a row, the value of th combobox's linked cell is duplicated onto the cell above it, and when remove a row, the value of the linked cell is duplicated onto the cel below it. The problem does NOT occur when I do not assign a macro to the comb box. The problem DOES occur when I assign an empty macro to the comb box (i.e. "sub nomacro() [newline] end sub&qu...
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...
ComboBox linkedcell population not recognized by Vlookup
I have created a worsheet in which I have Vlookup formulas performing calculations. I then created Combobox drop lists and linked given cells. Everything is working properly except the combobox populating the linkedcell is not being recognized by my Vlookup functions. Has anyone ran into this problem? If so can you tell me how to fix. Thanks Ed Hi you're probably searching for a number. If yes it could be that the value returned from your combo box is stored as 'Text' (though it represents a number). If you current VLOOKUP formula looks like =VLOOKUP(A1,'lookup'!A1:B...
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...
LinkedCell
I have a MSForm Checkbox on a worksheet that uses a linkedcell to update a cell on the worksheet. The sheet is protected and the cell is locked. In a class module, when the change event is triggered for the checkbox, I unprotect the form, and turn off enableevents. But I keep getting a cell protected message on the linkedcell. I tried unlocking the cell in the change event, but I still get the message and then the code finishes succesfully. How should I handle the lock issue? I thought about removing the linkedcell setting and setting it in the event (it's currently s...
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...
Checkbox and linkedcell question
I want to use the cell address of a checkbox's linked cell. How do I reference it in VBA so I don't have to write the code over and over. I have the location hardcoded now, but I have about 30 checkboxes. Here is what I want to happen when I click any checkbox. Private Sub TTWK1_Click() If Cells(3, 2).Value = True Then Cells(3, 1) = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Else Cells(3, 1) = "" End If End Sub Thank you for any ...
List box activeX control and linkedcell value
Hi, I am creating a list box containing serveral characters from the wingdings font set. I am using the activeX control version so I can set the font that is displayed in the list box to display correctly. So it displays an arrow symbol instead of the ASCII equivelent "é". I would then like to have this single listbox containg these items be applied to a range of cells instead of just one cell as defined by the LinkedCell setting. I tried using a range in this field, but it only accepts a single cell (for example A1). I would like to be able to have this apply to A1:A10...
"ActiveCell.Offset(1, 0).Select" for filtered cells?
Is there something that works on visible, filtered cells like "ActiveCell.Offset(1, 0).Select" works on visible, unfiltered ones? What I mean is the have cursor movement happening to what is visible yet that works when the cells are not filtered? Here's hoping. I thought everything was working right and tight till I saw that these types of things aren't working on my filtered sheet? <g> Thanks. Range("A1:A10").Specialcells(xlCellTypeVisible) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Star...
Syntax to save ActiveCell to MyActiveCell then select later
Using 2003 Have been attempting to capture the range (or whatever I need) of an active cell to reselect later. I would like to perform the essence of the code below (but the syntax is not correct) Sub ReturnToOriginalActiveCell() Dim MyActiveCell As Range MyActiveCell = ActiveCell.?????????? [Other VBA Code] MyActiveCell.Select End Sub TIA Dennis Hi try sub foo() dim old_cell as range set old_cell = activecell 'your code old_cell.select end sub -- Regards Frank Kabel Frankfurt, Germany Dennis wrote: > Using 2003 > > Have been attempting to capture the range (or wh...
Linkedcell protection problem
Hi. I am new to Excel (and discussion groups!). I am wondering if someone knows how I can fix this problem. I have a combobox that uses the linkedcell property to put a value into another cell. I am protecting the entire sheet except the combobox. The problem is that this protection is stopping the combobox from putting the value into the other cell. Any idea how I can get the value from the combobox to go into another cell and yet still protect the cell from users editting it? Thanks. -- Karin Karin - try this if I understand your dilema. I dont know what Excel you are using but in g...
Combo box and Linkedcell does not work in Excel 2003
Hi programmers, I created a combo box in Excel 2000 and a linkedcell. They worked very well. I updated to Excel 2003, and they don't work well any more. The problem is when I click down arrow to select a name in Combo box, it shows an error message as said that the linked cell must be unlocked. I have to locked the linked cell. Otherwise, everybody can change data in the linked cell. However, if I click on the middle of the combo box, it works well; and if I click on the down arrow, it shows error message. Any one knows how to fix this problem? Please help me. Thank you. Tv...
Combo Box "LinkedCell" option
do I need to manually type in the linked cell for a combo box or is there a way to automate this process? I have over 200 columns of combo boxes! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 ...
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 ...
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:=...
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....
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...
I've got some code that loops through a list of start/end dates and fills in cells in each row based on the number of days between those dates (kind of like a Gantt chart) This works fine, but I'd like a way to add borders to the cell range so they stand out a bit better. The difficulty I have is that the borders are currently being added to each individual cell, rather than one border for the whole selection: Each set of selections must stay within the specific row, so I can't have borders applied across multiple rows (hope that makes sense!) Here is the code:Sub Ga...
inserting rows duplicates combobox's linkedCell
I have the a combo box that calls a macro when changed. Its linked cel is positioned directly to its left. Far above the combo box, I wil need to frequently insert and delete rows. My problem is the following: whenever I insert a row, the value of th combobox's linked cell is duplicated onto the cell above it, and when remove a row, the value of the linked cell is duplicated onto the cel below it. The problem does NOT occur when I do not assign a macro to the comb box. The problem DOES occur when I assign an empty macro to the comb box (i.e. "sub nomacro() [newline] end sub&qu...
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...
ComboBox linkedcell population not recognized by Vlookup
I have created a worsheet in which I have Vlookup formulas performing calculations. I then created Combobox drop lists and linked given cells. Everything is working properly except the combobox populating the linkedcell is not being recognized by my Vlookup functions. Has anyone ran into this problem? If so can you tell me how to fix. Thanks Ed Hi you're probably searching for a number. If yes it could be that the value returned from your combo box is stored as 'Text' (though it represents a number). If you current VLOOKUP formula looks like =VLOOKUP(A1,'lookup'!A1:B...
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...
LinkedCell
I have a MSForm Checkbox on a worksheet that uses a linkedcell to update a cell on the worksheet. The sheet is protected and the cell is locked. In a class module, when the change event is triggered for the checkbox, I unprotect the form, and turn off enableevents. But I keep getting a cell protected message on the linkedcell. I tried unlocking the cell in the change event, but I still get the message and then the code finishes succesfully. How should I handle the lock issue? I thought about removing the linkedcell setting and setting it in the event (it's currently s...
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...
Checkbox and linkedcell question
I want to use the cell address of a checkbox's linked cell. How do I reference it in VBA so I don't have to write the code over and over. I have the location hardcoded now, but I have about 30 checkboxes. Here is what I want to happen when I click any checkbox. Private Sub TTWK1_Click() If Cells(3, 2).Value = True Then Cells(3, 1) = "=NOW()" Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False Else Cells(3, 1) = "" End If End Sub Thank you for any ...
List box activeX control and linkedcell value
Hi, I am creating a list box containing serveral characters from the wingdings font set. I am using the activeX control version so I can set the font that is displayed in the list box to display correctly. So it displays an arrow symbol instead of the ASCII equivelent "é". I would then like to have this single listbox containg these items be applied to a range of cells instead of just one cell as defined by the LinkedCell setting. I tried using a range in this field, but it only accepts a single cell (for example A1). I would like to be able to have this apply to A1:A10...
"ActiveCell.Offset(1, 0).Select" for filtered cells?
Is there something that works on visible, filtered cells like "ActiveCell.Offset(1, 0).Select" works on visible, unfiltered ones? What I mean is the have cursor movement happening to what is visible yet that works when the cells are not filtered? Here's hoping. I thought everything was working right and tight till I saw that these types of things aren't working on my filtered sheet? <g> Thanks. Range("A1:A10").Specialcells(xlCellTypeVisible) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Star...
Syntax to save ActiveCell to MyActiveCell then select later
Using 2003 Have been attempting to capture the range (or whatever I need) of an active cell to reselect later. I would like to perform the essence of the code below (but the syntax is not correct) Sub ReturnToOriginalActiveCell() Dim MyActiveCell As Range MyActiveCell = ActiveCell.?????????? [Other VBA Code] MyActiveCell.Select End Sub TIA Dennis Hi try sub foo() dim old_cell as range set old_cell = activecell 'your code old_cell.select end sub -- Regards Frank Kabel Frankfurt, Germany Dennis wrote: > Using 2003 > > Have been attempting to capture the range (or wh...
Linkedcell protection problem
Hi. I am new to Excel (and discussion groups!). I am wondering if someone knows how I can fix this problem. I have a combobox that uses the linkedcell property to put a value into another cell. I am protecting the entire sheet except the combobox. The problem is that this protection is stopping the combobox from putting the value into the other cell. Any idea how I can get the value from the combobox to go into another cell and yet still protect the cell from users editting it? Thanks. -- Karin Karin - try this if I understand your dilema. I dont know what Excel you are using but in g...
Combo box and Linkedcell does not work in Excel 2003
Hi programmers, I created a combo box in Excel 2000 and a linkedcell. They worked very well. I updated to Excel 2003, and they don't work well any more. The problem is when I click down arrow to select a name in Combo box, it shows an error message as said that the linked cell must be unlocked. I have to locked the linked cell. Otherwise, everybody can change data in the linked cell. However, if I click on the middle of the combo box, it works well; and if I click on the down arrow, it shows error message. Any one knows how to fix this problem? Please help me. Thank you. Tv...
Combo Box "LinkedCell" option
do I need to manually type in the linked cell for a combo box or is there a way to automate this process? I have over 200 columns of combo boxes! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 ...
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 ...
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:=...
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....
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...