Free Microsoft Excel 2013 Quick Reference

Iserror and match function Results

This is another question that requires layman explanation(for Small Function).
I just can't figure out how to operate the function.

I found the Small function in this Formula:

=IF(ISERROR(SMALL(Data!$F:$F,ROWS($A$1:A1))),"", INDEX(Data!A:A,
MATCH(SMALL(Data!$F:$F,ROWS($A$1:A1)),Data!$F:$F,0 )))
-Max xl 97.-

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

--Extracted from the HELPER!--
SMALL
Returns the k-th smallest value in a data set. Use this function to return
values with a particular relative standing in a data set.

Syntax
SMALL(array,k)

Array is an array or range of numerical data for which you want to
determine the k-th smallest value.

K is the position (from the smallest) in the array or range of data to
return.

Remarks

If array is empty, SMALL returns the #NUM! error value.

If k ≤ 0 or if k exceeds the number of data points, SMALL returns the #NUM!
error value.

If n is the number of data points in array, SMALL(array,1) equals the
smallest value, and SMALL(array,n) equals the largest value.

Example

SMALL({3,4,5,2,3,4,5,6,4,7},4) equals 4
SMALL({1,4,8,3,7,12,54,8,23},2) equals 3

I am even more confused after this.

Thanks.

1. The functions SEARCH(), SEARCHB(), FIND() and FINDB() return a #value
error if they didn't find the "find_text" string within the "within_text"
string. This forces the user to use long formulas such as
=IF(ISERROR(SEARCH(A1, A2)), 0, SEARCH(A1, A2)) in order to avoid errors.
Since in case of success, these functions return the serial number of the
first matching character, starting from 1, I suggest that these functions
return either 0 or -1 if they can't find that text.
2. I suggest to add functions that count the number of times that one text
exists within another, for example:
FINDNUM(find_text, within_text), SEARCHNUM(find_text, within_text)
FINDNUM is case-sensitive, SEARCHNUM is not.
Find_text is the text you want to find.
Within_text is the text containing the text you want to find.
Example: FINDNUM("ma", "Mamma mia!") returns 1, SEARCHNUM("ma", "Mamma
mia!") returns 2.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

One non-array formulas play ..

Assume source data is in Sheet1, cols A to O, data from row2 down
Assume the key col is col B, with the criteria: >150

In an empty col to the right, say col Q
Put in Q2: =IF(B2="","",IF(AND(ISNUMBER(B2),B2>150),ROW(),"") )
Copy Q2 down to say Q100 to cover the max extent of data expected in the
source
(Leave Q1 empty)

Col Q is the criteria col which will simply assign arbitrary row numbers to
lines which satisfy the criteria. Col Q's returns will be read by the
formulas in Sheet2.

In a new Sheet2
-------
Copy > paste the same headers from Sheet1 into A1:O1

Put in A2:
=IF(ISERROR(SMALL(Sheet1!$Q:$Q,ROWS($A$1:A1))),"", INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$Q:$Q,ROWS($A$1:A1)),Sheet1!$Q:$Q,0)))

Copy A2 across to O2, fill down to O100
(cover the same range as done in col Q in Sheet1)

Sheet2 will return only the rows from Sheet1 which satisfy the criteria, i.e
those rows with values in col B > 150. Result rows will be bunched neatly at
the top
---
If the criteria to be set in col Q is say:
where the text string "OK" appears in col B

Then we could put instead in Q2:
=IF(B2="","",IF(ISNUMBER(SEARCH("OK",B2)),ROW(),"" ))
and just copy down as before

(Change SEARCH to FIND if the case is important. FIND is case sensitive)

Sheet2 will then return the desired results ..

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik yahoocom
----
"Gary Braida" > wrote in message
news Hello,
>
> I have a spreadsheet consisting of 15 columns and 100 rows. I would like
to
> search a column for a specific string or based on some other criteria
(e.g.,
> > 150) and for columns meeting the desired criteria, I would like to
print
> the entire row (all columns) within the same worksheet or on a new
> worksheet. Can this be done using one or more formulas but without
getting
> into VB or fancy macros? Can this be done period? I'm looking for
> something that searches the column using the same or similar approach as
the
> "sumif" function but instead of summing the specificed range, I want to
> print the rows.
>
> Thank you in advance for your support. Please reply to Thank you very much!
>
> Regards,
> Gary Braida
>
>

