Help with VBA to insert formulas if cell is blank

D

DeanRobinson

New Member
Joined
Sep 1, 2011
Messages
35
Hi all, was wondering if anyone could help, what id like to happen is id have a button on a sheet then once click it will loop down a column and if the cell is blank it would insert a formula, this would be done on four columns, then once its got the values paste special column CK back to values only.

these are the columns and the formulas needed

in Column CK

Code: 
=IF(ISBLANK(R2),"",VLOOKUP(R2,'Daily Repulls'!$R$2:$V$10000,5,0))

in Column CO

Code: 
=IF(CJ2="CANX","CANX",IF(CJ2="DROP CABLE","ND",IF(CJ2="CONSTRUCTION","CON",IF(CJ2="NON SERVE","CANX",IF(CJ2="COMP","CP",IF(CJ2="MDU","CP",IF(CJ2="REPULL CP","REP","")))))))&IF(CJ2="FI CON","FI","")

in Column CP

Code: 
=IF(ISBLANK(A2),"",A2+7)

in Column CQ

Code: 
=IF(OR(C2=314,C2=371,C2=415,C2=512,C2=516,C2=518),"S",IF(C2=544,"N",IF(C2=511,"N",IF(C2=577,"NW",""))))

Any help would be greatly appricaited.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
DeanRobinson said:
Hi all, was wondering if anyone could help, what id like to happen is id have a button on a sheet then once click it will loop down a column and if the cell is blank it would insert a formula, this would be done on four columns, then once its got the values paste special column CK back to values only.

these are the columns and the formulas needed

in Column CK

Code: 
=IF(ISBLANK(R2),"",VLOOKUP(R2,'Daily Repulls'!$R$2:$V$10000,5,0))

in Column CO

Code: 
=IF(CJ2="CANX","CANX",IF(CJ2="DROP CABLE","ND",IF(CJ2="CONSTRUCTION","CON",IF(CJ2="NON SERVE","CANX",IF(CJ2="COMP","CP",IF(CJ2="MDU","CP",IF(CJ2="REPULL CP","REP","")))))))&IF(CJ2="FI CON","FI","")

in Column CP

Code: 
=IF(ISBLANK(A2),"",A2+7)

in Column CQ

Code: 
=IF(OR(C2=314,C2=371,C2=415,C2=512,C2=516,C2=518),"S",IF(C2=544,"N",IF(C2=511,"N",IF(C2=577,"NW",""))))

Any help would be greatly appricaited.
Click to expand...

Maybe something like this?

Code: 
Sub DeanRobinson()
Dim lr As Long
Dim rCell As Range

lr = Cells(Rows.Count, 1).End(3).Row

For Each rCell In Range("CK2:CK" & lr)

    If rCell.Value = "" Then

        rCell.Formula = "=IF(ISBLANK(R2),"",VLOOKUP(R2,'Daily Repulls'!$R$2:$V$10000,5,0))"
        rCell.Value = rCell.Value
        
    End If
    
Next rCell
    
End Sub
 
Upvote 0
Run-time error 1004

Ive just tried that and i get a application-defined or object-defined error.

it then goes back in the vba and highlights line

rCell.Formula = "=IF(ISBLANK(R2),"",VLOOKUP(R2,'Daily Repulls'!$R$1:$V$10000,5,0))"
 
Upvote 0
DeanRobinson said:
Run-time error 1004

Ive just tried that and i get a application-defined or object-defined error.

it then goes back in the vba and highlights line

rCell.Formula = "=IF(ISBLANK(R2),"",VLOOKUP(R2,'Daily Repulls'!$R$1:$V$10000,5,0))"
Click to expand...

Change that line to read (note the double quotations in the formula):

rCell.Formula = "=IF(ISBLANK(R2),"""",VLOOKUP(R2,'Daily Repulls'!$R$1:$V$10000,5,0))"
 
Last edited:
Upvote 0
DeanRobinson said:
Run-time error 1004

Ive just tried that and i get a application-defined or object-defined error.

it then goes back in the vba and highlights line

rCell.Formula = "=IF(ISBLANK(R2),"",VLOOKUP(R2,'Daily Repulls'!$R$1:$V$10000,5,0))"
Click to expand...

Dean:

Also in order to get the formula to feed properly you have to modify them: See an example in the revised code.

Code: 
Sub DeanRobinson3()
Dim lr As Long
Dim rCell As Range

lr = Cells(Rows.Count, 1).End(3).Row

For Each rCell In Range("CK2:CK" & lr)

    If rCell.Value = "" Then

        rCell.Formula = "=IF(ISBLANK(R" & rCell.Row & "),"""",VLOOKUP(R" & rCell.Row & ",'Daily Repulls'!$R$2:$V$10000,5,0))"
        rCell.Value = rCell.Value
        
    End If
    
Next rCell
    
End Sub
 
Upvote 0
Thanks John for the help, ive now got a finished code which has evolved from the orginal request but now does everything i wanted

