Excel, filling a blank Column 1

[darinmc]

Hi
I have an excel sheet where the data appears as below. The excel book alread has 1 macro (Which i got code from a previous thread), I would like to add another if possible,
which will run down Col B (Especially in Red) and take the last 3 numbers, and paste them into col A???

---------------------------
A B C etc......
Totley School,-SITE ID:A080
Date Employee Name
03 Sep Elsharif Abdalla, 1
Town Hall,-SITE ID:A391
Date Employee Name
03 Sep , 149
03 Sep Brhan Hagos, 134
03 Sep Enock Geb, 28
03 Sep Enock Geb, 28
03 Sep Enock Geb, 28
03 Sep Fasika Metaferia,
03 Sep Roma Tewelde, 1
03 Sep Samira Habib, 80
03 Sep Samirawit Getachew,
03 Sep Tesfom Habte, 109
Verdant Way Housing,-SITE ID:A397
Date Employee Name
03 Sep Tirhas Debesay, 160
Wainwright Crescent Hostel,-SITE ID:B654
Date Employee Name
03 Sep Ahmed Ali, 55
--------------------------

How it should appear:
---------------------------
A B C etc......
80 Totley School,-SITE ID:A080
Date Employee Name
03 Sep Elsharif Abdalla, 1
391 Town Hall,-SITE ID:A391
Date Employee Name
03 Sep , 149
03 Sep Brhan Hagos, 134
03 Sep Enock Geb, 28
03 Sep Enock Geb, 28
03 Sep Enock Geb, 28
03 Sep Fasika Metaferia,
03 Sep Roma Tewelde, 1
03 Sep Samira Habib, 80
03 Sep Samirawit Getachew,
03 Sep Tesfom Habte, 109
397 Verdant Way Housing,-SITE ID:A397
Date Employee Name
03 Sep Tirhas Debesay, 160
654 Wainwright Crescent Hostel,-SITE ID:B654
Date Employee Name
03 Sep Ahmed Ali, 55
--------------------------

Any help would be greatly appreciated, code written out for me...

thx
Darin

 

[AnotherHiggins]

There's no need to use a macro, you can just use a formula in column A.

[tab][COLOR=blue white]=if(isnumber(find("SITE", B1)), right(B1, 3), "")[/color]

If you want column A to be stored as numbers rather than strings (the first row will be 80 instead of 080), then change the formula like this:

[tab][COLOR=blue white]=if(isnumber(find("SITE", B1)), value(right(B1, 3)), "")[/color]

If you need to accomplish this with a macro, use the macro recorder and then add that formula. Post back for help streamlining the code to put that formula into all populated rows.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[darinmc]

Thx
That seems to work fine, VERY good solution.
It would be better to run it as a Macro so that we dont have to keep typing in the formula...
I already use a fill macro which fills the blanks e.g.
363 Palatine Chambers,-SITE ID:A363
363 Date Employee Name Pin
363 03 Sep Ruta Zoyd, 24 17667
369 Redvers House,-SITE ID:A369
369 Date Employee Name Pin
369 03 Sep Beniam Kifle Woldu, 23633

using code:

Public Sub FillBlanks()
    Dim lngLastKnownVal As Long
    Dim pntr As Long
    Dim lngLastRow As Long
    
' ALL Data in column A must be numbers
    lngLastRow = Cells.Find("*", Cells(Cells.Rows.Count, Cells.Columns.Count), , , xlByRows, xlPrevious).Row
        
    For pntr = 1 To lngLastRow
        If Sheet1.Cells(pntr, 1) <> "" Then
            lngLastKnownVal = Sheet1.Cells(pntr, 1)
        Else
            Sheet1.Cells(pntr, 1) = lngLastKnownVal
        End If
    Next pntr
End Sub

this is the code which I wouldnt mind incorporating in the macro, if possible???

Obviously, this part of the code has to run first, then the fill part

=IF(ISNUMBER(FIND("SITE",$B1)),VALUE(RIGHT($B1,3)),"")

thx
Darin

 

[darinmc]

Is there anyone Plz that can help??

Thx
Darin

 

[globalbear]

Hi,

I put an extra loop in your code. Think it should do the trick.

Public Sub FillBlanks()
    Dim lngLastKnownVal As Long
    Dim pntr As Long
    Dim lngLastRow As Long
    
' ALL Data in column A must be numbers
    lngLastRow = Cells.Find("*", Cells(Cells.Rows.Count, Cells.Columns.Count), , , xlByRows, xlPrevious).Row
[COLOR=red]        
    For siteid = 1 To lngLastRow

        If (InStr(1, Cells(siteid, 2), "SITE ID")) Then
            pos = InStrRev(Cells(siteid, 2), "SITE ID") + 9
            Cells(siteid, 1) = Mid(Cells(siteid, 2), pos, 3)
        End If

    Next siteid
[/color]

    For pntr = 1 To lngLastRow
        If Cells(pntr, 1) <> "" Then
            lngLastKnownVal = Cells(pntr, 1)
        Else
            Cells(pntr, 1) = lngLastKnownVal
        End If
    Next pntr
End Sub

The loop searches for "SITE ID" in col B and finds out in which position in the cell the text is. Then it takes that postion + 9 more and from there copies 3 characters to col A. That would give you only the numbers.

 

[darinmc]

Thx GlobalBear

Works wonderfully

Darin