Aug 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!
Aug 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
Aug 10 2020 08:26 AM
Aug 10 2020 08:31 AM
Aug 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