Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Macro - Move one cell down

Hello-

I am looking to create a macro where I click the macro button and it moves one cell down at a time. For example my data is on sheet 1, A1:A100, on sheet 2 is where i have all my formulas that are contigent upon the data being pulled in from sheet 1. I want to view the outputs created by pulling in the data from Sheet 1, A1, after reviewing that data i then want to click the macro button so that it moves one cell down to the next one.

Hope this make sense, any help would be helpful!!!!

- Thanks in advanced


Post your answer or comment

comments powered by Disqus
Hi Guys,

This will easily be the simplest question ever posted here, which ofcourse just means you have to give an even more brilliant answer

How do i make a VBA code which does the following:

Moves one row down from the current cell

example: from a4 to b4

when i try recording a Macro it is to specific about the cells, i need it to work in a loop

thx guys!

Tiffany

Hello, I am new to VB code in excel and I am stuck on a problem. I column F and G i have text that starts with (ZBA ??????) and I want to take that cell and move it 5 column over in one cell down if it is in Column F. If it is in Column G then 6 column over in one cell down.

A B C D F G
Test


	VB:
	
 MoveCellTest() 
    Dim myrange, cell As Range 
     
    Set myrange = ActiveSheet.Range("F1", Range("G65536").End(xlUp)) 
    For Each cell In myrange 
         
        myrange = Cells.Find(What:=ZBA, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
        False, SearchFormat:=False).Value 
         
         'If cell.Value = "ZBA XFER" Then
        cell.Cut 
        Range("F" & Application.WorksheetFunction.Max(2, cell.Row)).Offset(1, -5).Select 
        ActiveSheet.Paste 
        Selection.EntireRow.Insert 
         
    Next cell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a macro that I need help finishing.On the specific page I have used a
find to find the appropriate starting point. Once this is found, and it will
be different each time the macro runs, I need to tell excel to move one cell
to the right. Then I'll cut and copy the information in that column to
another column. To the right of that column I will be entering a formula. I
need to figure out how to do this each time the macro runs but as of now, it
is selecting the specific cells. I can run it with my test file and it will
work but when I run the macro with new information, it is selecting the cells
used in the test file. I need it to proceed from the find each time. Any help
would be appreciated. Here is an excerpt from the code:

Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
Range("E21").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
Range("F21").Select
ActiveSheet.Paste
Range("G21").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12"
Range("G21").Select
Selection.AutoFill Destination:=Range("G21:G31")
Range("G21:G31").Select
Selection.Copy
Range("E21").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

--
A Waller

Hi,

I am trying to assing a Macro to one cell that has a validation list (options: yes and No)

The goal is to run a macro everytime the user selects yes or no.

Thanks in advance for your help

Hi All,

I have the following VBA code in Excel that looks in sheet called mri.txt for a cell called “AcquisitionMatrix”,

Then goes one cell down and two cell to left and copy them all into Application.Workbooks(imgMain).Worksheets("Sheet1").Activate cell “AC2”,

But for some reason it copies the field’s name as well such as:
AcquisitionMatrix , 0, , 256, , 256,.

Is there a way to avoid copying the filed name into cell AC2? Such as :
0, , 256, , 256,?

here is the code:
''''''''''''*****find AcquisitionMatrix and move one cell down then one cell to left cell to the right and copy
it******
                Dim rFound As Range
                Windows("mri.txt").Activate
                Set rFound = Cells.Find(What:="AcquisitionMatrix", After:=ActiveCell, LookIn:=xlFormulas, _
                 LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False)
                  On Error GoTo 0
         If Not rFound Is Nothing Then
                   rFound.Offset(1, -1).Copy
   'THE REST OF YOUR PASTE CODE HERe
  ''''''''''
  ''''''******''''''''''Find Cell "PixelSpacing:
            
                     Windows("mri.txt").Activate
                                                    
                                    
       ''''''''
                     Dim xcombine As String
                                                     
     Cells.Find(What:="AcquisitionMatrix", After:=ActiveCell, LookIn:=xlFormulas, _
                       LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                       MatchCase:=False, SearchFormat:=False).Activate
                                      
                        '''select detination cell in main workbook and sheet
  ''' ''''''combining all the copied cells
                         xcombine = ActiveCell.Value2
