Macro Help
 

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.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

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

Macro Help
 

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.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

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

Macro Help
 

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

Macro Help
 

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

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=51879

Macro Help
 

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

Macro Help
 

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.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

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

Macro Help
 

Sorry.

I get it now.

Thank you Carim.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

Sorry.

I get it now.

Thank you Carim.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

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


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

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

Macro Help
 

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!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

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

Macro Help
 

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
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

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.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

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 D6:P500
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

Macro Help
 

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?


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

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

Macro Help
 

AHHHHhhhhh!!!

That's the problem exactly. I was testing this macro on a spreadsheet
I created a year ago on XL2000. We are now using XL2003.

It works just fine now.

Thanks for all your help Carim. I truly do appreciate it!


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

Glad to see that you are truly enjoying your new VBA experience...
It' s a lot of fun ... and you obviously have what is needed to become
excel-lent ... !!!
All the Best

Carim

Macro Help
 

Thanks very much Carim!:)

Hey if you have a second, have a look at this post...

http://www.excelforum.com/showthread...highlight=celt

Its another macro I am writing for the same spreadsheet. I'm having
trouble with a Text comparison argument.


--
Celt
------------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
View this thread: http://www.excelforum.com/showthread...hreadid=518793

Macro Help
 

Hi Celt,

Take a look at Chip's brilliant solution :
http://cpearson.com/excel/CFColors.htm

HTH
Cheers
Carim

Macro Help
 

Celt,

Before going any further ...
Replace your line :
If ActiveCell.Value = Evaluate("OR(EXACT(" & ActiveCell.Value &
",DropDown))") = False Then
by the line :
If Evaluate("OR(EXACT(" & ActiveCell.Value & ",DropDown))") Then
and see what happens ...
Tom is certainly on the most knowledgeable MVP ... !!!
Cheers
Carim

Macro Help
 

Carim,

I absolutely know Tom knows his stuff. I see his name and advic
everywhere when researching how to code in VB (and he's alway
right!!). I wouldn't dream of arguing coding with anyone on this sit
(not with my meager coding skills :) .

I dropped his coding in to my macro and I am getting a "type mismatch
error on a code that should return a "True" result with this functio
(ie. input exactly the same in my scanned area as it appears in th
checklist). I think that error refers to having too many elements i
my array???

I'll keep playing around with it.

Thanks for the link to the Pearson site

--
Cel
-----------------------------------------------------------------------
Celt's Profile: http://www.excelforum.com/member.php...fo&userid=1941
View this thread: http://www.excelforum.com/showthread.php?threadid=51879