Selecting a cell without cell referance

Hello.

Im trying to find a way to select a cell without the cell ref. Fo
example i have created a macro that searches for a particular word
When it find that word i want it to move across and clear the content
of the 5 cells next to it. When i try to record this it find th
correct cell but then when i movre accross it inserts cell refs. 

Thank you

--
Message posted from http://www.ExcelForum.com

0
8/9/2004 10:46:14 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
413 Views

Similar Articles

[PageSpeed] 15

Use the Offset function. Example fro VBA help:
ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

Best wishes

-- 
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in e-mail address


"LB79 >" <<LB79.1apwb0@excelforum-nospam.com> wrote in message
news:LB79.1apwb0@excelforum-nospam.com...
> Hello.
>
> Im trying to find a way to select a cell without the cell ref. For
> example i have created a macro that searches for a particular word.
> When it find that word i want it to move across and clear the contents
> of the 5 cells next to it. When i try to record this it find the
> correct cell but then when i movre accross it inserts cell refs.
>
> Thank you!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
bliengme5824 (3040)
8/9/2004 11:30:07 AM
Hi,

This VBA code should do the trick. Just change the phrase you ar
searhing for:

Sub FindNClear()
Cells.Find(What:="find this").Activate
ActiveSheet.Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), _
Cells(ActiveCell.Row, ActiveCell.Column + 6)).ClearContents
End Sub


- Asse

--
Message posted from http://www.ExcelForum.com

0
8/9/2004 11:36:18 AM
I would be a little more careful--just in case what you're looking for isn't
found:

Option Explicit
Sub FindNClear2()
    Dim FoundCell As Range
    With ActiveSheet.Cells
        Set FoundCell = .Cells.Find(What:="find this", _
                            after:=.Cells(.Cells.Count), lookat:=xlWhole, _
                            LookIn:=xlValues, searchorder:=xlByRows, _
                            searchdirection:=xlNext, MatchCase:=False)
        If FoundCell Is Nothing Then
            MsgBox "not found"
        Else
            FoundCell.Offset(0, 5).ClearContents
        End If
    End With
End Sub

And Find is one of those strange beasts that remember the settings from the last
time it was used.  So it's probably a good idea to set them the way you want.

"Jazzer <" wrote:
> 
> Hi,
> 
> This VBA code should do the trick. Just change the phrase you are
> searhing for:
> 
> Sub FindNClear()
> Cells.Find(What:="find this").Activate
> ActiveSheet.Range(Cells(ActiveCell.Row, ActiveCell.Column + 1), _
> Cells(ActiveCell.Row, ActiveCell.Column + 6)).ClearContents
> End Sub
> 
> - Asser
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/9/2004 10:36:16 PM
Dave Peterson wrote:
> *I would be a little more careful--just in case what you're lookin
> for isn't found:* 

Jeah, you're right. It's always good to have error checking.

> *And Find is one of those strange beasts that remember the setting
> from the last time it was used.  So it's probably a good idea to se
> them the way you want.*

and that's right also. I just took off all the "extra" arguments fro
the FIND function, because it seems that MS has a habit t
change/add/remove those arguments from one Excel version to an other
So just for compatibility.

- Asse

--
Message posted from http://www.ExcelForum.com

0
8/10/2004 6:25:53 AM
Reply:

Similar Artilces:

Help!!! Want 2 print blank cells.......
in execel with a header and footer, got those worked out, but when want to print out blank cells with the horizontal and vertical lines they don't show up!!!!! I need this for a customer log in sheet and I need a pae with 9 row and 5 columns, all with the lines printed out. HELLLP!!!!!!!: -- Message posted from http://www.ExcelForum.com <File> <PageSetUp> <Sheet> tab, Under the "Print" section, check the "Gridlines" box. Then <OK>. -- HTH, RD ============================================== Please keep all correspondence within the Group, ...

