Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

If function need false to return a blank cell not 0 Results

I have cells containing employee names, production hours per job and
produciton quantities per job for multiple jobs. I need a function to read
the production hours cell and
1. process a formula is there is a value (TRUE)
2. return a blank cell, rather than zero is there is no value (FALSE)
My ultimate goal is to generate an average productivity % across multiple
jobs and multiple employees.

Example:
Name Hrs/job 1 Parts Hrs/Job 2 Parts Prod.% Job 1 Prod.% Job 2
Avg. Prod
Joe 4 10 75%
75%
Mary 4 20
100% 100%

Any help will be greatly appreciated!

Thanks. Maestro

I've googled this and i'm stumped. In short, I have a worksheet to display information from another worksheet using a drop menu. The following function is returning a 0 (or blank after tools->view zero cell blank checked), instead of the TRUE argument.

=IF(ISNA(VLOOKUP(B302,ACCOUNTS!$A$2:$K$300,4,FALSE)),"Not Found",(VLOOKUP(B302,ACCOUNTS!A2:K300,4,FALSE)))

The cell in question is blank, nothing in there. However, if I type something then that will appear.

Why can't I get it to say "N/A" instead of returning a blank, but it WILL show if something is there?

Thanks for any and all help! Let me know if clarification is needed.

I had been using Harlan Grove's PULL function in order to link to data in another workbook that wasn't necessarily currently
open in Excel.

The PULL function allows you to specify the details of your linked range as a cell value (unlike a direct link), similar to the
INDIRECT function. However, INDIRECT doesn't work with closed workbooks. I like Harlan's PULL function because the code is
open and can be easily copied and pasted into additional VBA workbooks as required so that no add-ins are needed. (I have not
had much experience with using INDIRECT.EXT via the MoreFunc add-in, although it is possible to "attach" MoreFunc to a workbook
so that functions can be used without the add-in being installed.)

The PULL function works well - however, it can be slow to use when returning large ranges of cells. For example, we had a
spreadsheet that was taking 5 minutes to update when using PULL to refer to a range of 3000 cells. So I looked into creating
an alternative function that would be more suited to my circumstance. I feel I was successful and wanted to share my findings
here, since the publishing of the PULL function was very helpful to me. (Harlan - I hope that you don't mind that I have used
some of your ideas in the new function.)

The PULL function uses the ExecuteExcel4Macro command to get values from a cell from a closed workbook. The LINKEDRANGE
function that I present here differs from PULL in that it actually opens the linked workbook (in a separate Excel instance,
since spreadsheets cannot be normally be opened in a UDF), gets the values it needs and closes the workbook.

LINKEDRANGE may be faster than PULL when returning ranges of values from linked workbooks. LINKEDRANGE may be slower than PULL
when returning single values or small ranges.

Furthermore, LINKEDRANGE can be used to link to named ranges that refer to a range of more than one cell. (PULL works with
named ranges that refer to a single-cell only.)

The VBA code and sample spreadsheets are located he
http://www3.sympatico.ca/sstackho/LinkedRange.zip

The .bas file can be used for easy importing into spreadsheets.

Although I have tested the code on a couple of machines, it certainly will not be as bulletproof as the PULL function. Harlan
has added several layers of armor to the PULL function over the years so that it works on more Excel versions and more
operating systems. Since I don't fully understand all of the error-checking logic in the PULL function, I wasn't comfortable
adding it to the LINKEDRANGE function. Harlan or anybody: please feel free to make this function better by adding any
additional logic as you see fit.

I will paste the code below, although it might not look very good with line-wrapping, etc. The code is also available at the
link above.

'-------------------------------------------------
'-------------------------------------------------

Option Explicit

Function LINKEDRANGE(Link As String) As Variant

' Developed by Shawn Stackhouse
' Inspired (and partially developed) by Harlan Grove and his PULL function
' that was in turn inspired by Bob Phillips and Laurent Longre
'-----------------------------------------------------------------
'This code is free software; you can redistribute it and/or modify
'it under the terms of the GNU General Public License as published
'by the Free Software Foundation; either version 2 of the License,
'or (at your option) any later version.
'-----------------------------------------------------------------

' Version History
'
' v1 - 2006-08-24
' v1.1 - 2006-08-25
' - changed structure to have the function accept a single input and split out the LINKREFERENCE logic to a separate
function
' - fixed problem with conflict between workbook-level and worksheet-level named ranges

' Purpose:
' This user-defined function can be used to get values from another spreadsheet, even if it is not open.
'
' This provides similar functionality to using regular Excel links, but allows the locations and names of
' linked workbooks to be specified via cells.
'
' LINKEDRANGE provides similar functionality to Harlan Grove's PULL function.
'
' LINKEDRANGE differs from Harlan Grove's PULL function in that LINKEDRANGE actually opens the linked workbook (in a
' separate Excel instance, since spreadsheets cannot be normally be opened in a UDF) to get the linked values.
' Furthermore, LINKEDRANGE can be used to link to named ranges that refer to more than one cell.
'
' LINKEDRANGE may be faster than PULL when returning ranges of values from linked workbooks. LINKEDRANGE may
' be slower than PULL when returning single values or small ranges.

' **** NOTE ****
' This function requires a full recalculation (Ctrl+Alt+F9) in order to update values

' Function Output:
' - a range of variable size
' - to return a range of cells, use LINKEDRANGE as a formula array (Ctrl+Shift+Enter)

Dim xlapp As Object, xlwb As Workbook, xlws As Worksheet
Dim r As Range, iChrPos As Long
Dim Directory As String, WorkbookName As String, WorksheetName As String, WorksheetRange As String
Dim NamedRangeRefersTo As String

On Error GoTo CleanUp

' Check to see if the referenced range is currently open in this Excel instance,
' by using an Evaluate function. If the function returns an error, then the
' range is not open (or the range is invalid).

' Do an EVALUATE on Link to see if the referenced range is currently open in this Excel instance.
' The Evaluate function will return an error if the range is not open
LINKEDRANGE = Evaluate(Link)

' If the range is not open (or invalid), an error will be returned from the above statement and
' the following section will be processed

If CStr(LINKEDRANGE) = CStr(CVErr(xlErrRef)) Then

' Let's decipher the Directory, WorkbookName, WorksheetName and WorksheetRange from the Link string.
' The Link string can be in a variety of formats.

' If the first character is not a single quote, then a Directory has not been defined.
If Left(Link, 1) "'" Then
Exit Function
End If

' Remove the leading single quote
Link = Mid(Link, 2, Len(Link) - 1)

' the Directory name will end at the last occurrence of ""
' find last occurrence of ""
iChrPos = InStrRev(Link, "")
Directory = Left(Link, iChrPos)
Link = Mid(Link, iChrPos + 1, Len(Link) - iChrPos)

' The next character will be a "[" unless the worksheet name has not been defined (and a workbook-level named range is
being used)
If Left(Link, 1) = "[" Then

' a worksheet is defined, the Workbook name will be until "]"
iChrPos = InStr(Link, "]")
WorkbookName = Mid(Link, 2, iChrPos - 2)
Link = Mid(Link, iChrPos + 1, Len(Link) - iChrPos)

