Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP!!! I need macro to autofill | Excel Discussion (Misc queries) | |||
Autofill in macro | Excel Discussion (Misc queries) | |||
AutoFill Using a macro | Excel Programming | |||
Autofill Macro | Excel Programming | |||
autofill macro | Excel Discussion (Misc queries) |