xcombine = xcombine & " , " & ActiveCell.Offset(1, -1).Value2 '1 cell dowm 1 left
 xcombine = xcombine & " , " & ActiveCell.Offset(2, -1).Value2 '2 cell dowm 1 left
 xcombine = xcombine & " , " & ActiveCell.Offset(3, -1).Value2 '3cell dowm 1 left
                                                    
                                                      
Application.Workbooks(imgMain).Worksheets("Sheet1").Activate
                                                     
  ''shtTarget.Range("AG2").Value2 = combine
                          Range("AC2").Value2 = xcombine
                          ActiveSheet.Paste
                          Range("AC2").Select
                                                        
End If

Thanks for any help.

Abrahim

Can Anyone Tell me Please the Command to Move the Curser One Cell Down from
Any Cell as a Stating Point to Any Other Cell Without Using the Command for
Example - "Range ("A1") .Select" as this limits me to selecting this one cell
only. I need to Write a Formula That Will Find a Number in a Row Based on a
No. in One Perticular Cell.

My Idea is To Move Down a Cell at a time within a List of No.s and Loop
Until the Found Cell Matches the No. in a Reference Cell i.e. "Move Down
Until = A1" or "move Down One Cell at a time, End When Cell = A1" I'm sure
you get the gist of what I'm After,

Please Help

I have a spreadsheet with up to 2000 rows which I run each day to check for matching errors. All data is general (alpah number mix).
I compare in rows 1-2000 the col A number with the col C number and the col B number with the col D number.
I am running Excel 2003.
I do not know VB or scripting.
Col A and B are automatically populated by input from a data base with up to 2000 numbers in a new worksheet.
Col C and D are populated by hand scanner input.
After scanning in the row of data into col C then col D I need to automatically return to the next empty row in col C. My scanner allows me to go from row "X" col D one cell to the right to col E (tab) or one cell down in col D (return). So I need to go left one cell or down one cell and left two cells to get back to the next empty row in col C. My scanner does not allow further commands.
I am stuck in one of these two cells after entering data in row "X" col D.

Not a complicated process. However, I want to expedite the process.
I need a way to automatically return to the next empty row of col C without hitting a key so I can scan in the next rows data.
Entering in up to 2000 rows of data in col C and D takes a lot longer without the automation I am looking for.
I built a macro but it still requires a keyed entry. I think what I need is an automatic shift/tab to move left one cell from the col D I can end up in.
Is there a way to automate the "move" or "go to" to the next empty col C row?

Other info:
In col E I compare data in col A (each row 1 - 2000) with data in col C.
In col F I compare data in col B (each row 1 - 2000) with data in col D.

The formulas for col E and F are easy. Formulas are in rows 1-2000.
=IF(A1=C1,"","BAD")
=IF(B1=D1,"","BAD")

In G1 I count the number of "BAD" compares with: =COUNTIF(E1:E2000,"BAD")
In H1 I count the number of "BAD" compares with: =COUNTIF(F1:F2000,"BAD")
In I1 I sum G1:H1 for a total of "BAD" for the day.

Help appreciated, thanks.

I have attached the sample file where I have noted what I want as a comment.

I need to check for the value " Function Name: RF Pick" if this is there then the code should move 2 rows down check whether that row has the value " Each" if its there just move one row down and move the cells as I need in the output.

If possible please guide me as to how to code that part.

Request you to guide me if you can.

Thanks for all your help.

Good Afternoon,
I have to match up accounts and payments that my company receives with the information from our client which can be different.

There may be different payments for the same account or none on one side but some on the other.