' the worksheet name will be until a single quote
iChrPos = InStr(Link, "'")
WorksheetName = Mid(Link, 1, iChrPos - 1)
Link = Mid(Link, iChrPos + 2, Len(Link) - iChrPos)

Else

' a worksheet is not defined
WorksheetName = ""

' the workbook name will be until a single quote
iChrPos = InStr(Link, "'")
WorkbookName = Mid(Link, 1, iChrPos - 1)
Link = Mid(Link, iChrPos + 2, Len(Link) - iChrPos)

End If

' the WorksheetRange will be what is left over in the Link string
WorksheetRange = Link

' Create a new Excel instance
Set xlapp = CreateObject("Excel.Application")

' Open the linked workbook as read-only and do not update any links in the linked workbook.
' If the workbook doesn't exist, an error will be triggered.
Set xlwb = xlapp.Workbooks.Open(Directory & WorkbookName, UpdateLinks:=False, ReadOnly:=True)

' If a workbook-level name has been used (i.e. no WorksheetName was specified), then we need to refer
' to the RefersTo property of the named range to ascertain the proper worksheet.
If WorksheetName = "" Then

' temporarily add a blank worksheet to avoid problems with worksheet-level named ranges
Set xlws = xlwb.Worksheets.Add

NamedRangeRefersTo = xlwb.Names(WorksheetRange).RefersTo
' find the '!' in the range
iChrPos = InStr(1, NamedRangeRefersTo, "!")
WorksheetName = Mid(NamedRangeRefersTo, 2, iChrPos - 2)

'check for single quotes around WorksheetName
If Left(WorksheetName, 1) = "'" Then
WorksheetName = Mid(WorksheetName, 2, Len(WorksheetName) - 2)
End If
End If

' Refer to the WorksheetName worksheet.
' If the worksheet doesn't exist, an error will be triggered.
Set xlws = xlwb.Worksheets(WorksheetName)

' Refer to the WorksheetRange range on the worksheet.
' If the range doesn't exist or is invalid, an error will be triggered.
Set r = xlws.Range(WorksheetRange)

LINKEDRANGE = r

End If

CleanUp:
Set xlws = Nothing
If Not xlwb Is Nothing Then xlwb.Close 0
Set xlwb = Nothing
If Not xlapp Is Nothing Then xlapp.Quit
Set xlapp = Nothing

End Function

'-------------------------------------------------
'-------------------------------------------------

' In order to assist with building the 'Link' parameter above (or 'xref' for PULL),
' I created another little UDF below that returns the Link parameter based on
' directory, workbook, worksheet and range inputs.

'-------------------------------------------------
'-------------------------------------------------

Function LINKREFERENCE(Directory As String, WorkbookName As String, WorksheetName As String, WorksheetRange As String) As
String

' This function can used as a helper for the LINKEDRANGE function. This function takes in information about the linked range
' and returns a link reference in the form needed by LINKEDRANGE.

' Function Inputs:
'
' Directory - the full path that contains the workbook from which values will be pulled
' - trailing "" is optional
' - e.g. C:LinkedData
' - relative directories can be used
' - e.g. "C:DummyDirectory..LinkedData" (evaluates to C:LinkedData)
'
' WorkbookName - the name of the workbook from which values will be pulled
' - e.g. LinkedWorkbook.xls
'
' WorksheetName - optional - leave blank if referring to a workbook-level named range
' - the name of the worksheet from which values will be pulled
' - e.g. LinkedSheet
'
' WorksheetRange - the cell range or named range from which values will be pulled
' - e.g. A1:E5
' - e.g. $A$1:$E$5
' - e.g. LinkedNamedRange

' Function Output:
' LINKEDRANGE - a string that contains the link reference in the form used by LINKEDRANGE

Dim sLinkReference As String

On Error GoTo CleanUp

' If the Directory, WorkbookName or WorksheetRange fields are not defined, then exit the function immediately.
If IsEmpty(Directory) Or IsEmpty(WorkbookName) Or IsEmpty(WorksheetRange) Then
Exit Function
End If

' Trim the inputs of any excess spaces
Directory = Trim(Directory)
WorkbookName = Trim(WorkbookName)
WorksheetName = Trim(WorksheetName)
WorksheetRange = Trim(WorksheetRange)

' check the Directory string and append a '' to its end if it doesn't already have one
If Right(Directory, 1) "" Then
Directory = Directory & ""
End If

' prefix with a single quote
sLinkReference = "'" & Directory ' e.g. 'C:LinkedData

