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?

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!

I'd select that range to fix and use Edit|Replace to remove those 000 values.

Are they really just 0's with a custom format--or are they the text "000"?
Either way, be careful when you do this. You want to match the whole cell (not
turn A001 into A1).

Then you can use the techniques at Debra Dalgleish's site to file those cells:

http://contextures.com/xlDataEntry02.html

There's a manual technique and some code at that site.

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?

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!

--

Dave Peterson

Thanks! The range/length of column (and file) will change from month to
month. However, the cells are actually parsed from a description field/cell.
So, they are in 'general number' format. I want to automate this process
because of the length of the file (up to 10k records).

Should I still proceed with Debra's technique?

"Dave Peterson" wrote:

I'd select that range to fix and use Edit|Replace to remove those 000 values.

Are they really just 0's with a custom format--or are they the text "000"?
Either way, be careful when you do this. You want to match the whole cell (not
turn A001 into A1).

Then you can use the techniques at Debra Dalgleish's site to file those cells:

http://contextures.com/xlDataEntry02.html

There's a manual technique and some code at that site.

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?

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!

--

Dave Peterson

Hi Dave,

This is what I have so far, most of which is code I borrowed from you
anyway. But I'm still not getting the autofill to work. I need it to
automatically fill until it reaches a new department code. Then I want that
code to autofill until the next code is reached, as so on...


Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Dim sPV As String 'Previous Value
Set wks = ActiveSheet
With wks
Col = .Range("G6").Column

Set Rng = .UsedRange
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

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

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

"Dave Peterson" wrote:

I'd select that range to fix and use Edit|Replace to remove those 000 values.

Are they really just 0's with a custom format--or are they the text "000"?
Either way, be careful when you do this. You want to match the whole cell (not
turn A001 into A1).

Then you can use the techniques at Debra Dalgleish's site to file those cells:

http://contextures.com/xlDataEntry02.html

There's a manual technique and some code at that site.

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?

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!

--

Dave Peterson

First, your code looks like your data is in column G--in my testing, I used
Columns A:C. It seemed to match your initial layout better.

A warning: If your data contains formulas in those columns, then they'll be
converted to values with this routine.

Option Explicit
Sub FillColBlanks()

Dim wks As Worksheet
Dim rng As Range
Dim LastRowInCol As Long
Dim LastRowToUse As Long
Dim myCol As Range
Dim RngToFix As Range

Set wks = ActiveSheet
With wks

Set RngToFix = .Range("a:c")

'fix those 0's first
'if they're really 000's then fix this next line
RngToFix.Replace What:="0", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

LastRowToUse = 0
For Each myCol In RngToFix.Columns
LastRowInCol = .Cells(.Rows.Count, myCol.Column).End(xlUp).Row
If LastRowInCol LastRowToUse Then
LastRowToUse = LastRowInCol
End If
Next myCol

'resize the rngtofix and avoid row 1
Set RngToFix = RngToFix.Resize(LastRowToUse - 1).Offset(1, 0)

Set rng = Nothing
On Error Resume Next
Set rng = RngToFix.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 RngToFix
.Value = .Value
End With
End With

End Sub

I used the columns to determine the last row to fix. I wasn't sure if that's
the best way for your data. The code at Debra's site just relied on the last
used cell (same as control-end manually).

I wasn't sure what to use.

Lacey wrote:

Hi Dave,

This is what I have so far, most of which is code I borrowed from you
anyway. But I'm still not getting the autofill to work. I need it to
automatically fill until it reaches a new department code. Then I want that
code to autofill until the next code is reached, as so on...

Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Dim sPV As String 'Previous Value
Set wks = ActiveSheet
With wks
Col = .Range("G6").Column

Set Rng = .UsedRange
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

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

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

"Dave Peterson" wrote:

I'd select that range to fix and use Edit|Replace to remove those 000 values.

Are they really just 0's with a custom format--or are they the text "000"?
Either way, be careful when you do this. You want to match the whole cell (not
turn A001 into A1).

