VBA to replace empty cells with the cell above value

[Twin]

Hi

I am trying to write a macro that will search for the blank cells in the workbook and replace the blank cell with the value from the cell directly above it.

I have written the below code but it returns the word false. Any help would be appreciated

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set Rng = ActiveSheet.UsedRange
Cells.Replace What:="", Replacement:=ActiveCell.Value = ActiveCell.Offset(-1, 0).Value

End Sub

Thanks

 

[Richard Schollar]

Hi

You can use the SpecialCells method to return blank cells (note these are not the same as cells containing a formula resulting in "" eg =""):

With Range("A2:G100")
  On Error Resume Next
  Set myRange = .SpecialCells(xlCellTypeBlanks)  'check for blank cells
  On Error Goto 0
  If Not myRange Is Nothing Then   'if there are blanks then...
     myRange.FormulaR1C1 = "=R[-1]C"   'Formula to return data in cell above
     .Value = .Value    'fix formula vales to fixed constant values
  End If
End With

 

[tigeravatar]

Twin,

Alternate method:

Sub tgr()
    
    Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange
    Dim rngBlank As Range: Set rngBlank = rngUR.Find("")
    
    While Not rngBlank Is Nothing
        rngBlank.Value = rngBlank.Offset(-1, 0).Value
        Set rngBlank = rngUR.Find("", rngBlank)
    Wend
    
End Sub

 

[Peter_SSs]

Twin,

Alternate method:

Sub tgr()
    
    Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange
    Dim rngBlank As Range: Set rngBlank = rngUR.Find("")
    
    While Not rngBlank Is Nothing
        rngBlank.Value = rngBlank.Offset(-1, 0).Value
        Set rngBlank = rngUR.Find("", rngBlank)
    Wend
    
End Sub

Note though that this code

- will error on this worksheet

Excel Workbook
	A	B	C
1	x		x
2	x	x	x
3	x	x	x
Fill Blanks 1

- and will go into an infinite loop on this worksheet.

Excel Workbook
	A	B	C	D	E
1
2		x		x
3		x	x	x
4		x	x	x
5
Fill Blanks 2

 

[dwarek]

hi i have error as object required in the underline statement please help me asap
Private Sub CommandButton1_Click()Dim lrow As LongDim x As LongDim y As LongDim ws As WorksheetSet ws = Worksheets("sheet1")lrow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).RowWith ws.Cells(lrow, 1).Value = TextBox1.Value.Cells(lrow, 2).Value = TextBox2.Value.Cells(lrow, 3).Value = TextBox3.Value.Cells(lrow, 5).Value = TextBox4.ValueIf OptionButton1 = True Then.Cells(lrow, 4).Value = "NEW"ElseIf OptionButton2 = True Then.Cells(lrow, 4).Value = "MODIFY"ElseIf OptionButton3 = True Then.Cells(lrow, 4).Value = "NAR"End IfIf OptionButton4 = True Then.Cells(lrow, 6).Value = "YES"ElseIf OptionButton5 = True Then.Cells(lrow, 6).Value = "NO"End IfEnd WithUnload UserForm1With Range("A2:G2")On Error Resume NextSet myrange = .SpecialCells(xlCellTypeBlanks)On Error GoTo 0If Not myrange Is Nothing Thenmyrange.FormulaR1C1 = "=R[-1]c".Value = .ValueEnd IfEnd WithEnd SubPrivate Sub UserForm_Click()End Sub

 

[Peter_SSs]

@dwarek
You will need to post your code in a better format than that. See the second last line in my signature block below for advice on how to post code.

 

[dwarek]

So this is my code but i encountered a problem in one statement that is RUN TIME ERROR 1004- APPLICATION DEFINED OR OBJECT DEFINED ERROR I HAVE HIGHLIGHTED THE STATEMENT WITH ERROR WITH SAD SMILEY SYMBOL PLS HELP ME
Private Sub CommandButton1_Click()


Dim lrow As Long
Dim x As Long
Dim y As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")


lrow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Cells(lrow, 2).Value = TextBox1.Value
.Cells(lrow, 3).Value = TextBox4.Value
.Cells(lrow, 5).Value = TextBox3.Value
.Cells(lrow, 8).Value = TextBox2.Value
.Cells(lrow, 9).Value = TextBox6.Value
.Cells(lrow, 10).Value = TextBox8.Value
.Cells(lrow, 14).Value = TextBox9.Value
.Cells(lrow, 11).Value = TextBox5.Value


If OptionButton1 = True Then
.Cells(lrow, 4).Value = "NEW"
ElseIf OptionButton2 = True Then
.Cells(lrow, 4).Value = "MODIFY"
ElseIf OptionButton3 = True Then
.Cells(lrow, 4).Value = "NAR"
ElseIf OptionButton6 = True Then
.Cells(lrow, 4).Value = "REX"
End If


If OptionButton7 = True Then
.Cells(lrow, 11).Value = "EQ/EQTY/ALL"
ElseIf OptionButton8 = True Then
.Cells(lrow, 11).Value = "FI/ALL/ALL"
ElseIf OptionButton9 = True Then
.Cells(lrow, 11).Value = "FI/CORP/ALL"
ElseIf OptionButton10 = True Then
.Cells(lrow, 11).Value = "FI/GOVT/ALL"
End If


If OptionButton11 = True Then
.Cells(lrow, 7).Value = "3 TO 10"
ElseIf OptionButton12 = True Then
.Cells(lrow, 7).Value = "LESS THAN 10"
ElseIf OptionButton13 = True Then
.Cells(lrow, 7).Value = "MORE THAN 10"
End If


If OptionButton14 = True Then
.Cells(lrow, 12).Value = "ADDING NEW SSI"
ElseIf OptionButton15 = True Then
.Cells(lrow, 12).Value = "DELETED SSI"
ElseIf OptionButton16 = True Then
.Cells(lrow, 12).Value = "SSI ALREADY PRESENT WITH CORRECT DATA AND FORMAT"
ElseIf OptionButton17 = True Then
.Cells(lrow, 12).Value = "UPDATE TO EXISTING SSI"
ElseIf OptionButton18 = True Then
.Cells(lrow, 12).Value = "UPDATETO FORMAT OF EXISTING SSI"
End If


If OptionButton4 = True Then
.Cells(lrow, 6).Value = "YES"
ElseIf OptionButton5 = True Then
.Cells(lrow, 6).Value = "NO"
End If
End With


Static rngUR As Range: Set rngUR = ActiveWorkbook.ActiveSheet.UsedRange
Dim rngBlank As Range: Set rngBlank = rngUR.Find("")

While Not rngBlank Is Nothing
rngBlank.Value = rngBlank.Offset(-1, 0).Value
Set rngBlank = rngUR.Find("", rngBlank)
Wend



Dim ctrl
For Each ctrl In Me.Controls
If TypeOf ctrl Is msforms.TextBox Then
ctrl.Text = ""
End If
If TypeOf ctrl Is msforms.OptionButton Then
ctrl.Value = False
End If
Next ctrl



End Sub

 

[Peter_SSs]

My guess is that rngBlank is in (or at least includes) row 1, so that when that line of code tries to offset by one row upwards that would be off the worksheet, triggering an error.

 

[dwarek]

so how can i solve this problem please help me out

 

[Peter_SSs]

so how can i solve this problem please help me out

The thread, and I assume your code, is about filling a cell with the value from the cell above. That is not possible if the cell to be filled is in row 1. I can't help you to decide what to do in that circumstance.