' A worksheet does not need to be specified if using a workbook-level name.
' The format of the 'xref' statement to be evaluated differs if the workbook is not defined.
' Add a "[" if the worksheet name is defined.
If WorksheetName "" Then
sLinkReference = sLinkReference & "[" ' e.g. 'C:LinkedData[
End If

' Add the workbook name
sLinkReference = sLinkReference & WorkbookName ' e.g. 'C:LinkedData[LinkedWorkbook.xls

' Add a "]" if the worksheet name is defined
If WorksheetName "" Then
sLinkReference = sLinkReference & "]" ' e.g. 'C:LinkedData[LinkedWorkbook.xls]
End If

' Add the worksheet name (may be blank)
sLinkReference = sLinkReference & WorksheetName ' e.g. 'C:LinkedData[LinkedWorkbook.xls]LinkedSheet

' Add a single quote and exclamation point
sLinkReference = sLinkReference & "'!" ' e.g. 'C:LinkedData[LinkedWorkbook.xls]LinkedSheet'!

' Add the linked range (cell range or named range)
sLinkReference = sLinkReference & WorksheetRange ' e.g. 'C:LinkedData[LinkedWorkbook.xls]LinkedSheet'!A1:E5

LINKREFERENCE = sLinkReference

CleanUp:

End Function

'-------------------------------------------------
'-------------------------------------------------

' Finally, here's a small UDF to return the current workbook directory, which can be helpful
' for creating relative links. This is preferable to using the CELL("filename", A1) function
' since CELL("filename") is volatile (even though Microsoft claims it is not!)

'-------------------------------------------------
'-------------------------------------------------

Function ThisWorkbookDirectory() As String
' This function returns the directory of this workbook.

Dim sFullName As String
Dim iChrPos As Integer, iStrPos As Integer

sFullName = ThisWorkbook.FullName

' find last occurrence of ""
iChrPos = InStrRev(sFullName, "")
ThisWorkbookDirectory = Left(sFullName, iChrPos)

End Function

-------------------------------------------------

I hope that someone finds this helpful!

Thanks,
Shawn Stackhouse

--
----------------------------------------------
Posted with NewsLeecher v3.0 Final
* Binary Usenet Leeching Made Easy
* http://www.newsleecher.com/?usenet
----------------------------------------------

Hello:

My spreadsheet counts occurrences of incidents by month; I need to average
the number of actual occurrences without including zero values in the average.

the months row I want to average is populated by:
=IF(ISBLANK(Month_Vic_2005),"",COUNTIF(Month_Vic_2 005,"1"))

where Month_Vic_2005 is a named range that populates from another named
range that contains dates of occurrence. (COUNTIF "1" is replaced by 2,3 4,
etc. for later months in series.) I suspect the problem is that the ISBLANK
function is returning FALSE because the named range in question contains the
formula:
=IF(ISBLANK(Vic_Incident_Date_2005),"",MONTH(Vic_I ncident_Date_2005)).

Where there is no occurrence (say for April) I want the cell value blank,
not zero. The zeroes are causing the average function to return a false value
for the months for which I actually have data.

I'm probably approaching this from the wrong end; I've tried IF(range = 0,
>0,

Hi All

I have a worksheet that interprets values from a #255-#0 number system
and need to convert them to a 0%-100% number system and visa versa.
#255 = 0% . #250 = 1% and so on.

So I have made 2 columns of numbers, one for each number system so each
value from one number system lines up (closely) with it's corresponding
value from the other. Column R is 0-100% Column S is 255- 0.

I am using this formula now:
=IF(ISERROR(INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$ 3094,FALSE),1)),"
",INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE ),1))

The problem I am having is that the 255 - 0 column may not have the
exact value I am looking for or visa versa, and the function returns a
blank cell.

How do I change the function to get the closest value either higher or
lower than the exact number? At the most I am missing exact values by
..01. Can OFFSET be added? If so how?

Thanks in advance!

Bob

Hi All

I have a worksheet that interprets values from a #255-#0 number system
and need to convert them to a 0%-100% number system and visa versa.
#255 = 0% . #250 = 1% and so on.

So I have made 2 columns of numbers, one for each number system so each
value from one number system lines up (closely) with it's corresponding
value from the other. Column R is 0-100% Column S is 255- 0.

I am using this formula now:
=IF(ISERROR(INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1)),"
",INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1))

The problem I am having is that the 255 - 0 column may not have the
exact value I am looking for or visa versa, and the function returns a
blank cell.

How do I change the function to get the closest value either higher or
lower than the exact number? At the most I am missing exact values by
..01. Can OFFSET be added? If so how?

Thanks in advance!

Bob

Hello:

My spreadsheet counts occurrences of incidents by month; I need to average
the number of actual occurrences without including zero values in the average.

the months row I want to average is populated by:
=IF(ISBLANK(Month_Vic_2005),"",COUNTIF(Month_Vic_2005,"1"))

where Month_Vic_2005 is a named range that populates from another named
range that contains dates of occurrence. (COUNTIF "1" is replaced by 2,3 4,
etc. for later months in series.) I suspect the problem is that the ISBLANK
function is returning FALSE because the named range in question contains the
formula:
=IF(ISBLANK(Vic_Incident_Date_2005),"",MONTH(Vic_Incident_Date_2005)).

Where there is no occurrence (say for April) I want the cell value blank,
not zero. The zeroes are causing the average function to return a false value
for the months for which I actually have data.

I'm probably approaching this from the wrong end; I've tried IF(range = 0,
>0, <1, is null, NOT.

Tia for the help.

Score card managers working rev2.xlsI am currently trying to solve the issue in column G of Manager Tab. Formula currently gives store number is criteria of C or V is met and Employee i.d. matches in shrink tab. If employee i.d. does not appear at all in the Shrink tab the formula returns ID Not in list. If the employee i.d. does appear in the Shrink tab but the audit "type" criteria of C or V is not met it is returning a 0 (zero). I need to change the formula to return the cell empty instead of zero. Any ideas?

=IF(ISNA(VLOOKUP(E2,Shrink!$A:$K,11,0)),"ID Not in list",LOOKUP(E2,CHOOSE({1,2},0,LOOKUP(2,1/((Shrink!$A$2:$A$1000=MANAGERS!E2)*(ISNUMBER(MATCH(Shrink!$K$2:$K$1000,{"C","V"},0)))),Shrink!$G$2:$G$1000))))

Hi everyone - first time poster here and as probably most posts here are
I am looking for help.

Excel does not recognise €0.00 as the value 0. I need this for the
following. Basicaly I have created a loan amortisation sheet breaking
down each monthly payment into repayment amount / principal / interest
etc etc. I am using the PMT formula to calculate each monthly payment
as it goes.

=-PMT(D11/12,$I$4*12-A10,C11)
D11 being a cell referencing the interest rate / 12 to make monthly.
$I$4*12-A10 is calulating the payments left I4 is the original loan
term less the the number of payments made A10 to give how many are left
) and C11 is the balance left on the loan.

I hope this is easy enough to follow
It works a dream when I create exactly enough rows for the amount of
payments. My problem occurs when I change the original loan term in I4.
I would like to be able to change the loan term and have the appropriate
rows of calculations disappear / go blank when not needed. ie if I
change it from a 35 year term to a 30 year one that the last 60 rows of
calculations disappear. If I decrease this term I get a #Div/0! error
across the formulas for the time period past the new lesser loan time.
I am assuming this is within the PMT formula when the principle is at 0
as the loan has been paid off.

I tried to find a function that would check wether or not a value was
at 0 elsewhere and if so to return 0 itself but if the value elsewhere
was not at 0 to include that figure within a function of the original
cell.

I tried to use the IF function and insert the pmt function within it.
ie to see if C11 was equal to 0 and if it was to return 0 as a value
for the function but if it did not equal 0 to calculate the original
PMT function. =-PMT(D11/12,$I$4*12-A10,C11). but I cannot make the PMT
functtion work within one of the IF true or false options.

Furthermore it also appears that even in a simple IF function excel
does not recognise €0.00 as 0. eg if I put in =IF(A!=0,"yes","no") and
A1 contains €0.00 it gives the value of no. If I put it as
=IF(A!=€0.00,"yes","no") it is invalid and finally for
=IF(A!="€0.00","yes","no") it again shows no.

I am looking for any way to either get excel to recognise a zero in
currency to be the same as 0 or to get a PMT function active within IF
( preferably both as I suspect I need both of these at the same time to
do this for me )

I really hope that you can follow the above - it makes more sense in
your own mind than when you try to explain it.

Many thanks in advance for any advice.

--
CmTaz
------------------------------------------------------------------------
CmTaz's Profile: http://www.excelforum.com/member.php...o&userid=32169
View this thread: http://www.excelforum.com/showthread...hreadid=519180

Hi everyone - - - I thought that I had this working completely, but the test to see if the "file is already open" isn't working.

Something is wrong with my example.

I have marked in the code where the problem is. Everything else works.
It's marked as such:

HERE IS MY PROBLEM - - - - - - - - - - - - - - - - - - - - - - - - - - -
Can someone please tell me what I'm doing wrong ?

Sub NewExcelWithWorkbook()
    Dim oXL    As Object    'This is needed to open a new instance of Excel.
    'Without it, the file is only opened as a new Window
    Dim OpenFileName    '<-this isn't used
    Dim testFileFind As String
    Dim oWB    As Workbook
    Dim cl     As Range
    'This reads the cell 1 column to the Left so the path & file name can be read
    Set cl = ActiveCell.Offset(0, -1)


    'The following tests for a blank cell and ends processing
    'It is needed because dir() function will not work with a blank.
    If Len(Trim(cl)) = 0 Then
        MsgBox "You have not entered a Path and File name."
        End
    End If

    'The following tests for the existance of the file
    testFileFind = Dir(cl)
    
    'If the file is not found there will be nothing in the variable and processing ends.
    If Len(testFileFind) = 0 Then
        MsgBox "Invalid selection." & Chr(13) & _
               "Filename " & cl.Value & " not found"
        End
    End If




'HERE IS MY PROBLEM - - - - - - - - - - - - - - - - - - - - - - - - - - -
        'THIS TESTS TO SEE IF THE FILE IS ALREADY OPEN OR NOT
    If FileAlreadyOpen("cl") = True Then
        MsgBox "File is already open"
    End
    Else
    End If






    'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
    Set oXL = CreateObject("Excel.Application")

    'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
    oXL.Visible = True

    Set oWB = oXL.Workbooks.Open(cl)

End Sub
Please take note: The path and file name constantly change.

The following Function IS CALLED to check if the File Is Open - or Not

'It all works if the Path & File Name are hard coded. I have to have it working with changing Paths & File names.

Function FileAlreadyOpen(FullFileName As String) As Boolean
' returns True if FullFileName is currently in use by another process
' example: If FileAlreadyOpen("C:FolderNameFileName.xls") Then...
Dim f As Integer
    f = FreeFile
    On Error Resume Next
    Open FullFileName For Binary Access Read Write Lock Read Write As #f
    Close #f
    ' If an error occurs, the document is currently open.
    If Err.Number <> 0 Then
        FileAlreadyOpen = True
        Err.Clear
        'MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
    Else
        FileAlreadyOpen = False
    End If
    On Error GoTo 0
End Function


Hi everyone - first time poster here and as probably most posts here are I am looking for help.

Excel does not recognise €0.00 as the value 0. I need this for the following. Basicaly I have created a loan amortisation sheet breaking down each monthly payment into repayment amount / principal / interest etc etc. I am using the PMT formula to calculate each monthly payment as it goes.

=-PMT(D11/12,$I$4*12-A10,C11)
D11 being a cell referencing the interest rate / 12 to make monthly.
$I$4*12-A10 is calulating the payments left I4 is the original loan term less the the number of payments made A10 to give how many are left ) and C11 is the balance left on the loan.

I hope this is easy enough to follow
It works a dream when I create exactly enough rows for the amount of payments. My problem occurs when I change the original loan term in I4. I would like to be able to change the loan term and have the appropriate rows of calculations disappear / go blank when not needed. ie if I change it from a 35 year term to a 30 year one that the last 60 rows of calculations disappear. If I decrease this term I get a #Div/0! error across the formulas for the time period past the new lesser loan time. I am assuming this is within the PMT formula when the principle is at 0 as the loan has been paid off.

I tried to find a function that would check wether or not a value was at 0 elsewhere and if so to return 0 itself but if the value elsewhere was not at 0 to include that figure within a function of the original cell.

I tried to use the IF function and insert the pmt function within it. ie to see if C11 was equal to 0 and if it was to return 0 as a value for the function but if it did not equal 0 to calculate the original PMT function. =-PMT(D11/12,$I$4*12-A10,C11). but I cannot make the PMT functtion work within one of the IF true or false options.

Furthermore it also appears that even in a simple IF function excel does not recognise €0.00 as 0. eg if I put in =IF(A!=0,"yes","no") and A1 contains €0.00 it gives the value of no. If I put it as =IF(A!=€0.00,"yes","no") it is invalid and finally for =IF(A!="€0.00","yes","no") it again shows no.

I am looking for any way to either get excel to recognise a zero in currency to be the same as 0 or to get a PMT function active within IF ( preferably both as I suspect I need both of these at the same time to do this for me )

I really hope that you can follow the above - it makes more sense in your own mind than when you try to explain it.

Many thanks in advance for any advice.

Hi guys, I am having a problem with my code. I am getting a 'Byref Argument Type Mismatch' error and I am not sure why. The code works when I do not declare and set the 'intFindColumnMatch_Column' variable, but it does not work when it is set and declared in my function call. I sometimes need the 'intFindColumnMatch_Column' variable so I know what column to reference, but sometimes I might just need to know if it exists, hence why it is optional.

Any help would be much appreciated. Thanks in advance.


	VB:
	
 
Dim strFindColumnMatch_Value As String 
Dim blnFindColumnMatch_ErrorReport As Boolean 
Dim intFindColumnMatch_Column As Integer 
 
strFindColumnMatch_Sheet = strSheetName 
strFindColumnMatch_Value = "Column Name" 
 
blnFindColumnMatch_ErrorReport = True 
 
Call FindColumnMatch(strFindColumnMatch_Sheet, strFindColumnMatch_Value, blnFindColumnMatch_ErrorReport,
intFindColumnMatch_Columnn) 
 
Public Function FindColumnMatch(strFindColumnMatch_Sheet As String, strFindColumnMatch_Value As String, Optional
blnFindColumnMatch_ErrorReport As Boolean, _ 
    Optional blnFindColumnMatch As Boolean, Optional intFindColumnMatch_Column As Integer, Optional lngFindColumnMatch_Row As
Integer) 
     
     '----------Purpose----------
     
     'To check if a value exists within a column specified.
     
     '----------Variables----------
     
     'Used to check if a value exists within a column specified.
    Dim strFindColumnMatch_ColumnValue As String 
     
     'Set variable values if not specified.
    If lngFindColumnMatch_Row = 0 Then lngFindColumnMatch_Row = 1 
     
     '----------The Sub----------
     
     'Search each column of the 'intFindColumnMatch_Row' in the sheet 'strFindColumnMatch_Sheet' to check for the value
'strFindColumnMatch_Value'.
    For intFindColumnMatch_Column = 1 To 256 
         
         'Store the value of the column that is currently being checked.
        strFindColumnMatch_ColumnValue = Sheets(strFindColumnMatch_Sheet).Cells(lngFindColumnMatch_Row,
intFindColumnMatch_Column).Value 
         
         'If the value 'strFindColumnMatch_Value' is equal to 'strFindColumnMatch_ColumnValue', a match has been found and
'blnFindColumnMatch' needs to be set to true.
        If strFindColumnMatch_ColumnValue = strFindColumnMatch_Value Then 
             
             'Set 'blnFindColumnMatch' to true as a match has been found.
            blnFindColumnMatch = True 
             
             'As a match has been found, exit the function.
            Exit Function 
             
             'If the value 'strFindColumnMatch_Value' is blank, a match has not been found and 'blnFindColumnMatch' needs to
be set to false.
        ElseIf strFindColumnMatch_ColumnValue = "" Then 
             
             'Set 'blnFindColumnMatch' to false as a match has not been found.
            blnFindColumnMatch = False 
             
             'If 'blnFindColumnMatch_ErrorReport' is true, inform the user that the column must be present to continue.
            If blnFindColumnMatch_ErrorReport = True Then 
                 
                MsgBox "The column '" & strFindColumnMatch_Value & "' could not be found in the '" & strFindColumnMatch_Sheet
& " ' sheet, meaning the report is corrupt. " _ 
                & "Please delete the report and start again.", vbInformation, "Column Not Found - Report Corrupt" 
                End 
                 
            End If 
             
             'As no match has been found, exit the function.
            Exit Function 
             
        End If 
         
         'Move on to the next Column.
    Next intFindColumnMatch_Column 
     
     '----------End Sub----------

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to use a macro to take sales reps' opportunity data and filter by the reps and then copy each rep's opportunities to a new sheet. I have the macro below that works except I need it to copy the first 3 rows (these are the header rows) to every sheet and it really only works for the first row plus preserve the formating.

Also...this macro creates a new sheet for the header in addition to the reps..do you see any way to stop that?


	VB:
	
 
     '''/// RETURNS 'TRUE' IF THE SHEET EXISTS IN THE WORKBOOK
    Dim x As Object 
    On Error Resume Next 
    Set x = ActiveWorkbook.Sheets(sname) 
    If Err = 0 Then SheetExists = True _ 
Else SheetExists = False 
End Function 
 
Sub CopyToUniqueSheets() 
    Dim LstRw As Long, _ 
    ContractRng As Range, _ 
    ContractNum As Range, _ 
    ThsSht As String, _ 
    sht As Worksheet, _ 
    ShtExists As Boolean 
     
    Application.ScreenUpdating = False 
    ActiveSheet.AutoFilterMode = False 
     
     '''/// DETERMINE THE ACTIVESHEET'S NAME
    ThsSht = ActiveSheet.Name 
     
     '''// DETERMINE THE LAST ROW OF DATA
    With Sheets(ThsSht) 
        LstRw = .Cells(Rows.Count, "A").End(xlUp).Row 
         
         '''/// SET THE RANGE OF CONTRACT NUMBERS
        Set ContractRng = Range(.Cells(1, "A"), .Cells(LstRw, "A")) 
         
         '''/// LOOP THROUGH EACH CONTRACT NUMBER IN THE CONTRACT RANGE
        For Each ContractNum In ContractRng 
             
             '''/// SKIP ANY BLANK CELLS
            If IsEmpty(ContractNum) Then Goto SkipIt 
             
             '''/// SKIP DUPLICATE CONTRACT NUMBERS
            If ContractNum.Row  1 Then 
                If ContractNum.Value = ContractNum.Offset(-1).Value Then Goto SkipIt 
            End If 
             
             '''/// IF THE SHEET DOESN'T EXIST, CREATE ONE & NAME IT
            If Not SheetExists(ContractNum.Value) Then 
                Sheets.Add After:=Sheets(Sheets.Count) 
                ActiveSheet.Name = ContractNum.Value 
            End If 
             
             '''/// FILTER & COPY THE DATA TO IT'S OWN SHEET _
            (PASTE IT 1 BLANK ROW BELOW ANY EXISTING DATA) 
            ContractRng.AutoFilter Field:=1, Criteria1:=ContractNum 
            ContractRng.SpecialCells(xlCellTypeVisible).EntireRow.Copy _ 
            Sheets(ContractNum.Value).Cells(Rows.Count, "A").End(xlUp)(1) 
             
             '''/// TURN OFF THE AUTOFILTER
            .AutoFilterMode = False 
             
SkipIt: 
             '''/// CONTINUE THE LOOP
        Next ContractNum 
    End With 
     
     '''/// GO BACK TO THE ORIGINAL ACTIVE SHEET
    Sheets(ThsSht).Select 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I learning VBA but it is proving to be a slow experience so any help would be greatly appreciated!

Thank you!

Hi everyone - first time poster here and as probably most posts here are I am looking for help.

Excel does not recognise €0.00 as the value 0. I need this for the following. Basicaly I have created a loan amortisation sheet breaking down each monthly payment into repayment amount / principal / interest etc etc. I am using the PMT formula to calculate each monthly payment as it goes with each payment having it's own row of information.

=-PMT(D11/12,$I$4*12-A10,C11)
D11 being a cell referencing the interest rate / 12 to make monthly.
$I$4*12-A10 is calulating the payments left I4 is the original loan term less the the number of payments made A10 to give how many are left ) and C11 is the balance left on the loan.