Then you can use the techniques at Debra Dalgleish's site to file those cells:

http://contextures.com/xlDataEntry02.html

There's a manual technique and some code at that site.

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?

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!

--

Dave Peterson


--

Dave Peterson

PERFECT!!! It worked just fine...Thank you VERY much!


"Dave Peterson" wrote:

First, your code looks like your data is in column G--in my testing, I used
Columns A:C. It seemed to match your initial layout better.

A warning: If your data contains formulas in those columns, then they'll be
converted to values with this routine.

Option Explicit
Sub FillColBlanks()

Dim wks As Worksheet
Dim rng As Range
Dim LastRowInCol As Long
Dim LastRowToUse As Long
Dim myCol As Range
Dim RngToFix As Range

Set wks = ActiveSheet
With wks

Set RngToFix = .Range("a:c")

'fix those 0's first
'if they're really 000's then fix this next line
RngToFix.Replace What:="0", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

LastRowToUse = 0
For Each myCol In RngToFix.Columns
LastRowInCol = .Cells(.Rows.Count, myCol.Column).End(xlUp).Row
If LastRowInCol LastRowToUse Then
LastRowToUse = LastRowInCol
End If
Next myCol

'resize the rngtofix and avoid row 1
Set RngToFix = RngToFix.Resize(LastRowToUse - 1).Offset(1, 0)

Set rng = Nothing
On Error Resume Next
Set rng = RngToFix.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 RngToFix
.Value = .Value
End With
End With

End Sub

I used the columns to determine the last row to fix. I wasn't sure if that's
the best way for your data. The code at Debra's site just relied on the last
used cell (same as control-end manually).

I wasn't sure what to use.

Lacey wrote:

Hi Dave,

This is what I have so far, most of which is code I borrowed from you
anyway. But I'm still not getting the autofill to work. I need it to
automatically fill until it reaches a new department code. Then I want that
code to autofill until the next code is reached, as so on...

Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Dim sPV As String 'Previous Value
Set wks = ActiveSheet
With wks
Col = .Range("G6").Column

Set Rng = .UsedRange
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

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

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

"Dave Peterson" wrote:

I'd select that range to fix and use Edit|Replace to remove those 000 values.

Are they really just 0's with a custom format--or are they the text "000"?
Either way, be careful when you do this. You want to match the whole cell (not
turn A001 into A1).

Then you can use the techniques at Debra Dalgleish's site to file those cells:

http://contextures.com/xlDataEntry02.html

There's a manual technique and some code at that site.

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?

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!

--

Dave Peterson


--

Dave Peterson

Hey Dave,

