Macro to Autofill until there's a new value
 

Hi!

I need to write a macro to autofill a column or columns that contain a
Department Code, Product Code and Class, for example

Colmn A Column B Column C
Department Code Product Code Class
A001 000 010
000 100 000
000 100 000
000 000 020
A002 000 000
000 200 000
000 200 000
000 000 030

What I'd like the macro to do is autofill the department code in Column A
until it reaches the next department code. The Product Code and Class
correspond to the specific Department Code (until it changes). I'd also like
to autofill the Product Code and Class until each changes as well.

What would be the best way to write this macro?

Thanks,
Lacey

Macro to Autofill until there's a new value
 

Lacey, Are the values being filled over blank or zero? Here a quick attemp
assuming that they contain 0
Sub FillInData()
Dim cnt As Long
Dim sPV As String Previous Value
For cnt = 1 to 3 'Columns A, B, & C
Cells(2,cnt).Select 'Assumes row 1 is header
sPV = ActiveCell
ActiveCell.Offset(1).Select
Do Until ActiveCell = ""
If ActiveCell = 0 Then
ActiveCell = sPV
Else
sPV = ActiveCell
End If
Loop
Next
End Sub


HTH
--
Charles Chickering

"A good example is twice the value of good advice."


"Lacey" wrote:

Hi!

I need to write a macro to autofill a column or columns that contain a
Department Code, Product Code and Class, for example

Colmn A Column B Column C
Department Code Product Code Class
A001 000 010
000 100 000
000 100 000
000 000 020
A002 000 000
000 200 000
000 200 000
000 000 030

What I'd like the macro to do is autofill the department code in Column A
until it reaches the next department code. The Product Code and Class
correspond to the specific Department Code (until it changes). I'd also like
to autofill the Product Code and Class until each changes as well.

What would be the best way to write this macro?

Thanks,
Lacey

Macro to Autofill until there's a new value
 

I'm getting a compile error on line: Dim sPV As String Previous Value
So far this is my code:

Sub Autofill_Cells()
Dim wks As Worksheet
Dim Rng As Range
Dim Col As Long
Dim sPV As String Previous Value

Set wks = ActiveSheet
With wks
Col = .Range("A6").Column

sPV = ActiveCell
ActiveCell.Offset(1).Select
Do Until ActiveCell = ""
If ActiveCell = 0 Then
ActiveCell = sPV
Else
sPV = ActiveCell
End If
Loop
Next
End Sub



"Charles Chickering" wrote:

Lacey, Are the values being filled over blank or zero? Here a quick attemp
assuming that they contain 0
Sub FillInData()
Dim cnt As Long
Dim sPV As String Previous Value
For cnt = 1 to 3 'Columns A, B, & C
Cells(2,cnt).Select 'Assumes row 1 is header
sPV = ActiveCell
ActiveCell.Offset(1).Select
Do Until ActiveCell = ""
If ActiveCell = 0 Then
ActiveCell = sPV
Else
sPV = ActiveCell
End If
Loop
Next
End Sub


HTH
--
Charles Chickering

"A good example is twice the value of good advice."


"Lacey" wrote:

Hi!

I need to write a macro to autofill a column or columns that contain a
Department Code, Product Code and Class, for example

Colmn A Column B Column C
Department Code Product Code Class
A001 000 010
000 100 000
000 100 000
000 000 020
A002 000 000
000 200 000
000 200 000
000 000 030

What I'd like the macro to do is autofill the department code in Column A
until it reaches the next department code. The Product Code and Class
correspond to the specific Department Code (until it changes). I'd also like
to autofill the Product Code and Class until each changes as well.

What would be the best way to write this macro?

Thanks,
Lacey

Macro to Autofill until there's a new value
 

Doh! Previous Value was meant as a comment just forgot the ' change to this:
Dim sPV As String 'Previous Value
--
Charles Chickering

"A good example is twice the value of good advice."


"Lacey" wrote:

I'm getting a compile error on line: Dim sPV As String Previous Value
So far this is my code:

Sub Autofill_Cells()
Dim wks As Worksheet
Dim Rng As Range
Dim Col As Long
Dim sPV As String Previous Value

Set wks = ActiveSheet
With wks
Col = .Range("A6").Column

sPV = ActiveCell
ActiveCell.Offset(1).Select
Do Until ActiveCell = ""
If ActiveCell = 0 Then
ActiveCell = sPV
Else
sPV = ActiveCell
End If
Loop
Next
End Sub



"Charles Chickering" wrote:

Lacey, Are the values being filled over blank or zero? Here a quick attemp
assuming that they contain 0
Sub FillInData()
Dim cnt As Long
Dim sPV As String Previous Value
For cnt = 1 to 3 'Columns A, B, & C
Cells(2,cnt).Select 'Assumes row 1 is header
sPV = ActiveCell
ActiveCell.Offset(1).Select
Do Until ActiveCell = ""
If ActiveCell = 0 Then
ActiveCell = sPV
Else
sPV = ActiveCell
End If
Loop
Next
End Sub


HTH
--
Charles Chickering

"A good example is twice the value of good advice."


"Lacey" wrote:

Hi!

I need to write a macro to autofill a column or columns that contain a
Department Code, Product Code and Class, for example

Colmn A Column B Column C
Department Code Product Code Class
A001 000 010
000 100 000
000 100 000
000 000 020
A002 000 000
000 200 000
000 200 000
000 000 030

What I'd like the macro to do is autofill the department code in Column A
until it reaches the next department code. The Product Code and Class
correspond to the specific Department Code (until it changes). I'd also like
to autofill the Product Code and Class until each changes as well.

What would be the best way to write this macro?

Thanks,
Lacey

Macro to Autofill until there's a new value
 

P.S. The values being filled over are zero; however, I need the macro to
identify when there's a new Department code and then continue to fill those
fields with the new Department Code each time it changes. The spreadsheet
only shows the Department Code once, the following cells contain zeros until
the next Department Code. However, I want the cells filled with the correct
data (not zeros) until it reaches a new code...

Thanks!

"Charles Chickering" wrote:

Lacey, Are the values being filled over blank or zero? Here a quick attemp
assuming that they contain 0
Sub FillInData()
Dim cnt As Long
Dim sPV As String Previous Value
For cnt = 1 to 3 'Columns A, B, & C
Cells(2,cnt).Select 'Assumes row 1 is header
sPV = ActiveCell
ActiveCell.Offset(1).Select
Do Until ActiveCell = ""
If ActiveCell = 0 Then
ActiveCell = sPV
Else
sPV = ActiveCell
End If
Loop
Next
End Sub


HTH
--
Charles Chickering

"A good example is twice the value of good advice."


"Lacey" wrote:

Hi!

I need to write a macro to autofill a column or columns that contain a
Department Code, Product Code and Class, for example

Colmn A Column B Column C
Department Code Product Code Class
A001 000 010
000 100 000
000 100 000
000 000 020
A002 000 000
000 200 000
000 200 000
000 000 030

What I'd like the macro to do is autofill the department code in Column A
until it reaches the next department code. The Product Code and Class
correspond to the specific Department Code (until it changes). I'd also like
to autofill the Product Code and Class until each changes as well.

What would be the best way to write this macro?

Thanks,
Lacey