copy down info

[muzzy]

I have a vb that I got it work in other workbook and sheet. But I am trying to use it on a new worksheet and it will not work. Can some help me with it or give me a formulas that will do the same thing.

Sub copy()
Dim varData As Variant
Dim i As Long

varData = Sheet1.Range("C2:C4000") '// Read in the data.

For i = LBound(varData, 1) + 2 To UBound(varData, 1)
    If IsEmpty(varData(i, 1)) Then
        '// Cell is empty. Copy value from above.
        varData(i, 1) = varData(i - 1, 1)
    End If
Next i

'// Write result to sheet.
Sheet1.Range("B2").Resize(UBound(varData, 1) - LBound(varData, 1) + 1, 1) _
    = varData
End Sub

 

[muzzy]

The two columns are B and C
I am trying to copy cell C1 to B1 and down to it see the next number in column (C) then copy that one down column B.
Hope this help

 

[Joe4]

OK, I see. Your posting is what you want it to look like when complete (not before starting).
One question though, how do you know how far to copy the last entry in column C down (since there is nothing else below it)?
Are you looking at another column to see how far the data should go down?

 

[Joe4]

I was messing around with your code in post #5, and noticed a few things:

1. When you changed it, you changed "varData" to start in row 5, but when you wrote out the results in the last step, you started on row 6. This would cause all your data to shift one row down. Is that really your intention?

2. You named your VBA code procedure "copy". You should never used reserved words (names of functions, procedures, etc) as variable or procedure names, as it could cause intended consequences. I recommend using a name like "MyCopy".

3. With those few minor edits, your code in post #5 seems to work. If it does not work for you, where does the code reside?
Did you place it in a Standard Module, or a Sheet Module?
Are you getting errors or unintended results?
If unintended results, exactly what is happening?

 

[muzzy]

	70000	Runner, Carol - 70000
		Idle	Automatic Idle with Sign-on	4/3/2014	10:37:43AM	00:02:02
		Idle	Automatic/No reason	4/3/2014	12:58:25PM	00:03:09
		Idle	Break	4/3/2014	05:17:19PM	00:10:20
		Idle	Admin/offline work	4/3/2014	11:23:01AM	00:04:25
		Idle	Automatic/No reason	4/3/2014	01:25:26PM	00:00:06
		Idle	Admin/offline work	4/3/2014	03:36:07PM	00:07:28
		Idle	Automatic/No reason	4/3/2014	03:36:10PM	00:00:01
		Sign-off	Sign out	4/3/2014	03:36:19PM	00:00:09
		Date Sign-On Duration	32:09:57
		Total Reporting Period Sign-On Duration:	141:57:53
		Total Team - 018 Sign-On Duration:	38381:38:15
		System Totals:	Total Sign-On for All Agent Groups:	38381:38:15
		Filters Applied:	Relative Date:	Month To Yesterday	Start Date:	4/1/2014
			Thursday, April 17, 2014 11:27:23	Page 1 of 1

<tbody>
</tbody>

I am looking at columns B,C,D. I am looking to info of all the diffidence Idle time per agent. So if you look at Column D you will see the end of the report. Column D is the 3rd column in from the left. It is the column with the word Idle in it.

 

[Joe4]

Did you see my post after that (post #13)?
Heed that advice, and answer those questions, if you cannot get it to work.

 

[muzzy]

I changed the name and I changed the starting cell to b5 it still does not copy down I copy over but not down. I mean it copy c5 to b5 but not down.

 

[muzzy]

update I removed (+ 2 ) from the VB. also I have formula in column (C) so I can get the 5 digit number out of the name. when I remove all the formulas under the first 5 digit number and stop at the second 5 digit in column (C) and then run the VB it works up to the second 5 digit number.

 

[Joe4]

I would approach the problem a little differently (just because it is what I am comfortable with) and do it like this:

Sub MyCopy()


    Dim lastRow As Long
    
'   Find last row in column D with data
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Copy data in column C to column D (assuming starting on row 5)
    Range("C5:C" & lastRow).copy
    Range("B5").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'   Select all blank cells in column B
    Range("B5:B" & lastRow).SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    
End Sub

 

[muzzy]

You VB work if there is no formulas in the cell I have formulas in the cells at are in columns (C) so I can get the 5 digit emp ID from the name.

Can we get the VB to read Values not formulas if so your VB will work for what I need it to do.

 

[Joe4]

Do you mean like this?

Sub MyCopy()


    Dim lastRow As Long
    
'   Find last row in column D with data
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Copy data in column C to column D (assuming starting on row 5)
    Range("C5:C" & lastRow).copy
    Range("B5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
'   Select all blank cells in column B
    Range("B5:B" & lastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    Range("B5:B" & lastRow).copy
    Range("B5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
End Sub