VBA project

[vofzxy]

hi guys im trying to write a macro that fill in all the blank cells with the value thats above it.

problem : from my data set, i have a column of ~200 rows, some filled some empty
aim: i would like to write a script that find all the blank cells and fill it in with the value above it

from:
Value 1
blank
Value 2
blank
blank
Value 3

to:
Value 1
Value 1
Value 2
Value 2
Value 2
Value 3

thanks guys, if you can help guide me along, i will post my erroneous code later

 

[vofzxy]

Sub round()
Dim myRange as Range
Dim myCell as Range


'column that i want to patch
Set myRange = Worksheets("sheet1").Range("C5:C755")

For Each myCell in myRange
if IsEmpty(myCell) 'if cell is not empty
GoTo lastLine
End IF

if myCell.row = 755 ' stop at the last cell
End Sub
End if

Set Worksheets("sheet1").Range("A2") = myCell.value 'if cell is empty
Set myCell.Offset(1, 0) = myCell

if myCell.row = 755 ' stop at the last cell
End Sub
End if

GoTo lastLine

lastLine:
Next myCell

End Sub

 

[davidktw]

reserved
10 chars

loop thru from the TOP to bottom. use a temporary variable to store the last found VALUE. if detected a blank, fill in with temporary VALUE. If found a non-blank, store the value of the cell into the temporary value.

boundary case: what if there are blanks before any non-blank cells? What value do you want to fill them with? also you need a way to terminate your loop.

 

[vofzxy]

loop thru from the TOP to bottom. use a temporary variable to store the last found VALUE. if detected a blank, fill in with temporary VALUE. If found a non-blank, store the value of the cell into the temporary value.

boundary case: what if there are blanks before any non-blank cells? What value do you want to fill them with? also you need a way to terminate your loop.

i have added some pseudo code, so the first cell i start with will always be filled.

all the blank cells, i would like to fill them with the value in the cell above them.

cheers for your guidance davidktw

 

[peterchan75]

Maybe this can do. I am assuming your data start from first column and first row. First row will always has data.

Sub Fillblank()

Dim col as integer
Dim row as integer
Dim i as integer, j as integer

col = Thisworkbook.Sheets("Sheet1").UsedRange.Columns.Count - 1
row = Thisworkbook.Sheets("Sheet1").UsedRange.Rows.Count - 1

For i = 1 to row
    For j = 0 to col
        If Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i,j) = "" and _
           Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i-1,j) <> "" Then
           Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i,j) = Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i-1,j)
        EndIF
    Next j
Next i

End Sub

 

[davidktw]

i have added some pseudo code, so the first cell i start with will always be filled.

all the blank cells, i would like to fill them with the value in the cell above them.

cheers for your guidance davidktw

Actually you can do better than what I have shared earlier

All you need to do is insert a formula into all the blank cells as you scan thru the cells with your VBA code will do

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

 

[vofzxy]

Maybe this can do. I am assuming your data start from first column and first row. First row will always has data.

Sub Fillblank()

Dim col as integer
Dim row as integer
Dim i as integer, j as integer

col = Thisworkbook.Sheets("Sheet1").UsedRange.Columns.Count - 1
row = Thisworkbook.Sheets("Sheet1").UsedRange.Rows.Count - 1

For i = 1 to row
    For j = 0 to col
        If Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i,j) = "" and _
           Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i-1,j) <> "" Then
           Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i,j) = Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i-1,j)
        EndIF
    Next j
Next i

End Sub

wah i must say your code is damn smart hahaha

<> means is if (i,j) != (i-1,j) right?

i'll try later

EDIT: cannot assume first row first column, some columns cannot be touched
i prefer to specify the range of cells to do this though, but that means i need th code to detect the last cell and change column accordingly if not i can manual specify the range per column if needed

 

[vofzxy]

Actually you can do better than what I have shared earlier

All you need to do is insert a formula into all the blank cells as you scan thru the cells with your VBA code will do

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

sorry i don't really know what indirect does...
from Google it seems to direct the ActiveCell to the cell as in the indirect() input ?

so the input is read as an cell address/location ?

 

[peterchan75]

wah i must say your code is damn smart hahaha

<> means is if (i,j) != (i-1,j) right?

i'll try later

EDIT: cannot assume first row first column, some columns cannot be touched
i prefer to specify the range of cells to do this though, but that means i need th code to detect the last cell and change column accordingly if not i can manual specify the range per column if needed

Yes. <> is not equal in Excel VBA.
You can manually specify the range. Just remember, in the code, A1 is (0,0).

 

[vofzxy]

Maybe this can do. I am assuming your data start from first column and first row. First row will always has data.

Sub Fillblank()

Dim col as integer
Dim row as integer
Dim i as integer, j as integer

col = Thisworkbook.Sheets("Sheet1").UsedRange.Columns.Count - 1
row = Thisworkbook.Sheets("Sheet1").UsedRange.Rows.Count - 1

For i = 1 to row
    For j = 0 to col
        If Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i,j) = "" and _
           Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i-1,j) <> "" Then
           Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i,j) = Thisworkbook.Sheets("Sheet1").Range("A1").Offset(i-1,j)
        EndIF
    Next j
Next i

End Sub

Yes. <> is not equal in Excel VBA.
You can manually specify the range. Just remember, in the code, A1 is (0,0).

this works !!!

so awesome possum 1 thanks peterchan ! do you run teach-vba classes hahah