excel auto fill

I need to be able to work down the rows in the attached spreadsheet to do the following:

Column D has a number of text names
Column B has numbers associated with these names
bill always associates withy 123
john always associates with 456
etc .......
jill does not have an association

and so on

Problem:
sometimes there are gaps in the B column such that the correct associated number has not
been filled in

I need to be able to automate this process so that all gaps are filled in with the appropriate number
The number of rows could go into thousands

Thoughts please
example.xls
doctorbillTechAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
HI,

try this

Sub macro()
For Each c In Range(Range("C2"), Range("E" & Cells.Rows.Count).End(xlUp).Offset(0, -2))
    If c.Offset(-1, 2) = c.Offset(0, 2) Then
        c.Value = c.Offset(-1, 0).Value
    End If
Next
End Sub

Open in new window

Regards
0
 
Rgonzo1971Commented:
Hi,

pls try

Sub macro()
For Each c In Range("B:B").SpecialCells(xlCellTypeBlanks)
    If c.Offset(-1, 2) = c.Offset(0, 2) Then
        c.Value = c.Offset(-1, 0).Value
    End If
Next
End Sub

Open in new window

Regards
0
 
Saqib Husain, SyedEngineerCommented:
This works for the given scenario

Without code:

Select the column B range. In this case it is B2:B37
press F5
Click on special
Select blanks
Click OK
type   =
from the keyboard move the cursor one cell up
press ctrl-enter

With code

Sub fillwithuppercells()
ActiveSheet.UsedRange.Columns(2).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
End Sub
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
doctorbillTechAuthor Commented:
Re this formula:

Sub macro()
For Each c In Range("B:B").SpecialCells(xlCellTypeBlanks)
    If c.Offset(-1, 2) = c.Offset(0, 2) Then
        c.Value = c.Offset(-1, 0).Value
    End If
Next
End Sub

Sorry - my mistake. The numerical values should be in column C and the text names in Column E
0
 
Rgonzo1971Commented:
HI

then

Sub macro()
For Each c In Range("C:C").SpecialCells(xlCellTypeBlanks)
    If c.Offset(-1, 2) = c.Offset(0, 2) Then
        c.Value = c.Offset(-1, 0).Value
    End If
Next
End Sub

Open in new window

Regards
0
 
doctorbillTechAuthor Commented:
When I try to run it I get the following error:

400

any ideas ?
0
 
Rgonzo1971Commented:
Where does the code stop?
0
 
doctorbillTechAuthor Commented:
This is the file which does not work - it has your code in it
newtest.xls
0
 
doctorbillTechAuthor Commented:
Perfect - thanks very much
0
 
doctorbillTechAuthor Commented:
solved
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.