Macro to copy a certain cell onto another row [Solved/Closed]

Report
Posts
7
Registration date
Wednesday July 14, 2010
Status
Member
Last seen
September 22, 2010
-
Posts
7
Registration date
Wednesday July 14, 2010
Status
Member
Last seen
September 22, 2010
-
I'm trying to copy values from one cell to another excel via use of a macro, where E#99999 is the employee number to be put to column A until it reaches the end of file.

from this:
A B
1 E#12345
2 data1
3 data2
4 E#12346
5 data1
6 data2
...

to this:
A B
1 E#12345
2 E#12345 data1
3 E#12345 data2
4 E#12346
5 E#12346 data1
6 E#12346 data2

Help in this is highly appreciated.

Thanks.

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Try this

Sub FixData()
Dim sTgtSht As String
Dim lMaxRows As Long
Dim lRow As Long
Dim sLastEmp As String

    sTgtSht = "Sample"
    
    Sheets(sTgtSht).Select
    
    lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
    
    For lRow = 2 To lMaxRows
        
        If (Cells(lRow, "B") = "") Then GoTo Next_lRow
        
        If (Left(Cells(lRow, "B"), 2) = "E#") Then
            sLastEmp = Cells(lRow, "B")
        Else
        
            Cells(lRow, "A") = sLastEmp
        End If
        
        
Next_lRow:
    Next lRow
    
End Sub
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2778 users have said thank you to us this month

Posts
7
Registration date
Wednesday July 14, 2010
Status
Member
Last seen
September 22, 2010

Thanks rizvisa1, the macro works perfectly... however, I just noticed that some empl # in my data doesn't start with E#, there are those have "1 Syst" and "10 Syst" (need to show only 1 and 10) as well as others like "1050 Munz" which I like to show as "1050". Appreciate your help.

The spreadsheet can be found here: https://accounts.google.com/ServiceLogin?service=wise&passive=1209600&continue=https://docs.google.com/spreadsheets/d/1-rCNkOXX-eJ2cespFthx9lzDsFDoWQme70vtldAn7UA/edit?authkey%3DCNbs3Z4M%26hl%3Den%26hl%3Den%26authkey%3DCNbs3Z4M&followup=https://docs.google.com/spreadsheets/d/1-rCNkOXX-eJ2cespFthx9lzDsFDoWQme70vtldAn7UA/edit?authkey%3DCNbs3Z4M%26hl%3Den%26hl%3Den%26authkey%3DCNbs3Z4M<mpl=sheets&hl=en
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
Posts
7
Registration date
Wednesday July 14, 2010
Status
Member
Last seen
September 22, 2010

Column A is originally blank.