synconize outlook without exchange
Hello, Is there a simple "free" tool on the market that could syncronize outlook with for example a laptop. Thanks for any help!!! -- Hooters hooters jum jum jum, hooters hooters on a girls that's dumb ------------------------------------------ Al Bundy Spam sucks <nomail> wrote: > Is there a simple "free" tool on the market that could syncronize > outlook with for example a laptop. http://www.slipstick.com/outlook/sync.htm While it's not free, a Pocket PC would be one good way. -- Brian Tillman ...

Supressing a cell when printing
I have a range of cells that contain text. My question is how do I display the text on the spreadsheet but supress it when printing. You could use a macro that hides the particular rows/columns in a before print macro and then unhide them again Rows("3:3").EntireRow.Hidden = True ..PrintOut Copies:=1 Rows("3:3").EntireRow.Hidden = False the above will hide row number 3, print then unhide.. -- Regards, Peo Sjoblom "slac65" <anonymous@discussions.microsoft.com> wrote in message news:006501c3dc69$25fab510$a001280a@phx.gbl... > I have a range o...

Making a set value appear in a cell
Hi, I'm new to excel, I apologize if this question is too basic. I work for an accountant, and I have to enter an account purpose and the account number for that purpose in a cell right next to it. Is there any way to enter the purpose and make the set value of the account number appear next to it automatically? For example if I enter ABC for purpose, can I make a (fake) account number of 50-5025-02 appear next to it automatically? And the same goes for other purposes and their respective account numbers. You can do this using a VLOOKUP formula. First you would need to set up a...

How do you change the data type of a cell using VBA?
For instance, with VBA I am trying to place "February 2007" in a cell but it keeps showing up as Feb-07. I want it to show up as literal text. -- justme0010 Sub text_it() Range("A1").Value = Chr(39) & "February 2007" End Sub -- Gary''s Student - gsnu2007c You could keep the value a date and just format the cell the way you like: with activecell .numberformat = "mmmm yyyy" .value = dateserial(2007,2,1) end with You could also format the cell as text first: with activecell .numberformat = "@" 'text .value...

Why does cursor move position in a cell I'm trying to edit?
I have just updated from Microsoft 2000 to Microsoft 2003. My first attempt to use Excel has failed. When trying to edit a particular cell on an old spreadsheet I pressed f2 then placed the cursor where I wanted to add some more data. However, it only allowed me to type a couple of words & then the cursor moved itself to another position, a few words further on, not to the end of the line. When I moved the cursor back to where I wanted it & tried typing again it just moved away again. The cell in question contains only words, no sums or formulae. I'm sorry if this is a fu...

Take non-qualifying discounts Select Checks Screen default
Can anyone tell me how to default the checkbox to Take all non-qualifying discounts to always be checked? Eric, I'm trying to recall which window this is in but it doesn't really matter. There isn't anything in GP that defaults this value that I remember and so the solution is that you'd have to write a bit of VBA code to do this. Probably just one line to set it. My guess is the Window_AfterOpen() event and your line would look like: TakeAllNonQualifyingDiscounts = true This assumes that the field shows in VBA as I've guessed above. For further information on cu...

How can I assign a data for one cell from another fixed cell?
I can assign data to one cell by put formular, for example: =H5. But if I add a row, my cell will get data from H6, Excel automatically modified my formular to: =H6 And now I want the data always come from =H5, no matter how many rows I add on top of it. PLease help me. I remember somebody did it, but I forget it. Thank you very much! If I understand your question correctly, you would use absolute references ($ signs to fix the row, column or both)... =$H$5 Rick "new Excel user" <new Excel user@discussions.microsoft.com> wrote in message news:BCAE5E50-1EAF-4ADA-9F24...

Select criteria based on the field name the user selects
Hi all, I am facing with aproblem where in i have a small access db with forms and reports now i want to change or set the where condition of a query based on the value the user selects from the form through a combo box. For ex:- if user is selecting Invoice_date then the where condition should search based on invoice date if the user selects any other field the querys where condition should search based on that particular field.Can any one help me in getting a solution for this. I have a sample database that shows how you can use various controls to set the filter of a query that i...