Well, I never thought I'd run into this one! But, a problem came up
at the office with designing a worksheet where difference between two
time values is calculated. The problem was that a user may enter
something that isn't recognized by Excel as a time - for example,
"10am".

I figured I could make these assumptions: the entry will be either
"10am", "10:30am", or something else that will be recognized by Excel
as a time and thus will not need to be interpreted by the formula.
So, here was my first attempt (these are array formulas, use Ctrl+Shift
+Enter):

=IF(ISNUMBER(E14),E14,TIMEVALUE(LEFT(E14,MATCH(1,
(MID(E14,ROW(INDIRECT("1:"&LEN(E14))),
1)":")*ISERROR(MID(E14,ROW(INDIRECT("1:"&LEN(E14 ))),1)*1),0)-1)&"
"&RIGHT(E14,2)))-IF(ISNUMBER(D14),D14,TIMEVALUE(LEFT(D14,MATCH(1,
(MID(D14,ROW(INDIRECT("1:"&LEN(D14))),
1)":")*ISERROR(MID(D14,ROW(INDIRECT("1:"&LEN(D14 ))),1)*1),0)-1)&"
"&RIGHT(D14,2)))

Where D14 is Time In, and E14 is Time Out. Of course, this creates 9
levels of function nesting, which won't work in earlier versions of
Excel.

Well, the workaround was to specify a larger array than would ever be
necessary:

=IF(ISNUMBER(E14),E14,TIMEVALUE(LEFT(E14,MATCH(1,( MID(E14,ROW($1:$100),
1)":")*ISERROR(MID(E14,ROW($1:$100),1)*1),0)-1)&" "&RIGHT(E14,2)))-
IF(ISNUMBER(D14),D14,TIMEVALUE(LEFT(D14,MATCH(1,(M ID(D14,ROW($1:$100),
1)":")*ISERROR(MID(D14,ROW($1:$100),1)*1),0)-1)&" "&RIGHT(D14,2)))

Just right! So, one more thing to look out for when working with the
new version.

I can't figure out a way to get MATCH to use a lookup value that would be
anything except a blank cell inside a Pivot Table. I also tried OFFSET, but
the data I need to get to is to the left of the lookup column. Plus, I need
the row # for other functions.
=MATCH('anything but blank',CO869:CO933,0)
I have tried >"0", >0, NOT("0"), NOT(0), NOT(ISERROR(CO869:CO933), ISBLANK
with previous, NOT(""), NOT(" ").
The column I'm searching has various numbers and I need to find the data to
the left of any number (nonblank).
Please help?

Why does the statement give a run time error

If IsError(Application.WorksheetFunction.Match(chk_hd r1, hdr_rng2, 0)) Then

where chk_hdr1 is a variable and hdr_rng2 is a column range name.

It gives the run time error 1004,

Unable to get the match property of the worksheetfunction class.

This happens only when the value of chk_hdr1 is not found in hdr_rng2

--
Sajit
Abu Dhabi

Sub MakeQuestions() & Sub CopyRangeFromMultiWorksheets() are the two macros
that I pasted below. For some reason when I have Sub
CopyRangeFromMultiWorksheets() in my workbook Sub MakeQuestions() doesn't
work but as soon as I remove Sub CopyRangeFromMultiWorksheets() from my
workbook Sub MakeQuestions() starts working again. Sub
CopyRangeFromMultiWorksheets(), on the other hand, works regardless of
whether Sub MakeQuestions() is in the workbook or not.

When I have both Sub MakeQuestions() & Sub CopyRangeFromMultiWorksheets()
in my workbook and I try to run Sub MakeQuestions() I get a visual basic help
box with a message that reads Compile error: Argument not optional. Then the
LastRow = part of this part of the code LastRow = .Range("E" &
Rows.Count).End(xlUp).Row ....gets highlighted in blue.

Do you know how I might be able to fix this? Both macros are below.

Sub MakeQuestions()

Dim SortArray(Questions, 2)

With Sheets(StatSht)
LastRow = .Range("E" & Rows.Count).End(xlUp).Row
RowCount = LastRow + 1
End With

'Randomly choose 12 , 16, 24
Quest = Int(3 * Rnd())
Select Case Quest
Case 0: NumberofTests = 12
Case 1: NumberofTests = 16
Case 2: NumberofTests = 24
End Select

For TestNumber = 1 To NumberofTests

'create numbers questions
For I = 1 To Questions
SortArray(I, 1) = I
SortArray(I, 2) = Rnd()
Next I

Sheets(StatSht).Range("B" & RowCount) = Questions

'sort array to get random question
For I = 1 To Questions
For j = I To Questions
If SortArray(j, 2) < SortArray(I, 2) Then
Temp = SortArray(I, 1)
SortArray(I, 1) = SortArray(j, 1)
SortArray(j, 1) = Temp

Temp = SortArray(I, 2)
SortArray(I, 2) = SortArray(j, 2)
SortArray(j, 2) = Temp

End If
Next j
With Sheets(StatSht)
'Save numbers in worksheet
.Range("E" & RowCount).Offset(0, I - 1) = _
SortArray(I, 1)
End With
Next I
RowCount = RowCount + 1
Next TestNumber
MsgBox "Click Begin Sentence Completion"
End Sub

Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "Summary Report"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "Questions", "Status"), 0)) Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Range("A1:B24")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below
this macro
CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value =
sh.Name

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

