Macro command to move cell selection

I am writing a macro for a spreadsheet where I have values stored in one 
column. I want to run a loop where each time the loop runs, I select each 
cell in the column. For example, my values are stored in column A, cells 1 
through 7. On loop 1 I want to select cell A1, loop 2 select cell A2, loop 3 
select A3 and so on. How can I set this up in a macro to increment cell 
selection as I move through my loop?
0
Utf
12/17/2009 12:15:01 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
801 Views

Similar Articles

[PageSpeed] 19

I'm really not sure whay you can do with this, but is this what you are 
wanting?

Sub SelectionLoop()

Dim i As Long

    For i = 1 To 7
        Cells(i, "A").Select
    Next i

End Sub

Hope this helps!  If so, please click "YES" below.
-- 
Cheers,
Ryan


"DOOGIE" wrote:

> I am writing a macro for a spreadsheet where I have values stored in one 
> column. I want to run a loop where each time the loop runs, I select each 
> cell in the column. For example, my values are stored in column A, cells 1 
> through 7. On loop 1 I want to select cell A1, loop 2 select cell A2, loop 3 
> select A3 and so on. How can I set this up in a macro to increment cell 
> selection as I move through my loop?
0
Utf
12/17/2009 12:26:01 PM
Sub DoSomething()
Dim X as Long

For X = 1 To 7 'or any number that addresses the whole range
Range("A" & X).Select

.. . . your value manipulation code goes here

Next X
End Sub

HTH

"DOOGIE" wrote:

> I am writing a macro for a spreadsheet where I have values stored in one 
> column. I want to run a loop where each time the loop runs, I select each 
> cell in the column. For example, my values are stored in column A, cells 1 
> through 7. On loop 1 I want to select cell A1, loop 2 select cell A2, loop 3 
> select A3 and so on. How can I set this up in a macro to increment cell 
> selection as I move through my loop?
0
Utf
12/17/2009 12:33:01 PM
I am sure you are looking for something else and you dont need to really 
select the cell to acheive that. Go through the below code to see how to get 
the cell value from any row during this loop.


Sub Macro1()
Dim cell As Range
For Each cell In Range("A1:A7")

'cell value
MsgBox cell.Value
'cell value of same row 1st column to the right
MsgBox cell.Offset(, 1).Value, , "ColB value of the same row"

'Refer another column in the same row 2nd col in the same row
MsgBox cell.Offset(, 2).Value, , "ColC value of the same row"
'OR
MsgBox Range("C" & cell.Row)

'select the cell
cell.Select

Next
End Sub

-- 
Jacob


"DOOGIE" wrote:

> I am writing a macro for a spreadsheet where I have values stored in one 
> column. I want to run a loop where each time the loop runs, I select each 
> cell in the column. For example, my values are stored in column A, cells 1 
> through 7. On loop 1 I want to select cell A1, loop 2 select cell A2, loop 3 
> select A3 and so on. How can I set this up in a macro to increment cell 
> selection as I move through my loop?
0
Utf
12/17/2009 1:09:01 PM
Reply:

Similar Artilces:

Sum of specific cell from several worksheets
I have a workbook with 12 worksheets, one for each month of the year. I also want to add a "year to date" worksheet. Is there a simple way to take the sum and/or average of a specific cell, over the 12 month period, and carry it over to the same cell on the YTD worksheet? Example: I have a "total pay" in cell A1, on each of the 12 worksheets. I want the sum of all of the A1 cells to compute in cell A1 on the YTD worksheet. Ensure that the YTD sheet is placed outside of the group of 12 monthly sheets. Ensure that the 1st month (eg: Jan 09) and last month...

MultiList Select Box Search Form
I posted this question awhile ago, and never got a chance to get back to check for any responses till now. I see Doug's (Thank you) and did what he suggested, but got a kink while doing that. I also am not sure of if I should post again, like I am now, or just reply to the old post. So anyway..... I came up with a problem when typing in & _ (That is simply an ampersand and a underscore, correct?) """, " After I enter that code, those two lines turn red and I get a vb error window saying: Compile error: Expected: line number or label or statement ...

Shortcut for centring whatever is in a cell?
Is there any keyboard shortcut for centring whatever is inside a cell/selection of cells? For instance, in Word, "Ctrl E" will centre text on a line. Is there anything similar for Excel? Don't think there's a built-in shortcut key, but here's a play to set-up the centring for: CTRL+j (Try on a spare copy/new book) Press Alt+F11 (to go to VBE) Click Insert > Module Copy and paste the sub CentreAcrossCells below into the whitespace on the right '--------- Sub CentreAcrossCells() If Selection.Cells.Rows.Count > 1 Then GoTo Pop End If ...