Cell Comment--Not!
I just downloaded a spreadsheet that has something I've never seen before. When I select one of several possible cells, a yellow box with documentation about the cell pops up beneath the cell. Right-clicking on the box does nothing. The box is *not* a cell Comment (as in Insert Comment), for these reasons... ....It doesn't have a line connecting it to the cell. ....It doesn't have a little triangle in the top-right corner of the cell. ....Unlike a cell Comment, it automatically displays when the cell is selected and closes when another cell is selected. ....I **also** can assign ...

Displaying data based on Drop Down List Selection
Hello, hope someone can help. I have a list of records in Excel which are attributed to teams in my organisation. Say its a list of staff. What I need is to be able to send the file out so that a user can select from a drop down menu the team name and the list displays all the staff in that team. I know that they could just use a filter for this themselves but these these users are not even comfortable doing that!! I need to make is simple so wanted a big obvious drop down list with the teams in, they select it and see all the relevant records. Has anybody got any ideas? Would be ver...

Invalid cell reference
I am writing an Excel 2002 template, and wish to convert a large range of imported data. All of my formulae have been written in row A on a ‘Reformatted Data’ sheet. For example cell A1 contains =TEXT('Imported Data '!B3,"00"). The many formulae work successfully, but I need to delete the entire contents of the ‘Imported Data’ sheet. As a template it needs to be empty. Obviously the formulae in my example cell is now =TEXT('Imported Data '!#REF!,"00") because of the invalid cell reference. My question is, how do I retain the formulae in...

separating data in cells
I have a very long excel spreadsheet with data like this in each cell: Alaska Native Medical Center 4315 Diplomacy Drive Anchorage,��AK��99508 Phone: (907) 563-2662 Medicare provider code: 020026 Number of beds: 140 Is there any way to break up this data into columns, such as "name, address, phone, etc etc? ����The purpose of this is to import all the information into an address book or act. Any help would be appreciated. ~Chris Chris, No help from here yet, but... Are the entire five lines that you posted in one cell? or Are the five lines in separate row...

Converting a Cell Value to Cell Reference...
Here's the deal: I've set up a MAX formula that displays resultant value, and I need to figure out how to "tell" a particular function what cell that value is located in. I am working with the OFFSET function, and I need a cell reference as the first parameter, and all that I can provide right now is the value for a MAX from a chosen range. Any advice is greatly appreciated. ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** I suppose what I'm asking is... Is there ...

Excel cell formatting
Sometimes a cell in a worksheet won't allow me to format. If I select "format cells" by right-clicking or from the "Format" menu nothing happens, even though I can edit the format of an adjacent cell. This happens in many different worksheets. Any ideas about why this happens? ...

Validation Boxes and dependant cells
Here is the problem... I have a list on sheet 2: THis is not the complete list, the actual i 400 names long. the range is called "Names" A B Derek | 575 Bob | 500 Jonny| 200 Doug | 300 On sheet 1 i have a validation box in row A that applies to the rang of "names" in sheet two I would like to have the number that coresponds with the name come u in cell B when the validation box is filled in A. Thanks, Dere -- Message posted from http://www.ExcelForum.com Hi use can use VLOOKUP for this in cell B on sheet 1 type =VLOOKUP(A1,Names,2,0) where A1 ...

Splitting cells in Excel 2007
Hello Microsoft, I've spent about an hour on various Internet sites and discussing this in forums. Many seem to know it's not possible, but nobody has told me why it's not possible. The question has been asked as far back as 1999, from what I've been seeing. How does one split a single cell either vertically or horizontally? I don't mean the data within the cell, but the cell itself. Many "solutions" seem to use techniques of adding new rows or columns and doing some creative merges to all the cells affected. The spreadsheet I want to change...

fix cell contents before deleting column from which formula's are based
I want to e-mail a spreadsheet to someone showing only the calculated results and deleting the column from which the formula's are based. I have done this (fixing the cell contents) before but can't remember how and am obviously not wording my query correctly for the help function. Thanks. Erica Copy, paste special, values? then delete the column? >-----Original Message----- >I want to e-mail a spreadsheet to someone showing only the >calculated results and deleting the column from which the >formula's are based. I have done this (fixing the cell >cont...

Can I set an audio alert that triggers as Excel cell value chgs?
I would like to add an audio alert to a spreadsheet cell whenever it changes to a specific value? Can this be done? ....I'm not familiar with any audio alerts but, you can use conditional formatting to the cell's format change when it value changes. Go to Format...Conditional Formatting. You can then either set a value or use a formula and set the format (what it will look like when the cell is the value that you set). "SellUnHi" wrote: > I would like to add an audio alert to a spreadsheet cell whenever it changes > to a specific value? Can this be done? L...

Keeping correct references when replacing a corrupted sheet.
I have a workbook with a corrupted sheet. When I copy the sheet to a new workbook and then back again, (OR make a copy in the same workbook) and then delete the original corrupted sheet it solves the problem but, I get a whole bunch of #REF on another worksheet . All the formulas have a Named range that were in the corrupted sheet. Although the copy is exactly the same and, the named ranges still exist in the Name dialog box, they now also show the dreaded #REF. I expect that one way to correct this problem is to paste the name of the sheet over the #REF in all those named ranges but, as th...

Help
I am using store ops version 1.3.1010. We are using a windows and not an OPOS printer for our receipt printing. When we post a transaction, I would like the cashier to have the ability to enter how many copies of receipt to print before it prints. To do this I added the PrintReceipt hook to my windows registry, and have code added to my custom DLL that is triggered when this hook is activated. That piece is working fine. My code contains the following lines: Dim MyCount as Integer MyCount = InputBox("Enter receipt quantity", "Receipt Quantity") posSession.Configu...

Restricting items based on order total or previously selected item
We have several items (mostly coupons) whcih should only be available for sale if the order total is a certain level (ex: if the order total is $500 or more, let them use a $50 coupon. Coupon should not be alowed on the order if order total prior to the coupon is less than $500) or if certain items are already on the order (ex: we have a coupion for $25 off all jackets [category Jacket]. Coupon should not be allowed if a jacket is not already on the order). In a similar vein, have special item pricing based on the order total (ex: if the order total is $500 or more, allow them to purcha...

Move cell data to another worksheet cell automatically.
I have a sum formula in block F3 of a worksheet called Furlong. It is his total season bowling pinfall. I want the program to automatically transfer this total, each time it is updated, to his team record on a worksheet called Score in block C4. Both worksheets are in the same file. Click in C4 and enter an equal sign, = Then, navigate to the other sheet, and click in F3, And hit <Enter> And you're done! You've let XL create a link for you between the two cells, where C4 will automatically display whatever value is in F3. -- HTH, RD -----------------------------------...

cell display is blank
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I am creating a table that summarizes results that reference cells in other worksheets. Some cells display a hyphen or zero rather than the actual number. I have checked the cell formatting and not identified anything unusual. I sense the application may be having some software problems. I get #REF in some cells mysteriously, and occasional problems with server connections to Help. On 5/19/10 1:08 PM, rreisley@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I am > creating a tab...

how set cells don't print?
Thanks in advance! I have some cells where the user inputs filenames and some data that I don't want printed when the user prints the worksheet. Is there a way to set certain cells not to print even thought the whole worksheet is printed? Thanks Ian, The whole worksheet is rather large, 16.8 million cells and printing the entire sheet would take up many many pages (40,000-50,000), and would take a few days for the job to be completed on a standard laser printer running at 10-15 pages per minute. Usually people only use a portion of that space for their active work area. Possibly you ...