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

DRJ4444 · Mar 30th 2019

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:

Code

Option Explicit
 
Private Sub CmdButton_CONTINUE1_Click()
 
[COLOR=#696969]Dim TargetRow As Integer [/COLOR]
Dim [B]FullName [/B]As String  [COLOR=#008080][I] 'Variable for FULL NAME =[B] CELL RANGE J4:J4000[/B][/I][/COLOR]
Dim [B]QBFileName [/B]As String   [COLOR=#008080][I]'Variable Quick Books File Name[B] = CELL RANGE B4:B4000[/B][/I][/COLOR]
[COLOR=#808080]Dim UserMessage As String[/COLOR]
 
FullName = Txt_Client_First_Name & " " & Txt_Client_LAST_Name  [I][COLOR=#008080]'Variable configured: shows full name[/COLOR][/I]
QBFileName = Txt_QB_File_Name
 
[COLOR=#008080][I]'begin check if EDIT or ADD New Entry Mode[/I][/COLOR]
If Sheets("Engine").Range("B4").Value = "NEW" Then  [COLOR=#008080][I]'ADD New Entry Mode[/I][/COLOR]
   
[COLOR=#008080][I]    '''**'BEGINS VALIDATION CHECK: IF in ''ADD New Entry Mode" mode to prevent duplicate FULL NAME J Column entries**''[/I][/COLOR]
    If Application.WorksheetFunction.CountIf(Sheets("Database").Range("J3:J4000"), FullName) > 0 Then
 
    MsgBox "Client's Full Name already exists", 0, "Check"
    Exit Sub
   
    End If  [COLOR=#008080][I]'ends validation check OF Duplicate FULLNAME (J Column)[/I][/COLOR]
 
 
[COLOR=#008080][I]    '''**'BEGINS VALIDATION CHECK: IF in "ADD New Entry Mode" to prevent duplicate QBFileName B Column entries**''[/I][/COLOR]
    If Application.WorksheetFunction.CountIf(Sheets("Database").Range("B3:B4000"), QBFileName) > 0 Then
 
    MsgBox "QuickBooks File Name already exists", 0, "Check" [COLOR=#008080][/COLOR]
    Exit Sub
 
    End If  [COLOR=#008080][I]'ends validation check[/I][/COLOR]
 
 
TargetRow = Sheets("Engine").Range("B3").Value + 1 'make variable equal to COUNTA formula on worksheet + 1
UserMessage = " has been added to the database"  
 
Else  [COLOR=#008080][I]'in EDIT Mode[/I][/COLOR]
 
TargetRow = Sheets("Engine").Range("B5").Value 'make variable equal to the value saved in the engine
UserMessage = "'s details have been edited" [COLOR=#008080][/COLOR]
 
End If
[COLOR=#008080][I]'end check if in 'edit' or 'add new mode'[/I][/COLOR]
 
 
[COLOR=#008080][I]'''****BEGIN INPUT DATA INTO DATABASE****'''[/I][/COLOR]
 
Sheets("Database").Range("Data_Start").Offset(TargetRow, 0).Value = Txt_QB_File_Name [COLOR=#008080][I]'QuickBooks File Name[/I][/COLOR]
Sheets("Database").Range("Data_Start").Offset(TargetRow, 1).Value = Txt_Date_Recvd
Sheets("Database").Range("Data_Start").Offset(TargetRow, 2).Value = Txt_Client_LAST_Name[COLOR=#008080][I] 'Last Name of Client[/I][/COLOR]
Sheets("Database").Range("Data_Start").Offset(TargetRow, 3).Value = Txt_Client_First_Name [COLOR=#008080][I]'First Name of Client[/I][/COLOR]
Sheets("Database").Range("Data_Start").Offset(TargetRow, 4).Value = Txt_Commercial_Name
Sheets("Database").Range("Data_Start").Offset(TargetRow, 5).Value = Txt_WorkOrder_Number
Sheets("Database").Range("Data_Start").Offset(TargetRow, 6).Value = Txt_Property_Street_Address
Sheets("Database").Range("Data_Start").Offset(TargetRow, 7).Value = Txt_Property_City
Sheets("Database").Range("Data_Start").Offset(TargetRow, 8).Value = Txt_Client_First_Name & " " & UCase(Txt_Client_LAST_Name)
  [COLOR=#008080][I]'COMBINES AUTOMATICALLY First and Last Name in COLUMN J FullName[/I][/COLOR]

Display More

Mumps · Mar 30th 2019

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?

DRJ4444 · Mar 30th 2019

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]

Mumps · Mar 31st 2019

Try:

Code

Sub FillBlankNames()
    Dim bottomJ As Long, rng As Range
    bottomJ = Range("J:J").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Sheets("2019_ClientDatabase")
        .ListObjects("tblClients").Range.AutoFilter Field:=9, Criteria1:="="
        For Each rng In .Range("J4:J" & bottomJ).SpecialCells(xlCellTypeVisible)
            rng = Range("B" & rng.Row)
        Next rng
        .Range("J3").AutoFilter
    End With
End Sub

Display More

DRJ4444 · Apr 6th 2019

Hey Mumps!

This looks like it SHOULD work.... maybe I'm not putting it in the correct location??? I've tried different ways, but I'm not getting it to work.

It sucks that I am a total novice at this! :smash:

Are you able to direct me a bit more specially as to how to place this code? Please!??? :poke:

Here's a link to the file via dropbox.

https://www.dropbox.com/s/lfu5…20needs%20Fixed.xlsm?dl=0

Ingo_Ingo · Apr 6th 2019

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

Mumps · Apr 6th 2019

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.

Mumps · Apr 7th 2019

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

Mumps · Apr 7th 2019

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

DRJ4444 · Apr 7th 2019

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?

Mumps · Apr 7th 2019

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

Code

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

with this code:

Code

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

DRJ4444 · Apr 13th 2019

[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]

DRJ4444 · Apr 14th 2019

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...

Code

Dim UserMessage As String


'begin ck if EDIT or ADD NEW Mode
If Sheets("Engine").Range("B4").Value = "NEW" Then  'in 'NEW' mode
    
    '''**'BEGINS VALIDATION CHECK: IF in 'add NEW' mode to prevent duplicate FULL NAME J Column entries**''
    If Application.WorksheetFunction.CountIf(Sheets("2019_ClientDatabase").Range("J3:J4000"), FullName) > 0 Then


    MsgBox "Client's Full Name already exists", 0, "Check"  'notify user of existing client
    Exit Sub 'exit the program routine
    
    End If  'ends validation check OF Duplicate FULLNAME (J Column)




    '''**'BEGINS VALIDATION CHECK: IF in 'add NEW' mode to prevent duplicate QBFileName B Column entries**''
    If Application.WorksheetFunction.CountIf(Sheets("2019_ClientDatabase").Range("B3:B4000"), QBFileName) > 0 Then


    MsgBox "QuickBooks File Name already exists", 0, "Check"  'notify user of existing client
    Exit Sub 'exit the program routine


    End If  'ends validation check




TargetRow = Sheets("Engine").Range("B3").Value + 1 'make variable equal to COUNTA formula on worksheet + 1
UserMessage = " has been added to the database" 'configure user message for add new entry


Else  'in EDIT Mode


TargetRow = Sheets("Engine").Range("B5").Value 'make variable equal to the value saved in the engine
UserMessage = "'s details have been edited" 'configure user message for edit entry


End If




MsgBox FullName & UserMessage, 0, "Entry Complete"

Display More

Mumps · Apr 14th 2019

Try deleting this line of code:

Code

FullName = Txt_Client_First_Name & " " & Txt_Client_LAST_Name

and inserting this line:

Code

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

below this code:

Code

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

DRJ4444 · Apr 14th 2019

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

FullName = Txt_Client_First_Name & " " & Txt_Client_LAST_Name

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

Code

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:

Mumps · Apr 15th 2019

You are most welcome.

Mumps · Apr 15th 2019

You are very welcome.

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

Participate now!

Share