• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 867
  • Last Modified:

Simple Marco Excel 2007

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
Sam_Purdy
Asked:
Sam_Purdy
  • 7
  • 5
  • 3
  • +1
1 Solution
 
SiddharthRoutCommented:
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
 
Sam_PurdyAuthor Commented:
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
 
Chris BottomleyCommented:
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.

 
SiddharthRoutCommented:
Sorry

Change line 2 to

Dim lastrow As Long, i as long

Sid
0
 
point_pleasantCommented:
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
 
SiddharthRoutCommented:
One should always avoid the use of .Select.

Sid
0
 
Chris BottomleyCommented:
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
 
SiddharthRoutCommented:
Wit respect Chris. That is where the experts come in. To suggest the right way of doing it.

Sid
0
 
Chris BottomleyCommented:
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
 
Sam_PurdyAuthor Commented:
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
 
SiddharthRoutCommented:
Did you try the code that I gave?

Sid
0
 
Chris BottomleyCommented:
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
 
Sam_PurdyAuthor Commented:
Thanks Chris it worked!!!
Sorry Sid.  I tried your correction and the macro did not run.  

0
 
SiddharthRoutCommented:
Did you see my post after you said that there was a problem?

Sid
0
 
SiddharthRoutCommented:
I am speechless...
0
 
point_pleasantCommented:
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
 
Chris BottomleyCommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 7
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now