See also: Dynamic Named Ranges | Vlookup | Hlookup Formula | Left Lookup in Excel | Excel Lookup Functions | Multi-Table Lookup | Dynamic Excel LookupsExcel Dashboard Reports & Excel Dashboard Charts 50% OffExcel Dynamic FormulasRather than bog you Spreadsheet down with hundreds, if not thousands of formulas, use a single formula with flexible and changeable Arguments. In this example I will use the INDEX/MATCH functions nested together. You can also instruct the end formula to return the corresponding cell, to the match, on the left or right.  However, the the same principles can apply to most Excel formulas.In this example I have used the range A2:D14 as my table range. I have also made good use of Named Ranges and Data Validation. The single formula, in this case, ends being;=IF(ISERROR(INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right="Right",MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column))),"Invalid Criteria",INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right="Right",MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column)))Or, if don't mind see formula errors IF invalid argument criteria is used, it is simply;=INDEX(DataTable,MATCH(Look_For,INDIRECT(Column_To_Look),0)+1,IF(Look_Left_Right="Right",MATCH(Column_To_Look,Heads,0)+Offset_To_Column,MATCH(Column_To_Look,Heads,0)-Offset_To_Column))Download Example

On Thu, 11 Aug 2005 12:00:20 -0700, "keith m" <keith
m@discussions.microsoft.com> wrote:

>I am using the formula "=Workday" but I want this formula to include
>Saturdays. I have a Start Date and a duration and want to calculate the end
>date with Saturday included as a work day. Can anyone tell me how to do
>this? Any assistance would be greatly appreciated. Thank you.

Here is a UDF (User Defined Function) that will accomplish that.

To enter this function, <alt><F11> opens the Visual Basic Editor. Ensure your
project is highlighted in the Project Explorer window, then Insert/Module and
paste the code below into the window that opens.

To use the function, in some cell enter the formula:

=WDincSat(Start,NumDays,Holidays)

The variables can be cell references or entered directly. The Holidays
argument can be a range; but it is optional.

====================================
Function WDincSat(StartDate As Date, ByVal NumDays As Long, _
Optional Holidays As Range = Nothing) As Date

'Workday function without Analysis Toolpak

Dim i As Long
Dim TempDate As Date
Dim c As Range
Dim Stp As Integer

Stp = Sgn(NumDays)
TempDate = StartDate
For i = Stp To NumDays Step Stp
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)

If Not Holidays Is Nothing Then
Do Until Not IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False
If IsError(Application.Match(CDbl(TempDate), Holidays, 0)) = False Then
TempDate = TempDate + Stp
If Weekday(TempDate) = vbSunday Then _
TempDate = TempDate + Stp + (Stp < 0)
End If
Loop
End If
Next i

WDincSat = TempDate
End Function
========================================

HTH,
--ron

OK...For now, I'll assume you really do want to use ONLY Excel functions.

1) I copied your data into Shee1 of a new workbook, beginning in cell B1.
(Note: I changed the date formats to US)

2) I built a pseudo criteria range, beginning in cell I1:

Col_I Col_J Col_K
--------- ------- ---------
StartDate EndDate COUNTRY
04/01/05 04/30/05 Syria
Yemen

3) Col_A is a helper column with the following formula entered in A2 and
copied down:
A2: =IF(OR(B2<$I$2,B2>$J$2,ISERROR(MATCH(C2,$K$2:$K$3,0))),0,MAX($A$1:A1)+1)

The data table looks like this:

