CODEWITHSUNDEEP
excelvbaexcel-formula
FBeckenbauer4
FBeckenbauer4

Reputation: 27

Find All Empty Cells in Column and Paste Formula in them

I'm close to what i want to do but not sure if I'll have to change method completely to get this done.

The below code works almost completely. I want to find all empty cells in range: C2:C120 and enter a formula from another worksheet: Worksheets("Sheet2").Range("F57").

It finds empty cells but it copies the text in the F57 cell, which is #N/A at the moment, not the formula. The formula is =VLOOKUP(D57,'[Example.xlsx]Sheet1'!$A$2:$D$37,2) but I can't enter it directly into the code as it will always look for D57, not dynamically.

Any help is hugely appreciated, hopefully it's a simple fix.

Private Sub CommandButton3_Click()

Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Sheet1")

   For Each rng In ws.Range("C2:C120")
   If IsEmpty(rng) Then
   rng.Formula = Worksheets("Sheet2").Range("F57")

   End If

   Next

End Sub

Upvotes: 0

Views: 128

Answers (2)

VBasic2008
VBasic2008

Reputation: 54777

Update Data From Different Workbook

Adjust the constants in updateCustomers.

The Code

Sheet1 (or wherever you have CommandButton3)

Option Explicit

Private Sub CommandButton3_Click()
    updateCustomers
End Sub

Module1

Sub updateCustomers()
    ' Source
    Const wbsName As String = "Example.xlsx"
    Const srcName As String = "Sheet1"
    Const srcAddr As String = "A2:B37"
    
    ' Target
    Const tgtName As String = "Sheet1"
    Const LookupCol As String = "A"
    Const tgtAddr As String = "C2:C120"
    
    ' Ranges
    Dim src As Range
    Set src = Workbooks(wbsName).Worksheets(srcName).Range(srcAddr)
    Dim tgt As Range
    Set tgt = ThisWorkbook.Worksheets(tgtName).Range(tgtAddr)

    ' The Loop
    Dim cel As Range
    For Each cel In tgt.Cells
        If IsEmpty(cel) Then
            On Error Resume Next
            cel.Value = WorksheetFunction _
              .VLookup(tgt.Parent.Cells(cel.Row, LookupCol).Value, src, 2, False)
            On Error GoTo 0
        End If
    Next

    MsgBox "Customers updated.", vbInformation, "Success"

End Sub

Upvotes: 1

Samuel Everson
Samuel Everson

Reputation: 2102

I couldn't find an answer for this specifically though I'm sure i've come accross one before.

If I understand correctly, the following should work.

In your loop you can reference the rng row number in your formula.

You could use (note, not tested): 

For Each rng In ws.Range("C2:C120")
    If IsEmpty(rng) Then
        rng.Formula = "=VLOOKUP(D" & rng.Row & ",'[Example.xlsx]Sheet1'!$A$2:$D$37,2)"
    End If
Next rng

This uses the Row property of the Range object, which is the row number of whatever cell you are accessing in your loop in each iteration, and uses it as the row number for your D57 part of your formula (per your posted formula).

Upvotes: 1

Related Questions