VBA Formula

[chrono2483]

Good Evening,

Wondering is there is such a macro to do the following:

I have a worksheet that has 300 rows. Every few rows (not the same distance apart) is there word "Raking" in column A. On the same row as the work ranking are values, from column D:AD.

What I need to do is set up a script in VBA, that will:

1. Find the cell where there is the word "Ranking"
2. Move 3 cells to the right (column D)
3. Select from D:AD, of that same row
4. Cut this data
5. From the D column, shift up 2 cells and left 2 cells
6.Paste the cut data
7. repeat for all other rows that have the word "Ranking" until the end.

Does such exist?

Thank you.

 

[alansidman]

Try this:

Option Explicit


Sub Chrono()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Dim i As Long


Application.ScreenUpdating = False
For i = 3 To lr
If Range("A" & i) = "Ranking" Then
Range("D" & i & ":AD" & i).Cut Range("B" & i - 2)
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "complete"




End Sub

 

[chrono2483]

Try this:

Option Explicit


Sub Chrono()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Dim i As Long


Application.ScreenUpdating = False
For i = 3 To lr
If Range("A" & i) = "Ranking" Then
Range("D" & i & ":AD" & i).Cut Range("B" & i - 2)
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "complete"




End Sub

------------------------------------------
Thank you alansidman - I tried the script but unfortunately no success. I do get the msgbox, but the data doesn't get cut and pasted. Any thoughts?

Thanks in advance.

 

[alansidman]

Worked for me when I tested. Did you put this in a module and not in a worksheet? Try stepping through the code and seeing what happens with each line of code in your spreadsheet. This is in the Debug menu in the VBE. Without actually seeing your spreadsheet, I have no idea what is happening. Also, there is data in Column A. Check the spelling of Ranking in your spreadsheet and make sure it is the same in the code. Uppercase/lowercase, no spaces before or after the word. It needs to be exactly the same in the code as in your spreadsheet.

 

[Jim885]

I liked your code. Worked for me without any problems. Nice job.

 

[Michael M]

One thing that may be causing a problem....is "Ranking" in proper case or is it "RANKING" or "ranking"
If so, insert this line before the start of the sub

Option Compare Text