I yanked this code from another site and tried to modify it. The If greater portion is the part I tried to change. The second part still works. I'd like to change the code to shift a range of cells down so that they match up with the appropriate account instead of color the accoutns colors. Please let me know if you need clarification or can provide any help.

Thanks in advance!

Updated code: Macro now shifts the last mismatch down, but not all. Anyway to make this shift on each side until all accounts match up?
The way I see it. There would be an if one side is greater than the other then shift these cells down, but if the other side is greater shift the others down and do a loop. I can't figure it out though :/
Sub ShiftDown()
Dim active_sheet As Worksheet
Dim name1 As String
Dim name2 As String
Dim range1 As Range
Dim range2 As Range
Dim cells1 As Collection
Dim cells2 As Collection
Dim cell1 As Range
Dim cell2 As Range
Dim key As String
Dim no_match As Boolean
Dim greater As Boolean
Dim less As Boolean


    Set active_sheet = ActiveSheet
'    name1 = InputBox$("First Range Name:", "First Range",
' "")
name1 = "Range1"
    If Len(name1) = 0 Then Exit Sub
    Set range1 = active_sheet.Range(name1)

'    name2 = InputBox$("Second Range Name:", "Second
' Range", "")
name2 = "Range2"
    If Len(name2) = 0 Then Exit Sub
    Set range2 = active_sheet.Range(name2)

    ' Make normal collections holding the cells.
    Set cells1 = New Collection
    For Each cell1 In range1.Cells
        key = cell1.Row - range1.Row & "," & cell1.Column - _
            range1.Column
        cells1.Add cell1, key
    Next cell1

    Set cells2 = New Collection
    For Each cell2 In range2.Cells
        key = cell2.Row - range2.Row & "," & cell2.Column - _
            range2.Column
        cells2.Add cell2, key
    Next cell2

    ' Examine the cells in the first collection.
    For Each cell1 In cells1
        On Error Resume Next
        Err.Clear
        key = cell1.Row - range1.Row & "," & cell1.Column - _
            range1.Column
        Set cell2 = cells2(key)
        If Err.number <> 0 Then
            ' The second cell is missing.
            greater = True
        ElseIf cell1.Text > cell2.Text Then
            ' The cells don't match.
            greater = True
        Else
            greater = False
            
    
         End If
         ' Edit to shift Cells in S-U down one row
        If greater Then
            With cell2.Offset(0, -0).Range("a1:c1").Select
            Selection.Insert Shift:=xlDown
            End With
        Else
            With cell1.Interior
                .ColorIndex = xlNone
            End With
        End If
    Next cell1

    ' Examine the cells in the second collection.
    For Each cell2 In cells2
        On Error Resume Next
        Err.Clear
        key = cell2.Row - range2.Row & "," & cell2.Column - _
            range2.Column
        Set cell1 = cells1(key)
        If Err.number <> 0 Then
            ' The second cell is missing.
            less = True
        ElseIf cell2.Text > cell1.Text Then
            ' The cells don't match.
            less = True
        Else
            less = False
        End If

        ' Edit to shift Cells in A-Q down one row
        If less Then
            With cell2.Interior
                .ColorIndex = 35
                .Pattern = xlSolid
            End With
        Else
            With cell2.Interior
                .ColorIndex = xlNone
            End With
        End If
    Next cell2
End Sub


I want a makro that copy a number from a cell and paste into another cell and I will activate it by a button. This is OK.

But the next time I activate the macro I want it to do the same (The value in the cell I copy from changes by a formula) only that I need the to paste the value in the row below so that after some activations I've got a table.

This is what I've got now:

Sub Hent()
'
' Hent Makro
'

'
Range("J21").Select
Selection.Copy
Range("H26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B13").Select
Application.CutCopyMode = False
Selection.Copy
Range("I26").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

So the problem is that when I avtivate the macro, it overwrites the previous values. I want them in the row below. Not only once, but as many times I want to.

Pleas helpe someone. I hope my question is clear!

Hi.

Can someone help me with this one?

I have a long list of numbers in columns A to F