I hope this is easy enough to follow
It works a dream when I create exactly enough rows for the amount of payments. My problem occurs when I change the original loan term in I4. I would like to be able to change the loan term and have the appropriate rows of calculations disappear / go blank when not needed. ie if I change it from a 35 year term to a 30 year one that the last 60 rows of calculations disappear. I set it not to view 0 but I can't get ithe unneeded cells to actually return 0 at all. If I decrease this term I get a #Div/0! error across the formulas for the time period past the new lesser loan time. I am assuming this is within the PMT formula when the principle is at 0 as the loan has been paid off.

I tried to find a function that would check wether or not a value was at 0 elsewhere and if so to return 0 itself but if the value elsewhere was not at 0 to include that figure within a function of the original cell.

I tried to use the IF function and insert the pmt function within it. ie to see if C11 was equal to 0 and if it was to return 0 as a value for the function but if it did not equal 0 to calculate the original PMT function. =-PMT(D11/12,$I$4*12-A10,C11). but I cannot make the PMT functtion work within one of the IF true or false options.

Furthermore it also appears that even in a simple IF function excel does not recognise €0.00 as 0. eg if I put in =IF(A!=0,"yes","no") and A1 contains €0.00 it gives the value of no. If I put it as =IF(A!=€0.00,"yes","no") it is invalid and finally for =IF(A!="€0.00","yes","no") it again shows no.

