formula needed

[kylefoley76]

I need a macro where if a 1 appears in column B, the copy what is in column A into the next blank column in column C.

Note: a 1 in column B will appear sporadically but in column C I need each row to be filled one after the other. So in column B many rows will be empty but no rows in column C will be empty.
Thanks in advance.

 

[cstimart]

Try this

Sub FindTheOne()
Dim LR As Long
Dim i As Long

Sheets(1).Select
LR = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR
    If Range("B" & i) = 1 Then
        Range("C" & Range("C" & Rows.Count).End(xlUp).Row + 1) = Range("A" & i)
    End If
Next i

End Sub

 

[kylefoley76]

thank you for helping me but it doesn't work. i'm on sheet 2. i'm not sure if sheet(1) has anything to do with it, but when i run the macro, excel will freeze for about 2 minutes, then switch to a different sheet.

 

[cstimart]

thank you for helping me but it doesn't work. i'm on sheet 2. i'm not sure if sheet(1) has anything to do with it, but when i run the macro, excel will freeze for about 2 minutes, then switch to a different sheet.

Yes, change the sheet number to your specific location.

 

[kylefoley76]

ok, actually the macro is a bit more complicated. the columns are on two sheets. i can't get the syntax exactly right. the other sheet is called time and the sheet in it is also called time.


Sheets(7).Select
LR = Range([time.xlms].time!"K" & Rows.Count).End(xlUp).Row

For i = 1 To LR
If Range([time.xlms].time!"l" & i) = 1 Then
Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1) = Range("A" & i)
End If
Next i

 

[cstimart]

ok, actually the macro is a bit more complicated. the columns are on two sheets. i can't get the syntax exactly right. the other sheet is called time and the sheet in it is also called time.

Both sheets are call "Time"? Are these is separate workbooks?

 

[cstimart]

Are the Columns A & B in 1 worksheet and Column C is in another?

 

[kylefoley76]

yes, seperate workbooks. the time work book uses column l and k, and the sheet in the workbook is also called time. the workbook where the macro outputs doesn't need to be named.

 

[cstimart]

yes, seperate workbooks. the time work book uses column l and k, and the sheet in the workbook is also called time. the workbook where the macro outputs doesn't need to be named.

How's this?

Sub FindTheOne()
Dim LR As Long
Dim i As Long
Dim thisFile As String
Dim thatFile As String
Dim Temp

thisFile = ActiveWorkbook.Name
thatFile = "Time.xlms"

Windows(thatFile).Activate
    Sheets(1).Select
    LR = Range("K" & Rows.Count).End(xlUp).Row
    
    For i = 1 To LR
        If Range("I" & i) = 1 Then
            Temp = Range("A" & i)
            Windows(thisFile).Activate
                Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1) = Temp
            Windows(thatFile).Activate
        End If
    Next i

End Sub

 

[kylefoley76]

i don't have windows. so that line window(thatfile) will have to be deleted

plus on thisfile, it needs to be sheet 2. i don't anywhere in that macro that specifies sheet 2