move data within macro

[88rizer]

Hi All
I need a way to move data (if it contains the word "Memo:") up 1 and to the right 1 cell, within a macro. I am using excel 2010 on a laptop, any help will be appreiciated. Tks

 

[iyyi]

Welcome to the forum. Try this ...

Sub MoveUpRight()
For Each cl In [a1:e20] 'Change to meet your needs
    If InStr(cl.Text, "Memo:") > 0 Then
        cl.Offset(-1, 1) = cl.Text
        cl.Value = ""
    End If
Next
End Sub

 

[BatesAC]

Welcome to the forum. Try this ...

Sub MoveUpRight()
For Each cl In [a1:e20] 'Change to meet your needs
    If InStr(cl.Text, "Memo:") > 0 Then
        cl.Offset(-1, 1) = cl.Text
        cl.Value = ""
    End If
Next
End Sub

Just improving on the previous answer for you ....

Sub MoveUpRight()
For Each cl In ActiveSheet.UsedRange '<< Across all used cells.
    If InStr(cl.Text, "Memo:") > 0 Then
        cl.Offset(-1, 1) = cl.Text
        cl.Value = ""
    End If
Next
End Sub

 

[88rizer]

I used BatesAC routines, I put them in a macro and accessed the macro within a control macro and it works great. Tks

 

[Peter_SSs]

Welcome to the MrExcel board!

As a matter of interest ..

1. About how many rows and how many columns are used on your worksheet?

2. Can the "Memo:" cells be anywhere on the sheet or only in certain column(s)?
- If certain columns(s), which one(s)?

3. Can you say about how many "Memo:" cells are likely to be encountered?

 

[88rizer]

Welcome to the MrExcel board!

As a matter of interest ..

1. About how many rows and how many columns are used on your worksheet?

2. Can the "Memo:" cells be anywhere on the sheet or only in certain column(s)?
- If certain columns(s), which one(s)?

3. Can you say about how many "Memo:" cells are likely to be encountered?

Memo: is in 1 designated column and 250 rows, I am geting data from a bank acct format and changing it to fit my BankBalancing excel spreadsheet

 

[Peter_SSs]

Memo: is in 1 designated column and 250 rows, I am geting data from a bank acct format and changing it to fit my BankBalancing excel spreadsheet

Well. that answered about 1.5 of my 4 questions

However, that is enough to suggest a tidier code. Instead of checking every cell in your sheet's used range you would be better to just target those in the particular column. The particular column is one of the questions you didn't answer, so I have guessed column G. You'd need to alter that in the code to match your actual "Memo" column.

If you are using BatesAC code then I'd suggest amending the second line like this

For Each cl In Intersect(ActiveSheet.UsedRange, Columns("G"))

Edit: Actually safer would probably be

For Each cl In Range("G1", Range("G" & Rows.Count).End(xlUp))