G
Guest
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???
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 said: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",yourotherformulathatparsestheproductcode)
By having it start at 000, the macro won't have to change.
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 said: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
:
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...
:
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"
:
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
:
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...
:
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).