Forum Discussion

Veraborn64's avatar
Veraborn64
Copper Contributor
Aug 10, 2020

Filling in blank cells with values from another workbook

Hello all, I'm looking for help writing a macro which does the following but struggling with a few areas, I'd like to do the following: 

 

1) Identify all blank cells in a given range. All blank cells are completely randomly placed, not full columns, rows or blocks so Excel won't recognise the set of cells as a range. The workbook here is called "NIR". I've got this "if" statement set up already because that was nice and easy, nothing in it yet.

 

If IsEmpty(Range("C8:MX")) = True Then

End If

 

 

2) Fill the blank values from the NIR workbook with values from a second workbook called "VIS" but this can only effect the blank cells in the NIR workbook NOT the filled cells. The values I want to paste in are in the same cells respectively; so if I have a blank cell A12 in NIR I would fill it in with the value in cell A12 in VIS. 

 

Thanks in advance!

 

excel
Macros and VBA

4 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 10, 2020

    Veraborn64 

    This outlines an approach that load values to an from the workbook(s) as a block and sorts the data transfer between arrays held in memory.

    Option Explicit
Option Base 1

Sub transfer()
Dim vSource
Dim vDestination
Dim i As Long, j As Long
Dim v
'   Load Source and Destination ranges into variant arrays
    vSource = [Source].Value
    vDestination = [Destination].Value
'   Loop over elements of array copying values to empty locations
    For i = 1 To UBound(vDestination, 1)
        For j = 1 To UBound(vDestination, 2)
            v = vDestination(i, j)
            If v = vbEmpty Then _
                vDestination(i, j) = vSource(i, j)
        Next j
    Next i
 '  Return augmented array to destination range
    [Destination].Value = vDestination
End Sub
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Aug 10, 2020
    Hallo Mr. Jan Karel Pieterse, I saw your contribution too late, after the translation was not so clear to me it was requested ... but if it is, thank you from me too 🙂
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Aug 10, 2020
    It would be helpful for all of us if you could add a file (without sensitive data). This way you can hope for an answer faster and we can offer you an answer faster, as far as possible.

    Unfortunately, I can't do much with the term "empty values from the NIR workbook".

    Nikolino
    I know I don't know anything (Socrates)
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Aug 10, 2020

    Veraborn64 You could use a macro similar to this:

    Sub FillTheGaps()
    Dim sourceCell As Range
    Dim NIR As Workbook
    Dim VIS As Workbook
    Set NIR = ThisWorkbook 'Replace this with something like Workbooks("NIR.xlsx")
    Set VIS = ThisWorkbook 'Replace this with something like Workbooks("VIS.xlsx")
    'Assuming the name of the worksheet is the same
    
    For Each sourceCell In NIR.Worksheets("Sheet1").Range("C8:M100").SpecialCells(xlCellTypeBlanks)
        sourceCell.Value = VIS.Worksheets("Sheet1").Range(sourceCell.Address).Value
    Next

End Sub

     

Resources