Automating Autofill Coding Question
 

Using Excel 2000.

Situation: Importing a text file where column a contains part number,
columns b, c, and d contain information relating to column a. Column a's
value is only listed once for many values in columns b, c, and d.

Goal: Automate autofilling column a's value until that value changes. So,
if cell a1's first value is 12345 with 4 blank cells below it, we need to
autofill cells a2:a5 with 12345, then skip a6 and copy its value to the empty
cells beneath it until that value changes.

The number of cells vary between values.

Any help in accomplishing this would be greatly appreciated.

Trisha

Automating Autofill Coding Question
 

Trisha

You could do this without code.

Select column A and F5SpecialBlanksOK

In the active blank cell enter an = sign then point & click on cell above.

Hit CRTL + ENTER to copy down.

When happy with results, copy the column and in place Paste
SpecialValuesOKEsc.

Macro to do the same job.......

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").column

Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With

End Sub


Gord Dibben Excel MVP


On Mon, 10 Jan 2005 14:09:05 -0800, "TrishaB"
wrote:

Using Excel 2000.

Situation: Importing a text file where column a contains part number,
columns b, c, and d contain information relating to column a. Column a's
value is only listed once for many values in columns b, c, and d.

Goal: Automate autofilling column a's value until that value changes. So,
if cell a1's first value is 12345 with 4 blank cells below it, we need to
autofill cells a2:a5 with 12345, then skip a6 and copy its value to the empty
cells beneath it until that value changes.

The number of cells vary between values.

Any help in accomplishing this would be greatly appreciated.

Trisha

Automating Autofill Coding Question
 

Option Explicit

Sub FillColA()

Dim RowCount as Interger
Dim i As Integer
Dim NewValue

NewValue = Range("A1").Value

'Get the number of data rows
RowCount = Range("B65536").End(xlUp).Row

For i = 1 to RowCount

If Cells(i + 1, 1) = "" Then
Cells(i + 1, 1) = NewValue
Else
NewValue = Cells(i + 1, 1)
End If
Next i

End Sub

"TrishaB" wrote:

Using Excel 2000.

Situation: Importing a text file where column a contains part number,
columns b, c, and d contain information relating to column a. Column a's
value is only listed once for many values in columns b, c, and d.

Goal: Automate autofilling column a's value until that value changes. So,
if cell a1's first value is 12345 with 4 blank cells below it, we need to
autofill cells a2:a5 with 12345, then skip a6 and copy its value to the empty
cells beneath it until that value changes.

The number of cells vary between values.

Any help in accomplishing this would be greatly appreciated.

Trisha

Automating Autofill Coding Question
 

Thank you both for your responses. I learned a lot and the problem is solved.

Trisha

"gocush" wrote:

Option Explicit

Sub FillColA()

Dim RowCount as Interger
Dim i As Integer
Dim NewValue

NewValue = Range("A1").Value

'Get the number of data rows
RowCount = Range("B65536").End(xlUp).Row

For i = 1 to RowCount

If Cells(i + 1, 1) = "" Then
Cells(i + 1, 1) = NewValue
Else
NewValue = Cells(i + 1, 1)
End If
Next i

End Sub

"TrishaB" wrote:

Using Excel 2000.

Situation: Importing a text file where column a contains part number,
columns b, c, and d contain information relating to column a. Column a's
value is only listed once for many values in columns b, c, and d.

Goal: Automate autofilling column a's value until that value changes. So,
if cell a1's first value is 12345 with 4 blank cells below it, we need to
autofill cells a2:a5 with 12345, then skip a6 and copy its value to the empty
cells beneath it until that value changes.

The number of cells vary between values.

Any help in accomplishing this would be greatly appreciated.

Trisha

Automating Autofill Coding Question
 

Gord Dibben wrote:

Macro to do the same job.......

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above


This macro is very helpful!

How can it be modified to perform the same action on columns B and C
(in addition to column A?)


Thanks in advance!,

- Kobi

Automating Autofill Coding Question
 

Gord Dibben wrote:



Macro to do the same job.......

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above



This macro is very helpful!

How can it be modified to perform the same action on columns B and C
(in addition to column A)?


