RPGWatch Forums

RPGWatch Forums (https://www.rpgwatch.com/forums/index.php)
-   Tech Help (https://www.rpgwatch.com/forums/forumdisplay.php?f=25)
-   -   Microsoft Excel noob needs some help (https://www.rpgwatch.com/forums/showthread.php?t=25727)

Deleted User September 21st, 2014 23:39
Microsoft Excel noob needs some help
 
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

HiddenX September 22nd, 2014 00:14
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

Deleted User September 22nd, 2014 00:47
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?

Deleted User September 22nd, 2014 00:55
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.

HiddenX September 22nd, 2014 01:02
1 Attachment(s)
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)

HiddenX September 22nd, 2014 01:08
1 Attachment(s)
Like this:

Deleted User September 22nd, 2014 01:09
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…

Deleted User September 22nd, 2014 01:13
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?

HiddenX September 22nd, 2014 01:15
You have to copy the macros to your Excel file, or you create a personal.xlsb file and copy the macros to this file.

All macros in a personal.xlsb file can be used in all other sheets.

HiddenX September 22nd, 2014 01:22
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.

Deleted User September 22nd, 2014 01:23
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.

HiddenX September 22nd, 2014 01:25
Open the vba editor -> menu developer tools
create a new vba module
copy the macros with copy/paste

Deleted User September 22nd, 2014 01:36
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!

Pladio September 22nd, 2014 13:51
I suggest you add a column sold and a column archived.

You then just fill in yes or no.

Then at the top you can have a formula
=countif(range,"yes")

Deleted User September 22nd, 2014 16:10
Edit - Nevermind!

HiddenX September 22nd, 2014 16:21
1 Attachment(s)
Already done :)

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

Pladio September 22nd, 2014 19:44
Quote:
Originally Posted by HiddenX (Post 1061275323)
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)

Deleted User September 23rd, 2014 02:42
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!


All times are GMT +2. The time now is 10:09.

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
vBulletin Security provided by DragonByte Security (Pro) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
Copyright by RPGWatch