Code: 
Sub Button1_Click()
Application.ScreenUpdating = 0
Call ck
With Sheets("Master Sheet")
    With .Range(.[a2], .Cells(Rows.Count, "a").End(xlUp)).Offset(, 91)
        .Value = "=IF(CI2=""CANX"",""CANX"",IF(CI2=""DROP CABLE"",""ND"",IF(CI2=""CONSTRUCTION"",""CON"",IF(CI2=""NON SERVE"",""CANX"",IF(CI2=""COMP"",""CP"",IF(CI2=""MDU"",""CP"",IF(CI2=""REPULL CP"",""REP"","""")))))))&IF(CI2=""FI CON"",""FI"","""")"
    End With
    With .Range(.[a2], .Cells(Rows.Count, "a").End(xlUp)).Offset(, 92)
        .Value = "=IF(ISBLANK(A2),"""",A2+7)"
    End With
    With .Range(.[c2], .Cells(Rows.Count, "c").End(xlUp)).Offset(, 91)
        
        .Value = "=IF(OR(C2=314,C2=371,C2=415,C2=512,C2=516,C2=518),""S"",IF(C2=544,""N"",IF(C2=511,""N"",IF(C2=577,""NW"",""""))))"
        
    End With
End With
Application.ScreenUpdating = 1
End Sub
Private Sub ck()
Dim a As New Collection, master As Worksheet, daily As Worksheet, d, x, y, i As Long, n As Long, j As Long
Set master = Sheets("Master Sheet")
Set daily = Sheets("Daily Repulls")
With daily
    d = Intersect(.UsedRange, .[r:v])
End With
With master
    .AutoFilterMode = 0
    With .Range(.[r2], .Cells(Rows.Count, "r").End(xlUp))
        x = .Value
        y = .Offset(, 70).Value
    End With
        On Error Resume Next
        j = 1
        For i = 2 To UBound(d)
            If d(i, 1) <> "" Then
                j = j + 1
                a.Add j, d(i, 1)
                If Err.Number <> 0 Then
                    j = j - 1
                    Err.Clear
                End If
            End If
        Next
        Err.Clear
        For n = 1 To UBound(x)
            If x(n, 1) <> "" Then
                If y(n, 1) = "" Then
                    a.Add 0, x(n, 1)
                    If Err.Number <> 0 Then
                        y(n, 1) = d(a.Item(x(n, 1)), 5)
                        Err.Clear
                    End If
                End If
            End If
        Next
        .[cj2].Resize(n, 1).Value = y
         .Rows(1).AutoFilter
        
        End With
 
 End Sub
 
Upvote 0
DeanRobinson said:
Thanks John for the help, ive now got a finished code which has evolved from the orginal request but now does everything i wanted

Code: 
Sub Button1_Click()
Application.ScreenUpdating = 0
Call ck
With Sheets("Master Sheet")
    With .Range(.[a2], .Cells(Rows.Count, "a").End(xlUp)).Offset(, 91)
        .Value = "=IF(CI2=""CANX"",""CANX"",IF(CI2=""DROP CABLE"",""ND"",IF(CI2=""CONSTRUCTION"",""CON"",IF(CI2=""NON SERVE"",""CANX"",IF(CI2=""COMP"",""CP"",IF(CI2=""MDU"",""CP"",IF(CI2=""REPULL CP"",""REP"","""")))))))&IF(CI2=""FI CON"",""FI"","""")"
    End With
    With .Range(.[a2], .Cells(Rows.Count, "a").End(xlUp)).Offset(, 92)
        .Value = "=IF(ISBLANK(A2),"""",A2+7)"
    End With
    With .Range(.[c2], .Cells(Rows.Count, "c").End(xlUp)).Offset(, 91)
        
        .Value = "=IF(OR(C2=314,C2=371,C2=415,C2=512,C2=516,C2=518),""S"",IF(C2=544,""N"",IF(C2=511,""N"",IF(C2=577,""NW"",""""))))"
        
    End With
End With
Application.ScreenUpdating = 1
End Sub
Private Sub ck()
Dim a As New Collection, master As Worksheet, daily As Worksheet, d, x, y, i As Long, n As Long, j As Long
Set master = Sheets("Master Sheet")
Set daily = Sheets("Daily Repulls")
With daily
    d = Intersect(.UsedRange, .[r:v])
End With
With master
    .AutoFilterMode = 0
    With .Range(.[r2], .Cells(Rows.Count, "r").End(xlUp))
        x = .Value
        y = .Offset(, 70).Value
    End With
        On Error Resume Next
        j = 1
        For i = 2 To UBound(d)
            If d(i, 1) <> "" Then
                j = j + 1
                a.Add j, d(i, 1)
                If Err.Number <> 0 Then
                    j = j - 1
                    Err.Clear
                End If
            End If
        Next
        Err.Clear
        For n = 1 To UBound(x)
            If x(n, 1) <> "" Then
                If y(n, 1) = "" Then
                    a.Add 0, x(n, 1)
                    If Err.Number <> 0 Then
                        y(n, 1) = d(a.Item(x(n, 1)), 5)
                        Err.Clear
                    End If
                End If
            End If
        Next
        .[cj2].Resize(n, 1).Value = y
         .Rows(1).AutoFilter
        
        End With
 
 End Sub
Click to expand...

I'm impressed. The code you came up with looks good.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Find the missing numbers from the range of numbers and filling the missing number in the cells below the last row
Replies
6
Views
603
sekar
S
J
  • Question Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
436
johnnyL
J
A
  • Solved
Lookup PAAF ID based on Worker ID and date range
Replies
3
Views
127
Fluff
Fluff
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
299
Peter_SSs
Peter_SSs
W
  • Question Question
Sort data with calculated fields gives unexpected results
Replies
4
Views
505
wpryan
W

Forum statistics

Threads
1,224,609
Messages
6,179,882
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top