• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

copy numbers back to column i if column i is blank.

don lincoln

New Member
Hello

I’m looking for a macro

I have a spread sheet with column A through K

Column I, J, K contains phone numbers

Column I is the one which has most of the phone numbers. I’m looking for a macro that will look at column I each cell by cell and if it finds a blank cell then it should go to COLUMN J in that row to see if it contains any data, if it does then it should copy that over to COLUMN I.

And if in COLUMN J if that row is BLANK then it should look for data in COLUMN K and if it finds it then copy that cell’s data to COLUMN I

e.g Row 3 contains number ending 0091 in COLUMN J and COLUMN I is empty in that row so the macro should copy the phone number 760-475-0091 to COLUMN I in row 3

another example the 4th row from the bottom is all blank except its got 866 number in COLUMN K, so the macro should copy that number back to COLUMN I in that row.

Any help would be greatly appreciated.

Thanks.

BELOW IS THE DUMMY DATA.


COLUMN I COLUMN J COLUMN K
MobilePhoneHomePhoneOfficePhone
7609634614​
760-244-5461
760-475-0091760-475-0091
6269451121​
626-945-1121760-407-6441
6269451121​
626-945-1121760-407-6441
760-662-9411760-249-5001
619-729-7231619-584-9421
6269451121​
626-945-1121760-407-6441
619-313-3201619-333-8081619-333-8081
6269451121​
626-945-1121760-407-6441
866-344-7471
760-900-0031760-245-7321
951-454-4851951-729-3341
714-483-1331951-304-2121
 
Hi !​
A reminder of what is written before to log in :​
« When starting a new thread, to receive a quicker and more targeted answer, Please include a sample file in the initial post. »​
A demonstration as a starter :​
Code:
Sub Demo()
    Dim R&
        Application.ScreenUpdating = False
    For R = 2 To ActiveSheet.UsedRange.Rows.Count
        If Cells(R, 9).Value2 = 0 Then Cells(R, 10 - (Cells(R, 10).Value2 = 0)).Copy Cells(R, 9)
    Next
        Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Another option if your interested
Code:
Sub Don_Lincoln()
   Dim Cl As Range
  
   For Each Cl In Range("I:I").SpecialCells(xlBlanks)
      Cl.Value = IIf(Cl.Offset(, 1) <> "", Cl.Offset(, 1).Text, Cl.Offset(, 2).Text)
   Next Cl
End Sub
 
Back
Top