SHEET1
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
0 05/15/05 Kuwait xxxx
2 04/21/05 Yemen xxxx
0 05/12/05 Oman xxxx
3 04/04/05 Syria xxxx

4) On Sheet2, I copied the column headings from Sheet1
5) Down Col_A, I entered sequential numbers beginning with 1.
6) I entered the following formulas in cells B2, C2, and D2...and copied
them down:

B2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,2,0)
C2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,3,0)
D2: =VLOOKUP($A2,Sheet1!$A$1:$D$6,4,0)

The table in Sheet2, only picks up valid items from Sheet1:

SHEET2
Col_A Col_B Col_C Col_D
------- -------- ------- ----------
TEST DATE COUNTRY DATA
1 04/10/05 Syria xxxx
2 04/21/05 Yemen xxxx
3 04/04/05 Syria xxxx
4 #N/A #N/A #N/A
5 #N/A #N/A #N/A
6 #N/A #N/A #N/A

Note: I left the #N/A items to demonstrate that there is no match for those
items.

Am I on the right track here?

Ron
--
Regards,
Ron

Hi,
The below code manages to pick 5000 random items out of 10000 from Sheet1
columnA and display them on Sheet2. But the program locks if there are not
10000 data written in Sheet1 columnA.
Hope I need not have to enter that much data just to try it functions as it
should...
Help will be appreciated.
Regards
J_J

'----------------------------------------------
Sub Rast()
Dim RS As Long
Dim objRangeA As Range
Dim objRangeB As Range
Dim objRangeC As Range
Dim blnNotThere As Boolean

Set objRangeA = Worksheets(1).Range("A1:A10000")
Set objRangeB = Worksheets(2).Range("B1:B10000")
Set objRangeC = Worksheets(2).Range("C1:C10000")

'
If WorksheetFunction.CountA(objRangeA) < 10000 Then
MsgBox "Missing items from " & objRangeA.Parent.Name & " ", _
vbExclamation, " Maks10000"
GoTo DontCallMe
End If

Worksheets(2).Select
StartOver:

If WorksheetFunction.CountA(objRangeC) = 0 Then
objRangeC.Value = objRangeA.Value
objRangeC.Columns.AutoFit
objRangeB.ClearContents
objRangeB.ColumnWidth = objRangeC.ColumnWidth
'Range("A1").ClearContents
Range("A1").ColumnWidth = objRangeC.ColumnWidth
GoTo DontCallMe
End If

Do While blnNotThere = False
Randomize
RS = Int(Rnd * 10000 + 1)

If Not IsError(Application.Match(objRangeC(RS), objRangeC, 0)) Then
blnNotThere = True
Range("A1").Value = objRangeC(RS)
objRangeB(WorksheetFunction.CountA(objRangeB) + 1).Value =
objRangeC(RS)
objRangeC(Application.Match(objRangeC(RS), objRangeC, 0)).Delete
shift:=xlUp
End If
Loop

If WorksheetFunction.CountA(objRangeC) = 0 Then
If MsgBox("OK? ", vbQuestion + vbYesNo, _
" Randomly") = vbYes Then GoTo StartOver
End If

DontCallMe:
Set objRangeA = Nothing
Set objRangeB = Nothing
Set objRangeC = Nothing
End Sub

Private Sub CommandButton1_Click()
For z = 1 To 5001
Rast
Next z
End Sub

In Excel 2003, I have a list of names in column A and column C; I need to
find any duplicates between the two columns. Column A has 469 rows and
Column C has 4713 rows. Example -- Column A -- Duck Donald and Column B --
Duck Donald

I found the formula below and it does work, but I need to know if I can use
a wildcard character in this formula to also find for instance: in Column A
-- Duck Donald and in Column B -- Duck Donald D. The difference being the
middle initial D. Any suggestions are greatly appreciated.

=IF(ISERROR(MATCH(A10,$C$1:$C$4713,0)),"",A10)

Is there a quick way in VBA to tell if a function used in
a cell formula is a userdefined function? That is, I know
I can load the Excel Functions into an array and then do a
match on the function found in the cell against the array
and check to see if it is in the array. If its not found
(iserror =true) its userdefined. But this means I have to
actually find funtions in formulas to do this? Is there
an easier way to spot userdefined functions in formulas?

Thanks

Hi using matching to identify a particular cell, I want to search and sum the
range of the contents of a cell - in a different column for the same row.

