using VBA in Excel I would like to replace only blank cells with a formula without selecting all the time - Stack Overflow

admin管理员组

文章数量:1123593

I have searched and have found various answers but I am concerned that I may code inefficiently based on the use of "select".

At the moment, I have code (below) that clears out a range of cells, adds in some formula so that the cells are populated as needed by the driving data and then replaces the formula with the results.

I want to only clear some of the columns and adding the formula only to cells that are currently blank.

'clean out the entries in F:F
Worksheets("Bar Details").Range("f3:f425").ClearContents

'clean out row numbers and styles
Worksheets("Bar Details").Range("g3:h330").ClearContents

'clean out above / below
Worksheets("Bar Details").Range("j3:k302").ClearContents

'enter a "y" against any row with a number in the input data
With Worksheets("Bar Details").Range("d3:d302")
    .FormulaR1C1 = "=if(iserror(vlookup(rc1,Auto_Calc_rows,1,false)),"""",""y"")"
    .Calculate
End With
'Worksheets("Bar Details").Range("d3:d302").Value = Worksheets("Bar Details").Range("d3:d302").Value

'enter the names into E:E
With Worksheets("Bar Details").Range("e3:e302")
    .FormulaR1C1 = "=if(rc4="""","""",trim(vlookup(rc1,task_details_lookup,2,false)))"
    .Calculate
End With
'Worksheets("Bar Details").Range("e3:e302").Value = Worksheets("Bar Details").Range("e3:e302").Value ' suspect

'enter name in F:F unless it is a milestone
With Worksheets("Bar Details").Range("f3:f302")
    .FormulaR1C1 = "=if(rc4="""","""",IF(VLOOKUP(rc1,MS_Data,9,FALSE)=""y"","""",rc5))"  'IF(VLOOKUP(rc1,MS_Data,9,FALSE)=""y"","""",rc5)
    .Calculate
End With

'enter vlooklup against original data to pull the rows across
With Worksheets("Bar Details").Range("g3:g302") 
    .FormulaR1C1 = "=if(rc4="""","""",vlookup(rc1,Auto_Calc_rows,7,false))"
    .Calculate
End With

'wipe out all the calculations
Worksheets("Bar Details").Range("d3:g302").Value = Worksheets("Bar Details").Range("d3:g302").Value

I have found the following but am concerned by the use of selection which tends to slow things down: VBA - Replace blank cells

Updated: I have got the following to work, however is there a better or faster way?

Sub test_blank_replacement()

TestOverWrite = MsgBox("Do you want to replace all existing data?" & vbCr & " " & vbCr & _
    "Please select 'No' if you have previously used similar data" & vbCr & "Selecting 'Yes' will replace all data" & vbCr & "  " _
    , vbQuestion + vbYesNo + vbDefaultButton1, "Options")

On Error Resume Next
With ActiveSheet.Range("A1:A10")
    If TestOverWrite = vbYes Then
        .ClearContents
        .FormulaR1C1 = "=RC4"
        .Calculate
        .Value = .Value
    Else
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC3"
        .Calculate
        .Value = .Value
    End If
End With

MsgBox "all done"
End Sub

Many thanks in advance.

I have searched and have found various answers but I am concerned that I may code inefficiently based on the use of "select".

At the moment, I have code (below) that clears out a range of cells, adds in some formula so that the cells are populated as needed by the driving data and then replaces the formula with the results.

I want to only clear some of the columns and adding the formula only to cells that are currently blank.

'clean out the entries in F:F
Worksheets("Bar Details").Range("f3:f425").ClearContents

'clean out row numbers and styles
Worksheets("Bar Details").Range("g3:h330").ClearContents

'clean out above / below
Worksheets("Bar Details").Range("j3:k302").ClearContents

