Free Microsoft Excel 2013 Quick Reference

Could really use some help Results

Hi,

I was wondering if I could get some help here with VBA codes. Recently, I bought a new laptop that comes with Windows 7 and Office 2010.

I have some codes that is designed to send something out via email and these codes work really well on excel 2003 as shown below,

If Val(Application.Version) >= 12 Then
'You run Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
Else
'You run Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If

But because my work colleagues are still using excel 2003, what I send out from my excel 2010 has a different file extension such as .xlsm

They have had trouble opening the file I sent them, even with the file compatibility software.

so I decided to change the code from above to the one below

If Val(Application.Version) >= 12 Then
FileExtStr = ".xls": FileFormatNum = 52
Else
'You run Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
End If

This gives a different error with the message saying "this file is in an unrecognised format" when trying to open it with Excel 2003.

Hence, I wonder if there are any ways of converting the files from excel 2010 to excel 2003 so that my work colleagues can open and read it.

Any help would be greatly appreciated.

Thank you in advance

Hey all, I could use some tips with my current excel project. I've attached the sheet I am working on to this post.

The part I am specifically trying to work on is the far right of the first tab. where it shows Class room usage and 13 weeks of information. Below that is the same table but in green. When you place a figure in the top section of the weeks that creates a training class that starts that week. Down below you'll see the week you chose go red as well as the 3 weeks after that going red as well. The values in the lower cells become 0, which changes the total available in the cells like AH12 for example.

While this almost works like I need it to I need the bottom class room usage table to also zero out days that can't really be the start of a training class. For example If I put an X in cell AX2 that will block out AX-BA36 then if I put an X in say BD2 it will block out the corresponding cells down below and lower the values in them to 0. The problem arises with the two green cells between the scheduled classes.

On the left cells like AH12 are showing the total slots available to put people into training for that week, these draw from the values in the bottom classroom usage table. When I have something like the situation I described it's still showing those two weeks between the training classes as available, I need to set up some kind of condition that if there is not a block of 4 available they will turn to 0 and go red. So the two between the weeks i selected will also zero out since they cant be used.

These won't be scheduled after that 13 week mark so a class can be scheduled at the end without having the 3 weeks after available here since the table does not reach that far.

Any help would be appreciated.

Hello, I'm a complete novice when it comes to using Excel VBA and am
looking for help and advice please.

I've been given the job of porting some software over from OS2 onto
Windows - this software is used to build code for the companys
systems and it includes a "screen builder" which allows users to
design screens and this also creates the necessary code which generates
the screens when run.

Assuming I can't get the actual screen builder source code to work on
Windows (still looking at this) then I will need a replacement screen
builder and I was wondering if Excel might be able to do it. I've
mocked up a simple screen which looks very similar to the system we use
but obviously it doesn't generate the source code, but the question
is can Excel do this either by using a Macro or Visual Basic?

I'd need some VB which could look at the Excel worksheet/template and
see what has been added and then using that generate some source code
and output it to an IDE, notepad would do but VIM would be better.

I've seen various tutorials for Excel VBA on the net but none of them
are really appropriate for what I want to do - is it possible? The
alternative would be for me to write a screen builder from scratch
which I don't really want to do!

Thanks.

David

Hey everyone, quick question. Im looking to have some code that will first try to find a value on a page. If it can't find that value, then it does a set of actions, otherwise, it does a different set of actions. At first I thought to use an If, then, else statement, but I don't really know how to incorporate the Find condition. If I could get some help that'd be great. thanks!!!

Formula Calculation of cells required

guys could really do with some help on this please without using a pivot table

Basically column A has a whole load of statuses going down starting form a2

column 1 has a whole load of the list ID's

I want it to search if b1 = 118 & a2 = answering machine auto

how many times list ID 118 appears as answering machine auto from the stats tab

the sheets is attached guys please help :S

Hi,

I am working on a timetable which has several groups of tasks (e.g. month-end, budget etc).

The users want to see only those groups of tasks that concern them. To do that, I have a form in the first tab where the users select the groups of tasks that they want to see. Then they run a macro with advanced filter and see the filtered timetable in the next tab. The macro I recorded is very simple:

   
Sheets("Timetable").Range("$A$17:$V$85").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Timetable").Range("criteria"), Unique:=False
    Sheets("Timetable").Select


However the filtering does not work the way I want it to. The problem is (I think) that I have to tell Excel the precise size of the criteria range for the filtering to work correctly.