What I want to do is to make a macro that makes cell B and C turn bold and filled with some color if cell D in the same line is blank. And make this macro run through all my lines ( a few thousand).

Is this possible ?

Thank you!

how would I copy the contents of one cell all the way down to row 60,000.

Please give me all the details as I am not a expert at excell.

Thanks

butch

I have resisted using Excel because I couldn't make it do the time saving tricks that I enjoy in version 8 of Quattro Pro. I have been motivated recently to take another look at Excel and have spent many hours trying to make the transition. Several things I have learned to do but one thing I can't figure out is how to reproduce the two macros I use daily to perform the sum function.

The first macro in Quattro Pro is as follows:

Shift+control+t activates the sequence

-
creates a series of hyphens across the current cell. This a Lotus transition that serves as an underline for the column of figures to be summed. I prefer it because it is easily copied, moved or deleted as needed.

~
tilde executes the enter key function and the current cell accepts the previous entry and moves one cell down

=sum({up}{shift+.}
enters the sum command, moves up to the cell containing the underline, anchors and waits for me to block the cells to include in the total afterwhich I hit enter and the summing is completed.

The second method I use is to forgo the underline and place the sum in the cell at the right of the bottom of the column.

I have tried the autosum techniques suggested in books on the subject but my macro is faster and requires less effort. I read that "data-entry macros" are not possible in Excel 2003 and later but I find it hard to believe that such a sophisticated app cannot do what it's predecessors could.

Is there a way to write a macro to perform the function I described?

Thanks in advance.

I have this macro that inserts 26 rows when the value in B changes. (Works
wonderfull)
Now I want the macro to after 26 rows were inserted copy Range B2:K25 to the
2nd row in the range that were inserted.
in other words find a value in B move 2 cells down and past B:K
Hope I make sence. I am inserting a header for invoice lines.

Sub Deilv()
Dim LastRow As Long
Dim row_index As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
For row_index = LastRow - 1 To 26 Step -1
If Cells(row_index, "B").Value _
Cells(row_index + 1, "B").Value Then
Cells(row_index + 1, "B").Resize(26).EntireRow. _
insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub

Well, Duke, I tend to use Excel as a 'pipe' in which to transfer/convey the
data from one source to another OR to provide some calculations on various
spreadsheets that start out as an idea and end up being an application of
vast significance (at least to some people!!)
Thanks for your input. I'm already part of several newsgroups/communities
so it's hard to 'add' time into an already crowded schedule. I do, however,
peruse the various Excel, Word, Outlook, Exchange, Windows XP, Windows 2003,
SQL, communities from time to time.
Tom

"Duke Carey" wrote:

> Blush!!
>
> Well, Tom, for one thing, it's not vast, at least not compared to the MVPs &
> near-MVPs who post here and who frequently and gently correct my public
> errors. For another thing, there's a staggering amount of knowledge, tips, &
> tricks available here in these newsgroups, free but for the cost of the time
> it takes to scroll through some messages each day.
>
>
> "Tom" wrote:
>
> > Thanks, Duke!
> > I never would have figured this one out!
> > Where do you get your vast knowledge of little tricks in Excel?
> > Tom
> >
> > "Duke Carey" wrote:
> >
> > > Assuming that column F has now blanks in it:
> > >
> > > Copy cell G2
> > > Select cell F2
> > > Press the End key followed by the down arrow
> > > Move one cell to the right
> > > Hold down the Shift key and press the End key followed by the up arrow
> > > Paste
> > >
> > >
> > > "Tom" wrote:
> > >
> > > > This seemingly very simply question has me baffled. I have a column of data
> > > > that I need to perform an operation, something similar to '=f2-36161'.
> > > > Column F has the data in numeric format. I add a column, say Column G. Then
> > > > I put into G2 the '=f2-36161' and then convert this new number to a date
> > > > formatted 'mm/dd/ccyy'. I'd like to replicate (copy/paste) this G2
> > > > calculation and operation down through the entire Column G. [Column G is
> > > > about 15000 rows deep.]
> > > > Is there an easy way to do the copy/paste or replicate without copying G2
> > > > and then copy/paste MANUALLY down the column until I reach the end of the
> > > > data?
> > > > Seems like a simply question but I can't find the 'key'.
> > > > Thanks,
> > > > Tom

I have this macro that inserts 26 rows when the value in B changes. (Works
wonderfull)
Now I want the macro to after 26 rows were inserted copy Range B2:K25 to the
2nd row in the range that were inserted.
in other words find a value in B move 2 cells down and past B:K
Hope I make sence. I am inserting a header for invoice lines.

Sub Deilv()
Dim LastRow As Long
Dim row_index As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row
For row_index = LastRow - 1 To 26 Step -1
If Cells(row_index, "B").Value <> _
Cells(row_index + 1, "B").Value Then
Cells(row_index + 1, "B").Resize(26).EntireRow. _
insert Shift:=xlDown
End If
Next
Application.ScreenUpdating = True
End Sub

I have cells in a row with formulas that have the number 25 in them, such as, for example for two of them: =Project1!E25+... and =Project2!G25-....

I need to go to the cell and edit out everything that appears after (to the right of) the 25, then accept the revised formula as is. It's never more than, say, 20 more characters to the right, in case that matters. Can someone (carefully, please) write me a macro that will do this on the current cell, then move one cell to the right and repeat until it gets to the last column in that same row?

Thanks so much!
Dean

Ay suggestions on how I can get this done?

After I make a selection from a data validation drop down list, I'd
like the active cell to move one row down.

Currently, the active cell stays in the cell with the data validation
drop down.

-M

Hi!

I'm trying to do the following:

If you select a cell, (let's say B10), you can move around the text in B10 by using the arrow keys. For example if B10 = "moveit", press right arrow then C10 = "moveit" and B10 is empty again. The same would go for downward and upward movement.

Is this possible? If yes, is it also possible with multiple cells? eg. select range a1:b3, keypress down and the entire selection moves one cell down?

Many thanks in advance!

hi guys must be simple but when i try it on macro recorder it just tells me the cell i have pressed return to.
all i want is the code for

go to the next cell down directly underneath

thanks

I'm looking for someone to help me edit this code some. I need it to look in column "F" and find cells which say <####> and "Total" (IE a subtotal row). If it finds that, then offset one cell to the left and copy the cell that is directly above it.

Sub Test()
Dim lngrow as long
lngRow = Range("I4").End(xlDown).Row
Do While lngRow > 0
    If Cells(lngRow, 6).Value Like "*Total" Then
    ActiveCell.Offset(-1, 0).Select
    Selection.FillDown
    End If
    lngRow = lngRow - 1
Loop
End Sub
Thanks!

Can someone please post the code to create a macro that does the following.

Pause for input on the current cell
Move one cell to the right
Pause for input
Move one cell down and one cell to the left
Re-invoke the macro

Thanks,
J

Hi, I wondered if anyone knows a formula, or can come up with a formula, that copies the information from one cell to the next when the first cell is changed or deleted. For example:

starting situation:

||||||Column 1
row1 | 1
row2 | 2
row3 | 3
row4 | 4
row5 | 5
row6 |

now when i change row 1 into "x"; the rest goes automaticly as followed:

||||||Column 1
row1 | "x"
row2 | 1
row3 | 2
row4 | 3
row5 | 4
row6 | 5

So if i change a cell the cells beneath this one will shift one cell down. Is there a simple formula for my problem? Thanks in advanced!

Ok, I am using the find function to search the headers of a range, then auto-filter for a specific value. That part of my code is works fine.
The problem is I am trying to find a value in the header, then move one cell down and name that cell. I tried using offset, but it doesn't return the right cell. I assume the problem is the cell I want is in say, row 6, but I do not know what row number to use in offset. Can you please help me?


No luck finding an answer? You could always try Google.