CODEWITHSUNDEEP
excelvbacell
swapnali
swapnali

Reputation: 1

Excel 2007 - VBA Cut paste value from one cell to another below each other

**I am counting strings like if a word is appearing 10 times. then that word is in cell a1 and count is in cell a2 ** - I am generating this from code...(SUM((LEN(range_chk)-LEN(SUBSTITUTE(UPPER(range_chk),UPPER(Sheet2!E8),"")))/LEN(Sheet2!E8)) ) ( My data is in sheet1 and I am finding words in sheet2 ....FYI - range_chk is a offset function used in excel through name manager---and this code works fine)

I have tried worksheet_change(byval target as range) function but its going in loop. please help on code.

What I need is

I am finding word : in cell A1: Me

I am getting count : in Cell A2: 5

cutting from above cells n pasting it in

Cell A7 --"Me" Cell B7---"5"

Next searched word: "Really" with count 3

cut n Paste in Cell A8 --"Really" Cell B8-- 3

Next Searched word: "sense" with count 6

cut n Paste in Cell A9 --"sense" cell B9 --6 ....and so on.

Then sort the range from A7:B9 according to count in ascending order.

Upvotes: 0

Views: 507

Answers (1)

Goos van den Bekerom
Goos van den Bekerom

Reputation: 1493

If you want to use the Worksheet_Change method do this and it will stop looping:
It's looping because another thing on the worksheet changes when you run your code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    'Your code here

    Application.EnableEvents = True
end sub

Upvotes: 1

Related Questions