[VBA]: set cell equal to a cell in another column(same row) if empty

[fr0z3nfyr]

Hello Ex(cel)perts. Im working on a macro that looks at column headers to perform some actions on the corresponding cells. I'm almost done with the VBA except for one thing that's making me bang my head on walls, I want to look at the cell to check if it's length is less than 8 and substitute from another cell if true. Both the cells are in same row but order of columns may change in every sheet/book, that is why I look for a column using its header.

My code so far looks like this:

Function LastString(TS As String) As String
Dim Str As Variant
Str = Split(TS, " ")
    If UBound(Str) < 2 Then
       LastString = " "
       Else
       LastString = Str(UBound(Str))
    End If
End Function

Sub createImportFile()
Dim x As String
Dim rngMyrange As Range
Dim lookFor As Long
Dim temp As String
Dim cell As Range
Dim bScrnUpd As Boolean
bScrnUpd = Application.ScreenUpdating
Application.ScreenUpdating = False

'==================================================================================================================
On Error Resume Next
'Creates Publication Number column
    Do
        lookFor = WorksheetFunction.Match("XPN", Rows("1:1"), 0)
        Set rngMyrange = ActiveSheet.Columns(lookFor)
            On Error GoTo 0
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    Loop
        With rngMyrange
            For Each cell In rngMyrange.SpecialCells(xlCellTypeConstants)
                cell = WorksheetFunction.Trim(cell)
            Next cell
        End With
    rngMyrange.Cells(1, 1) = "publicationnumber"
'==================================================================================================================
'Creates Publication Date column
    Do
        lookFor = WorksheetFunction.Match("PN", Rows("1:1"), 0)
        Set rngMyrange = ActiveSheet.Columns(lookFor)
            On Error GoTo 0
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    Loop
        With rngMyrange
            For Each cell In rngMyrange.SpecialCells(xlCellTypeConstants)
                temp = LastString(WorksheetFunction.Trim(Left(cell, InStr(1, cell, " ["))))
                cell = Left(temp, 4) & "-" & Mid(temp, 5, 2) & "-" & Right(temp, 2)
            Next cell
        End With
    rngMyrange.Cells(1, 1) = "publicationdate"
'==================================================================================================================
'Creates Title column
    Do
        lookFor = WorksheetFunction.Match("TI", Rows("1:1"), 0)
        Set rngMyrange = ActiveSheet.Columns(lookFor)
            On Error GoTo 0
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    rngMyrange.Cells(1, 1) = "title"
'==================================================================================================================
'Creates IPC column
    Do
        lookFor = WorksheetFunction.Match("IC", Rows("1:1"), 0)
        Set rngMyrange = ActiveSheet.Columns(lookFor)
            On Error GoTo 0
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    rngMyrange.Cells(1, 1) = "ipcsubclass"
'==================================================================================================================
'Creates Family ID column
    Do
        lookFor = WorksheetFunction.Match("FAN", Rows("1:1"), 0)
        Set rngMyrange = ActiveSheet.Columns(lookFor)
            On Error GoTo 0
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    rngMyrange.Cells(1, 1) = "fid"
'==================================================================================================================
'Creates Application Date column
    Do
        lookFor = WorksheetFunction.Match("AP", Rows("1:1"), 0)
        Set rngMyrange = ActiveSheet.Columns(lookFor)
            On Error GoTo 0
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    Loop
        With rngMyrange
            For Each cell In rngMyrange.SpecialCells(xlCellTypeConstants)
                temp = LastString(WorksheetFunction.Trim(Left(cell, InStr(1, cell, " ["))))
                cell = Left(temp, 4) & "-" & Mid(temp, 5, 2) & "-" & Right(temp, 2)
            Next cell
        End With
    rngMyrange.Cells(1, 1) = "applicationdate"
'==================================================================================================================
'Creates Priority Date column
    Do
        lookFor = WorksheetFunction.Match("PRD", Rows("1:1"), 0)
        Set rngMyrange = ActiveSheet.Columns(lookFor)
            On Error GoTo 0
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    Loop
        With rngMyrange
            For Each cell In rngMyrange.SpecialCells(xlCellTypeConstants)
                'cannot check the format,  default formatted as General
                If Len(cell) < 8 Then                      'Sometimes the value can be yyyymmdd
                cell = "How can I substitute the value from PRD1?"
            Next cell
        End With
    rngMyrange.Cells(1, 1) = "prioritydate"
'==================================================================================================================
'Creates Abstract column
    Do
        lookFor = WorksheetFunction.Match("AB", Rows("1:1"), 0)
        Set rngMyrange = ActiveSheet.Columns(lookFor)
            On Error GoTo 0
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    rngMyrange.Cells(1, 1) = "abstract"
'==================================================================================================================
'Creates CPC class column
    Do
        lookFor = WorksheetFunction.Match("CPC", Rows("1:1"), 0)
        Set rngMyrange = ActiveSheet.Columns(lookFor)
            On Error GoTo 0
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    rngMyrange.Cells(1, 1) = "cpcclass"
'==================================================================================================================
End Sub

Please pardon me if my code is inconsistent, I'm an amatuer. The code may look incomplete, because i have picked a part of the code that is relevant, macro is working fine without errors.

Please look at the section that says "Creates Priority Date column"(3rd from the end) I need help there.

In above sample,
1. For row 2, I want PRD column to retain its value (because it not empty)
2. For row 3, I want PRD column should get value from PRD1

I hope I have supplied sufficient information, please ask if you need to know more.

 

[fr0z3nfyr]

Just wanted to share an update here that I have also posted the same question on another forum due to the urgency I'm in. I did try to add a link back but I'm not sure if it worked because i can't see any change in the way my OP looks. Thanks to @HaHoBe for pointing me to adding linkbacks and explaining why it's necessary.

Please help me.

 

[fr0z3nfyr]

UPDATE: tried the solutionas suggested here but didn't work for me. The situation is more or less same.

 

[fr0z3nfyr]

The problem was solved by @Holger here. I think I should close this thread as solved now so that others don't put their time on this but unfortunately, I can't find an option to do just that, may be a moderator can do that for me when someone bumps in.