MS Office Excel/VBA Macro experts

little_pob

Jnr admin
Staff member
Jnr Admin
Joined
Dec 10, 2004
Messages
8,753
Likes
441
#1
TL;DR: I need a macro for Excel 2010 that checks across two columns of a named table for two different text values; and if they meet then change the number value in a third column to 0 (zero).

I've just redesigned the stats spreadsheet for work. Having not used Excel in any depth since my GCSEs back in '97, I was able to get my head round the formulas, functions and conditional formatting side of the project; but the last piece of the puzzle is a macro and I don't know where to start (having never used them).

What I'm hoping is possible is to get the macro to check A2 for a specific text value, if it doesn't contain that value then skip to A3. If it does match, then check C2 for a different specified text value. If the C2's value doesn't match, then restart at A3. If it does then the number value in E2 needs to be changed to 0 (rather than blanked, or deleted).

If it helps; the data is formatted as a table, which is named and has column headers.

If it makes any difference I'm using Excel 2010.

I've tried searching on Google for off the peg solutions; but this proved fruitless. (Probably down to the fact I know nothing of macros or VBS, so lack key search words that would help.)
 

Spectre

Administrator
Staff member
Jnr Admin
Joined
Jun 3, 2001
Messages
39,540
Likes
875
#2
I think I get what you mean, let me know if I made a booboo :). There is no error handling or whatnot in here but it should give you an idea.

Sunday mornings aren't great Excel VBA times for me, workdays are better.
 

Attachments

Last edited:

Spectre

Administrator
Staff member
Jnr Admin
Joined
Jun 3, 2001
Messages
39,540
Likes
875
#3
I have read that wrong, there are two search strings. I think.
 

Spectre

Administrator
Staff member
Jnr Admin
Joined
Jun 3, 2001
Messages
39,540
Likes
875
#4
This will look for String1 in Column A and String2 in Column C and when they both match it will put a zero in column E.

I bet I've still got it wrong :).

EDIT

Is that better? I think I had a mistake in there anyway.
 

Attachments

Last edited:

little_pob

Jnr admin
Staff member
Jnr Admin
Joined
Dec 10, 2004
Messages
8,753
Likes
441
#5
Thanks, Spectre.

I'll have to test it in work, as my home PC is just error chiming at me... (could be an issue with only having Excel 2007 at home)

I'm also struggling to get it into my workbook.

I'll upload a sample data set in the admin room though.
 

Spectre

Administrator
Staff member
Jnr Admin
Joined
Jun 3, 2001
Messages
39,540
Likes
875
#6
I don't think there are any unusual functions, I wrote it in Excel 2013. Maybe I should have saved as 2003 compatible or something like that?
 
TEST
Top