Free Microsoft Excel 2013 Quick Reference

Why Doesn't My Calculation Work

Sorry for such a simple question, but I give up.

My comments in the code tells where the error is, using either 1 or blank

Sub AutoCalcPosting()

'My activecell has a value of one in it.

    If (ActiveCell = 1) Then
         K12 = K12 + 1   'this row does not work
    
    Else
    
    If (ActiveCell = blank) Then
         j12 = 1 'this row does not work either
    End If
    End If

End Sub


Guys, why doesnt the below code working, i am simply asking the macro to delete entries which are less than 5 days old, but it doesnt do it, i dont get any errors either

Code:
 
  With Range("A1:J1")
        .AutoFilter Field:=6, Criteria1:="

I have a validation list Jan to Dec in cell D8.
Cell F8 has the following formula:
=IF(D8="","",VLOOKUP(D8,list!G:H,2,FALSE))
cell G8 has the following formula:
=IF(F8="","",VLOOKUP(F8,list!H:I,2,FALSE))
On sheet called List I have the following
col G has the months Jan to Dec
col H has the begining date of each month
col I has the end date of each month.

My problem is that whenever I select November (and only November) I get the
end date as 2 Dec 07. All the rest works great.
What is the problem here!!!!!!!......Help!
law

any ideas this is driving me insane!!!!!!

Private Sub Worksheet_Calculate()
Dim i As Integer
For i = 8 To 30
If Range("U" & i).Value > 0 And "E2" = "In Play" And "F2" = "Suspended" Then
If Range("U" & i).Value = WorksheetFunction.Min(Range("O5:O30")) Then
Range("Z" & i).Value = "W" And Range("AA" & i).Value = ("V1")
Else
Range("Z" & i).Value = "L" And Range("Z" & i).Value = ("V2")
End If
End If
Next i
End Sub


Howdie,

Or question

Two columns g and H
in G is a formulae that will result in "Match" or N/A
In H is a formulae that will result in True or false

In column I, I'd like to be able to know if there is a "match" in column
G OR if there is a TRUE in column I. IF this condition is met put an
"okay", or else put "xx"

Why doesnt my formulae work....

IF(OR(G9="Match",H9=TRUE),"okay","xx")

Thanks guys....

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Why doesnt my look up formula work? I have 2 colums of data as follows:

Col A..COLB
22%.....10
21%......9
20%......8
16%......7
12%......6
10%......5
8%........4
6%........3
4%........2
2%........1
1%........0

My lookup formula says: LOOKUP(I8,Scales!$A$3:$A$13,Scales!$B$3:$B$13). Cell I8 is 13.33%. So it should return a value of 6. Why does it return a value of 0 instead??

Cell G5 = Day()-F5
Cell G5 is titled "Days left"
F5 is titled "expires date" -formated 1/1/01
So the outcome to G5 is a integer in days

When I put
=IF(G5>90,"NO","YES")
into H5 which is titled "Send email"
H5 column does not change it just displays my equation.

This is becoming really fursterating!

I opened a new excel and it worked that is why I am not sure why it is not working for this one!

I opened some Excel files that I have been working on. All of these files
are pretty well established and the formulas have worked fine up until now.

When I tried to make a few changes this morning, none of the numbers would
update. After some searching around I discovered that my "Calculation" tab
had somehow changed to "Manual." I know I didn't change it and, even though
these worksheets are out on a project network at work, I don't think there's
any reason why anyone else would have changed it.

The problem has been resolved, but it is a little disturbing. Any idea how
or why something like this may have happened?

I opened some Excel files that I have been working on. All of these files
are pretty well established and the formulas have worked fine up until now.

When I tried to make a few changes this morning, none of the numbers would
update. After some searching around I discovered that my "Calculation" tab
had somehow changed to "Manual." I know I didn't change it and, even though
these worksheets are out on a project network at work, I don't think there's
any reason why anyone else would have changed it.

The problem has been resolved, but it is a little disturbing. Any idea how
or why something like this may have happened?

Hey guys,