Is there any reason why I receive a "No blanks found" message when I re-fun
the macro. I created a series of macros which parse the description field
into column A,B, and C using formulas. Then I autofill the formulas down
each column and the next set is to autofill the codes (as we've discussed).
For some reason, the macro we've discussed suddently gives the
above-referenced. error...


"Dave Peterson" wrote:

First, your code looks like your data is in column G--in my testing, I used
Columns A:C. It seemed to match your initial layout better.

A warning: If your data contains formulas in those columns, then they'll be
converted to values with this routine.

Option Explicit
Sub FillColBlanks()

Dim wks As Worksheet
Dim rng As Range
Dim LastRowInCol As Long
Dim LastRowToUse As Long
Dim myCol As Range
Dim RngToFix As Range

Set wks = ActiveSheet
With wks

Set RngToFix = .Range("a:c")

'fix those 0's first
'if they're really 000's then fix this next line
RngToFix.Replace What:="0", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

LastRowToUse = 0
For Each myCol In RngToFix.Columns
LastRowInCol = .Cells(.Rows.Count, myCol.Column).End(xlUp).Row
If LastRowInCol LastRowToUse Then
LastRowToUse = LastRowInCol
End If
Next myCol

'resize the rngtofix and avoid row 1
Set RngToFix = RngToFix.Resize(LastRowToUse - 1).Offset(1, 0)

Set rng = Nothing
On Error Resume Next
Set rng = RngToFix.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 RngToFix
.Value = .Value
End With
End With

End Sub

I used the columns to determine the last row to fix. I wasn't sure if that's
the best way for your data. The code at Debra's site just relied on the last
used cell (same as control-end manually).

I wasn't sure what to use.

Lacey wrote:

Hi Dave,

This is what I have so far, most of which is code I borrowed from you
anyway. But I'm still not getting the autofill to work. I need it to
automatically fill until it reaches a new department code. Then I want that
code to autofill until the next code is reached, as so on...

Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Dim sPV As String 'Previous Value
Set wks = ActiveSheet
With wks
Col = .Range("G6").Column

Set Rng = .UsedRange
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

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

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

"Dave Peterson" wrote:

I'd select that range to fix and use Edit|Replace to remove those 000 values.

Are they really just 0's with a custom format--or are they the text "000"?
Either way, be careful when you do this. You want to match the whole cell (not
turn A001 into A1).

Then you can use the techniques at Debra Dalgleish's site to file those cells:

http://contextures.com/xlDataEntry02.html

There's a manual technique and some code at that site.

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?

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!

--

Dave Peterson


--

Dave Peterson

If your formulas returned "", then you convert to values, then those cells
aren't really empty.

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab
Toggle Transition Navigation keys on.

Then select on of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

If you need to do this lots, you can record a macro when you do it manually.

Lacey wrote:

Hey Dave,

Is there any reason why I receive a "No blanks found" message when I re-fun
the macro. I created a series of macros which parse the description field
into column A,B, and C using formulas. Then I autofill the formulas down
each column and the next set is to autofill the codes (as we've discussed).
For some reason, the macro we've discussed suddently gives the
above-referenced. error...

"Dave Peterson" wrote:

First, your code looks like your data is in column G--in my testing, I used
Columns A:C. It seemed to match your initial layout better.

A warning: If your data contains formulas in those columns, then they'll be
converted to values with this routine.

Option Explicit
Sub FillColBlanks()

Dim wks As Worksheet
Dim rng As Range
Dim LastRowInCol As Long
Dim LastRowToUse As Long
Dim myCol As Range
Dim RngToFix As Range

Set wks = ActiveSheet
With wks

Set RngToFix = .Range("a:c")

'fix those 0's first
'if they're really 000's then fix this next line
RngToFix.Replace What:="0", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False

LastRowToUse = 0
For Each myCol In RngToFix.Columns
LastRowInCol = .Cells(.Rows.Count, myCol.Column).End(xlUp).Row
If LastRowInCol LastRowToUse Then
LastRowToUse = LastRowInCol
End If
Next myCol

'resize the rngtofix and avoid row 1
Set RngToFix = RngToFix.Resize(LastRowToUse - 1).Offset(1, 0)

Set rng = Nothing
On Error Resume Next
Set rng = RngToFix.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 RngToFix
.Value = .Value
End With
End With

End Sub

I used the columns to determine the last row to fix. I wasn't sure if that's
the best way for your data. The code at Debra's site just relied on the last
used cell (same as control-end manually).

I wasn't sure what to use.

Lacey wrote:

Hi Dave,

This is what I have so far, most of which is code I borrowed from you
anyway. But I'm still not getting the autofill to work. I need it to
automatically fill until it reaches a new department code. Then I want that
code to autofill until the next code is reached, as so on...

Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long
Dim sPV As String 'Previous Value
Set wks = ActiveSheet
With wks
Col = .Range("G6").Column

Set Rng = .UsedRange
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

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

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

"Dave Peterson" wrote:

I'd select that range to fix and use Edit|Replace to remove those 000 values.

Are they really just 0's with a custom format--or are they the text "000"?
Either way, be careful when you do this. You want to match the whole cell (not
turn A001 into A1).

Then you can use the techniques at Debra Dalgleish's site to file those cells:

http://contextures.com/xlDataEntry02.html

There's a manual technique and some code at that site.

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?

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!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson