Microsoft Excel noob needs some help

September 21st, 2014, 23:39

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

Quote

Deleted User

Guest

#1

Posts: n/a

Mentioned: Post(s)

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

Last edited by HiddenX; September 22nd, 2014 at 00:27.

Quote

HiddenX

The Elder Spy

RPGWatch Team
Original Sin 1 & 2 Donor

#2

Join Date: Oct 2006
Location: NRW/Germany
Posts: 15,148

Mentioned: 124 Post(s)

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?

Quote

Deleted User

Guest

#3

Posts: n/a

Mentioned: Post(s)

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.

Quote

Deleted User

Guest

#4

Posts: n/a

Mentioned: Post(s)

September 22nd, 2014, 01:02

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)

Attached Files

Fluent.zip (14.2 KB, 3 views)

Quote

HiddenX

The Elder Spy

RPGWatch Team
Original Sin 1 & 2 Donor

#5

Join Date: Oct 2006
Location: NRW/Germany
Posts: 15,148

Mentioned: 124 Post(s)

September 22nd, 2014, 01:08

Like this:

Attached Images

Fluent.png (33.2 KB, 30 views)

Quote

HiddenX

The Elder Spy

RPGWatch Team
Original Sin 1 & 2 Donor

#6

Join Date: Oct 2006
Location: NRW/Germany
Posts: 15,148

Mentioned: 124 Post(s)

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…

Quote

Deleted User

Guest

#7

Posts: n/a

Mentioned: Post(s)

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?

Quote

Deleted User

Guest

#8

Posts: n/a

Mentioned: Post(s)

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.

Quote

HiddenX

The Elder Spy

RPGWatch Team
Original Sin 1 & 2 Donor

#9

Join Date: Oct 2006
Location: NRW/Germany
Posts: 15,148

Mentioned: 124 Post(s)

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.

Quote

HiddenX

The Elder Spy

RPGWatch Team
Original Sin 1 & 2 Donor

#10

Join Date: Oct 2006
Location: NRW/Germany
Posts: 15,148

Mentioned: 124 Post(s)

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.

Quote

Deleted User

Guest

#11

Posts: n/a

Mentioned: Post(s)

September 22nd, 2014, 01:25

Open the vba editor -> menu developer tools
create a new vba module
copy the macros with copy/paste

Quote

HiddenX

The Elder Spy

RPGWatch Team
Original Sin 1 & 2 Donor

#12

Join Date: Oct 2006
Location: NRW/Germany
Posts: 15,148

Mentioned: 124 Post(s)

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!

Quote

Deleted User

Guest

#13

Posts: n/a

Mentioned: Post(s)

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")

Quote

Pladio

Guardian of Nonsense

RPGWatch Donor
Original Sin Donor

#14

Join Date: Nov 2006
Location: Manchester, United Kingdom
Posts: 7,893

Mentioned: 80 Post(s)

September 22nd, 2014, 16:10

Edit - Nevermind!

Quote

Deleted User

Guest

#15

Posts: n/a

Mentioned: Post(s)

September 22nd, 2014, 16:21

Already done

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

Attached Files

Fluent1.zip (12.9 KB, 1 views)

Quote

HiddenX

The Elder Spy

RPGWatch Team
Original Sin 1 & 2 Donor

#16

Join Date: Oct 2006
Location: NRW/Germany
Posts: 15,148

Mentioned: 124 Post(s)

September 22nd, 2014, 19:44

Originally Posted by HiddenX
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

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

)

Quote

Pladio

Guardian of Nonsense

RPGWatch Donor
Original Sin Donor

#17

Join Date: Nov 2006
Location: Manchester, United Kingdom
Posts: 7,893

Mentioned: 80 Post(s)

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!

Quote

Deleted User

Guest

#18

Posts: n/a

Mentioned: Post(s)

Microsoft Excel noob needs some help

Deleted User

HiddenX vbmenu_register("postmenu_1061275193", true);

Deleted User

Deleted User

HiddenX vbmenu_register("postmenu_1061275199", true);

HiddenX vbmenu_register("postmenu_1061275200", true);

Deleted User

Deleted User

HiddenX vbmenu_register("postmenu_1061275203", true);

HiddenX vbmenu_register("postmenu_1061275204", true);

Deleted User

HiddenX vbmenu_register("postmenu_1061275206", true);

Deleted User

Pladio vbmenu_register("postmenu_1061275289", true);

Deleted User

HiddenX vbmenu_register("postmenu_1061275323", true);

Pladio vbmenu_register("postmenu_1061275374", true);

Deleted User

HiddenX

HiddenX

HiddenX

HiddenX

HiddenX

HiddenX

Pladio

HiddenX

Pladio