Can't Seem to find problem with this VBA Code
Glad it worked and I've sent you a private email.
Woohoo!
Kevin Baker wrote:
Dave,
It's done... works PERFECT! Thanks so much for taking the time to help me
out with this, would love to send you a IWO JIMA ballcap..
Kevin
"Dave Peterson" wrote in message
...
First, I don't think I'd copy it down all 65536 rows.
I would think doing it 20, 30, even 100 at a time would be sufficient.
(Actually, I'd just copy it down a row at a time--just when I need it.)
===
I still like the formula approach, but if you want to add the formula
automatically:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Intersect(.Cells, Me.Range("F:F,H:H")) Is Nothing Then Exit Sub
If .Cells.Count 1 Then Exit Sub
If Me.Cells(.Row, "I").HasFormula = True Then Exit Sub
Application.EnableEvents = False
Me.Cells(.Row, "I").FormulaR1C1 _
= "=IF(COUNT(RC[-3],RC[-1])<2,""""," _
& "IF(RC[-3]=RC[-1],"""",RC[-3]-RC[-1]))"
Application.EnableEvents = True
End With
End Sub
It adds the formula the first time you put something in F or H.
Kevin Baker wrote:
Dave,
The Conditional Formatting is the perfect solutions.. awesome!
When I use the formula and copy it down (I have to copy it into the
entire
row) my spreadsheet just from 68K to 3.5 meg. Is there, so I think I
will
use the code for that part.
Thanks for all your help!
Kevin
"Dave Peterson" wrote in message
...
I don't think I'd use an event for this. I'd use conditional formatting
and a
formula. (It just seems simpler to me.)
Select column G and then
Format|Conditional Formatting
Formula is: =AND(COUNTA(F1,H1)=1,G1="")
(and use a format of Yellow under the format button, Patterns tab)
If you have only one of the amounts, you'll see a yellow cell.
then in I2 (with headers in row 1???).
=IF(COUNT(F2,H2)<2,"",IF(F2=H2,"",F2-H2))
and drag down as far as you need.
(H2-F2 depending on which way you want the subtraction to be)
===
Just a suggestion, I'd use another column for that indicator--it may
look
prettier with the yellow, but that's difficult to work with.
I think I'd add one more column and use this kind of formula:
=if(and(counta(f2:h2)0,counta(f2:h2)<3),"missing data","")
Then you could apply Data|Filter|autofilter to your range and filter to
show
just the missing data rows or the good rows.
===
One more thing, if you drag these formulas down, you'll be extending
the
print
range.
If that bothers you, apply that data|filter|autofilter and pick a
column
that
always has data in it.
Then filter to show non-blanks. Print the data, then
data|filter|showall.
What do you think?
(and be safe!)
Kevin Baker wrote:
Dave..
Here is what I want Excel to do using VBA code vice formulas:
Column F is the Amount of the Purchase Order
Column G is the Invoice Number from the Vendor
Column H is the Invoice Amount
Column I is the Difference between F and H if any
When I enter a dollar amount into F AND H is blank, THEN G will change
to
a
yellow background. Once I enter an amount into G (when I get the
vendor
invoice via US Mail), then the yellow background will turn back to
nothing
(white). The purpose of the yellow background in G is to allow me to
see
which vendors I need an invoice from.
Okay... For Column I.. the difference is ONLY displayed if I have an
amount
in F AND an amount in H and ONLY if there is a difference... I don't
need
to
see the $0.00.
Hope this makes sense. Thanks again for all your help.
Kevin
"Dave Peterson" wrote in message
...
This part of your expression is checked at the top:
If Target.Column = 6 And Target.Value 1
So that means we have to consider the rest.
Ouch. I just saw that .column = 7 stuff. And I'm not sure how the
check
for
numeric should be handled anymore. I'm also confused about this
comment:
I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank
You seem to be checking for 1 in the code (in a few spots).
Is this close?
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, Me.Range("g:g")) Is Nothing Then
'keep checking for other stuff...
Else
.Interior.ColorIndex = xlNone
'and get out
Exit Sub
End If
'back to the other checks
If IsNumeric(.Value) = False Then Exit Sub
If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If
If Target.Value <= 1 Then Exit Sub
On Error GoTo errHandler:
Application.EnableEvents = False
Select Case .Column
Case Is = 6
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Interior.ColorIndex = 36
Else
.Offset(0, 1).Interior.ColorIndex = xlNone
End If
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0,
2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value -
.Value
End If
End Select
End With
errHandler:
Application.EnableEvents = True
End Sub
=========
I don't think it's gonna work the way you want. But instead of
posting
code, it
might be better to just post what you want to do.
Kevin Baker wrote:
Dave,
Your code was perfect.. I do have more code under the same Private
Sub
and
wanted to get your take on how to "tweak" it. I've tried but I
can't
seem
to get it to work. Here is the code:
If Target.Column = 6 And Target.Value 1 And Target.Offset(0, 1) =
""
Then
Target.Offset(0, 1).Interior.ColorIndex = 36
ElseIf Target.Column = 6 Then
Target.Offset(0, 1).Interior.ColorIndex = xlNone
ElseIf Target.Column = 7 Then
Target.Interior.ColorIndex = xlNone
End If
I want to change the color of the cell in Column G if cell F is
greater
than
0 AND Cell H is blank.. I was copied the above code from somewhere
else,
so
I'm not exactly sure why the elseif statements are there. The
above
worked
but I would sometimes get the same error code I was having trouble
with
before. Any ideas?
Thanks a million,
Kevin
"Dave Peterson" wrote in message
...
In fact, if I'm going to check for numbers, I should do it before
I
add/subtract, too:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Cells.Count 1 Then Exit Sub
If IsNumeric(.Value) = False Then Exit Sub
If Intersect(.Cells, Me.Range("f:f,h:h")) Is Nothing Then
Exit Sub
End If
If Target.Value <= 1 Then Exit Sub
On Error GoTo errHandler:
Application.EnableEvents = False
Select Case .Column
Case Is = 6
If IsNumeric(.Offset(0, 2).Value) Then
.Offset(0, 3).Value = .Value - .Offset(0,
2).Value
End If
Case Is = 8 'only thing left
If IsNumeric(.Offset(0, -2).Value) Then
.Offset(0, 1).Value = .Offset(0, -2).Value -
.Value
End If
End Select
End With
errHandler:
Application.EnableEvents = True
End Sub
Dave Peterson wrote:
This portion:
Target.Offset(0, -2).Value
Refers to the cell that is 2 columns to the left. If you're
typing
something
into column A (or even column B), then there's trouble.
I'm not quite sure if this fits your needs, but I'd do the
checking
up
front:
<<snipped
The .enableevents stuff tells excel to not invoke the
worksheet_change
event
when the code writes to the worksheet.
Kevin Baker wrote:
If Target.Column = 6 And Target.Value 1 And Target.Offset(0,
2).Value
1
Then
Target.Offset(0, 3).Value = Target.Value -
Target.Offset(0,
2).Value
ElseIf Target.Column = 8 And Target.Value 1 And
Target.Offset(0, -2).Value 1 Then
Target.Offset(0, 1).Value = Target.Offset(0, -2).Value -
Target.Value
End If
I get the following when entering data in Cell A on a new
line:
"Run-Time error '1004'
Application-defined or object-defined error
Any thoughts?
Kevin
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
|