Microsoft Excel noob needs some help

D

Deleted User

Guest
Hello everyone!

I'm currently cataloging my collection of vinyl records and I'd like to add a few features via a formula in Excel, which I'm pretty sure I can do.

Basically, I want to tally up the number of records I've sold in my collection (their boxes are filled with the color red), and also tally up the records that I've archived (these are denoted by having their name in italics.

So, I haven't the first clue what to do, except I know it has something to do with creating a formula...

Can someone spell it out in dummy terms what exactly I need to do to get this to work? I'm using 2007 Excel. Thanks in advance.

- Fluent
 
Code:
'Counts the cells in Italic in the current selection
Sub CountItalic()
Dim i As Integer
Dim cell As Range

i = 0
For Each cell In Selection
    If cell.Value <> "" And cell.Font.Italic = True Then
      i = i + 1
    End If
Next

MsgBox "Cells in Italic:" & Str$(i)
End Sub

Just add this Macro - it counts the cells in Italic in the current selection
Similar for cells in red:

Code:
'Counts the cells in red in the current selection
Sub CountRed()
Dim i As Integer
Dim cell As Range
Const cRed = 3

i = 0
For Each cell In Selection
    If cell.Value <> "" And cell.Interior.ColorIndex = cRed Then
      i = i + 1
    End If
Next

MsgBox "Cells in red:" & Str$(i)
End Sub
 
Last edited:
Joined
Oct 18, 2006
Messages
19,710
Location
Germany
HiddenX, how did I know you would respond ;). Thanks!!

I'm not sure how to add this macro, though. Can you give me instructions from start to finish please?

Edit - and will this macro update itself as I add more LPs?
 
Okay I figured out how to add a macro but it doesn't seem to be doing anything...Again, more information is needed to better understand what to do , thanks.
 
These macros work like this:

Mark the range of cells you want to evaluate with your mouse. Let's say A1 to A100
and then you can run the macros.
They will pop up with a message dialog.

Getting Started with VBA in Excel 2010

Look in Fluent.xlsm for Examples (it's zipped)
 

Attachments

  • Fluent.zip
    14.2 KB · Views: 3
Joined
Oct 18, 2006
Messages
19,710
Location
Germany
It works in your zipped file but not in my document. Do I have to record the macro first? It just seems to do nothing when I click "Run" on the macro after I've created it...
 
Instead of a macro isn't there some kind of formula I can use that will automatically tally everything up without running a macro? I'd like to have a few columns set up specifically to record this data and I don't want to have to remember to run the macro every time I make changes in the document...

Do you know of a formula that could do these 2 things?
 
Backcolor and Italic cannot be so good evaluated with formulas. Sorry. VBA is the better way to go.

I've seen a more complicated formula for backcolor evaluations.
 
Joined
Oct 18, 2006
Messages
19,710
Location
Germany
How do I copy the macros to my document? I see them open in the document you sent me but I can't copy them.
 
Open the vba editor -> menu developer tools
create a new vba module
copy the macros with copy/paste
 
Joined
Oct 18, 2006
Messages
19,710
Location
Germany
Confusing, but I think I got it working, although I couldn't figure out VBA and modules and all that, but as long as I open the file you sent me along with my list, I can use the macros freely.

Thanks for the help!
 
Already done :)

BTW: The solution from Pladio is easier for people with no VBA skills
 

Attachments

  • Fluent1.zip
    12.9 KB · Views: 1
Joined
Oct 18, 2006
Messages
19,710
Location
Germany
Already done :)

BTW: The solution from Pladio is easier for people with no VBA skills

Yeah, when I realised from Fluent's response that he was having trouble with the VBA part, I thought offering a more standard solution would be more useful to him :ahoy:

Fluent, if you want I could do it for you.

I'm quick with Excel ... (It's part of my job - a bigger part than I sometimes would want it to be :D)
 
Joined
Nov 13, 2006
Messages
9,175
Location
Manchester, United Kingdom
Pladio, I may take you up on that offer in the future, but for now I think I have it how I want it. Thanks!
 
Back
Top Bottom