I have some worksheet_change code that is not working properly for some reason and I cant for the life of me figure it out. The purpose of the change code is to make the oleobjects in the two adjacent cells visible based on whether or not the current cell has data (all of the oleobjects are named after the cell they are in.) The first part of the if deals with changes made to singular cells the second part of the if deals with changes made to multiple cells at once. It worked great until I added a custom function that basically turns disarrayed values into an array. When I added the custom function VB suddenly the second part of my worksheet_change vb that dealt with changes made to multiple cells stopped working (it didnt return any errors it just isnt changing the oleobjects when I paste several values.) Can anyone think of any reason the second part of the worksheet_change code would stop working because of the custom formula?

Private Sub Worksheet_change(ByVal Target As Range)

On Error GoTo ErrHandler
Application.EnableEvents = False
If Selection.Rows.count = 1 Then

'First Part

If Target.Value "" Then
With ActiveSheet.OLEObjects((Chr(Target.Offset(0, 1).column + 64)) & Target.Offset(0, 1).row)
.Visible = True
End With
With ActiveSheet.OLEObjects((Chr(Target.Offset(0, 2).column + 64)) & Target.Offset(0, 2).row)
.Visible = True
End With

Else

With ActiveSheet.OLEObjects((Chr(Target.Offset(0, 1).column + 64)) & Target.Offset(0, 1).row)
.Visible = False
.Object.Value = ""
End With
With ActiveSheet.OLEObjects((Chr(Target.Offset(0, 2).column + 64)) & Target.Offset(0, 2).row)
.Visible = False
.Object.Value = ""
End With

End If

Else

'Second Part

For Each c In Selection
If c.Value "" Then
With ActiveSheet.OLEObjects((Chr(c.Offset(0, 1).column + 64)) & c.Offset(0, 1).row)
.Visible = True
End With
With ActiveSheet.OLEObjects((Chr(c.Offset(0, 2).column + 64)) & c.Offset(0, 2).row)
.Visible = True
End With

Else

With ActiveSheet.OLEObjects((Chr(c.Offset(0, 1).column + 64)) & c.Offset(0, 1).row)
.Visible = False
End With
With ActiveSheet.OLEObjects((Chr(c.Offset(0, 2).column + 64)) & c.Offset(0, 2).row)
.Visible = False
End With

End If
Next c
End If
ErrHandler:
Application.EnableEvents = True

End Sub

Custom Formula:

Function ArrayFromDisarray(Source)
Dim v()
Dim r As Range
Dim x As Long
Dim y As Long

Application.Volatile True

ReDim v(1 To Application.Caller.count)

x = 1
For Each r In Source
If Application.IsError(Application.Search(",", r)) = False Then
v(x) = r
x = x + 1
End If
Next
For y = x To UBound(v)
v(y) = ""
Next

ArrayFromDisarray = Application.Transpose(v)
End Function

Thanks

Hi Folks.. I thought I had all bases covered but after rechecking calculations in my Coastguard Rescue Worksheet system, I've discovered a problem and I can't seem to work it out.

If a Rescue Officer is called out at 23:00 and is back at 04:00, this should equate to 5 hours worked.

It seems that if my times are all on one side or the other of a 24 hour cycle, my calculation work fine but it it breaks across the 24 hour (as above, it doesn't work.

A2=04:00
A1=23:00

Using (A2-A1)*24 give me -19.00 hours

My SS macro has a line:

s = (wks.Cells(c, 3) - wks.Cells(c, 2)) * 24 'calculates the duration of time worked

Is there any way of getting excel to calculate an elapsed time in hours when the start and end times roll over from one day to the next?

Hope that makes sense.

Declan

Excel 2003 (and earlier versions)
I frequently run into the problem where I type a formula in one cell and it
doesn't work (all I see is the text of the formula in the cell), yet I can
type the exact same formula in a different cell on the same spreadsheet and
get it to calculate.

For example - I'm trying to use this formula (where XA:XZ represents any
random range):

=SUMIF(XA:XZ,">750000")

And it won't work in all the cells. I've tried clearing the contents and
also deleting the cell, but nothing works. Any suggestions?

I am not very good at writing macros but this is a simple one and I don't know why it wont work, It is meant to request a number and a team find the player matching the number in the list and move it to that team

 
Sub Transfer()

PlayerID = InputBox("Enter Player ID", "Select Player", "")
TeamID = InputBox("Enter Team ID", "Select Team", "")

