Filling in blank cells with values from another workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-1578374%22%20slang%3D%22en-US%22%3EFilling%20in%20blank%20cells%20with%20values%20from%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578374%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%20I'm%20looking%20for%20help%20writing%20a%20macro%20which%20does%20the%20following%20but%20struggling%20with%20a%20few%20areas%2C%20I'd%26nbsp%3Blike%20to%20do%20the%20following%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20Identify%20all%20blank%20cells%20in%20a%20given%20range.%20All%20blank%20cells%20are%20completely%20randomly%20placed%2C%20not%20full%20columns%2C%20rows%20or%20blocks%20so%20Excel%20won't%20recognise%20the%20set%20of%20cells%20as%20a%20range.%20The%20workbook%20here%20is%20called%20%22NIR%22.%20I've%20got%20this%20%22if%22%20statement%20set%20up%20already%20because%20that%20was%20nice%20and%20easy%2C%20nothing%20in%20it%20yet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EIf%20IsEmpty(Range(%22C8%3AMX%22))%20%3D%20True%20Then%0A%0AEnd%20If%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Fill%20the%20blank%20values%20from%20the%20NIR%20workbook%20with%20values%20from%20a%20second%20workbook%20called%20%22VIS%22%20but%20this%20can%20only%20effect%20the%20blank%20cells%20in%20the%20NIR%20workbook%20NOT%20the%20filled%20cells.%20The%20values%20I%20want%20to%20paste%20in%20are%20in%20the%20same%20cells%20respectively%3B%20so%20if%20I%20have%20a%20blank%20cell%20A12%20in%20NIR%20I%20would%20fill%20it%20in%20with%20the%20value%20in%20cell%20A12%20in%20VIS.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1578374%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578569%22%20slang%3D%22en-US%22%3ERe%3A%20Filling%20in%20blank%20cells%20with%20values%20from%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578569%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754810%22%20target%3D%22_blank%22%3E%40Veraborn64%3C%2FA%3E%26nbsp%3BYou%20could%20use%20a%20macro%20similar%20to%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20FillTheGaps()%0A%20%20%20%20Dim%20sourceCell%20As%20Range%0A%20%20%20%20Dim%20NIR%20As%20Workbook%0A%20%20%20%20Dim%20VIS%20As%20Workbook%0A%20%20%20%20Set%20NIR%20%3D%20ThisWorkbook%20'Replace%20this%20with%20something%20like%20Workbooks(%22NIR.xlsx%22)%0A%20%20%20%20Set%20VIS%20%3D%20ThisWorkbook%20'Replace%20this%20with%20something%20like%20Workbooks(%22VIS.xlsx%22)%0A%20%20%20%20'Assuming%20the%20name%20of%20the%20worksheet%20is%20the%20same%0A%20%20%20%20%0A%20%20%20%20For%20Each%20sourceCell%20In%20NIR.Worksheets(%22Sheet1%22).Range(%22C8%3AM100%22).SpecialCells(xlCellTypeBlanks)%0A%20%20%20%20%20%20%20%20sourceCell.Value%20%3D%20VIS.Worksheets(%22Sheet1%22).Range(sourceCell.Address).Value%0A%20%20%20%20Next%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578578%22%20slang%3D%22de-DE%22%3ESubject%3A%20Filling%20in%20blank%20cells%20with%20values%20from%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578578%22%20slang%3D%22de-DE%22%3EIt%20would%20be%20helpful%20for%20all%20of%20us%20if%20you%20could%20add%20a%20file%20(without%20sensitive%20data).%20This%20way%20you%20can%20hope%20for%20an%20answer%20faster%20and%20we%20can%20offer%20you%20an%20answer%20faster%2C%20as%20far%20as%20possible.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Unfortunately%2C%20I%20can't%20do%20much%20with%20the%20term%20%22empty%20values%20from%20the%20NIR%20workbook%22.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578588%22%20slang%3D%22de-DE%22%3ERE%3A%20Filling%20in%20blank%20cells%20with%20values%20from%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578588%22%20slang%3D%22de-DE%22%3EHello%20Mr.%20Jan%20Karel%20Pieterse%2C%20I%20saw%20your%20contribution%20too%20late%2C%20after%20the%20translation%20was%20not%20so%20clear%20to%20me%20it%20was%20requested%20...%20but%20if%20it%20is%2C%20thank%20you%20from%20me%20too%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1578882%22%20slang%3D%22en-US%22%3ERe%3A%20Filling%20in%20blank%20cells%20with%20values%20from%20another%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1578882%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F754810%22%20target%3D%22_blank%22%3E%40Veraborn64%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20outlines%20an%20approach%20that%20load%20values%20to%20an%20from%20the%20workbook(s)%20as%20a%20block%20and%20sorts%20the%20data%20transfer%20between%20arrays%20held%20in%20memory.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EOption%20Explicit%0AOption%20Base%201%0A%0ASub%20transfer()%0ADim%20vSource%0ADim%20vDestination%0ADim%20i%20As%20Long%2C%20j%20As%20Long%0ADim%20v%0A'%20%20%20Load%20Source%20and%20Destination%20ranges%20into%20variant%20arrays%0A%20%20%20%20vSource%20%3D%20%5BSource%5D.Value%0A%20%20%20%20vDestination%20%3D%20%5BDestination%5D.Value%0A'%20%20%20Loop%20over%20elements%20of%20array%20copying%20values%20to%20empty%20locations%0A%20%20%20%20For%20i%20%3D%201%20To%20UBound(vDestination%2C%201)%0A%20%20%20%20%20%20%20%20For%20j%20%3D%201%20To%20UBound(vDestination%2C%202)%0A%20%20%20%20%20%20%20%20%20%20%20%20v%20%3D%20vDestination(i%2C%20j)%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20v%20%3D%20vbEmpty%20Then%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20vDestination(i%2C%20j)%20%3D%20vSource(i%2C%20j)%0A%20%20%20%20%20%20%20%20Next%20j%0A%20%20%20%20Next%20i%0A%20'%20%20Return%20augmented%20array%20to%20destination%20range%0A%20%20%20%20%5BDestination%5D.Value%20%3D%20vDestination%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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!

 

4 Replies

@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

 

Highlighted
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)
Highlighted
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
Highlighted

@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