Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel challenge, please save a doctor some time!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Doctor | Links and Linking in Excel | |||
Excel Doctor | Excel Worksheet Functions | |||
Excel Doctor | Excel Programming | |||
Excel Doctor | Excel Worksheet Functions | |||
Excel Doctor | Links and Linking in Excel |