Free Microsoft Excel 2013 Quick Reference

- IF function - need FALSE to return a blank cell, not 0
- IF function returning 0(blank) instead of TRUE.
- LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook)
- I need to return "" instead of 0
- Complicated Index Match Offset function
- Complicated Index Match Offset function
- I need to return "" instead of 0
- If function to return empty cell (blank cell) if false
- Error In macro that checks if the file is already open
- Return Row & Column Number Of Found Text
- Excel Macro Help....Filter and Copy to New Sheets
- #N/A -- The Ever Recurring Dreaded Problem
- Combo Box Range with Blanks
- How to Diagnose Why "Code Execution has been interrupted"
- Returning data from Array is blank
- Multisheet data retrieval and sorting issues.
- VBA Code Vlookup Skip Cell if no new information

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

=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.

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

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

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,

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

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

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.

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

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

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 SubPlease 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

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.

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

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

VB:I learning VBA but it is proving to be a slow experience so any help would be greatly appreciated!'''/// 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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Thank you!

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.

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 ]

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

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

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

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 xx 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*

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!