Inserting cells based on values in that cell
Posted by Martin on January 10, 2001 8:34 AM
I have a column of data with some values which are greater than 100. In each cell which has a value of less than 100 I want to insert a blank cell to shift the cells in the next column to the right. Has anyone any ideas on how to do this using a macro??
Posted by Aaron on January 10, 2001 9:32 AM
Try This:
Dim i As Integer
On Error Resume Next
For i = 1 To 20
If ActiveSheet.Range("A" & i).Value < 100 Then
Range("A" & i).Insert Shift:=xlToRight
End If
Next
End Sub
This assumes that your data column is in A1:A20
Posted by Martin on January 11, 2001 2:12 AM
Thanks a lot Aaron...that worked a treat!! I've got a whole (65000 +) cells to work on but when I change the 1 To 20 to 1 To 65000, it doesn't seem to work..any ideas?? Or is there a quick way of doing it on a user selection of cells??
Cheers, Martin
Posted by Tim Francis-Wright on January 11, 2001 6:33 AM
In VBA, an Integer can't exceed 32767; change
Dim i As Integer
to
Dim i as Long
if you're going to use a large number like 65000.
--Tim F-W
