Data Fill in Visual Basic

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carrie - 23 Jan 2008 02:23 GMT

I have a worksheet designed in columns. There are 0's, 1's & 2's in the
portion I am working with. I have set the 1 and 2's to coinside with a
calendar schedule. The 1 representing the start date and the 2 representing
the end date. In order to refresh my model I need the fill in all the 0's
between the 1 and 2 with 1's. (I may want to convert the 2 to a 1 as well,
not sure yet) Can anyone suggest a way to do this in visual basic? I have had
no luck and I do not want to do it with a formula.

Thx in advance
Carrie

Reply to this Message

Mark Ivey - 23 Jan 2008 04:27 GMT

Here is something you can start with: (this is a mod I got from
http://www.mvps.org/dmcritchie/excel/delempty.htm

Sub tester()
ZeroToOne (ActiveSheet.UsedRange) ' modify as needed to pass the range
you need changed to "1"
End Sub

Private Sub ZeroToOne(rng As Range)
Dim ix As Long
If rng Is Nothing Then
GoTo done
End If
For ix = rng.Count To 1 Step -1
If rng.Item(ix).Value = 0 Then
rng.Item(ix).Value = 1
End If
Next
done:
End Sub

> I have a worksheet designed in columns. There are 0's, 1's & 2's in the
> portion I am working with. I have set the 1 and 2's to coinside with a
[quoted text clipped - 8 lines]
> Thx in advance
> Carrie

Reply to this Message