'enter a "y" against any row with a number in the input data
With Worksheets("Bar Details").Range("d3:d302")
    .FormulaR1C1 = "=if(iserror(vlookup(rc1,Auto_Calc_rows,1,false)),"""",""y"")"
    .Calculate
End With
'Worksheets("Bar Details").Range("d3:d302").Value = Worksheets("Bar Details").Range("d3:d302").Value

'enter the names into E:E
With Worksheets("Bar Details").Range("e3:e302")
    .FormulaR1C1 = "=if(rc4="""","""",trim(vlookup(rc1,task_details_lookup,2,false)))"
    .Calculate
End With
'Worksheets("Bar Details").Range("e3:e302").Value = Worksheets("Bar Details").Range("e3:e302").Value ' suspect

'enter name in F:F unless it is a milestone
With Worksheets("Bar Details").Range("f3:f302")
    .FormulaR1C1 = "=if(rc4="""","""",IF(VLOOKUP(rc1,MS_Data,9,FALSE)=""y"","""",rc5))"  'IF(VLOOKUP(rc1,MS_Data,9,FALSE)=""y"","""",rc5)
    .Calculate
End With

'enter vlooklup against original data to pull the rows across
With Worksheets("Bar Details").Range("g3:g302") 
    .FormulaR1C1 = "=if(rc4="""","""",vlookup(rc1,Auto_Calc_rows,7,false))"
    .Calculate
End With

'wipe out all the calculations
Worksheets("Bar Details").Range("d3:g302").Value = Worksheets("Bar Details").Range("d3:g302").Value

I have found the following but am concerned by the use of selection which tends to slow things down: VBA - Replace blank cells

Updated: I have got the following to work, however is there a better or faster way?

Sub test_blank_replacement()

TestOverWrite = MsgBox("Do you want to replace all existing data?" & vbCr & " " & vbCr & _
    "Please select 'No' if you have previously used similar data" & vbCr & "Selecting 'Yes' will replace all data" & vbCr & "  " _
    , vbQuestion + vbYesNo + vbDefaultButton1, "Options")

On Error Resume Next
With ActiveSheet.Range("A1:A10")
    If TestOverWrite = vbYes Then
        .ClearContents
        .FormulaR1C1 = "=RC4"
        .Calculate
        .Value = .Value
    Else
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC3"
        .Calculate
        .Value = .Value
    End If
End With

MsgBox "all done"
End Sub

Many thanks in advance.

• excel
• vba

Share Improve this question Follow edited 21 hours ago Miles asked 22 hours ago MilesMiles 12788 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default -1

Your code can look like

  Dim cc As Range
  With ActiveSheet
    If TestOverWrite = vbYes Then
      .[A1:A10] = .[D1:D10].Value ' credits to @MGonet
    Else
      On Error GoTo NoBlanks ' credits to @CDP1802
      For Each cc In .[A1:A10].SpecialCells(xlCellTypeBlanks)
        cc = .Cells(cc.Row, 3)
      Next
    End If
  End With
Continue:
  On Error GoTo 0
  ' more code here
  Exit Sub ' or function
NoBlanks:
  Resume Continue

General suggestions

1. To replace a formula by the result use

.Formula = .Value

2. .Calculate is extra. Formulas calculate immediately in the automatic calculation mode.
3. Use the Evaluate function to calculate any expression

a = Evaluate("A1+A2")

4. Use an intermediate Variant variable to transfer values from one range to another

Dim r
r = [A1:A3]
' you can process data in r here
[C1:C3] = r

Some points above are not mutual compatible, they are different ways to solve a task. You can use Evaluate or item 4 regardless of the calculation mode.

UPD

Evaluate can be very helpful in many cases. For example, if you need to generate the sequence:

Dim r
r = Evaluate("SEQUENCE(10)")

Evaluate as well as the Worksheetfunction collection provides you with the power of Excel application - many algorithms already available, fast and efficient.

You can choose between Evaluate and functions of Worksheetfunction because any one can be better than another in every particular case.

本文标签：