Getting error in output using formula in VBA

Question

I am using below VBA macro that looks in D column cell values and equates/ copy pastes in Col A those values that are available in Col D but not in A.

After using below formula, I however get missing values in Col A but additionally 0's in Col A. Please help me to correct this error. Or, is there any way using vba to get desired results. Thank you

Sub fillIntheBlank()
    Set WkRg = Worksheets("sheet1").UsedRange
    Set WkRg = Intersect(WkRg, WkRg.Offset(3, 0))

    If Application.CountBlank(WkRg.Columns(1)) <> 0 Then
        With WkRg.Columns(1).SpecialCells(xlCellTypeBlanks)
                .Formula = "=RC[3]"
        End With
    End If
End Sub

trying to compare column A and D and put missing values in Col A that are available in Col D thats why use this code to put eqaute value in Blank cells of Col A from column D — user12227875
– user12227875, Commented Nov 20, 2019 at 12:44
putting 0's in the cells of Col A Or 0's in the blank cells of Col A at the end — user12227875
– user12227875, Commented Nov 20, 2019 at 13:08

FaneDuru · Accepted Answer · 2019-11-20 13:38:17Z

1

Try this:

Dim WkRg as Range

With WkRg.Columns(1).SpecialCells(xlCellTypeBlanks)
       .Formula = "=if(RC[3]="""","""",RC[3])"
End With

Otherwise, it returns 0 instead of the nullString (nothing) if nothing is found on column D:D. But, I must confess I do not understand the meaning/need of

Set WkRg = Intersect(WkRg, WkRg.Offset(3, 0))

edited Nov 20, 2019 at 13:38

answered Nov 20, 2019 at 13:12

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Davesexcel Over a year ago

Upp'd one so you can get closer to commenting

Davesexcel · Accepted Answer · 2019-11-20 13:30:02Z

0

I believe you don't want 0's if d is blank

Sub Button1_Click()
    On Error GoTo er
    Columns("A:A").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[3]<>"""",RC[3],"""")"
er:     MsgBox "No Blanks Found"
End Sub

You can also loop through the range and make the blank cell equal the d cell.

Sub Button2_Click()
    Dim rng As Range, c As Range
    On Error GoTo er
    Set rng = Range("A:A").SpecialCells(xlCellTypeBlanks)
    For Each c In rng.Cells
        c = c.Offset(, 3)
    Next
er:
End Sub

edited Nov 20, 2019 at 13:30

answered Nov 20, 2019 at 13:17

Davesexcel

6,9922 gold badges31 silver badges44 bronze badges

Collectives™ on Stack Overflow

Getting error in output using formula in VBA

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related