If a Cell Is Blank, Fill With Other Cell’s Text Data

  • This is probably simple for you experts out there. I've tried so many ways, and in so many locations w/in the code, but none are working for me. Please help!

    All I’m trying to do is get data from CELL RANGE B4:B4000 to copy automatically to CELL RANGE J4:J4000 should CELL RANGE J4:J4000 be empty.

    FYI: Data being inputted via a user-form.


    This is my code:


  • Rather than trying to go through your code, could you attach a copy of your file and explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hi Mumps, Thanks for coming to my rescue!!


    I'll do my best in explaining. I'm a super beginner to VBA and don't know the lingo very well.


    1. Here's the link to my database. [SIZE=8px]https://www.dropbox.com/s/lfu5…20needs%20Fixed.xlsm?dl=0[/SIZE]


    2. Here's my attempt to explain.


    a. I have a userform to enter client data into database worksheet.
    b. User enters client data.
    -Database can't have duplicate entries
    -Database can't have a blank cell under FullName column (Range=J4:J4000)


    Dim FullName As String 'Variable for FULL NAME = CELL RANGE J4:J4000



    Since I can't have a blank cell in FullName range, I'm trying to write code that will COPY data from the row I'm entering data into (TargetRow) to another cell in the same worksheet, same row, just different cell.


    TargetRow = Sheets("Engine").Range("B5").Value



    The LOCATION I need the copied data to go into is the blank cell FullName (Range=J4:J4000) within the TargetRow we're in.



    The DATA I want to copy FROM is called the QBFileName (in Range B4:B4000), which is in the same worksheet, same row


    Dim QBFileName As String 'Variable Quick Books File Name = CELL RANGE B4:B4000



    [SIZE=20px]I've probably totally lost you now! [/SIZE][SIZE=14px]:^([/SIZE]



    Bottom line...



    I just need to copy data = QBFileName (B4:B4000) into FullName (J4:J4000), within same the TargetRow... IF... and only IF... User does NOT enter a FullName leaving this cell BLANK.



    I'm so sorry my explanation is poorly written. I hope this won't cause you to drop me like a hot potato!
    Hopefully you can review the file I've put in dropbox link and it will help clear up my attempt to explain.
    [SIZE=14px]I sure hope you can help![/SIZE]

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top, click "Insert" and then click "Module. Copy/paste the macro in the empty window that opens up. Press the F5 key to run the macro. Close the window to return to your sheet. There are easier ways to run a macro such as assigning to a short cut key or to a button on your worksheet. If you are still having problems, please attach a copy of your file.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Unfortunately, the link you posted takes me to the Dropbox home page. I need the link to your file.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try the attached file. Click on the "Update Client Full Names" button.

    Files

    • DRJ.xlsm

      (289.75 kB, downloaded 94 times, last: )

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • As you can see it's a database with a userform to ADD or EDIT an entry.


    If when the user adds an entry, and (first/last name) = FullName is not entered, then I want that entry to automatically put the QBFileName in the FullName place.


    Is it clearer now that you can see my file?

  • In the Private Sub CmdButton_CONTINUE1_Click() macro, replace this line of code:

    Code
    1. Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 8).Value = Txt_Client_First_Name & " " & UCase(Txt_Client_LAST_Name)

    with this code:

    Code
    1. If Txt_Client_First_Name = "" Or Txt_Client_LAST_Name = "" Then
    2. Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 8).Value = Txt_QB_File_Name
    3. Else
    4. Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 8).Value = Txt_Client_First_Name & " " & UCase(Txt_Client_LAST_Name)
    5. End If

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • [SIZE=14px]MUMPS!!! YOU DID IT!!!!!!!!!!! IT WORKED!!!! [/SIZE]:sing: :rock:


    THANK YOU SO MUCH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! :congrats: :rose: :sing: :dance: :cheers: :kisshear: :thumbcoo:
    I've been trying to get a solution to this for sooo long! Finally someone was able to get it to work!! [SIZE=14px] You ROCK!! :drum: LITERALLY!
    :ole:
    [/SIZE]

  • Ok... I spoke too soon. Yes it is working and I'm thrilled, but for some reason now my message (UserMessage) is no longer picking up the FullName in the message when reading the new code.


    It works if a First/Last Name were entered which creates a Full name, but not when your new code is used taking the QBFileName to the FullName. hummmm....


    I'm trying to figure it out, as it seems to me it should still work. Maybe it's a simple fix that I'm just missing???


    You're definitely on your game with this, can you please see if you can figure it out?


    This is the usermessage code...


  • Try deleting this line of code:

    Code
    1. FullName = Txt_Client_First_Name & " " & Txt_Client_LAST_Name

    and inserting this line:

    Code
    1. FullName = Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 8).Value

    below this code:

    Code
    1. If Txt_Client_First_Name = "" Or Txt_Client_LAST_Name = "" Then
    2. Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 8).Value = Txt_QB_File_Name
    3. Else
    4. Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 8).Value = Txt_Client_First_Name & " " & UCase(Txt_Client_LAST_Name)
    5. End If

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • WITHOUT A DOUBT YOU ROCK!!! :music:


    That was sooo close, I was able to figure it out from there!


    I had to LEAVE IN:


    Code
    1. FullName = Txt_Client_First_Name & " " & Txt_Client_LAST_Name


    BUT STILL ADD your new line below YOUR previous AMAZING IF statement code...


    Code
    1. FullName = Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 8).Value


    AND IT'S WORKING GREAT!!!!!!!!!!!!!!!!!!!!!!!!!! :drum: :thumbcoo:


    THANKS SO MUCH MUMPS! You have no idea how much you are appreciated! :thumbcoo: :rock:
    YOU'VE SAVED ME! :sing: :jumpupdo: :thanx:


    [SIZE=16px]THANK YOU! THANK YOU! THANK YOU!!![/SIZE]
    :saychees:

  • You are most welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.