e.g. I want to sum the range contained in cell H36 - e.g. "query!$M$2$M13$'
and I have already found the matching row by locating the single range cell
B36 which is set via Set rng2 = rng1(resFinal)

I set a particular cell as a range(rng2 is set to the value of B36) and then
try to refer to the same row but another column in the totals line

Can someone explain to me how ranges work e.g. what is Cells(rng2.Row,
"H").Value and why the totals line below is falling over
- I did not write it but when I run it the locals window blanks.

Many thanks again
Jello.

Public Function regionTotal(countryRng As String) As Long
>
> Dim rng As Range
> Dim rng1 As Range
> Dim rng2 As Range
>
> Dim Total As Long
>
> Dim res1 As Variant
> Dim res2 As Variant
> Dim resFinal As Integer
>
> Set rng = Range("calc!B10") ' Apples
> Set rng1 = Range("calc!B33:B65") ' range to be searched
> res1 = Application.Match("*" & rng & "*", rng1, 0)
> res2 = Application.Match("*" & countryRng & "*", rng1, 0)
>
> resFinal = Application.WorksheetFunction.Max(res1, res2)
>
> If Not IsError(resFinal) Then
> Set rng2 = rng1(resFinal)
> Total = Application.WorksheetFunction.Sum(rng2.parent.Range( _
> rng2.parent.Cells(rng2.Row, "H").Value).Value)
> MsgBox Total & " for range: " & Cells(rng2.Row, "H").Value
> Else
> MsgBox Range("calc!B10").Value & " was not found"
>
> End If
>
> regionTotal = Total
>
> End Function

Hello Everyone,

I have found a formula on the Microsoft support site that will compare two columns, and show the similar cells.

http://support.microsoft.com/kb/213367/en-us

I want to do the opposite e.g. show the unique cells.

Does anyone know how to do this?

dvent

Hello all- my first post here and I'm a little lost as to how to change the way one of my codes pulls values. I have the following code that I use the attached example file:

Option Explicit
Function LastRow(sh As Worksheet)
    On Error Resume Next
    LastRow = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
    On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
    On Error Resume Next
    LastCol = sh.Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    On Error GoTo 0
End Function

Sub CopyRangeFromMultiWorksheets()
    Dim sh As Worksheet
    Dim DestSh As Worksheet
    Dim Last As Long
    Dim CopyRng As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Delete the sheet "Timberline" if it exist
    Application.DisplayAlerts = False
    On Error Resume Next
    ActiveWorkbook.Worksheets("Timberline").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add a worksheet with the name "Timberline"
    Set DestSh = ActiveWorkbook.Worksheets.Add
    DestSh.Name = "Timberline"

    'loop through all worksheets and copy the data to the DestSh
    For Each sh In ActiveWorkbook.Worksheets
        If IsError(Application.Match(sh.Name, _
Array(DestSh.Name, "FP SUMMARY"), 0)) Then

            'Find the last row with data on the DestSh
            Last = LastRow(DestSh)

            'Fill in the range that you want to copy
            Set CopyRng = sh.Range("X1:Z600")

            'Test if there enough rows in the DestSh to copy all the data
            If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                MsgBox "There are not enough rows in the Destsh"
                GoTo ExitTheSub
            End If

            'This example copies values/formats, if you only want to copy the
            'values or want to copy everything look at the example below this macro
             With CopyRng
        DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
            .Columns.Count).Value = .Value
    End With

            'Optional: This will copy the sheet name in the H column
            DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name

        End If
    Next

ExitTheSub:

    Application.Goto DestSh.Cells(1)

    'AutoFit the column width in the DestSh sheet
    DestSh.Columns.AutoFit

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

When I run the macro it populates a new sheet titled "Timberline" with all of the values in the X:Z columns. I would like to only pull those rows that have values of greater than 0, as it is set up to pull the first 500 or so rows from each tab. Some of these files get to be 30 sheets, so of the 15,000 rows that are populated, roughly 1/10th of them are really needed. It's not a big deal for myself to clean the page up, but I'm not the end user. Any help would be greatly appreciated. Thanks.

