Solved

Simple Marco Excel 2007

Posted on 2011-03-01
17
851 Views
Last Modified: 2012-05-11
I am attempting a simple macro in Excel 2007.  I have a long list of descriptions in say column B.  I need to add a space at the end of each description.  My active cell is B2.
1. I turn on "Record Macro"
2. I press F2 to edit the cell.
3. I press the space bar.
4. I press the Enter Key to accept my space.  The cursor goes down to B3.(set to go Down in Excel Options)
5. I click "Stop Recording".
When I press the assigned Ctrl-key the macro puts the space in the field just fine.  But it always returns to cell B3 instead of the cell immediately below.   How do I make so it goes down one cell instead of always returning to B3?  Thank you!
0
Comment
Question by:Sam_Purdy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
17 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35010237
Try this code

Sub AddSpace()
    Dim lastrow As Long
    
    lastrow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lastrow
        Sheets("Sheet1").Range("B" & i).Value = _
        Sheets("Sheet1").Range("B" & i).Value & " "
    Next i
End Sub

Open in new window


Sid
0
 

Author Comment

by:Sam_Purdy
ID: 35010360
Thanks Sid.  When I tried it I got this message..."Compile Error.  Variable not found"  The letter " i " is highlighted in the statement,  For i = 2 to lastrow
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35010362
For the specific scenario to simply move the cursor down one row then see the snippet instead of selecting B2 it moves down the one row and stops.

Chris
Sub spacer()
    
    ActiveCell.FormulaR1C1 = " "
    ActiveCell.Offset(1, 0).Select
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35010365
Sorry

Change line 2 to

Dim lastrow As Long, i as long

Sid
0
 
LVL 8

Expert Comment

by:point_pleasant
ID: 35010379
if you have something like

Range("B3").Select

get rid of it and replace with


Selection.Offset(1, 0).Select

which will move one cell down


Mike
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35010388
One should always avoid the use of .Select.

Sid
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35010399
One should ... but if the asker wants the cell selecting i.e. as in the original question it's a bit hard to avoid!

Chris
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35010407
Wit respect Chris. That is where the experts come in. To suggest the right way of doing it.

Sid
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35010457
WIth respect it depends on what the author wants ... if they are for example learning and simply want to move the cursor then we cannot do better.  If the goal is to take a populated column and add a space to every cell in the used range then your code is of course the right way around.

I humbly suggest however that they are unlikely to replace real data with a space and that therefore they are probably learning their way around ... but lets leave that to the asker to see what they are trying to achieve.

Chris
0
 

Author Comment

by:Sam_Purdy
ID: 35010469
chris,
that code replaces my description with a blank.  I just want to add a space to the end of the text that is already populating the cell.  Thanks!!
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35010476
Did you try the code that I gave?

Sid
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 35010522
IN that case for the scenario of a single file edit and change active cell:

Chris
Sub spacer()
    
    ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & " "
    ActiveCell.Offset(1, 0).Select
End Sub

Open in new window

0
 

Author Comment

by:Sam_Purdy
ID: 35010572
Thanks Chris it worked!!!
Sorry Sid.  I tried your correction and the macro did not run.  

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35010582
Did you see my post after you said that there was a problem?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35010589
I am speechless...
0
 
LVL 8

Expert Comment

by:point_pleasant
ID: 35010603
so in the long run the question asked how do i move down a row which both answer with the


ActiveCell.Offset(1, 0).Select


statement do
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35010608
Glad it helped ... but please note Sids comment for the future that where you can avoid selecting a cell a macro will run much quicker .. i.e. in a macro you can process cells without actually selecting them and add or change data .

Chris
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question