Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Applying ID numbers to multiple rows 2

Status
Not open for further replies.

albop

MIS
Nov 12, 2013
6
GB
Hi,

I've not used VBA in a number of years and found myself needing to use it this afternoon and I've gone totally blank! and I'm really hoping one of you could help

I have an Excel sheet (see attached) where I have a membership number and on the same number there could be several people attached. The file I'm provided with details the number once and the people attached in the next column, there is then a break until the next member number. I need to populate the blank cells with the appropriate number.

I'm assuming a do loop until would do the trick, but, I'm falling short... If anyone could help I'd greatly appreciate it.

Many Thanks
 
 http://files.engineering.com/getfile.aspx?folder=ca25b8dc-0062-4fab-a0fb-f0493232f9fe&file=tektips.xlsx
Assuming you will always have a 'starting' value in A4:

Code:
Option Explicit

Sub DoIt()

Dim intRow As Integer
Dim intMNo As Integer

intRow = 4

Do While Range("B" & intRow).Value <> ""
    If Range("A" & intRow).Value = "" Then
        Range("A" & intRow).Value = intMNo
    Else
        intMNo = Range("A" & intRow).Value
    End If
    intRow = intRow + 1
Loop

End Sub

Have fun.

---- Andy

There is a great need for a sarcasm font.
 
Hi,

Here's a solution that assumes that the Member number cell could be anywhere...
Code:
Sub FillMembNum()
    Dim r As Range, rFROM As Range, sPREV As String
    
    If ActiveSheet.Cells(1, 1).Value = "Member number" Then
        Set rFROM = ActiveSheet.Cells(1, 1)
    Else
        Set rFROM = ActiveSheet.Cells.Find("Member number", LookIn:=xlValues).Offset(1)
    End If
    
    If Not rFROM Is Nothing Then
        For Each r In Intersect(Range(rFROM, rFROM.End(xlDown).End(xlDown).End(xlDown)), ActiveSheet.UsedRange)
            If r.Value = "" Then
                r.Value = sPREV
            Else
                sPREV = r.Value
            End If
        Next
    End If
End Sub

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Thanks so much for your help - It's working perfectly :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top