| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill Macro
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
I'm sorry you just lost me here. I have a macro that read the following that
runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
Depending on the formulas you used to parse your data into columns A:C, you may
not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
Ok, there are formulas in the cells, but could I covert to 'text'? This
spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
You have a couple of choices.
One you can convert all the formulas in the range to values: select A:C edit|copy Edit|paste special|values If you wanted, you could add code that would do it for you: This portion: Set RngToFix = .Range("a:c") 'fix '000's first RngToFix.Replace What:="000", Replacement:="", _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ MatchCase:=False would become: Set RngToFix = .Range("a:c") rngtofix.value = rngtofix.value 'fix '000's first RngToFix.Replace What:="000", Replacement:="", _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ MatchCase:=False =========== Now before you get mad <bg, you may be able to add the formulas in your code. Then all of your manual work could be gone. If you share where you put the formulas (Axxx:Cyyy) where xxx and yyy are based on what??? And share what you used in each column. Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
ps. One of the things the code does is change those cells with "000" to empty
cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I
figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
There's nothing special in that code that would be different between column A
and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
ok,,,major break through! Everything works just fine now except for column
B2:B29, and Column C2:C57. These cells initially have 000's, so when I run the query to autofill it takes the heading for the column and pastes it into these cells. Although the code specifically skips the first row:) "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
What would be the best way have Column C (Class) default to '000' if
Description is like "Product'. For example, if the Product Code is like "XX" then Column C should be "000". Because what I'm seeing is the Class code continuing to autofill although the Product Code has changed. When actually Column C should be "000" until a class code is reached (or when description is like "Class". For example: Dept Product Class Descrption 100 000 000 Dept 100 000 000 Dept 100 001 000 Product - A 100 001 000 Product - A 100 001 bb2 Product A CLASS-bb2 100 001 bb2 Product A CLASS-bb2 100 002 000 Product - B 100 002 000 Product - B 100 002 cc1 Product - B Class-cc1 "Dave Peterson" wrote: There's nothing special in that code that would be different between column A and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
I'd use a different formula to parse the description.
Right now, the code expects that every 000 should be replaced by the value above. Maybe you could do something like: =IF(COUNTIF(K2,"product*")0,"000",IF((LEFT(K2,1)) ="D",TRIM(MID(K2,2,3)),NA())) Then you could leave those 000's alone (in the code). The edit|replace line would be changed to: RngToFix.Replace What:="#N/A", Replacement:="", _ (My formula was a guess. I'm not sure what column that sample formula was in.) Lacey wrote: What would be the best way have Column C (Class) default to '000' if Description is like "Product'. For example, if the Product Code is like "XX" then Column C should be "000". Because what I'm seeing is the Class code continuing to autofill although the Product Code has changed. When actually Column C should be "000" until a class code is reached (or when description is like "Class". For example: Dept Product Class Descrption 100 000 000 Dept 100 000 000 Dept 100 001 000 Product - A 100 001 000 Product - A 100 001 bb2 Product A CLASS-bb2 100 001 bb2 Product A CLASS-bb2 100 002 000 Product - B 100 002 000 Product - B 100 002 cc1 Product - B Class-cc1 "Dave Peterson" wrote: There's nothing special in that code that would be different between column A and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
Ok, Dave,,, you almost have me home! Everything looks ok, but I need one
last step: to check if there's a new DeptCode and IF ClassCode = '000' then ProductCode should = '000'. What's happening is the ProductCode autofills until it reaches the next ProductCode. And, that's what the program should do. However, when the ProductCode reaches '999', then a new DeptCode starts. The cells should look like: DeptCode ProductCode ClassCode 100 999 999 200 999 000 When it should look like: DeptCode ProductCode ClassCode 100 999 999 200 000 000 After running the various macros/code to convert to values, autofill cells, etc. what would be the best way to update the few cells under the Product Code column to '000' IF ClassCode = '000' and the spreadsheet has reached a new DeptCode? Dept can also be determined in the Description column. Any suggestions would be helpful.... Thanks! "Dave Peterson" wrote: I'd use a different formula to parse the description. Right now, the code expects that every 000 should be replaced by the value above. Maybe you could do something like: =IF(COUNTIF(K2,"product*")0,"000",IF((LEFT(K2,1)) ="D",TRIM(MID(K2,2,3)),NA())) Then you could leave those 000's alone (in the code). The edit|replace line would be changed to: RngToFix.Replace What:="#N/A", Replacement:="", _ (My formula was a guess. I'm not sure what column that sample formula was in.) Lacey wrote: What would be the best way have Column C (Class) default to '000' if Description is like "Product'. For example, if the Product Code is like "XX" then Column C should be "000". Because what I'm seeing is the Class code continuing to autofill although the Product Code has changed. When actually Column C should be "000" until a class code is reached (or when description is like "Class". For example: Dept Product Class Descrption 100 000 000 Dept 100 000 000 Dept 100 001 000 Product - A 100 001 000 Product - A 100 001 bb2 Product A CLASS-bb2 100 001 bb2 Product A CLASS-bb2 100 002 000 Product - B 100 002 000 Product - B 100 002 cc1 Product - B Class-cc1 "Dave Peterson" wrote: There's nothing special in that code that would be different between column A and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
It looks like your formula that parses the product code has to change. You
don't want na()'s in those cells, you want 000's. in B2 (product code column) =if(a1<a2,"000",yourotherformulathatparsestheprod uctcode) By having it start at 000, the macro won't have to change. Lacey wrote: Ok, Dave,,, you almost have me home! Everything looks ok, but I need one last step: to check if there's a new DeptCode and IF ClassCode = '000' then ProductCode should = '000'. What's happening is the ProductCode autofills until it reaches the next ProductCode. And, that's what the program should do. However, when the ProductCode reaches '999', then a new DeptCode starts. The cells should look like: DeptCode ProductCode ClassCode 100 999 999 200 999 000 When it should look like: DeptCode ProductCode ClassCode 100 999 999 200 000 000 After running the various macros/code to convert to values, autofill cells, etc. what would be the best way to update the few cells under the Product Code column to '000' IF ClassCode = '000' and the spreadsheet has reached a new DeptCode? Dept can also be determined in the Description column. Any suggestions would be helpful.... Thanks! "Dave Peterson" wrote: I'd use a different formula to parse the description. Right now, the code expects that every 000 should be replaced by the value above. Maybe you could do something like: =IF(COUNTIF(K2,"product*")0,"000",IF((LEFT(K2,1)) ="D",TRIM(MID(K2,2,3)),NA())) Then you could leave those 000's alone (in the code). The edit|replace line would be changed to: RngToFix.Replace What:="#N/A", Replacement:="", _ (My formula was a guess. I'm not sure what column that sample formula was in.) Lacey wrote: What would be the best way have Column C (Class) default to '000' if Description is like "Product'. For example, if the Product Code is like "XX" then Column C should be "000". Because what I'm seeing is the Class code continuing to autofill although the Product Code has changed. When actually Column C should be "000" until a class code is reached (or when description is like "Class". For example: Dept Product Class Descrption 100 000 000 Dept 100 000 000 Dept 100 001 000 Product - A 100 001 000 Product - A 100 001 bb2 Product A CLASS-bb2 100 001 bb2 Product A CLASS-bb2 100 002 000 Product - B 100 002 000 Product - B 100 002 cc1 Product - B Class-cc1 "Dave Peterson" wrote: There's nothing special in that code that would be different between column A and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
Ok, it's me again. This still is not working because the autofill macro
overrides the formula. Also, VB script does not like: Range("B2").Select ActiveCell.FormulaR1C1 = _ "=IF(("A2")<("A3"),""000"",IF((MID(E2,3,1))=""S"" ,(MID(E2,5,3)),""000""))" Where I'm trying to compare cell A2 < A3, etc., etc. Why doesn't it like this??? "Dave Peterson" wrote: It looks like your formula that parses the product code has to change. You don't want na()'s in those cells, you want 000's. in B2 (product code column) =if(a1<a2,"000",yourotherformulathatparsestheprod uctcode) By having it start at 000, the macro won't have to change. Lacey wrote: Ok, Dave,,, you almost have me home! Everything looks ok, but I need one last step: to check if there's a new DeptCode and IF ClassCode = '000' then ProductCode should = '000'. What's happening is the ProductCode autofills until it reaches the next ProductCode. And, that's what the program should do. However, when the ProductCode reaches '999', then a new DeptCode starts. The cells should look like: DeptCode ProductCode ClassCode 100 999 999 200 999 000 When it should look like: DeptCode ProductCode ClassCode 100 999 999 200 000 000 After running the various macros/code to convert to values, autofill cells, etc. what would be the best way to update the few cells under the Product Code column to '000' IF ClassCode = '000' and the spreadsheet has reached a new DeptCode? Dept can also be determined in the Description column. Any suggestions would be helpful.... Thanks! "Dave Peterson" wrote: I'd use a different formula to parse the description. Right now, the code expects that every 000 should be replaced by the value above. Maybe you could do something like: =IF(COUNTIF(K2,"product*")0,"000",IF((LEFT(K2,1)) ="D",TRIM(MID(K2,2,3)),NA())) Then you could leave those 000's alone (in the code). The edit|replace line would be changed to: RngToFix.Replace What:="#N/A", Replacement:="", _ (My formula was a guess. I'm not sure what column that sample formula was in.) Lacey wrote: What would be the best way have Column C (Class) default to '000' if Description is like "Product'. For example, if the Product Code is like "XX" then Column C should be "000". Because what I'm seeing is the Class code continuing to autofill although the Product Code has changed. When actually Column C should be "000" until a class code is reached (or when description is like "Class". For example: Dept Product Class Descrption 100 000 000 Dept 100 000 000 Dept 100 001 000 Product - A 100 001 000 Product - A 100 001 bb2 Product A CLASS-bb2 100 001 bb2 Product A CLASS-bb2 100 002 000 Product - B 100 002 000 Product - B 100 002 cc1 Product - B Class-cc1 "Dave Peterson" wrote: There's nothing special in that code that would be different between column A and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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). |
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
Can I use:
Set RngToFix = .Range(2, "A") To only autofill Column A??? Doesn't like it... "Dave Peterson" wrote: It looks like your formula that parses the product code has to change. You don't want na()'s in those cells, you want 000's. in B2 (product code column) =if(a1<a2,"000",yourotherformulathatparsestheprod uctcode) By having it start at 000, the macro won't have to change. Lacey wrote: Ok, Dave,,, you almost have me home! Everything looks ok, but I need one last step: to check if there's a new DeptCode and IF ClassCode = '000' then ProductCode should = '000'. What's happening is the ProductCode autofills until it reaches the next ProductCode. And, that's what the program should do. However, when the ProductCode reaches '999', then a new DeptCode starts. The cells should look like: DeptCode ProductCode ClassCode 100 999 999 200 999 000 When it should look like: DeptCode ProductCode ClassCode 100 999 999 200 000 000 After running the various macros/code to convert to values, autofill cells, etc. what would be the best way to update the few cells under the Product Code column to '000' IF ClassCode = '000' and the spreadsheet has reached a new DeptCode? Dept can also be determined in the Description column. Any suggestions would be helpful.... Thanks! "Dave Peterson" wrote: I'd use a different formula to parse the description. Right now, the code expects that every 000 should be replaced by the value above. Maybe you could do something like: =IF(COUNTIF(K2,"product*")0,"000",IF((LEFT(K2,1)) ="D",TRIM(MID(K2,2,3)),NA())) Then you could leave those 000's alone (in the code). The edit|replace line would be changed to: RngToFix.Replace What:="#N/A", Replacement:="", _ (My formula was a guess. I'm not sure what column that sample formula was in.) Lacey wrote: What would be the best way have Column C (Class) default to '000' if Description is like "Product'. For example, if the Product Code is like "XX" then Column C should be "000". Because what I'm seeing is the Class code continuing to autofill although the Product Code has changed. When actually Column C should be "000" until a class code is reached (or when description is like "Class". For example: Dept Product Class Descrption 100 000 000 Dept 100 000 000 Dept 100 001 000 Product - A 100 001 000 Product - A 100 001 bb2 Product A CLASS-bb2 100 001 bb2 Product A CLASS-bb2 100 002 000 Product - B 100 002 000 Product - B 100 002 cc1 Product - B Class-cc1 "Dave Peterson" wrote: There's nothing special in that code that would be different between column A and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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). |
|
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
You're puttin the formula in B2. You want to check A2 against A1 (not A3).
Range("B2").Formula = _ "=IF("A2"<"A1",""000"",IF(MID(E2,3,1)=""S"",MID(E 2,5,3),""000""))" And the code converts the formulas to values, but that's good. Lacey wrote: Ok, it's me again. This still is not working because the autofill macro overrides the formula. Also, VB script does not like: Range("B2").Select ActiveCell.FormulaR1C1 = _ "=IF(("A2")<("A3"),""000"",IF((MID(E2,3,1))=""S"" ,(MID(E2,5,3)),""000""))" Where I'm trying to compare cell A2 < A3, etc., etc. Why doesn't it like this??? "Dave Peterson" wrote: It looks like your formula that parses the product code has to change. You don't want na()'s in those cells, you want 000's. in B2 (product code column) =if(a1<a2,"000",yourotherformulathatparsestheprod uctcode) By having it start at 000, the macro won't have to change. Lacey wrote: Ok, Dave,,, you almost have me home! Everything looks ok, but I need one last step: to check if there's a new DeptCode and IF ClassCode = '000' then ProductCode should = '000'. What's happening is the ProductCode autofills until it reaches the next ProductCode. And, that's what the program should do. However, when the ProductCode reaches '999', then a new DeptCode starts. The cells should look like: DeptCode ProductCode ClassCode 100 999 999 200 999 000 When it should look like: DeptCode ProductCode ClassCode 100 999 999 200 000 000 After running the various macros/code to convert to values, autofill cells, etc. what would be the best way to update the few cells under the Product Code column to '000' IF ClassCode = '000' and the spreadsheet has reached a new DeptCode? Dept can also be determined in the Description column. Any suggestions would be helpful.... Thanks! "Dave Peterson" wrote: I'd use a different formula to parse the description. Right now, the code expects that every 000 should be replaced by the value above. Maybe you could do something like: =IF(COUNTIF(K2,"product*")0,"000",IF((LEFT(K2,1)) ="D",TRIM(MID(K2,2,3)),NA())) Then you could leave those 000's alone (in the code). The edit|replace line would be changed to: RngToFix.Replace What:="#N/A", Replacement:="", _ (My formula was a guess. I'm not sure what column that sample formula was in.) Lacey wrote: What would be the best way have Column C (Class) default to '000' if Description is like "Product'. For example, if the Product Code is like "XX" then Column C should be "000". Because what I'm seeing is the Class code continuing to autofill although the Product Code has changed. When actually Column C should be "000" until a class code is reached (or when description is like "Class". For example: Dept Product Class Descrption 100 000 000 Dept 100 000 000 Dept 100 001 000 Product - A 100 001 000 Product - A 100 001 bb2 Product A CLASS-bb2 100 001 bb2 Product A CLASS-bb2 100 002 000 Product - B 100 002 000 Product - B 100 002 cc1 Product - B Class-cc1 "Dave Peterson" wrote: There's nothing special in that code that would be different between column A and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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). -- Dave Peterson |
|
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
Hi Dave!
I'm still having a bit of trouble with the very last step autofill macro. Would you have an email account where I can send you a sample of the data so that you can see what I'm talking about? I'm sure with your expertise, you will be able to see quite easily what I'm overlooking.... And, you have been a TREMENDOUS help!!! Thanks much! "Dave Peterson" wrote: There's nothing special in that code that would be different between column A and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 |
|
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Autofill Macro
It's better to keep the discussion in the newsgroup--you'll get lots more
suggestions. And if it's a new subject, you may want to start a new thread. This one is getting pretty longgggggggg. Lacey wrote: Hi Dave! I'm still having a bit of trouble with the very last step autofill macro. Would you have an email account where I can send you a sample of the data so that you can see what I'm talking about? I'm sure with your expertise, you will be able to see quite easily what I'm overlooking.... And, you have been a TREMENDOUS help!!! Thanks much! "Dave Peterson" wrote: There's nothing special in that code that would be different between column A and B (or C). There must be something different in column A. My first guess is that sometimes your parsing formula returns a space character (or multiple space characters). Maybe adding =trim() around the portion of the formula that returns the string would help: =IF((LEFT(K2,1))="D",trim(MID(K2,2,3)),"000") Lacey wrote: Thanks! I beat you to the punch on the Edit/Copy/Paste Special/Values. I figured that much out. Everything works fine except Column A, which will not auto fill the code down until it reaches the next new code. Column B and C work just fine... "Dave Peterson" wrote: ps. One of the things the code does is change those cells with "000" to empty cells. So they do become empty (even if it's just for milliseconds). Lacey wrote: Ok, there are formulas in the cells, but could I covert to 'text'? This spreadsheet will eventually be imported into MS Access for further statistical analysis. The formulas are like: =IF((LEFT(K2,1)) = "D",(MID(K2,2,3)),"000"), parsing a description field into various codes. To that end, the cells are not empty. In fact, no cells are or should ever be "empty" "Dave Peterson" wrote: Depending on the formulas you used to parse your data into columns A:C, you may not have empty cells. If you left a formula that looks like: =if(x2="asdf","qwer","") and the formula evaluated to "", then this is not an empty cell--it has a formula in it. If you changed the formulas to values, then the cell may look empty--but it's not. So what did you do? If you still have formulas, then you don't have empty cells. If you converted to values, try the suggestion in the other post. Lacey wrote: I'm sorry you just lost me here. I have a macro that read the following that runs against 5,000+ records. When I ran the macro the first time it worked just fine, but now I'm getting a "no blanks found" error message. What part of this macro can I "extract" to get around this error WITHOUT creating additional steps (or more macros)??? Option Explicit Sub FillColumns() 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 '000's first RngToFix.Replace What:="000", 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 skip 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 "Dave Peterson" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Autofill in macro | Excel Discussion (Misc queries) | |||
| Macro to Autofill until there's a new value | Excel Programming | |||
| AutoFill Using a macro | Excel Programming | |||
| Autofill Macro | Excel Programming | |||
| autofill macro | Excel Discussion (Misc queries) | |||
Linear Mode
