Solved

Simple Marco Excel 2007

Posted on 2011-03-01
17
808 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now