Sheets("Team " & TeamID).Select

If Range("N1") < 32 Then

Sheets("Player Pool").Select
For player = 1 To 90
If Cells(plrow + 1, 1) = PlayerID Then
Range(Cells(plrow + 1, 2), Cells(plrow + 1, 9)).Cut
Sheets("Team " & TeamID).Select

For Roster = 1 To 32
If Cells(Roster + 1, 4) = "" Then
Cells(Roster + 1, 4).Select
ActiveCell.PasteSpecial
End If
Next Roster

End If

Next player

Else
Response = MsgBox("No free spots in roster")
End If

End Sub


It works if the first If function is false, but if it is true nothing happens.

I have the following formula in cell I2 (copied down as far as needed and
copied across to AA2):

=INDEX($A2:$A$253,MATCH(I$1, $D$2:$D$253,0))

which when copied to J2 becomes (for the sake of discussion):
=INDEX($A2:$A$253,MATCH(J$1, $D$2:$D$253,0))

In cells I1 through AA1 are numbers (representing Districts) A small sample
of the Districts is:
1;2;4;5 and a small sample of the lookup table is:
Store;Description;Zone;District
184;Chula Vista South;11;1
559;Mission Gorge;11;1
157;Orange - Chapman;11;2
520;Lake Forest;11;2
523;Newport-N/Wpc;11;2
519;Encino;11;4
548;Porter Ranch, CA;11;4
167;Studio City;11;5
188;Rolling Hills Estates;11;5
196;San Pedro CA;11;5

What I wanted to do was to get all of the stores that belonged to a district
(rather than doing a copious amount of copying and pasting)

I started with a very similar formula, which did not work. I ended up with
the 1st store found, repeated when copied down. Formula is the same as the
first formula showed, but with an absolute reference for the Index row
instead of relative as it is now:

=INDEX($A$2:$A$253,MATCH(I$1, $D$2:$D$253,0))

I thought that if I made the row relative, it would work for the first
column, but when I copied the formula across and down, I would end up with
the same problem in the subsequent columns (I would get the correct first
store, but all of the stores under that would be the same as the first.) My
plan was, once I had the first column working, I would experiment with
different parts of the formula to try to get subsequent columns working;
maybe using something like the Offset of the match from the previous row.

Much to my surprise, this formula basically did what I wanted. Since the
number of stores per District varied, my plan was to copy the formula across
and then down to a point where I figured I would start getting #N/A errors
because, for a given column, there were no more stores in that District.

However, what I found was that when all of the stores for a District were
returned, it would start displaying stores for the next District. I could
quickly identify where I needed to start deleting formulas by looking at the
first value in the next column and seeing it was the same as the store in the
column I was looking at.

So my question is, why does this formula work (for subsequent columns?)
Also, why don’t I get #N/A values when there are no more stores for a given
district? And, are there alternative formulas that would also work?

BTW, I am eventually going to create named ranges from the different
districts, so will be changing the # in row 1 to District# (i.e. District1)
since you apparently can’t use numbers for Named Ranges. Also, I discovered
(but should have realized before I tried) that D# won’t work (i.e. D1, D2,
etc.) as those look like cell references. So, so far, my new formula looks
like:

=INDEX($A2:$A$253,MATCH(VALUE(RIGHT(K$1,LEN(K$1) - 8)), $D$2:$D$253,0))

--
Kevin Vaughn

only problem I seem to have is that I don't seem to have the function. It
doesnt appear as a valid function. Even though I have Excel2003 and the
latest updates

thnx

Excel 2003 (and earlier versions)
I frequently run into the problem where I type a formula in one cell and it
doesn't work (all I see is the text of the formula in the cell), yet I can
type the exact same formula in a different cell on the same spreadsheet and
get it to calculate.

For example - I'm trying to use this formula (where XA:XZ represents any
random range):

=SUMIF(XA:XZ,">750000")

And it won't work in all the cells. I've tried clearing the contents and
also deleting the cell, but nothing works. Any suggestions?

Sample sheet attached. Basically, I want to know why averaging my averages is not coming to the correct number. I have a data set that calculates an average. I am then averaging the averages of that data. Each row on the sheet holds the same weight of the total. I then turn around and add up the total data and average that to get the average of the averages, and the number is different. I have set the decimal places all the same.

