Help with a offset macro script

rdvance

Active Member
Joined
May 9, 2004
Messages
268
I want a offset macro that will go back -11 to -18 colums down 6000 rows and will copy/paste/format/value back into there original cells but losing all there formulas , so really just wanting to get rid of the formulas, the first line does work but not the rest

Cells(1, Cells(1, 256).End(xlToLeft).Column + 1).Select
With ActiveCell.Offset(-11, 0).Resize(6000, 7)
.Value = .Value
End With
End Sub

Thanks for your help ..Bob :confused:
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Welcome Bob,

How about this:

Code:
Sub macro()
Cells(1, Cells(1, 256).End(xlToLeft).Column).Select
Application.ScreenUpdating = False
Application.StatusBar = "Creating values. Please wait."
counter = 0
Do While counter < 6000
ActiveCell.Offset(counter, -11).Formula = ActiveCell.Offset(counter, -11).Value
ActiveCell.Offset(counter, -12).Formula = ActiveCell.Offset(counter, -12).Value
ActiveCell.Offset(counter, -13).Formula = ActiveCell.Offset(counter, -13).Value
ActiveCell.Offset(counter, -14).Formula = ActiveCell.Offset(counter, -14).Value
ActiveCell.Offset(counter, -15).Formula = ActiveCell.Offset(counter, -15).Value
ActiveCell.Offset(counter, -16).Formula = ActiveCell.Offset(counter, -16).Value
ActiveCell.Offset(counter, -17).Formula = ActiveCell.Offset(counter, -17).Value
ActiveCell.Offset(counter, -18).Formula = ActiveCell.Offset(counter, -18).Value
counter = counter + 1
Loop
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

(y)
 
Upvote 0
Hi Bob:

Welcome to MrExcel Board!

Please try changing the following line in your code
Code:
With ActiveCell.Offset(-11, 0).Resize(6000, 7)
to
Code:
With ActiveCell.Offset(0,-11).Resize(6000, 7)
Does it help?
 
Upvote 0
With ActiveCell.Offset(0, -18).Resize(6000, 8)

Changed it slightly to get it right, Brilliant..Thanks..Bob
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top