How do I copy a formula/paste w/o losing the cell content?
I have a formula such as "=sum(d34+d35)", I decided to move the location, but when I move the location the formula is now "=sum(d31+d31). I would like to move the first formula anywhere on the spreadsheet w/o it being changed. How do you do that? Is it an absolute value or something like that? Yes. The formula must be written as =sum($d$34+$d$35) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "CShannon" <CShannon@discussions.microsoft.com> wrote in message news:04CAD401-FDF9-46C0-BCF4-D8F28A410ABE@microsoft.com... > I ...

Outlook Email Help: selecting Voting button option from excel VBA
Hi All, I had a question about sending email from excel. I know we can set certain properties of outlook from excel such as "Send Receipt" ect.. But can we set an voting button option from excel? I am able to send the voting buttons through excel by doing: ..VotingOptions = "Accept;Reject" But There is an option "Have Replies Sent To" in Voting Buttons section which I want checked everytime as I want the voting responses sent to the person who requested the vote AND to one more person. Is there a way to do this from excel? To select "Hav...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Making a command button work!!!!
Hello I have a sheet with a combo boxes and a command button. Is there a way that would make the command button work only when a selection is made in a combo box Eg combo box Please select Std shaped finish Shaped finish with trim So if the bottom select is made (shaped finish with trim) the command button when press will work. BUT if any of the other selections have been made pressing of the button will have no effect? The combo and command button are on a spreadsheet from the forms toolbox Thanks -- raw ----------------------------------------------------------------------...

Make Cells Diagonal
How do I make the top "label row" of the columns diagonal (45 degree angle) instead of horizontal. I have seen it done but don't know how to do it. Thank you. ...

White Space in a Cell
Hello I'm collating a lot of spreadsheets that many other people have filled in onto one big spreadhseet template. My problem is that there is a free text cell and, because people dont know about the 'alt & enter' option, they've hit the space bar many many times make it look like a new point starts on a new line. In Word, you have the option of showing all the keyboard strokes so you can tell what people have done - but I'm finding I'm having to go into each free text cell and hit delete, then the cell magically shifts up so that there is no white space. Any...

Change the file name in Macro
I created Macro that open several workbooks (actually 17 of them) at the same time like this; Workbooks.Open Filename:= _ "H:\Month End Work\FY 2009\02-09 RUI SALES\01 - Feb09 CFW.xls" Workbooks.Open Filename:= _ "H:\Month End Work\FY 2009\02-09 RUI SALES\11 - Feb09 CFW.xls" Workbooks.Open Filename:= _ "H:\Month End Work\FY 2009\02-09 RUI SALES\21 - Feb09 CFW.xls" It works fine till I have to use it for the following month. I have to go back to my Macro and change manually the directory from 02-09 to 03-09 and file name from Feb09 to Mar0...

connecting cells with connector lines
Hello! Is it possible in Excel (2007) to draw a connector line (with or without an arrow ...) between two cells in a worksheet, so that the line-tips will follow their cells even when the latter are moved ? Thanks Michael On Feb 25, 7:00=A0am, Michaelprem123 <michaelprem...@gmail.com> wrote: > Hello! > > Is it possible in Excel (2007) to draw a connector line (with or > without an arrow ...) between two cells in a worksheet, so that the > line-tips will follow their cells even when the latter are moved ? > > Thanks > > Michael The line will automatically a...

How do I merge in a selected range of cells out of Excel?
copy / right click / insert copied cells ...

Summing up content from hyperlink cells
Anyone have any idea on how I can make Excel sum up the displayed values of several yperlink cells? This is an EXCELLENT question ! I will use it next semester. Let's say that in A1 thru A3 we have: =HYPERLINK("http://www.microsoft.com","15") each of the cells displays 15 However =SUM(A1:A3) shows 0 This is because the 15's are actually text values. They must be converted to numbers. Use: =SUM(--A1:A3) which must be entered as an array formula with CNTRL-SHIFT-ENTER rather than just ENTER -- Gary''s Student - gsnu200745 "Mort" wrote:...

find the last occurance of a character in a cell
I can use the Find function to find the 1st occurance of a charater in a cell. But, how to i find the last occurence of a character. in this case, the i am looking for " ", or <space>. The length of the string can vary, and the number of spaces can vary also. example: "Get dog food 55" There are 14 spaces (1+1+12). i am intersted in the location of the last, or 14th, space, the one that precedes the number 5 Thanks, Tonso Look in vba help index for INSTRREV -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com &...

in Excel: How do I change "selected cells" highlight color?
When I select cells in Excel the shading is so light (blue) that I can barely tell which cells are selected from those that are not. Can I change the background color of cells that are selected? Thanks. Assuming Excel 2007 this is a known problem and as yet no patch for it. I don't have Excel 2007 but I have read that playing with your Contrast can help some. Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 12:51:00 -0700, flameretired <flameretired@discussions.microsoft.com> wrote: >When I select cells in Excel the shading is so light (blue) that I can barely >tell whic...

