Solved

Simple Marco Excel 2007

Posted on 2011-03-01
17
831 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
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

776 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