Hi All,

I am needing to calculate the hours between 2 dates (including weekend), but only counting the working hours.

I have had a go, but my fomula seems to be calculating a different answer, so somewhere along the lines it's just not right!!!

I have a start date of 29/01/2012 18:34:00 and end date of 30/01/2012 16:39:00, with working hours being Mon - Sun 08:00:00 - 17:30:00.

I manually worked this out to be 18:09:00, but as you can see on the attachment my formula works it out as 17:05:00!!!

Anyone see where its gone wrong!!! (has it subtracted the 1:04 on the start date passed teh 17:30:00 threshold!) as i don't want this to happen!!

cheers

Donna

How should I calculate working days, hours and minutes between two dates?I am creating a 'System Outage Logger' and I am trying to calculate the Network Days and Network Hours between two dates with times. I want to know how many business days, hours and minutes are between the two date/time stamps to determine lost production hours given the ff.:

Variables:
- 'Start time' (A1)
- 'Time resolved' (B1)
- 'Duration' (C1)

Constants:
Work Days = Monday to Friday
Work Hrs = 6:30 AM to 3:30 PM (1 hour 30 minute lunch break)
Public Holidays = (AA1:AA30)

Formats:
Start time: dd/mm/yyyy hh:mm AM/PM
Time resolved: dd/mm/yyyy hh:mm AM/PM
Duration: d "Days" hh:mm

Any help would be greatly appreciated!

MLEGASPI

Hi there,

Please help me with a formula for the situation below.

Work week is from Sunday to Thursday (8.30 am to 5.30 pm with one hour lunch break)

Example:
StartDate: 01/06/2010 10:00 AM in A1
EndDate: 09/06/2010 04:00 PM in B1

I need to calculate work hours (in decimal format e.g 30.75 hours) between the above start and end date_time considering a work day is of 8 hours and non-working days are Fridays & Saturdays along with any HolidayList.

I'm Feeling Lucky

Everything compiles, no error messages come up, the sort and AutoFilter method both work but the AdvancedFilter won't (doesn't get rid of the duplicate points).
I've tried sub stepping the two filters but it's stil a no go! Why can't they work under the same toggle?
Code:
Private Sub ToggleButton1_Click()
    ToggleButton1 = Shape your Data
    If ToggleButton1 = True Then
        Range("A1:C4000").Sort Key1:=Range("B1"), Key2:=Range("A1"), Header:=xlYes
        Range("A1:B4000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:B4000"), Unique:=True
        Range("A1").AutoFilter Field:=3, Criteria1:="

Can somebody please tell me why is my formula deleting my header?

Code:
What's wrong with this code?

Range("D1").AutoFilter , Field:=4, Criteria1:="Delete"
    Rows("2:65536").Delete Shift:=xlUp
    Range("D1").AutoFilter Field:=4
    Selection.AutoFilter
    Cells.Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlTrue, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal


I am working on cleaning up code from the recorder and I would like to know why this does not work.

Original from the recorder:
Code:
    Columns("O:O").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
What I tried to clean it up to:
Code:
    Columns("O:O").Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
What am I missing here? I am in the process of learning and want to know why this does not work.
Can this be cleaned up from the original to make it more efficient?

How to leave the lines on my exel work sheet

I have collected about 1000 points of data from the HyperTerminal program and
saved the data as two file formats one being .txt and the othet .csv. When I
load the .csv file I am not taken through the wizard which is okay. When I
load the .txt file I am. Anyways, my question is.. why will my cells not
format? Currently this is what the cell looks like that i want to format
"01/12/06 15:38:17" and there is one for every second for about 1000 seconds.
Excel reads these cells as general cells, I am curious why I can not change
them to a date format. The reason I want them to be a date format is because
I will be graphing this informat and it needs to be acurate as I am writing a
manual on how to setup a "Internal Data Logger for a portable D.O. analyzer"
and how to send the information to a computer. Any help?

Recap: Why will my date/time cell "01/12/06 15:38:17" not format to lets say
"Thursday, January 12, 2006?

I do believe the cells are locked.. but when I unlock them.. still nothing
happens.