Passing Names to Macro's
How can I have a macro recognize a named range? E.g. ADDON=5 is defined and named in a cell, and I want to reference it in the macro. Thanks.../RN RN Like this Sub referToRange() Dim sRangeName As String sRangeName = ThisWorkbook.Names("ADDON").Value MsgBox "ADDON Value" & sRangeName End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Randy Numbers" <ran@hotmail.com> wrote in message news:kmBIc.2$EM5.1@fe04.usenetserver.com... > How can I have a macro recognize a named range? E.g. ...

delete a selection
how do i delete part of a chart to leave no unwanted space.. Hi, We are going to need more information. What chart type? Where is the space you want to remove? Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "flirtacious" <flirtacious@discussions.microsoft.com> wrote in message news:7111EAE4-0C9A-480E-BDDF-73727FB892A1@microsoft.com... > how do i delete part of a chart to leave no unwanted space.. On Nov 22, 3:10 pm, flirtacious <flirtaci...@discussions.microsoft.com> wrote: > how do i delete part of a chart to leave no unwanted...

How to get all records in a Query even you select a record from a conbox box
Hello there well I have a big probem I am trying to create a query that I can select a month using a combo box in a form and returns with all the record and not ignoring the rest of the months for example If I have records for July it will come only july's except the other month I prefer to get all the months including july's I now is going to make duplicate in July but I don't care that is what I need I used is null in the criteria and it works just fine but I notice that if I Select the month of august all the records from july's are gone only shows august anyway here is a ex...

How do I get one cell to record the time another cell was changed.
I know there's a simple solution to this, but I'm not seeing it. How can I set up a cell to update the time (or date) whenever another cell has data entered into it; e.g. cell A1 has new info entered into it, and cell A2 automatically updates the time of that update. Thanks in advance. One way http://www.mcgimpsey.com/excel/timestamp.html Regards, Peo Sjoblom "Reigning in Seattle" wrote: > I know there's a simple solution to this, but I'm not seeing it. How can I > set up a cell to update the time (or date) whenever another cell has data > ente...

right click new commands are missing
I had MS works and MS office installed. I needed HD space so I uninstalled works and now my right click "new" commands for word and excel are missing. My "new" powerpoint is still there. How do I get my "new" mouse commands back? In article <E13C3B5C-F48E-4EDC-8E28-72AF78B7CE5B@microsoft.com>, Gene5133 wrote: > I had MS works and MS office installed. I needed HD space so I uninstalled > works and now my right click "new" commands for word and excel are missing. > My "new" powerpoint is still there. How do I g...

running a macro on several sheets
HI, I have a spreadsheet and I am trying to run a macro on all sheets. Here is my code for the first sheet called U.S. Sub whatColor() For counter = 5 To 15 cell = Worksheets("U.S.")Cells(counter, 38) If cell = 0 Then Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex = 0 'Better than last year, better than plan' ElseIf cell = 1 Then Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex = 4 'green' 'Better than last year, below plan' ElseIf cell = 2 The...

Workbook cells won't hold formatting in Office 2007
I have Office 2007 running in Vista Home Premium. My system got FUBARed and had to be reloaded. Now my Excel workbooks won't keep the proper formatting in cells. I have them formatted as general so that I can type in something like 4 - 1. As soon as I do that it converts the formatting to Custom and puts it in as a 1-Apr. If I try to reformat the cell as general I then get 39904. If I enter it as space 4 - 1 then it enters correctly. How can I make the cell give me the correct information without putting a space in front of it? One way in to format the cell as Text B...

Repeating instructions in a Macro.
Hello all, I have a Macro that clears any data on different 31 tabs and, due to my lack of knowledge, I do this one tab at a time. The 31 tabs represent the maximum possible days in a month. Here's a sample of my Macro: '====================================================================================================================== ' Clear the data on tab 1. Windows("Revenue Tracker.xls").Activate Sheets("1").Select Range("B4:O126").Select Selection.ClearContents Range("E4").Select '-----------------...

macro at page break
Place macro so it prints starting 6 lines above each page break -- Randy Not sure what you're trying to do. Could you please give more details? App, version? You want to print the macro 6 lines above each page break? You want to print a header 6 lines above each page break? You want to print automatically when you enter a page break? More info might help. -- Susan Ramlet **please reply to the newsgroup so others may benefit** "Randy" <Randy@discussions.microsoft.com> wrote in message news:C60553CF-D7E5-4968-B8B3-58C5A376E248@microsoft.com......

VBA Excel Macro worked for 2-years now dosen't??
Is there a limit on how much code you can place in a VBA file?=A0 All works well except the macro I call "VacUsed"=A0 It is called from a couple of procedures I post the last procedure=A0"ThisWorkBook" use to close and save the workbook.=A0 Private Sub Workbook_BeforeClose(Cancel As Boolean)=A0 =A0 =A0 Call FilterTestOff=A0 =A0 =A0 Call VacUsed=A0 =A0 =A0 Call DeleteMenu=A0 =A0 =A0 Call AllProtect=A0 =A0 =A0 Sheets("VacationAccrued").Activate=A0 End Sub=A0 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As=A0Boolean)=A0 =A0 =A0 C...