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

JDR444 · Mar 30, 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.

Rich (BB code):

Option Explicit

Private Sub CmdButton_CONTINUE1_Click() 

Dim TargetRow As Integer   
Dim FullName As String   'Variable for FULL NAME = CELL RANGE J4:J4000
Dim QBFileName As String   'Variable Quick Books File Name = CELL RANGE B4:B4000
Dim UserMessage As String
   
FullName = Txt_Client_First_Name & " " & Txt_Client_LAST_Name  'Variable configured: shows full name
QBFileName = Txt_QB_File_Name
 
'begin check if EDIT or ADD New Entry Mode
If Sheets("Engine").Range("B4").Value = "NEW" Then  'ADD New Entry Mode
   
    '''**'BEGINS VALIDATION CHECK: IF in ''ADD New Entry Mode" 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" 
    Exit Sub 
   
    End If  'ends validation check OF Duplicate FULLNAME (J Column)
 
 
    '''**'BEGINS VALIDATION CHECK: IF in "ADD New Entry 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 
 
    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
'end check if in 'edit' or 'add new mode'
 
 
'''****BEGIN INPUT DATA INTO DATABASE****'''
 
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 0).Value = Txt_QB_File_Name 'QuickBooks File Name
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 1).Value = Txt_Date_Recvd
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 2).Value = Txt_Client_LAST_Name 'Last Name of Client
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 3).Value = Txt_Client_First_Name 'First Name of Client
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 4).Value = Txt_Commercial_Name 
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 5).Value = Txt_WorkOrder_Number 
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 6).Value = Txt_Property_Street_Address 
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 7).Value = Txt_Property_City 
Sheets("2019_ClientDatabase").Range("Data_Start").Offset(TargetRow, 8).Value = Txt_Client_First_Name & " " & UCase(Txt_Client_LAST_Name)
  'COMBINES AUTOMATICALLY First and Last Name in COLUMN J FullName

Posted:
https://www.ozgrid.com/forum/forum/...ell-is-blank-fill-with-other-cells-text-data

shg · Mar 30, 2019

I didn't read your code, but in response to your question,

Code:

  On Error Resume Next
  With Range("J4:J4000")
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=rc2"
    .Value = .Value
  End With

JDR444 · Apr 6, 2019

Hello shg,

I appreciate your feedback, however I'm not clear as to how to apply this code.

I've tried several ways, but I'm not coming up with the solution.

I am still a beginner as I mentioned, is it possible for you to review my code and tell me how to apply your recommendation?

James006 · Apr 6, 2019

Hello,

Seems Mumps has already provided you with an answer ...

https://www.ozgrid.com/forum/forum/...ell-is-blank-fill-with-other-cells-text-data

JDR444 · Apr 6, 2019

Hello James, And thanks!

Yes, Mumps did provide me with an answer... AND it looked like it would work too!!

BUT...either I'm putting it in the wrong place within the existing code, or ???
I'm not sure.

I asked him for help in where I should put the code and I included the link to dropbox with the file, so maybe he (or some other EXPERT) can help determine how that code can work.

File via dropbox.
https://www.dropbox.com/s/lfu50347a31lslz/TEST--full name blank needs Fixed.xlsm?dl=0

JDR444 · Apr 13, 2019

Mumps is the BOMB!!

He provided the answer that works!!!!!!!!!!!!!!!

https://www.ozgrid.com/forum/forum/...ell-is-blank-fill-with-other-cells-text-data

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

JDR444

New Member

Excel Facts

shg

MrExcel MVP

JDR444

New Member

James006

Well-known Member

JDR444

New Member

JDR444

New Member

Similar threads

Forum statistics

Share this page

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

JDR444

New Member

Excel Facts

shg

MrExcel MVP

JDR444

New Member

James006

Well-known Member

JDR444

New Member

JDR444

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock