08-10-2020 07:22 AM
08-10-2020 07:22 AM
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!
08-10-2020 08:25 AM
@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
08-10-2020 08:26 AM
08-10-2020 08:31 AM
08-10-2020 10:24 AM
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