Thanks in advance!,


- Kobi

Automating Autofill Coding Question
 

One way:

Option Explicit
Sub Fill_Blanks2()

Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, "A"), .Cells(LastRow, "C")) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
With Intersect(Rng.EntireColumn, .UsedRange)
.Value = .Value 'do you want them converted to values?
End With
End If
End With

End Sub

I find that it's easier to do this manually--rather than finding the macro and
then running it. (Unless if it's part of a larger mechanized process)

I'd select the range I want "A2:C (lastrow)"
then edit|goto special|check Blanks
hit the equal sign, then hit up arrow (without looking at the screen)
then hitting ctrl-enter (to fill those empty cells)

Then select column A:C and edit|copy, edit|pastespecial|Values (if necessary).

I think it's quicker and it allows me to do Undo.

wrote:

Gord Dibben wrote:

Macro to do the same job.......

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above

This macro is very helpful!

How can it be modified to perform the same action on columns B and C
(in addition to column A?)

Thanks in advance!,

- Kobi

--

Dave Peterson

Automating Autofill Coding Question
 

I'm not sure how Google represents that code.

But this line:
Set Rng = .Range (.Cells(2,"A"), .Cells(LastRow, "C")) _
had a line right under it.

Set Rng = .Range(.Cells(2, "A"), .Cells(LastRow, "C")) _
.Cells.SpecialCells(xlCellTypeBlanks)

And this line:

Rng.FormulaR1C1 = "=R[-1]C "
was really:
Rng.FormulaR1C1 = "=R[-1]C"

But I'm not sure if there was a different error that was causing the problem.

Option Explicit
Sub Fill_Blanks2()

Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, "A"), .Cells(LastRow, "C")) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
With Intersect(Rng.EntireColumn, .UsedRange)
.Value = .Value 'do you want them converted to values?
End With
End If
End With

End Sub



wrote:

Hi Dave,

Thanks for the assistance!

I get an error message (Compile Error: Syntax Error) when
attempting to run the macro you provided. I think perhaps this is
happening because of the spacing. I am viewing your post on
googlegroups.

There seems to be a problem with 2 parts of the macro:

1:

Set Rng = .Range (.Cells(2,"A"), .Cells(LastRow, "C")) _
.

2:

Rng.FormulaR1C1 = "=R[-1]C "

"

Do you have any idea how to correct this?

Any and all help is sincerely appreciated.

I am using Excel 2002 SP-2 and running Windows XP.

THANKS!

- Kobi

--

Dave Peterson

Automating Autofill Coding Question
 

Dave,

Thanks alot - that fixed both problems!

However, I now get the following error on the following line of code:

.Value = .Value 'do you want them converted* to values?

It highlights the ".value" that appears AFTER the equals sign and gives
the error message:

Compile error:
Method or data member not found

Any idea why this is occurring?

THANKS SO MUCH FOR YOUR ASSISTANCE!


- Kobi

Automating Autofill Coding Question
 

I couldn't get it to break.

Did you make any changes to these lines?

With Intersect(Rng.EntireColumn, .UsedRange)
.Value = .Value 'do you want them converted to values?
End With

And just a silly guess.

Is your worksheet protected (with some cells locked and others unlocked)?

Any merged cells in column A:C?



wrote:

Dave,

Thanks alot - that fixed both problems!

However, I now get the following error on the following line of code:

.Value = .Value 'do you want them converted* to values?

It highlights the ".value" that appears AFTER the equals sign and gives
the error message:

Compile error:
Method or data member not found

Any idea why this is occurring?

THANKS SO MUCH FOR YOUR ASSISTANCE!

- Kobi

--

Dave Peterson

Automating Autofill Coding Question
 

Dave Peterson wrote:
I couldn't get it to break.

Did you make any changes to these lines?


And just a silly guess.

Is your worksheet protected (with some cells locked and others
unlocked)?

Any merged cells in column A:C?


Hi Dave,

I reinserted the original code you provided and corrected some
spacing issues - it works perfectly now. I guess Google Groups adds
spaces from time to time.


THANKS SO MUCH!


- Kobi