Need with with macro/visual basic

TCollin6

New Member
Joined
Jan 25, 2005
Messages
11
I am attempting to build a macro that finds content of a certain font and color (already figures that one out) and then cut that cell, move one cell to the left and one cell down and paste. I am wanting to be able to complete this function around 4000 times. Any ideas? I have only started toying with VB, so I'm not that wise yet.

Any assistance you may be able to provide would be greatly appreciated!

Tim
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Can you post the code you already have and also explain further exactly what you want?

I'm sure it can be adapted to work multiple times.
 

TCollin6

New Member
Joined
Jan 25, 2005
Messages
11
Here's what I got so far.....

Sub moveme()
'
' moveme Macro
' Macro recorded 1/25/2005 by TCollin6
'

'
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=True).Activate
Range("C1618").Select
Cells.FindNext(After:=ActiveCell).Activate
Range("C1619").Select
Application.FindFormat.Clear
Application.FindFormat.NumberFormat = "General"
With Application.FindFormat
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Application.FindFormat.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 9.75
.Strikethrough = False
.Superscript = False
.Subscript = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 10
End With
Application.FindFormat.Borders(xlLeft).LineStyle = xlNone
Application.FindFormat.Borders(xlRight).LineStyle = xlNone
Application.FindFormat.Borders(xlTop).LineStyle = xlNone
Application.FindFormat.Borders(xlBottom).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalDown).LineStyle = xlNone
Application.FindFormat.Borders(xlDiagonalUp).LineStyle = xlNone
Application.FindFormat.Interior.ColorIndex = xlNone
Application.FindFormat.Locked = True
Application.FindFormat.FormulaHidden = False
Cells.FindNext(After:=ActiveCell).Activate
Selection.Cut
Range("B1622").Select
ActiveSheet.Paste
End Sub

What I need changed is between the copy and paste. I need excel to select the cell one left and one down from the cell that was cut and then paste.

I hope that clears it up a bit!!
 

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
I haven't looked over all of your code, but in general to find a cell one down and one to the left, the Offset property is useful.

Try replacing these three lines:

Code:
Selection.Cut 
Range("B1622").Select 
ActiveSheet.Paste

With:

Code:
Selection.Cut Destination:=Selection.Offset(1,-1)
 

TCollin6

New Member
Joined
Jan 25, 2005
Messages
11

ADVERTISEMENT

Thanks scifibum, that's a step in the right direction!!!

Here's a little cleared what I'm trying to do:

I have alist of raw data in Excel as below:

Supervisor Z
Representative A
Representative B
Representative C

I need to move the supervisor name to show next to all the representative names. FYI....The worksheet I'm working with has over 12,000 lines and different sized groups of supervisor and representatives. It's a toughy allright!

Thanks again for you help, it is greatly appreciated!! :pray:
 

TCollin6

New Member
Joined
Jan 25, 2005
Messages
11
Any ideas how I make the selected cell the one that the last cell was moved to and repeat till the end of the spreadsheet?
 

Forum statistics

Threads
1,147,690
Messages
5,742,638
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top