Applying ID numbers to multiple rows 2

[albop]

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

 

[Andrzejek]

Assuming you will always have a 'starting' value in A4:

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.

 

[SkipVought]

Hi,

Here's a solution that assumes that the Member number cell could be anywhere...

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]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[albop]

Thanks so much for your help - It's working perfectly