I am looking for any way to either get excel to recognise a zero in currency to be the same as 0 or to get a PMT function active within IF ( preferably both as I suspect I need both of these at the same time to do this for me )

I really hope that you can follow the above - it makes more sense in your own mind than when you try to explain it.

Many thanks in advance for any advice.

A large proportion of questions in Excel newsgroups and Q&A boards regard #N/A (the Not Available error) the look up worksheet functions return.

The quotes that follow are from the Help file (of Excel 2000).

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Remarks

If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.

LOOKUP(lookup_value,lookup_vector,result_vector)

Important The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Result_vector is a range that contains only one row or column. It must be the same size as lookup_vector.

If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP gives the #N/A error value.

LOOKUP(lookup_value,array)

If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Remarks

If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.

If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.

MATCH(lookup_value,lookup_array,match_type)

Remarks

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.

MATCH does not distinguish between uppercase and lowercase letters when matching text values.

If MATCH is unsuccessful in finding a match, it returns the #N/A error value.

If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters, asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Although kindred, I excluded INDEX and CHOOSE, because they don't return #N/A.

What follows is a quote from a reply of mine at microsoft.public.excel.worksheet.functions to a post entitled "Conditional formatting to hide #N/A results", along with a reply to the same post by Harlan Grove:

It involves a proposal to extend the syntax of the look up functions quoted above with an (extra) optional argument.

QUOTE

A richer syntax for lookup functions would allow us to escape testing what
these functions compute (return), so avoiding the "compute twice" trap we
often see.

