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

Question

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.

rizvisa1 · Accepted Answer

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

DesertMoon20 · Answer

Column A is originally blank.

rizvisa1 · Answer

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

Macro to copy a certain cell onto another row

3 responses

Similar discussions

Subscribe To Our Newsletter!