Macro to copy a certain cell onto another row

Solved/Closed
DesertMoon20 Posts 7 Registration date Wednesday July 14, 2010 Status Member Last seen September 22, 2010 - Jul 14, 2010 at 05:19 PM
DesertMoon20 Posts 7 Registration date Wednesday July 14, 2010 Status Member Last seen September 22, 2010 - Jul 19, 2010 at 08:57 AM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 18, 2010 at 09:17 AM
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
DesertMoon20 Posts 7 Registration date Wednesday July 14, 2010 Status Member Last seen September 22, 2010
Jul 19, 2010 at 08:57 AM
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
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jul 14, 2010 at 07:02 PM
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
1
DesertMoon20 Posts 7 Registration date Wednesday July 14, 2010 Status Member Last seen September 22, 2010
Jul 14, 2010 at 05:21 PM
Column A is originally blank.
0