My proposal is simple: Add an optional slot to the sysntax of these
functions.

For VLOOKUP this surgery would give:

=VLOOKUP(lookup-value,table-array,col-index-num,{range-lookup},{return-value-when-not-available})

{} means optional; the default value for the 5th argument should be #N/A (in
view of backward compatibilty).

Examples:

=VLOOKUP(x,Table,c,0) [ return #N/A by default upon failure ]

=VLOOKUP(x,Table,c,0,"") [ return blank upon failure ]

=VLOOKUP(x,Table,c,0,0) [ return 0 upon failure ]

=VLOOKUP(c,Table,c,1) [ return #N/A by default upon failure; although in
most [cases] avoidable by approriate structuring of Table ]

=VLOOKUP(c,Table,c,1,0) [ return 0 upon failure; although in most [cases]
avoidable by approriate structuring of Table ]

PS. This is a renewal of a thread I was involved with at
http://www.mrexcel.com/archive2/messages/13513.html

What do you think? Am I overlooking something that would stand such a change
in the way?

Aladin

"Harlan Grove" wrote in message
news:xOuG8.27486$D41.1032708@bgtnsc05-news.ops.worldnet.att.net...
> Peo Sjoblom wrote...
> >One possible way would be to use a white font and the formula
> >
> >=iserror(a1)
> >
> >assuming you want to hide it in A1
> >
> >You can of course trap the error using if and isna in the formula as
well..
> >
> >=if(isna(formula),"",formula)
>
> But it involves evaluating formula twice, which can seriously slow down
> Excel when formula is long and/or complicated. There are times when VBA
> user-defined functions can actually speed up Excel.
>
> 'trap errvals and return specified value or "" instead - return v if
> 'it's not a marched errval
> 'args: v is the *scalar* value to check
> ' e is an optional list of additional args used as follows
> ' - if 1st arg after v isn't an errval, use it as the return value
> if v is a
> ' matched errval; otherwise, use "" as the return value
> ' - all remaining args are treated as errvals to match v against,
so
> if
> ' no remaining args, match all errvals
> 'note: nonerrval args after 2nd arg effectively ignored
> '
> Function errortrap(v As Variant, ParamArray e() As Variant) As Variant
> Dim i As Long, m As Long, n As Long, t As Variant
>
> errortrap = v
>
> If Not IsError(v) Then Exit Function 'return quickly when not errval
>
> n = UBound(e)
>
> If Not IsError(e(0)) Then
> m = 1
> t = e(0)
> Else
> m = 0
> t = ""
> End If
>
> If n < m Then 'no more args, so matches all errvals
> errortrap = t
> Exit Function
> End If
>
> For i = m To n 'check specified errvals
> If v = e(i) Then
> errortrap = t
> Exit Function 'can return now
> End If
> Next i
> End Function
>
>
> In this case, use as =errortrap(formula,#N/A) or
> =errortrap(formula,"",#N/A) (to be explicit). More generally, to pass
> #VALUE!, #NUM! and #DIV/0! errors but replace #N/A, #NULL!, #REF! and
#NAME?
> errors with, say, -1, use =errortrap(formula,-1,#N/A,#NULL!,#REF!,#NAME?)
.
>
> If formula is simple, this will likely slow Excel down. However, if
formula
> involves 6 levels of nested fucntion calls, this will likely speed Excel
up.
>
> >another way assuming
> >
> >=if(countif(d2:d100,b1)=0,"",vlookup(b1,d2:e100,2,0))
>
> Better than evaluating the VLOOKUP twice.
>
> >or
> >
> >=if(countif(d2:d100,b1)=0,"",index(e2:e100,match(b1,d2:d100,0)))
>
> No benefit in this case to using INDEX(.,MATCH(.,.,0)) vs
VLOOKUP(.,.,.,0).
>

UNQUOTE

As long as Microsoft has not taken up the above proposal, we should at least live by a set of reasonable rules shown in the figure below (I posted this figure a while ago at this board, but I can't give here the hyperlink, simply because I couldn't find it via the board's search facility, hence this re-post.)

Microsoft Excel - VLOOKUP.xls___Running: xl2000 : OS = Windows (32-bit) NT 5.00(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elpF6F7F8F9F10F16F17=
ABCDEFGHIJKLM1IDLast NameSalary

2010008Smith46,223.00
EXACT MATCH (0 or FALSE, both means the same thing to Excel)

3010002Miller70,000.00
VLOOKUP(lookup-value,lookup-table,table-column-to-look-in,0)

4010007Young46,223.00

What is better?

5010005Thomas44,662.00
Look UpSalary?

6010001Smith70,000.00
01000270,000.00this

7

70,000.00this, if #N/A must be avoided, because efficient

8

70,000.00inefficient

9

70,000.00inefficient & bad practice

10Commission Table

70,000.00inefficient & bad practice

11Sales%

1200.00%

APPROXIMATE MATCH (1 or TRUE or nothing, all means the same thing to Excel)
1325000.20%

VLOOKUP(lookup-value,lookup-table,table-column-to-look-in)

1450000.40%

What is better?

1560000.60%

Look UpCommission

1670000.80%

7,400.880.80%this

1780001.00%

0.80%this, if #N/A must be avoided, because efficient; seldom needed
1890001.20%

As above, no ISNA, ISERR, or ISERROR.

19

20SPECIAL NOTE: INDEX/MATCH is faster than VLOOKUP, in particular when the formula must be copied to a huge number of cells.Sheet1
To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above image was automatically generated by [HtmlMaker 2.0] If you want this FREE SOFT, click here to download
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-29 16:12 ]

I'm trying to pull the following range CatID!B2:B9 into Combo Box cboDisplayName. Problem is I expect blanks to be in the range. These are appearing in the combo box. How can I remove the Blanks or hide them?

Code:
Public gintTotalEntries As Integer
Public gintLineNumber As Integer
 
Private Sub cboDisplayName_Change()
 
End Sub
Private Sub cmdEditCatID_Change()
If cmdEditCatID  "" Then
gintLineNumber = cmdEditCatID
loadEditLine
cmdNext.Enabled = True
Else
clearEntries
End If
End Sub
Private Sub loadEditLine()
Dim Rng As Range
Set Rng = ActiveSheet.Range("A1")
txtCatID = Rng.Offset(gintLineNumber, 1)
cboDisplayName = Rng.Offset(gintLineNumber, 2)
cboFuture = Rng.Offset(gintLineNumber, 3)
'Deleted in version 7.33. No Longer needed
cboType = Rng.Offset(gintLineNumber, 4)
End Sub
Private Sub cmdEditMode_Click()
 
 
 
Dim intEntries As Integer
 
If gintTotalEntries = 0 Then
MsgBox ("You have no Contributions to view or Edit")
Exit Sub
End If
If gintEditMode = False Then
gintEditMode = True
lblEdit.Visible = True
cmdNext.Caption = "Save Contributions >>"
cmdNext.Enabled = True
cmdEditMode.Caption = "Switch to New Entries"
cmdEditCatID.Visible = True
cmdNext.Enabled = False
LoadcmdEditCatID
Else
gintEditMode = False
lblEdit.Visible = False
cmdNext.Caption = "Save Contributions >>"
cmdEditMode.Caption = "Switch to Edit Mode"
cmdEditCatID.Visible = False
cmdNext.Enabled = False
End If
clearEntries
End Sub
Private Sub clearEntries()
'Clear all Entries
txtCatID = ""
cboFuture = ""
cboType = ""
cboDisplayName = ""
If gintEditMode = True Then
cmdEditCatID = ""
End If
End Sub
Private Sub cmdNext_Click()
If (Not emptyfields()) Then
saveCatID
clearEntries
If cmdEditCatID.Enabled = False Then
cmdEditCatID.Enabled = True
End If
cmdNext.Enabled = False
End If
 
End Sub
Private Sub saveCatID()
'Save CatID to selected range
Dim intNextEntry As Integer
Dim Rng As Range
If gintEditMode = False Then
Set Rng = Cells(gintTotalEntries + 2, "A")
PostChoices Rng, gintTotalEntries + 1
Else
Set Rng = Cells(gintLineNumber + 1, "A")
PostChoices Rng, gintLineNumber + 1
End If
gintTotalEntries = Application.WorksheetFunction.Max(ActiveSheet.Range("a2:a100"))
End Sub
Private Sub PostChoices(Rng As Range, intEntry As Integer)
If gintEditMode = False Then
Rng = intEntry
End If
Rng.Offset(0, 1).Value = txtCatID
Rng.Offset(0, 2).Value = cboDisplayName
Rng.Offset(0, 3).Value = cboFuture
Rng.Offset(0, 4).Value = cboType 'Deleted Amount in Version 7.33
End Sub
 
Private Function emptyfields() As Boolean
If txtCatID.Value = "" Then
MsgBox ("Please enter a Cat ID!")
emptyfields = True
txtCatID.SetFocus
Exit Function
End If
If cboDisplayName.Value = "" Then
MsgBox ("Please enter a Display Name!")
emptyfields = True
cboDisplayName.SetFocus
Exit Function
End If
 
If cboFuture.Value = "" Then
MsgBox ("Please enter a value!")
emptyfields = True
cboFuture.SetFocus
Exit Function
End If
 
 
If cboType.Value = "" Then
MsgBox ("Please enter a type!")
emptyfields = True
cboType.SetFocus
Exit Function
End If
 
emptyfields = False
End Function
Private Sub cmdReturn_Click()
' Return to the Other form
Unload Me
End Sub
 
Private Sub txtCatID_AfterUpdate()
cboDisplayName.SetFocus
cmdNext.Enabled = True
cboDisplayName.SetFocus
End Sub
Private Sub UserForm_Activate()
gintTotalEntries = Application.WorksheetFunction.Max(ActiveSheet.Range("A2:A100"))
If gintTotalEntries  0 Then
cmdEditMode.Enabled = True
cmdEditCatID.Enabled = True
End If
LoadcmdEditCatID
End Sub
Private Sub LoadcmdEditCatID()
Dim intRows As Integer, I As Integer
Dim Rng As Range
 
I = 1
Set Rng = ActiveSheet.Range("A1")
cmdEditCatID.Clear
While (I

Hi All,
For going on 2 years I've been building a large addin, over 100 modules,
with 800 or so Subs and Functions.

I'm not a 'professional' developer and am naive techie aspects of
VIBDE enironment.

Two days ago I started getting the Code execution has been interrupted
error.
The problem does not happen all the time.
Macro's with not a lot of calls seem to do 'better'.

Some of the code below is in called Subs or functions that are 5 levels
'deep' from the Sub executed via the menu I've built for the addin.

Other than adding a new module to the app, I don't recall doing anything
really different.

I don't know what to do next to diagnose the condition causing the
interruption, and am seeking suggestions.

Samples of code with the error are below, they are NOT the same from
running to running.
Work done, and some facts are below, please let me know if you need more
facts.

Dell desktop, 346 meg memory, both disk drives recently defragged, XP
home version.

4 workbooks are open in addition to addin, addin is 10 meg, workbook size
ranges from < 30K to 7 meg.

Have used Rob Bovey's code cleaner on ALL of the above. (daily on the
addin)

In ALL cases: each execution continues to successful end when Continue is
clicked, err.number is zero in the immediate window.

Every module in the addin is < 64K in size. Subs and functions range from
a couple of lines to a max of about 1500.

I get the error when running Bovey's code cleaner and Documentor as well
as my own code and it too completes when Continue is clicked.

Put in some "On error resume next" lines before the locations where the
interruption seems to repeat, and the code stops on the 'On error...' too.

Thanks much,
Neal Z.

Examples of code seen when debug button clicked:

If Chars = "x" Or Chars = "" Or Chars = Dflt Then 'THIS LINE WAS YELLOW

'PART OF A ROW LOOP EDITING VALUES IN CELLS

' ELIM any lead/trail blanks from column B.
If Len(.Cells(Row, iColB).Value) Len(Trim(.Cells(Row, iColB).Value)) Then
.Cells(Row, iColB).Value = Trim(.Cells(Row, iColB).Value)
End If 'THIS LINE WAS YELLOW

'below part of larger macro formating a sheet

If .Rows("2:" & (gSVCcolHdrRow - 1)).RowHeight = 16 Then Else: .Rows("2:"
& (gSVCcolHdrRow - 1)).RowHeight = 16

'below, same macro as above
.Rows((FirstCpyRow - 10) & ":" & (FirstCpyRow + PaEndRow +
10)).ClearContents

'different Mac from above, but again, a .rows process

If SyPLastRow > 0 Then
SyPws.Columns.Hidden = False
SyPws.Rows("1:" & SyPLastRow).Hidden = False 'THIS LINE WAS YELLOW
Else
GoSub Err_ColBNotFound
End If

Sub SCRNback(bASU As Boolean)
'Restore screenupdating to prior value.
Application.ScreenUpdating = bASU
End Sub 'this LINE was YELLOW

Public Function Find_ValInColF(Ws As Worksheet, ByVal sLookFor As String, _
ByVal Row As Long, ByVal FmCol As Integer, ByVal ToCol As Integer) As
Integer
' Return column of the cell where a value is found in a row.
Dim Arg As Range

If FmCol < 1 Then FmCol = 1
If ToCol < 1 Or ToCol > MSoMaxCol Then ToCol = MSoMaxCol 'mso max=256

Set Arg = Ws.Range(Ws.Cells(Row, FmCol), Ws.Cells(Row, ToCol)) _
.Find(sLookFor, LookIn:=xlValues, Lookat:=xlWhole)

' LINE BELOW WAS YELLOW, Arg HAD a value.

If Not Arg Is Nothing Then Find_ValInColF = Arg.Column
End Function

--
Neal Z

Hey all,

I have a feeling this is a daft question, but I cannot suss the answer. I have an Array of names that has been created in my code, and I need to output the unique names (name1, name2, name3) in the desired Cell. Using a function I was given on here previously I am able to pass the Array and another Array containg the unique values is created, but I cannot for the life of me work out how the Array is passed back so that I can extract the info? The way I have here causes no errors, but FeeEarnerTotal is blank.

Any help greatly appricated.
Thanks.

' Write the Fee Earners names
                ' FeeEarnerTotal is a Variant
                FeeEarnerTotal = UniqueItems(FeeEarnerArray, False)
                Range("C2").Value = FeeEarnerTotal
Function UniqueItems(ArrayIn, Optional Count As Variant) As Variant
'   Accepts an array or range as input
'   If Count = True or is missing, the function returns the number of unique elements
'   If Count = False, the function returns a variant array of unique elements
    
Dim Unique() As Variant         ' array that holds the unique items
Dim Element As Variant
Dim i As Integer
Dim FoundMatch As Boolean
Dim NumUnique As Integer
    
' If 2nd argument is missing, assign default value
    If IsMissing(Count) Then Count = True
' Counter for number of unique elements
    NumUnique = 0
' Loop through the input array
    For Each Element In ArrayIn
        FoundMatch = False
' Has item been added yet?
        For i = 1 To NumUnique
            If Element = Unique(i) Then
                FoundMatch = True
                Exit For '(exit loop)
            End If
        Next i
AddItem:
' If not in list, add the item to unique list
        If Not FoundMatch And Not IsEmpty(Element) Then
            NumUnique = NumUnique + 1
            ReDim Preserve Unique(NumUnique)
            Unique(NumUnique) = Element
        End If
    Next Element
' Assign a value to the function
    If Count Then UniqueItems = NumUnique Else UniqueItems = Unique
    
End Function


Best title I could think of.

Ok so I have 6 sheets Mon - Fri and a week sheet, currently I have the sheets being sorted and pulled to week which is what i want however when the sort is complete data from lets say Tuesday is no longer in the column section of tuesday. Yet under monday in fact if any of the values are not found in all days of the week they are currently being placed under the Monday column instead of sorting to their respective columns.

There are multiple ways to fix that but what I need is for id number last and first names to be under a2b2c2 respectively it auto sort the days of the week and peoples names to their respective rows.
However I need the people who are missing from the list on a given day of the week to only show a blank cell for that day and if they are missing from 2 days they would be blank on the other day as well, yet show up on the day column they were on the list from that day of the week sheet.

It is sort of like an attendence sheet however the list for each day of the week is generated at random so no day or week will be the same. just when i receive the data it will be sorted with said vba function.

I found parts of this source online that worked a bit better and faster in sorting than my original file and was able to modify some of the code to get partially what I needed. This is the main sort code for pulling from the data sheets. Do not mind the offsets they should be (0,1) respectively. I have tried to adjust them but it does not work. And unfortunately MSDN is not giving me a viable solution I can think of to fix the issue. Hense why I am asking for help after wrapping my head around this issue for 3 days. My luck I probably just need to add another variable and have it point to the column needed to paste the data.

Public Sub WeekSort()


Dim c As Range, cfind As Range, x, cfind1 As Range
On Error Resume Next
With Worksheets("Monday")
.UsedRange.Copy Worksheets("Week").Range("a2")

For Each c In Range(.Range("a3"), .Range("a3").End(xlDown))
x = c.Value

    With Worksheets("Tuesday")
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
    If cfind Is Nothing Then GoTo line1
    .Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy
        With Worksheets("Week")
        Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole)
        If cfind1 Is Nothing Then GoTo line1
       cfind1.End(xlToRight).Offset(0, 2).PasteSpecial
        End With 'W
     End With  'T
     With Worksheets("Wednesday")
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
    If cfind Is Nothing Then GoTo line1
    .Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy
        With Worksheets("Week")
        Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole)
        If cfind1 Is Nothing Then GoTo line1
       cfind1.End(xlToRight).Offset(0, 5).PasteSpecial
        End With 'W
     End With  'Wed
     With Worksheets("Thursday")
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
    If cfind Is Nothing Then GoTo line1
    .Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy
        With Worksheets("Week")
        Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole)
        If cfind1 Is Nothing Then GoTo line1
       cfind1.End(xlToRight).Offset(0, 8).PasteSpecial
        End With 'Week
     End With  'Th
     With Worksheets("Friday")
    Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
    If cfind Is Nothing Then GoTo line1
    .Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy
        With Worksheets("Week")
        Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole)
        If cfind1 Is Nothing Then GoTo line1
       cfind1.End(xlToRight).Offset(0, 11).PasteSpecial
        End With 'Week
     End With  'F
line1:
 Next
 End With
    Application.CutCopyMode = False
End Sub
basically 
ID L F    M       T         W     Th     F
1   xx    x        x        x       x      x
2   xx             x                x      x
3   xx    x        x        x
x under M-F are the days they appear on list blanks are days they do not

any advice in the right direction or even some reference sources I can lookup to solve this issue would be greatly appreciated.

thanks in advance to anyone that can help me out.

*edit could have sworn I returned to programming section before not function section sorry mods*

Hi,

Thank you for taking the time to read this post. I am trying to build a VBA program to identify the appropriate project on my master list and update that information based on a smaller file I place on my share drive for fellow employees. This is my first post so if I can structure this better or post in a different area please tell me.

The specifics:

I am trying to pull information from my CM Update sheet to another sheet named the PSR. The PSR is the master sheet and much larger than the CM update sheet (many more columns, much more complicated--> Therefore a "merge" would not work). I have been able to run a simple Vlookup which, if the project numbers are the same for the CM Update sheet and the PSR, to update the PSR accordingly. However, there are two problems with this.

1) Sometimes the CM Update sheet is left blank due to accidental deletion or no updating. If it is left blank, and I have information stored in the PSR, I want the Macro to skip that cell in the PSR and leave the pre-existing information which was originally in the PSR. i.e., I want to retain outdated info rather than wipe out the information completely.
2) Sometimes I add new projects before the CM Update file gets back to me. I want that information to remain untouched by the Macro (i.e., if the Macro does not find a match between the project ID's in the PSR and the CM Update file, I want the PSR file data to remain rather than have the data be wiped out and have a 0 return due to no corresponding information in the CM Update file).

I have incorporated this into a simple point-and-click Macro, for which each column of information I pull has a vlookup function that looks like this:

=IF(ISNA(VLOOKUP(B3,'[CM Upload 03.04.2009.xlsx]Query - Excel Extract Pipeline '!$1:$1048576,9,FALSE)), 0, VLOOKUP(B3,'[CM Upload 03.04.2009.xlsx]Query - Excel Extract Pipeline '!$1:$1048576,9,FALSE))

I know the above function is not good enough. I have tried using IS(NOTBLANK as well, but this attempt I made misses the fundamental point. I need the cell in question in the PSR to remain untouched by the function and remain as it was before the code was executed should the cell be blank in the CM Update file.

Can anyone help me with this? I think this requires VBA code, and I have never done this before. Does anyone have any suggestions, ideas, ect? Should I post the entire macro I had been using in VBA code? I have been teaching myself the basics of VBA programming in Excel, but it is slow going. Any advice would be appreciated.

Thank you!


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