Script/Formula to move data to another cell

Mike Page
Mike Page used Ask the Experts™
on
Hi Experts,

See attached.

How do I

1) Move all numeric values in cell 'H' to the adjecent cell 'I' (same row)
2) Clear the cell 'H' after step 1 is completed
3) Move text with 'Answer' to the cell J (same row)
4) Clear the cell after the move

is this possible? new to intense VBA !!

Kind Regards

Mike
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Analyst Assistant
Commented:
Mike

There's no attachment I'm afraid.

For the first part you could try this.
Dim Ar As Range

    For Each ar In Range("H:H").SpecialCells(xlCellTypeConstants, 1)
        ar.Offset(,1).Value = ar.Value
        ar.ClearContents
    Next ar

Open in new window

[/code]
Mike Page

Author

Commented:
cool first part works
NorieAnalyst Assistant

Commented:
Mike

I didn't post anything for the 2nd part as, to me anyway, it isn't clear what you want to do - kind of hoped the attachent would have helped.:)

Anyway, is it that you want to find 'Answer' in column H and if found move it to column J?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Mike Page

Author

Commented:
Anyway, is it that you want to find 'Answer' in column H and if found move it to column J?

Yes please..

Sorry about the attached thing.. internet is down at the office now using smart in the meantime!!
Mike Page

Author

Commented:
Actually move to J if the word contains 'Answer'
NorieAnalyst Assistant
Commented:
We can use similar code for that but we since we are looking for a specific value, ie 'Answer', we need another loop.
Dim ar As Range
Dim cl As Range

    For Each ar In Range("H:H").SpecialCells(xlCellTypeConstants, 2)
        For Each cl In ar.Cells
            If ar.Value = "Answer" Then
                ar.Offset(, 2).Value = ar.Value
                ar.ClearContents
            End If
        Next cl
    Next ar

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial