Macro Help

[Celt]

Thanks in adavance for any help provided!!

I am very new to VBA code. I am trying to create a macro for use in a
budget template. My template has accounts across the top and cost
drivers down the side. My general ledger will not accept pennies on
the dollar in budgetary figures. I have used data validation to
prevent this, but users can still paste over the validation. I was
looking for a simple macro that would search the range of entered
figures (from Acct 1 & ABC thru Acct 4 & PQR ... below) and highlight
any cell containing a decimal yellow. Heres the catch, once the error
is corrected, I'd like the cell to return to normal. I was thinking
the best way to do that was once the macro found a cell with a decimal,
it would enter some sort of conditional format into the cell. I was
also hoping there was a way for the macro to indicate in a cell on
another spreadsheet tab if an error was present (ie. the cell turns red
indicating an error or green indicating no error). My information looks
like this...


Acct 1 Acct 2 Acct 3 Acct 4
ABC 1200.00
DEF 1300.00
GHI 1000.00 2000.00
JKL 1500.50
MNO
PQR 1600.00


Budget 2200.00 2800.50 2000.00 1600.00

on my spreadsheet the amount under Acct 1 & ABC oocupies cell B2.

If anymore info is needed, I will gladly provide it. Thanks again for
any help.

 

[Carim]

Hello Celt,

Why don't you try conditional formatting with a formula like :
=INT(B2)/B2<>1
and your selection of format to see your decimals

HTH
Cheers
Carim

 

[Celt]

Thanks Carim.

However, people are going to have the ability to paste data into this
template and that will wipe out any conditional formating I already
have in place. That's why I was hoping someone could help me come up
with a macro that would use dynamic ranges to check all the data
entered and identify errors as found.

 

[Carim]

Hello Celt,

A macro solution is always possible ...
However, one has to fully understand your constraints...
An important process is the one that extracts data from the Budget to
go to the General Ledger, how is it done today ?

Carim

 

[Carim]

Hello Celt,

In the meantime, the following macro will color in red cells which have
decimals ...

Sub MacroRed()
Dim i As Long
Dim j As Long
Dim Lrow As Long
Dim Lcol As Long

Range("A1").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Lrow = ActiveCell.Row
Lcol = ActiveCell.Column
For i = 2 To Lrow
For j = 2 To Lcol
Application.Goto Cells(i, j)
If Int(ActiveCell.Value) / ActiveCell.Value <> 1 Then
ActiveCell.Font.ColorIndex = 3
End If
Next j
Next i
Range("A1").Select
End Sub


HTH
Cheers
Carim

 

[Celt]

Thank you Carim!!

Getting my budget into my general ledger is a 2 part process. I use
contactenate formula to pull the required pieces of the informatio
into the "upload" format. Then a general ledger upload process pull
the info in. It won't take pennies on the dollar when uploading, so
was trying to find a way to prevent my staff from entering decimals.
have used Data Validation, but they can just copy and paste over i
nullifying the Validation. So I was looking for a macro that woul
search the "input range" and turn any cell with a decimal yellow.
However, once they fix the error I would like the cell to go back o
normal...that's why i was toying with a macro that somehow use
conditional formatting.

Thank you for this macro!! I am going to play around with it and se
what I can get it to do

 

[Carim]

Hello Celt,

It seems to me that you could make your life easier if ...
in your " general ledger upload process ", you could use the integer
function ...
Int(anycell) will systematically extract the number without its
decimals ...

HTH
Cheers
Carim

 

[Celt]

Hi Carim,

That actually is a much better idea (I knew I was making this too
complicated!!). That would solve my upload issues.

However, I would still like the macro to identify the decimals in the
input area. I am trying to get the employees to learn to stop entering
pennies on the dollar. So to break them of that habit, I would love to
be able to point out the error (even though with the Int(anycell)
suggestion.....it would no longer hinder my upload process).

In the macro you gave me earlier, I get a "Run-time error '6':
Overflow" message. I was testing the macro to see what it did... I have
my data in a single column from A1:A9 with no blanks. Excel gets stuck
on this line...

If Int(ActiveCell.Value) / ActiveCell.Value <> 1 Then

Any idea why excel gets caught here?

Thanks again for your time and help Carim. I really appreciate it.

 

[Carim]

Celt,

The original macro was designed to scan an area starting in B2, down to
an unknow number of lines and across an unknow number of columns ...
hence the use of i and j starting from 2 and going to the lasr row and
last column of your area ...
Should you want to test the macro on A1: A9 ...

Sub MacroRed()
Dim i As Long
Dim Lrow As Long

Range("A1").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Lrow = ActiveCell.Row
For i = 1 To Lrow
Application.Goto Cells(i, 1)
If Int(ActiveCell.Value) / ActiveCell.Value <> 1 Then
ActiveCell.Font.ColorIndex = 3
End If
Next i
Range("A1").Select
End Sub

By the way the colorindex 3 means Red .. you can pick any other color
.... yellow is 6 ...

HTH
Cheers
Carim

 

[Celt]

Sorry.

I get it now.

Thank you Carim.

 

[Celt]

Sorry.

I get it now.

Thank you Carim.

 

[Celt]

quick question.... I have altered the code I was given earlier in this
thread. Works well up till it encounters a cell that contains text. I
wanted to add an arguement that allows the macro to identify if a cell
contains a number or text. If text then just skip to the next cell.
If a number then preform these steps. I have been messing around with
Isnumeric and Istext. Any ideas? Here is the code:

Sub MacroYellow()
Dim i As Long
Dim j As Long
Dim Lrow As Long
Dim Lcol As Long

