Help with an Excel macro please?
Hi,
Am Mon, 28 Apr 2014 21:14:21 +0100 schrieb Victor Delta:
However, there is one final modification I would like to make if it's
possible. Rather than copying the sets of numbers straight down column
B, I would like to only copy them to those rows where the cells of
column E contain a 'Y'. So if C1 was 15, the 15 numbers might actually
be spread down a much greater number of rows.
try:
Sub Randomize()
Dim i As Long, n As Long
Dim c As Range
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
i = 1
Set c = .Range("E:E").Find("Y", after:=.Range("E1"), _
LookIn:=xlValues, LookAt:=xlWhole)
Do
c.Offset(0, -3) = .Cells(i, 1)
i = i + 1
n = n + 1
If i = 7 Then
.Calculate
i = 1
End If
Set c = .Range("E:E").FindNext(c)
Loop While n < 15
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|