Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Simple Marco Excel 2007

Posted on 2011-03-01
17
Medium Priority
?
863 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

604 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