copy down info

W

wigi

Well-known Member

Joined

Jul 10, 2006

Messages

7,964

Office Version

1. 365

Platform

1. Windows
2. Mobile
3. Web

• Apr 19, 2014

• #2

I would prefer built-in methods in Excel:

Code:

Sub wigi()
    For Each ar In Sheet1.Range("C2:C4000").SpecialCells(4)
        ar.Value = ar.Cells(1).Offset(-1).Value
    Next
End Sub

 

Upvote 0

M

muzzy

Active Member

Joined

Apr 8, 2014

Messages

333

• Apr 21, 2014

• #3

I can not get that to work

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,171

Office Version

1. 365

Platform

1. Windows

• Apr 21, 2014

• #4

Note your references to "Sheet1". That means it is specifically written to work on sheet with index of 1.
If the sheet you are trying to run it on in your other book is not this sheet, you will need to change this code.
If you remove the Sheet references all together, it will run on the Active Sheet, regardless of what sheet that is.

 

Upvote 0

M

muzzy

Active Member

Joined

Apr 8, 2014

Messages

333

• Apr 21, 2014

• #5

I took out the sheet1 and still it does not copy down the cell in columns B. I am open for a new vb if you have or know of one.

Code:

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

varData = Range("c5:c40000") '// 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.
Range("B6").Resize(UBound(varData, 1) - LBound(varData, 1) + 1, 1) _
    = varData
End Sub

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,171

Office Version

1. 365

Platform

1. Windows

• Apr 21, 2014

• #6

Instead of us having to figure what you are trying to do by looking at your code and guessing what your intention is, can you explain in English exactly what you are trying to do?

 

Upvote 0

W

wigi

Well-known Member

Joined

Jul 10, 2006

Messages

7,964

Office Version

1. 365

Platform

1. Windows
2. Mobile
3. Web

• Apr 21, 2014

• #7

muzzy said:

I am open for a new vb if you have or know of one.

Click to expand...

Euh, my code above?
If you can't get it to work, you will be helped in a better way if you indicate what is going on.
Do you get an error? What do your cells look like? And so on.

 

Upvote 0

M

muzzy

Active Member

Joined

Apr 8, 2014

Messages

333

• Apr 22, 2014

• #8

Sorry what I am trying to do is copy down into blink cell the number or word. until you see the next number or word in column (C). column (C) is the data and column (B) is the VB or formulas that will copy the info down. Hope the explain helps.

45678	45678
45678
45678
45678
54698	54698
54698
54698
54698
54698
54698
569874	569874
569874
569874
569874
569874
569874
569874
569874
569874
569874
569874	58694
569874
569874
569874
569874
569874
569874
569874

<tbody>
</tbody>

 

Upvote 0

M

muzzy

Active Member

Joined

Apr 8, 2014

Messages

333

• Apr 22, 2014

• #9

Wigi I run your VB and it did not work but it did not give me any error so I do not know what to say. But if you look at my explain you will find out what I am looking for Sorry for before.

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,171

Office Version

1. 365

Platform

1. Windows

• Apr 22, 2014

• #10

what I am trying to do is copy down into blink cell the number or word. until you see the next number or word in column (C). column (C) is the data and column (B) is the <acronym title="vBulletin" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VB</acronym> or formulas that will copy the info down.

Click to expand...

That isn't too clear.
The data you posted, are those columns B and C?
Are you trying to populate the blank cells in the select column there?
If so, is the value you are populating coming from the cell above (same column, different row), or from the cell to the left (same row, different column)?

 

Upvote 0