Solved

Simple Marco Excel 2007

Posted on 2011-03-01
17
845 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 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