Excel challenge, please save a doctor some time!
Try this:
Sub DrMacro()
Dim myRange As Range
Dim r As Range
Dim lRow As Long
Dim bRow As Long
Dim newSht As Worksheet
bRow = 2 '<~~~change as needed to identify where the first row of data is
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Cells(1, 1)
Set myRange = myRange.Offset(bRow - 1, 0).Resize(lRow - bRow + 1, 1)
Set newSht = Sheets("Sheet2") '<~~~~change as needed
lrow1 = newSht.Cells(Rows.Count, 1).End(xlUp).Row
For Each r In myRange
If r.Offset(0, 1).Value 0 Then
For i = 1 To r.Offset(0, 1).Value
newSht.Cells(lrow1, 1).Value = r.Value
newSht.Cells(lrow1, 2).Value = 1
lrow1 = lrow1 + 1
Next i
End If
If r.Offset(0, 2).Value 0 Then
For i = 1 To r.Offset(0, 2).Value
newSht.Cells(lrow1, 1).Value = r.Value
newSht.Cells(lrow1, 2).Value = 0
lrow1 = lrow1 + 1
Next i
End If
Next r
End Sub
HTH,
Barb Reinhardt
" wrote:
I need to convert cells in one worksheet into 1's and 0's and
correlate it with a value.
For example, if this was my starting worksheet (which I'm provided
with):
RowA RowB=1 RowC=0
6 3 2
12 5 4
18 1 2
I want to convert the above worksheet into a worksheet that had 2
rows, one being the value of RowA above, and the other being either a
1 or a 0, depending on whether it was rowB or rowC, and the number of
rows equals the number in rowB or rowC above. So, this example would
convert to a new worksheet of:
RowA RowB
6 1
6 1
6 1
6 0
6 0
12 1
12 1
12 1
12 1
12 1
12 0
12 0
12 0
12 0
18 1
18 0
18 0
Does anyone know how to do this without having to enter each value
separately?
Thanks,
Frustrated Doctor
|