My main task is to make it possible to press a commandbutton, start a macro that deletes all empty cells in a collumn (which I've done). However I have loads of other cells that contain a formula that is dependent of the cells in the collumn and when an empty cell is deleted those cells miss a reference. I managed to solve this problem by using the INDIRECT-function and it worked however it seems impossible to auto adjust the formulas and i can't enter it manually since there are more than 10000 cells that contain the formulas.
There are six types of different formulas in the cells

=IF(ISERROR(MATCH(INDIRECT("K"&3);$B$3:$B$35);"";"X") (formula for collumns L, N,P, R, T, V)
=IF(COUNTIF($B$3:$B$35;INDIRECT("K"&3))>1;"Dubblett";"") (formula for collumn M,O. Q, S, U, W)
both of these should always have the 3:35but should change collumn one step until collumn G is reached

=IF(COUNTIF($B$38:$B$68;INDIRECT("K"&3))>1;"Dubblett";"")
=IF(ISERROR(MATCH(INDIRECT("K"&3);$B$38:$B$68);"";"X")
same thing here only collumn should be changed

=IF(COUNTIF($B$56:$D$56;INDIRECT("K"&3))>1;"Dubblett";"")
=IF(ISERROR(MATCH(INDIRECT("K"&3);$B$56:$D$56);"";"X")
as wll as here

All formulas are supposed to go down to an INDIRECT("K"&110)

If the problem can be solved using another macro please describe here:
http://www.excelforum.com/excel-prog...ml#post2554689

So is it anyway to auto adjust when using the INDIRECT function?

I can't figure out a way to get MATCH to use a lookup value that would be
anything except a blank cell inside a Pivot Table. I also tried OFFSET, but
the data I need to get to is to the left of the lookup column. Plus, I need
the row # for other functions.
=MATCH('anything but blank',CO869:CO933,0)
I have tried >"0", >0, NOT("0"), NOT(0), NOT(ISERROR(CO869:CO933), ISBLANK
with previous, NOT(""), NOT(" ").
The column I'm searching has various numbers and I need to find the data to
the left of any number (nonblank).
Please help?

Hello,

Please bare with me, this is somewhat hard to explain.
I wish to grab a cell value from one sheet and bring it to another based on a previous cell from the same row on the original sheet compared to the column on the second sheet.

For instance
Sheet 1
A 1
B 2
C 3

Sheet 2
B Formula
C Formula
A Formula

Formula should make sheet 2 look like this
Desired Result
B 2
C 3
A 1

please also see attached excel sheet for example
I have gotten pretty close with
=IF(ISERROR(MATCH(A2,Sheet1!$A$2:$A$8,0)),"Missing",Sheet1!B2)
but of course that ends up pulling
B 1
C 2
A 3
which obviously is not what we are shooting for

Hi everyone,

I have a spreadsheet with 2 worksheets:
- Worksheet 1 is named Inventory
- Worksheet 2 is named Addresses

The Inventory worksheet contains 2 columns, ie:
- column 1 (named Hostname) is a list of names
- column 2 (named SN) is a list of serial number

The Addresses also contains 2 worksheet, ie:
- column 1 (named SN) is a list of serial number
- column 2 (named Address) is a list of addresses

I am using the macro below (which I kindly got from stanleydgromjr) to match the Serial Number from column 2 of the Inventory worksheet to column 1 of the Addresses worksheet, and then copy the associated address (from column 2 of the Addresses worksheet) to column 3 of the Inventory worksheet.

File Inventory - Addresses.xlm is an example.

Sub FillinAddress_column()
Dim LR As Long, a As Long
Application.ScreenUpdating = False
Worksheets("Inventory").Activate
LR = Cells(Rows.Count, 1).End(xlUp).Row

' Begin of column 3 update (i.e. Address column)
For a = 2 To LR Step 1
    With Cells(a, 3)
      .FormulaR1C1 =
"=IF(ISNA(INDEX('Addresses'!C2,MATCH(RC2,'Addresses'!C1,0))),"""",INDEX('Addresses'!C2,MATCH(RC2,'Addresses'!C1,0)))"
      .Value = .Value
    End With
Next a
' End of column 3 update (i.e. Address column)

Application.ScreenUpdating = True
End Sub

The macro works fine as long as the Serial Number being matched between both worksheets is exactly the same. However sometimes the Serial Number can slightly differ in the second worksheet, for example the Serial Number could be FOC09353DKS in the first worksheet while it could be listed as FOC09353DKS (123423) in the second worksheet. Hence I cannot retrieve the address for Serial Number FOC09353DKS

How could I modify the above macro to search for a string (i.e. FOC09353DKS in this case) instead of matching a value?

Note that actually it does not have to be done through a macro, I attempted to use something like =IF(ISERROR(VLOOKUP...) function without success.

Thanks,
Ant