auto_fill empty cells in a column with existing value from cell ab

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I should be grateful for any help/ guidance on the following:
I currently get a report in excel format that is very lengthy and worse
still gives a field value when it occurs for the first time but not
subsequent occurances - ie. the blank cells underneath, in the subsequent
rows/records are taken to have the same value as above.

In essence I'm looking for a macro/global way of using the function {Ctrl} +
{' } where the cell I'm in is blank.

How can I do this either in Excel or ideally in Access?
 
Ravvy,

Since this involves processing each record separately, it will be
awkward to do with a macro. It will be better to use a VBA procedure
for this. I assume the data includes a field which can define the
correct order of the records, so we always will know what "the same
value as above" means. Here's an example of one approach (caution:
untested "air code")...

Dim rst As DAO.Recordset
Dim TheValue As String
Set rst = CurrentDb.OpenRecordset("SELECT TheField FROM YourTable
ORDER BY Something")
TheValue = "NoneYet"
With rst
Do Until .EOF
If IsNull(!TheField) Then
.Edit
!TheField = TheValue
.Update
Else
TheValue = !TheField
End If
.MoveNext
Loop
.Close
End With
Set rst = Nothing
 
Hi Ravvy,

I'be been looking for a way to do this in Access myself, and Steve's answer
looks promising. Let me know how it goes for you.

I currently use the below Macro in Excel, which does exactly what you are
trying to do.

Good Luck,
David

Sub FillDownEmpty()
'
' APC1 Macro
' Macro recorded 4/20/2003 To fill down empty cells in a
' list of cells in col A with empty space
'

Dim X As Long
Dim Y As Long

Dim cellvalue As String

Dim UI1, UI2, UI3 As Variant


UI1 = InputBox(prompt:="This macro is designed to fill down data in columns
for data that is is non-contiguous rows. To begin, please enter the x
coordinate of the column that you wish to fill down. (A=1, B=2, C=3, etc.)")
If IsNumeric(UI1) Then
X = UI1
End If
UI2 = InputBox(prompt:="Please enter the number of the row that you want to
begin filling down from.")
If IsNumeric(UI2) Then
Y = UI2
End If
UI3 = InputBox(prompt:="Please enter the number of the row that you want to
stop filling down at.")



'Do While Not (IsEmpty(Cells(Y, X)))

'Copy current cell

'
Do Until (Y = UI3 + 1)

If Not (IsEmpty(Cells(Y, X))) Then

cellvalue = Cells(Y, X)

Y = Y + 1

Else

Cells(Y, X) = cellvalue
Y = Y + 1
End If
Loop



'XA = Cells(X, 1)

'If StrComp(XA, YA) = 0 And StrComp(XB, YB) = 0 Then



'
End Sub
 
Back
Top