However in my case the number of rows in the criteria field is not fixed. It changes depending on how many groups of tasks the users will select (up to 15). I do not know how to change the macro so that would provide under "criteria" the exact criteria range (or at least the number of rows in that range) that will results from the selection of users (if you look into the file, the range can be anything between D5 and D5:D18). In the file you'll see how as a workaround to using VBA I tried to define name "criteria" as formula that calculates the address of the criteria range but of course it does not work

I suppose this is not a problem for someone who knows VBA so I thought I'll ask the experts. The attachment will hopefully give some idea about what I'm trying to do.
I will be very grateful for any help, also if there is a solution that does not require VBA
(And if I could get help over this weekend, it would be really great!)

Cheers!

Hi,

This is my first post and i need some help! I've created a little spread sheet that cross references some information you input with a table of data. I've used this formula to check if the data exist

And it displays true if the data exists or false if the date doesn't exist. How can i make it so that it also highlights
(boldifies, changes cell colour etc) the relevant data. 

I'm really not sure. I'm thinking maybe conditional formatting could be sued to make the comparision and highlight matches without needing the countif function...

-Cy

G`day i was wondering if anyone would be kool enough to provide me with some code to achieve this transfer of data.I have included a screen shot of what i need with some comments / instructions,would be really grateful if anyone could help.
Cheers!!!!

Hello all from The Netherlands,

I've finally decided to register after I already used alot of archived posts to help me further, thanks for the great resource this forum provides! . This time, however, I really require some problem specific help. I have a excel workbook where I have one sheet monitoring the other. Both have the same lay-out with in the A column names of employees and then each following column represents a day. In one sheet each column contains the task for that day, the other sheet converts this in 1 (busy), or 0 (nothing to do) so it makes it easy for me to do some more advanced calculations and programming to it. (it also includes other numbers for other states).
When I delete something I keep getting #REF errors in my formula that converts it to either 1 or 0. I found out that this can be solved by using the INDIRECT or INDEX function. However, I failed to apply this properly to my function so could anybody help me with that?

Thanks!

Ok. So, I work in the entertainment industry, and I need to represent the foreign languages available on a film in two different ways. Rather than having to type them all up twice it'd be really useful if I could do a conditional concatenate...

On the attached sheet you can see the name of the film in column a.

Column B has the first language, C the second, D the third, E the fourth, and subtitled languages always go in column F.

Then in column G has the same languages combined together (this is the part I type up usually).

So... how do I get the information in columns B to F to concatenate to column G?

As you can see it is further complicated by the fact some movies have four languages and a subtitle (column F) and some have less languages (the blanks in columns B to E) and a subtitle (column F), some have no subtitle.

The information in column G has to be formatted like that with the comma.

The other option is to do it the other way, and have column G feed the information in columns B to F... however that is complicated because subtitles HAVE to be in column F.

Any help would be greatly appreciated.

Alf

Hi,

I'm new to this forum but though I'd share a newfound solution to a problem I'd been having for a long time. I have a spreadsheet of chemical compounds along with various properties and common uses etc... which I like to be able to sort by use, or molucular mass for example.

My problem was that I could easily sort those sort of things, but when it came to sorting the compound names themselves I was stuck, because they rarely begin with the letter of the name, rather with some other character or a number. Anyway, I thought there might be something in Excel you could use to help, and after literally DAYS of searching the Web I stumbled cross a little known add in for excel called 'Chemical Sorting' which lets you ignore parts of the name like hyphens, commas, brackets, numbers and single letters. I think it's basically a normal sort with the capability of ignoring whatever you want. Oh, you can also ignore italics and bold - which was REALLY useful I found when it came to names beginning 'tert' or 'exo' etc.

Anyway, there you go! If anyone has similar experiences, let me know. In the meantime, here’s the link to the tool if you're interested:

http://uk.geocities.com/chrisdjames1...calSorting.zip

Hi,

I'm trying to graph the distribution of views of some articles on my website. I'm a little stuck on how to effectively bring all the data together in Excel and was hoping to get some wisdom from you guys! :-)

What I'm looking to analyse is how long these articles take to reach maturity from their date of publication (so, when they achieve their optimum performance).

I'd then like to further analyse this across various metrics like article type/producer etc., which, I guess, would be in some kind of Pivot Table filter-type-thing.

Here's an example of some of my data:

ArticleName - PublishedDate - OctViews - NovViews - DecViews - ArticleType - Producer
Article1 - Oct-08 - 3701 - 4856 - 4561 - Food - ABCTV
Article2 - Nov-08 - 0 - 9824 - 1256 - Food - ABCTV
Article3 - Oct-08 - 1358 - 8953 - 9652 - Tech - XYZ.com
Article4 - Oct-08 - 1351 - 2354 - 3562 - Tech - ABCTV

The part I'm finding tricky is this; what we'd like to do is rather than look at a specific date (say, articles published on 09/04/08) it would be useful to combine all the dates so that the first month something is published is Month 1, regardless of the date it is actually published.

As the same article can be from the same producer but published at different times, if I could remove the "Jan-Mar = Month 1-Month 3" problem it would allow us to graph all the data into one snapshot of the publishing process and find trends. If this could then be cross examinable by the ArticleType etc. it'd be really useful.

If anyone can help it'd be much appreciated! I think the above makes sense, but let me know if anything else is needed to decipher my gibberish.

Cheers!

Hi,

this is my first time on the forum so please excuse what may seem like stupidity on my part - i'm trying to get excel to do something for me but am not very well practiced in all of its uses yet!

I am trying to compare 2 lists. Each will contain 3 columns: the first 2 columns being name and surname of a student, the third a high school subject. The original list could contain multiple entries under the same name, with the subject differing each time. I hope to compare this with an similar but updated list that will have some differences: a student's name may appear the same number of times (or more or less times), and the subjects may change (or remain the same, or some removed from the updated list).

My aim is to compare the original and updated lists, and if possible produce an output report that will highlight where a subject has been added, removed or remains on the updated list. Any suggestions?

As i say i am new to this, so really apologise if this is very unclear or it seems a stupid question. So far i am only really familiar with commands like conditional formatting and autofilters, which have i have so far not gotten far with. A friend suggested the "Vlookup" command? If you can offer any advice at all it will be much appreciated.

Thanks in advance!

Hi Excel people,

I have been sat here for 2 hours, trying to think of a solution to a problem i have.

I have spent a few days making a macro that:

Opens a CAP (like txt) file in excel
Trims it down to the required data
Finds the necessary bits, copies it all to a workbook
Extract first lot of data, and paste into the correct date column in a summary sheet.
Extract the second lot of data, do some lookup and put into the correct date column in a summary sheet.

It's the third lot of data i can't work out how to get it into the summary sheet.

The third lot of data is: Discount Method, and discount value.
Basically, it records how much discount has been given in a day, and by what code (ie. 50% off, 20% off, 3 for £12)
The CAP files only list which discounts have been used that day.

With the CAP file data in a "temp" sheet in my workbook, is there any way i could look which discount method it is, look at the value of that method, then go back into my main summary sheet and put it into the relevant discount row, in the correct date column (which is called capdatecol).

The main problem i have is i have to make Rows with the discounts in beforehand. I could do this, and possibly a MsgBox come up telling me if the Discount wasn't present in the summary sheet?

So, what i'm trying to do is:

Macro to look in "temp" worksheet - to find the discounts (ie. 1 cell below "DISCOUNT REASONS" down to "TOTAL DISCOUNTS"), to find out what values are against them.. then to go into "Till02", and put in against the Discount row headings. (The column it has to go in should already be named inthe macro as CapDateCol)

The cap size changes, so i can't reference it directly.

I would really, really appreciate any help on this.
I have attached what i've done so far, please don't laugh at my simple and long winded macro, i'm proper learning!

(The sheets to take notice of: Till02 and temp (temp being right at the end)

What i've done so far:

http://www.teampsp.co.uk/Stores.rar
(Main sheet plus a few example cap files - to run the macro, click the man in the top left corner on "Till02")

I work in Learning Support, and a lot of my students like to have a coloured
background on worksheets, to alleviate tracking difficulties when reading the
text etc... However, although anything other than white is better (e.g. cream
/ off white)they tend to have their own personal colour which is best for
them.

What I'd like to be able to do is to quickly change the colour of cells to
suit their individual preferences. However, I often use other colours within
my worksheets to indicate "notes" cells, table headers, input cells, output
cells, etc... etc... If I select the whole sheet to colour, so that every
cell gets a new interior (fill) colour, this obviously affects those cells
that I want to stay the same as well. So I then have to re-select the 30-odd
input cells, colour those back to their original colour, then the output
cells etc...

This can take far too long to make it worthwhile. Is there a way to select
all of just one particular type of cell (e.g. all the "background" cells) and
not the other coloured cells that I want to stay the same? I've tried naming
the input cells etc... and using "Go To", but it would seem that it can only
refer to a certain number of different ranges, and if the cells are spread
non-adjacently through the sheet, one name is not enough to capture all of
them.

Ideally I'd like a macro or similar so that I can specify which interior
colour index to pick out, and select all of the cells in the worksheet that
meet that criteria, so that I can then format those cells only, leaving the
others untouched. Is there some sort of code to do this?

Even better, does anybody know of a way such that on pressing a button, say,
a dialog box could pop up to ask me what interior colour to look for (which I
would enter), then ask me what colour I want to change these cells to (which
I would enter again), and then select them all and make the change for me?

If this was possible, I could really do a good job in differentiating for
the students without it taking me forever to do so. Any help would be really
appreciated.

Many thanks in advance, Neil

Hi everyone,

I need some help / advice.
I'm trying to figure out how I can determine an equation that will describe
my data. I am measuring product performance (PP) vs. time (T). In general
the products can behave in at least 5 ways as they age:
1. an initial performance improvement followed by a slow decline.
2. no change with time.
3. a slow decrease in performance until a threshold is reached, then a rapid
decrease in performance.
4. a straight-line decrease in performance.
5. a rapid initial decrease in performance, followed by a levelling off.

It is also possible to have combinations of these if several components
degrade at different rates, or degradation of each component is triggered by
different events.

The data has already been collected, and plotted. I would like to be able
to determine an equation that will describe the data, so that PP can be
predicted at any given T. I would also like to be able to determine the
level of confidence, etc. for the equation.

I've tried using LOGEST and LINEST but they don't really produce very good
results with this data. I've tried using Trendlines but it is sometimes
difficult to find a line that matches the data well, and it doesn't provide
any supporting statistics. Is there a tutorial that could help me get a
better understanding? Are there plug-ins available that would help?

Thank you,
Rob

Hi All,

I have been trying to get this function to work, but I am beating my head against a wall. I need some help.

Attached is a sample of a spread sheet which we are trying to change. On the sheet called "move", there is a seris of data which I need to return to one column onto the sheet called "lookup". So far so simple.... however I dont want it to return all the data and I need it to go work through the sheet until the end. So put another way:

Column B3,C3,D3,E3,F3,G3 Needs to be retuned into one column (i.e into A1, A2,A3,A4,A5,A6. Following that B4,C4,D4,E4,F4,G4 to be follow on until I reach the end of the data. Now I know I can use Paste special - transpose but I could have 300 odd lines of data to change at least once a week, so I really need a function/formular to paste in.

Can anyone help??

Lots of lager for anyone who can and live in the UK!

Thanks

Biggsy

Hi,

I have a list of latitude/longitude points and I want to get the driving distance between each of these points. Preferably through the use of some code or macro which automatically generates the driving distance as opposed to the 'as the crow flies' distance.

If this is not possible could you embed a link into Excel which when clicked opens up google maps and calculates the distance between addresses in A1 and A2?

Really appreciate your help.

Regards,
David

Hey guys,

Need some help. Should be a quick hitter. Just need a syntax/function lesson.

I've declared a range which spans several columns. I paste the range in a new location and now want to add a formula offsetting (0,-1) from the first column in my range.

Here is a simplified piece of code
Dim myr As Range
Set myr = Selection
myr.Cells(1, 1).Offset(0, -1).FormulaR1C1 = "=RC[26]"
This fills only the top cell in my range. Since the range changes, I don't know how many rows it will be. How can I fix this?

I'm trying to learn something new. I could autofill (but this might create complications with only 1 row in the range) or I would have selected the (0,-1) offset cell and run a loop filling in the formula until the offset cell to the right is blank. But there has to be a way to use the range to finish it off. As always, thanks for the help - I really appreciate it.

Hello everybody, how are you?

First I want to congratulate you on your forum and work you've done you really are an inspiration to my team.

Today we have come a very interesting question because we want to add images as a data point on a graph using vba but we could not achieve perform this action successfully, you know some way in which we can do this using vba?

Thank you for your help

JuanM


No luck finding an answer? You could always try Google.