First three values from cell + other cell data VBA Excel

Question

I have two tables, where I need to get data from one to another with VBA. I have a code to import data and put them in column ranges what I need, but I am stuck on formating them:

1. I need to check if date columns cell is empty and if it is, than put date from other cell

   If IsEmpty(oldtable.Range("L3", "L10").Value) Then

   newtable.Range("J3", "J10").Value = newtable.Rang("E3", "E10").Value

   Else newtable.Range("J3", "J10").Value = oldtable.Rang("L3", "E10").Value

   End if

2. Need to get first 3 string from number + value from cell in same range

   newtable.Range("O3", "O10").Value = Mid(newtable.Range("M3", "10").Value,1,3)&newtable.Range("N3", "N10").Value

Code is not working for me. Thank you for support!

fullcode:

Dim filter As String
Dim caption As String
Dim file As String
Dim oldtable As Workbook
Dim newtable As Workbook

Range("A3:R10").Select
Selection.ClearContents

Set newtable = Application.ActiveWorkbook

filter = "Text files (C:\Excel\file.xlsx),C:\Excel\file.xlsx"
caption = "Please Select an input file "
GREMPG1 = Application.GetOpenFilename(filter, , caption)

oldtable = Application.Workbooks.Open(GREMPG1)

Dim wsheet_new1 As Worksheet
Set wsheet_new1 = newtable.Worksheets(1)
Set wsheet_new2 = newtable.Worksheets(2)

Dim wsheet_old As Worksheet
Set wsheet_old = oldtable.Worksheets(1)

'This is OK
wsheet_new1.Range("C3", "C11").Value = wsheet_new1.Range("C2").Value 

'This is OK
wsheet_new1.Range("D3", "D11").Value = Application.WorksheetFunction.VLookup(wsheet_old.Range("E2", "E10"), wsheet_new2.Range("A1:B16").Value, 2, False)

'Empty values stay empty
    If IsEmpty(wsheet_old.Range("L3", "L11").Value) Then
        wsheet_new1.Range("J3", "J11").Value = wsheet_new1.Range("E3", "E11").Value
    Else
        wsheet_new1.Range("J3", "J11").Value = wsheet_old.Range("L2", "L10").Value
    End If

GREMPG1_wb.Close


End Sub

Answer 1

The Help for the IsEmpty function states:

IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False.False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

Because you are passing more than one cell ("L3", "L11") the IsEmpty functions always returns False. The simplest answer is to write a function that takes a Range and tests every cell and returns True/False. Here's the function:

Private Function RangeIsEmpty(ByRef theRange As Range) As Boolean

Dim cell As Range
Dim result As Boolean

    result = True
    For Each cell In theRange.Cells
        If Not IsEmpty(cell) Then
            result = False
            Exit For
        End If
    Next cell

    RangeIsEmpty = result

End Function

Copy the function into the same module as your code. Then change this line:

If IsEmpty(wsheet_old.Range("L3", "L11").Value) Then

To:

If RangeIsEmpty(wsheet_old.Range("L3", "L11").Value) Then