Range("A1").Select
Selection.SpecialCells(xlCellTypeLastCell).Select
Lrow = ActiveCell.Row
Lcol = ActiveCell.Column
For i = 6 To Lrow
For j = 4 To Lcol
Application.Goto Cells(i, j)
If ActiveCell.Value <> Fix(ActiveCell.Value) Then
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlNotEqual, _
Formula1:="Fix (ActiveCell.Value)"
Selection.FormatConditions(1).Interior.ColorIndex = 6
End If
Next j
Next i
Range("A1").Select
End Sub

 

[Carim]

Hi Celt,

Congratulations on your progress ...
You are right IsNumeric will solve your problem ...

Just after Application.Goto Cells(i, j)
insert a new condition
If IsNumeric(ActiveCell.Value) = True Then
followed by the existing code ...
Do not forget to insert a new End If just before the line Next j

HTH
Cheers
Carim

 

[Celt]

Thanks Carim! I hope I'm not bugging you too much.

Your solution worked great, but raised two questions:

1. As of now, this macro changes the conditional format of a cell that
has a decimal. My question is, if I go into the spreadsheet and alter
the highlighted decimal to a whole number, the cell remains yellow. I
am thinking it is beacause of the way the macro enters the conditional
formatting arguement like so...

="Trunc(ActiveCell.Value)"

is there a way around this? Some adjustment that I can make that will
get rid of the " " and actually input the active cell adress (ie. D14
or whatever it may be)?


2. I would like to change the area the macro is searching. Right now
it is searching from cell D6 over and down to the last line of my data
in column E. how would I go about limiting the search area to D6:-N-40
(N=as many columns as there is data present).

thanks again for you help!

 

[Carim]

Hi Celt,

If I may congratulate you again on your progress ...!!!
For somebody who started in VBA just a while ago, it is truly
impressive ...

To answer your questions :
1. Since you are intervening manually, you will need to use event
macros, which are macros stored in the specific worksheet ... not in a
"common" module like standard macros ...
In the sheet, you have to input following code

Private Sub Worksheet_Change(ByVal Target As Range)
If (Int(Target.Value) / Target.Value) = 1 Then
Target.Interior.ColorIndex = xlNone
End If
End Sub

2. As far as playing around with the scanned area, I do not understand
if you are talking about the same macro, because it is exactly supposed
to do that Lrow is LastRow and Lcol is LastColumn

HTH
Cheers
Carim

 

[Celt]

Ok, I think I am following you Carim (thanks so much for your patience
and guidance!!!).

I read up on the event macros real quick. I have dropped the code you
gave me into the specific worksheet. How is this event macro activated
(right now, it doesn't seem to be doing anything).

I don't know what the deal is with the scan area. I am hoping I didn't
do something to mess it up. Right now it is only scanning columns D and
E down to the last row of data entered. If it would be easire for me to
share my spreadsheet with you so you can see what's going on, just let
me know.

 

[Celt]

I think I got the event macro working. I used this.

Private Sub Worksheet_Change(ByVal Target As Range)
If (Int(Target.Value) / Target.Value) = 1 Then
Target.FormatConditions.Delete
End If
End Sub

It seems to work. Once the Macro is run, identifies the decimals and I
go in and "correct" them, removing the conditional formatting with the
event macro makes the cell go back to normal. For some reason I
couldn't get this statement to work.

Interior.ColorIndex = xlNone

It left the cell yellow.

 

[Carim]

Celt,


Glad you could fix the yellow/non-yellow with the "private sub" macro
....

To answer to an earlier remark, these macros are events macro, i.e. the
macros are triggered
by the event ... there is no intervention needed ... But the one which
is the event itself ...
in your example, "removing the decimals" is the event ... which
triggers the private sub which deletes the Format conditions ...

As far as monitoring the range your macro is scanning and therefore
correcting, there are only two possibilities :
1. either you hard-code it , i.e the sheet range is for example D6500
and in your macro, you make this info appear ... , or
2. your range will keep on changing and you do not want to go back to
your macro, each and every time to adjust the range accordindly.
In this case, you have to use Variables which will "recognize" the last
row and last column used ... before the macro even starts to perform
its actual job ...

I hope these explanations are clear enough ...
If not, do not hesitate to drop a message ...

HTH
Cheers
Carim

 

[Celt]

Very clear explanations, thanks Carim!

I have everything working the way I want it to now, with the exception
of the "scan area". I am using the Lcol and Lrow statement you
originally gave me, but for some reason the macro is on "scanning"
columns D & E. My spreadsheet does have a small amount of data (both
text and numeric) in columns F through I. The macro is just ignoring
it.

Is it possible for spreadsheet formatting or data validation (or
somthing else) to hinder the macro or the Lcol statement?

 

[Carim]

Hello Celt,

I am glad to hear you are making good progress ...!!!
As far the last cell is concerned, if you are using xl2000, there is
sometimes a bug related to spreadsheet formatting, with the instruction
xlCellTypeLastCell, which is used to identify the "bottom end" cell.
However, with worksheets which have been extensively manipulated,
sometimes this function gets corrupted.
Two tiny recommendations :
1. Insert a new worksheet, in which you copy paste all the cells of the
worksheet you are currently using ... Then, in this newly created
worksheet hit "end" "home" to visualize where Excel jumps to ... it
should be the clean last cell ... if so ... in VB, don't forget to copy
your private modules from the old to the new worksheet and then get rid
of your old worksheet ...
2. To be on the safe side, in your macro just before the line
Selection.SpecialCells(xlCellTypeLastCell).Select
go and insert the following line
ActiveSheet.UsedRange

Go ahead and test your updated macro in your new worksheet ...
everything should be fine ...
If some reason, it does not fix your problem , do not hesitate to drop
me a line ...

HTH
Cheers
Carim