Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Excel Range Find Function In VB


I have a spreadsheet where I am doing the find lookup of 1 range of values in another range in vb code.

I have come across an issue with my lookup. I initialy set the range of the lookup to start at cell a2 to the end. I then find my value, which could be more then once in the range. When found, I perform another test on the corresponding b cell. If that is not true, I reset the range to the next line and search for my value in column a again.

My issue is I have just discovered that the excel find lookup, when a range is specified, starts its search 1 cell down from the start of the range.

This means for me, if the value I was searching for was in the first cell, I miss it because when I find it later in the list, I reset the range to below that cell.

Here is the code I am using:

lastrowbyBranch = Worksheets("Secid by Branch").Cells(65536, 1).End(xlUp).Row
Check_Branch = "No"

Worksheets("Secid by Branch").Activate
secidrange = "a2:a" + Trim(Str(lastrowbyBranch)) 'setting range of worksheet
Set rngSearch = Worksheets("Secid by Branch").Range(secidrange)

Do While Check_Branch = "No"

Set IDRow = rngSearch.Find(secid, , LookAt:=xlWhole)

If Not IDRow Is Nothing Then
rownum = IDRow.Row
If Worksheets("Secid by Branch").Cells(rownum, 2).Value = branch Then
Check_Branch = "Yes"
GoTo SkipFunction
rownum = rownum + 1
If rownum > lastrowbyBranch Then
GoTo SkipFunction
End If
secidrange = "a" + Trim(Str(rownum)) + ":a" + Trim(Str(lastrowbyBranch)) 'setting range of worksheet
Set rngSearch = Worksheets("Secid by Branch").Range(secidrange)
End If
GoTo SkipFunction
End If
Is there anyway to make the function look in the first cell of the range?

And I have thought about setting the range 1 row earlier, that will work for the first cell of the range, but I would then need to reset my range to the cell I find rather then the cell after. The result of this is if the value is not found later on in the list, it will return to that cell, which has already failed the second validation on the b cell and end up in a loop!

Any ideas?

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Find Selected Excel Range
I have a form in excel that needs to display the selected range in a label. The form ‘Show Modal’ property is set to false allowing users to use excel features while the form is visible.

The following code is what I have and is not working since I don’t know ho to refer to a selection.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If frmCF.Visible = False Then
frmCF.lblsearch.Caption = ActiveCell.Address
End If
End Sub

The above code displays the address of an active cell. I however want the range in case user drags the mouse and selects more than one cell.

VB To Find A Range In Excel Document

I have the following code, which opens an excel document selects a range and displays it in a listbox, code as follows:

Dim objXLApp As Excel.Application
Dim intLoopCounter As Integer
Dim count As Integer

Set objXLApp = New Excel.Application
Set fo_xlApp = GetObject("", "Excel.Application")

With objXLApp
.Workbooks.Open FileName:=App.Path & "Data.xls"

count = 1

While StrComp(.Cells(count, 1), "") <> 0

Form2.List1.AddItem .Range("A" & count)

count = count + 1



Form2.List1.ListIndex = 0


End With

Set objXLApp = Nothing


This works fine..... however what I want is for its to only display values that start with a specified letter.


User clicks a button, and in turn only values that begin with the letter A are displayed in the listbox.

Is this possible? Many thanks in advance for your assistance.


Excel XP How Would I Find Out The Workbook That The Named Range Exists In?
Is there a way to return the workbook name from a named range in Excel VBA? I am attaching the excel spreadsheet so you can use the VBA code I am attaching.

VB Code:
Sub tester2()Dim DataLookupRange2 As RangeSet DataLookupRange2 = Workbooks("NewEOIFunctions").Sheets("Data").Range("a1:a100")'MsgBox DataLookupRange2.AddressLocal(RowAbsolute:=False)'MsgBox DataLookupRange2.AddressLocal(RowAbsolute:=True)t = DataLookupRange2.AddressLocal(ReferenceStyle:=xlR1C1)u = DataLookupRange2.Worksheet.Namev = DataLookupRange2.??????????r = 1End Sub

Write To Another Range From An Excel Function
I am trying to wite an excel funtion to update another cell in the workbook.

Every time i try to write to the new range I get an 'error 1004 Application-defined or object-defined error.

I vcan read all the values of the range but cannot wiite to any range from a cell formula call!!

Any ideas?

Which Excel Function Finds A String In A Given Range?

I doubt that I'd even have to use any VBA code to pull this off:

I have one spreadsheet with 5,000 records (rows) and each row is full of useful info about each record.

I have a separate spreadsheet with 800 records - all of them a subset of the 5,000 - containing only record ID's, none of the useful info contained in the larger spreadsheet.

What's the quickest way to whittle down the 5,000 list so it contains just the 800 records named in the smaller spreadsheet? I thought about copying the 800 id's into a column in the bigger spreadsheet and using the IF function to label the rows where the ID's match, but the problem with that is that the smaller list of course won't be serially aligned with the larger one.

Isn't there a function that can just search a column or a range to see if it contains the specified value and populate the cell with TRUE or FALSE as appropriate?

Passing A Cell Range In A Function. Excel
How do i pass a range into a function that can be used in the functions code to do things like find the greatest value or smallest, etc.

Excel Range Function Killing A Routine
Here's something I can't get a grip on y'all. The function below calculates the mean from a set of Access records. It works fine. What I want to do now, is since I can have a variable # of records returned depending on my search criteria I'd like to report how many records were returned.

Should be easy but if I add this line:

Range("Calculations!NumRecords") = NumRecords

Everything goes to crap and I get a swath of "#!VALUE"s in my cells.
Public Function CalcMean(Field As String, IMethod As String) As Single
'Public Sub CalcMean()

Dim QueryString As String
Dim Total As Single
Dim NumRecords As Integer
Dim Mean As Single

Mean = 0
NumRecords = 0
Total = 0
LowerLimit = Range("Calculations!LowerLimit")
UpperLimit = Range("Calculations!UpperLimit")

QueryString = "SELECT Numbers." & Field & ", Patient.[Immobilization Method] " & _
                "FROM Patient INNER JOIN Numbers on Patient.Name = Numbers.Name " & _
                "WHERE ((Numbers." & Field & "<>7919) " & _
                "AND (Numbers." & Field & " BETWEEN " & LowerLimit & " and " & UpperLimit & ") " & _
                "AND (Patient.[Immobilization Method] LIKE '*" & IMethod & "*'));"

Set db = OpenDatabase("C:Documents and SettingsAdministratorMy Documentspitch-roll-yaw.mdb")
Set rsNumbers = db.OpenRecordset(QueryString)

With rsNumbers
        Total = Total + .Fields(Field)
    Loop Until .EOF
    NumRecords = .RecordCount
    Mean = Total / NumRecords
End With
Range("Calculations!NumRecords") = NumRecords <---- Here's where I would add the line
CalcMean = Mean
End Function

When I step through the routine, the debugger will hit the line and then jump back up to my interruption point. And I can just keep cycling through it over and over. No errors, no warnings, no nothing except a whole bunch of #VALUE!s I've even put in a simple line having nothing to do with the function at all like:

And still I get the #VALUE!s However simple two line test subs like
Sub test()
    Range("Calculations!RecordNum) = 55
End Sub

Work like a charm. ARGH!!

Any help would be appreciated.


Problem Passing Excel Range To Function
Greetings and salutations y'all

I'm having a little trouble with a function which is meant to load all values from an excel range into an array then remove any null values (-9999) from the array, then put the array back into excel. Unlucky for me, I get a 'Type Mismatch' error when I try to load the range into the array.... something I am not doing.

I'd be grateful if you could take a look at my code and give me a few pointers:


Private Function RemoveNulls(ByVal srcRange As Excel.Range, ByVal lastRow As Long)
Dim i As Long
Dim xlVals() As String
ReDim xlVals(1 To lastRow)
xlVals = srcRange.Value
For i = LBound(xlVals) To UBound(xlVals)
If xlVals(i) = -9999 Then xlVals(i) = 0
Next i
srcRange = xlVals
Set srcRange = Nothing
End Function

Thanks for your time

.calculate (for Explicit Range Calculation) Cannot Find Public Functions, EXCEL VBA
Hello Experts,

if I switch off "automatic calculation" and want to calculate only singel rows [Worksheets("data").Rows(i).Calculate] the result in the cells calling self-defined public functions is "NAME?"
The function work fine if calculated automatically or manual "F9"?


Devising A Custom Excel Array/range Function For A Schedule
I've traced out the logic of a particular problem, but I lack the skill to translate it into action. I need to craft a function or formula that will choose the highest numbers within a group of arrays without exceeding an overall sum to which that array is attached. If this were a math problem, my situation would be something like:

(2 or 3 or 4 or 5) + (1 or 2 or 3) + (3 or 4 or 5 or 6) + (2 or 3 or 4) = 15

The "best" solution is one where you have to choose the largest possible number within each bracket without exceeding the final total sum. In this case, the good answers would be 4, 3, 5, 3 or 4, 2, 5, 4. I need to be able to return each item to the corresponding cell.

I am building a schedule. The final due date applies to all the parts of the set of stuff I'm making. The start date for each part gets later and later. The range of days for each stage changes as well and is entered as a maximum and a minimum.

Each of these items is part of a set that will be sold all at once, and there are (say) seven items in the complete set. The items are similarly sized and constructed so that it will take the same amount of time to fashion them.

It takes four distinct, consecutive stages to manufacture and inspect each item. So in Excel I have four columns, each representing a stage of production that needs to be completed before the next one can commence. There are seven rows, one for each item.

Each stage can take a maximum and a minimum number of days to perform. That is, we'd like that part to be built in five working days, but it can be done in two if we have to (that is, the usual last-minute rush). At some point in the course of the seven items, the start date for a single item will pass the point where we can use the maximum interval at each stage, so we need to subtract days within the schedule to make sure we make the final date.

In my exploration of the answer, I think the most economic answer would be a VBA script that acts like a function, taking in all the variables and returning a single item from the final array answer. But the Excel help screens offer limited help in creating one. Is this even possible? Could a script act like a function and recalculate with every update to the sheet?

Excel Find Function
what does the find function return if it isnt found?


I'm searching to see if a certain code is in a column and right now I'm automatically pulling the corresponding price from the 2nd column (using r.row,2) and storing it. If it isn't found, r.row is undefined

Set r = obworkbook.Worksheets(1).Range("A1:A10000").Find(oont.Text, LookIn:=xlValues)
dtemp = obworkbook.Sheets(1).Cells(r.Row, 2).Value
this is what I tried...maybe this will give you a good idea what I'm attempting to do

If r.Row Is Nothing Then
dtemp = obworkbook.Sheets(1).Cells(r.Row, 2).Value
'alternate commands
End If
I'm a dork...I had not r.row and it should have just been r.row

still doesnt work though :P


Excel FIND Function
Could anyone possibly suggest an alternative for the code below.
The reason I ask is that it is very slow, as it works on a line-by-line basis, and has to cover 12000+ lines.

What I am doing is looking for an occurence of a particular word/words, and then colouring in the line that the word sits on. As the data is variable in size then these lines are never in the same place twice.

I know its quite a big ask, but any help much appreciated.

' Colour the SBC Total Lines
IntY = 1
IntX = 0
With .ActiveSheet
.Cells.Find(What:="SBC", After:=objExcel.ActiveCell, LookIn:=objExcel.XlFindLookIn.xlFormulas, LookAt _
:=objExcel.XlLookAt.xlPart, SearchOrder:=objExcel.XlSearchOrder.xlByRows, SearchDirection:=objExcel.XlSearchDirection.xlNext, MatchCase:= _
Do Until IntX >= IntY
IntX = objExcel.ActiveCell.Row
objExcel.Selection.EntireRow.Font.Bold = True
objExcel.Selection.EntireRow.Font.ColorIndex = 10
IntY = objExcel.ActiveCell.Row

End With

' Colour the CAT Total Lines
IntY = 1
IntX = 0
With .ActiveSheet
.Cells.Find(What:="CAT", After:=objExcel.ActiveCell, LookIn:=objExcel.XlFindLookIn.xlFormulas, LookAt _
:=objExcel.XlLookAt.xlPart, SearchOrder:=objExcel.XlSearchOrder.xlByRows, SearchDirection:=objExcel.XlSearchDirection.xlNext, MatchCase:= _
Do Until IntX >= IntY
IntX = objExcel.ActiveCell.Row
objExcel.Selection.EntireRow.Font.Bold = True
objExcel.Selection.EntireRow.Font.ColorIndex = 5
IntY = objExcel.ActiveCell.Row

End With

' Colour the BAS Total Lines
IntY = 1
IntX = 0
With .ActiveSheet
.Cells.Find(What:="Total for", After:=objExcel.ActiveCell, LookIn:=objExcel.XlFindLookIn.xlFormulas, LookAt _
:=objExcel.XlLookAt.xlPart, SearchOrder:=objExcel.XlSearchOrder.xlByRows, SearchDirection:=objExcel.XlSearchDirection.xlNext, MatchCase:= _
Do Until IntX >= IntY
IntX = objExcel.ActiveCell.Row
objExcel.Selection.EntireRow.Font.Bold = True
objExcel.Selection.EntireRow.Font.ColorIndex = 46
IntY = objExcel.ActiveCell.Row

End With

Thanks a lot.

Excel Find() Function
Hi there!

I am looking for a way to preevent the use of wildcards by the MS Excel function Find().

I have to serach for the sign '*' and if the function uses wildcards it will find every cell in the entire worksheet.

Any ideas anyone?

Visit our new forum:
Feel free to contact me via ICQ# 82806695

Find And Replace Function - Excel VBA
Hi to anyone who might be able to help me. I'm looking for a way to find and replace a series of code, ~ 6 lines, with a few words. The problem is, the code is on multiple lines throughout the project and the Find and Replace function only can look at the first line when I select it all. Does anyone know how to solve this problem? I can clarify if I'm being dense...Thanks!

Find In Excel With A User-defined Function

i'm having a problem in Excel 2000. I wrote a function 'SumCriteria' wich looks like this

Public Function SumCriteria(rngTable As Range, strFind As String, ColumnToSum As Integer) As Variant
Dim c As Range
Set c = rngTable.Find(strFind, rngTable.Cells(1), xlValues, xlPart, xlByRows, xlNext, False)
SumCriteria = 0
If Not c Is Nothing Then
firstAddress = c.Address
SumCriteria = SumCriteria + c.Offset(, ColumnToSum-1).Value
Set c = rngTable.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

End Function
data on sheet starts at cell A60


if i try this function from my immediate screen everything looks good. (SumCriteria(range("$A$60:$G$70"),"ODF*",4))
The result is indeed 72,4.

But if i try this from a built-in function, the result is always 0 because 'C' gets the value Nothing

Does anybody now why and how i can fix it

tnx in advance,

Find In Excel With A User-defined Function

i'm having a problem in Excel 2000. I wrote a function 'SumCriteria' wich looks like this
Code:Public Function SumCriteria(rngTable As Range, strFind As String, ColumnToSum As Integer) As Variant
    Dim c As Range
    Set c = rngTable.Find(strFind, rngTable.Cells(1), xlValues, xlPart, xlByRows, xlNext, False)
    SumCriteria = 0
    If Not c Is Nothing Then
        firstAddress = c.Address
            SumCriteria = SumCriteria + c.Offset(, ColumnToSum-1).Value
            Set c = rngTable.FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End Function

data on sheet starts at cell A60
OBSB    58    245,40    53,40    80,70    44,30    67,1
ODAB    3    1,70    0,30    0,40    1,00    0,00
ODFI80    19    3,20    0,50    0,40    1,00    1,2
ODFX10    171    188,50    71,90    50,30    36,10    30,2
ODIV    60    30,80    6,50    8,10    5,90    10,4
ODOL    14    54,30    24,00    3,70    19,20    7,5
ODOP    4    6,60    0,00    4,10    1,00    1,5
ODRV    207    704,20    160,00    151,10    225,60    167,6
ODST    12    95,60    27,90    15,70    29,20    22,8
ODWU    75    178,50    44,20    32,30    59,60    42,4
PJ    3    59,80    15,10    9,40    24,00    11,4

if i try this function from my immediate screen everything looks good. (SumCriteria(range("$A$60:$G$70"),"ODF*",4))
The result is indeed 72,4.

But if i try this from a built-in function, the result is always 0 because 'C' gets the value Nothing

Does anybody now why and how i can fix it

tnx in advance,

Excel Range In Variant Array, 'Subscript Out Of Range' Error
Morning Guru's

I have been having problems with this function for a few days now. I'd be most grateful if one of you could set me straight with this. Here is my code:


Private Function RemoveNulls(ByVal srcRange As Excel.Range, ByVal lastRow As Long)
MsgBox srcRange.Address

Dim i As Long
Dim xlVals() As Variant
xlVals = srcRange.Value
For i = LBound(xlVals) To UBound(xlVals)
If xlVals(i) = -9999 Then xlVals(i) = 0
Next i
srcRange.Value = xlVals
Set srcRange = Nothing
End Function

The compiller gives me the 'Subscript out of Range' error on the line:
If xlVals(i)=-9999 Then xlVals(i)=0

In-fact if I try to get a msgbox or something to display any value in the array, I get the same error. The Lbound and UBound functions are returning the expected values, therefore I believe that the array definately contains data!?

Any Ideas Amigo's?


Excel Macro, Subscript Out Of Range, But Its In Range?
i have a 2d array:
wds(1 to 24, 0 to 1)
the values in the array are set up correctly, but when i try to
output the values of the array to a cell i get the subscript out of
range error.

Below is the code that ive used to write to the cells, to test
that the array has the correct values the commented out
msgbox was used. (it returned the correct values)
except when i use the same code to assign the value to a cell's value i getthe error.


dim count1 as integer
count1 = 4
For counter2 = 1 To 24
'MsgBox (wds(counter2, 0))
Sheets("Doubles Results").Range("C" & count1) = wds(counter2, 0)
Sheets("Doubles Results").Range("E" & count1) = wds(counter2, 1)
count1 = count1 + 1
Next counter2

thanks for any replies

with other languages ive used to do simple calculations like
variable += 3 to add 3 to the variable
variable ++ to increment the variable
is there something similar to this in VB?

Excel: Named Range Will Not Accept New Range Value
I have 2 worksheets in the same workbook
On worksheet "PIR-DT MTH" there is a cell (i.e. "E3") which contains a string value representing a range of cells, eg. "C4:L32"
This range value changes due to specific triggers which aren't important
In "PIR-DT MTH", there is a named range called "PIR1DB" with the same cell range that is stored in the cell "E3"
On the other worksheet "PIR-DT CAT", there is a pivot table "PIR1DTCAT" which has it's data range defined as the named range "PIR1DB" on the previous worksheet
So, the jist of the idea is that when the content in cell "E3" on "PIR-DT MTH" changes, I update the range that the workbook Name "PIR1DB" refers to
When the named range is updated, the pivot table "PIR1DTCAT" will be refreshed to show data for the changed range
I've toiled over this code over and over and over and all the variables show the correct values (using the VB Debug window)
But for some strange reason, the line which is supposed to set a new range to the Name doesn't work at all

The call to the function looks like this:
Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT", "DTCAT", "1", "E3")

The actual function looks like this (I have placed the actual values in comments at the end of the line so you can follow what is happening):

Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName As String, _
                                ObjWkSheetName As String, InfoType As String, _
                                IRNumber As String, RangeInfoCell As String)

Dim myexcel As Object
Dim myworkbook As Object
Dim sourceworksheet As Object
Dim objworksheet As Object
Dim PivotTableName As String
Dim PivotSourceData As String
Dim NameRef As String
    Set myexcel = GetObject(, "Excel.Application") 'Point to active excel application
    Set myworkbook = Excel.Application.Workbooks("IRReports.xls") 'Point to the relevant workbook
    Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName) 'Point to the relevant worksheet
    Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point to the relevant worksheet

    PivotTableName = "PIR" & IRNumber & InfoType    ' PIR1DTCAT
    If sourceworksheet.Range(RangeInfoCell).Value = "None" Then    ' C4:L33
        'There is no downtime data, hence the pivot table will not be updated
        PivotSourceData = "='" & sourceworksheet.Name & "'!" & sourceworksheet.Range(RangeInfoCell).Value    ' 'PIR-DT MTH'!C4:L33
        NameRef = "PIR" & IRNumber & "DB"    ' PIR1DB
        myworkbook.Names.Add NameRef, PivotSourceData    'This is the line which doesn't work
        myworkbook.ShowPivotTableFieldList = False
        Application.CommandBars("PivotTable").Visible = False
    End If

End Sub

Changing the Named range doesn't work
The source variable (i.e PivotSourceData) would have the correct information in but and after the line is executed and you check the source range for PIR1DB is says something completely different like "='PIR-DT MTH'!BJ9:BS38"
I have no clue where it gets this from
And everytime you run the code, the strange range changes
Even when I hardcode the value (see below) , it still doesn't work
myworkbook.Names.Add "PIR1DB", "='PIR-DT MTH'!C4:L33"

This is just weird, the fact that hardcoding the values doesn't work means something is drastically wrong somwhere
Am I using any predefined VB words?
Is the sky falling?

RESOLVED: Finding A 'range' From Within A 'range'? - Excel VBA
Relative newbie...

Wanting to get coding that will perform a search through a spreasheet range to find a unique row of data (i.e. a match to the contents of first 3 columns), returning the row No. where the match is found. I had thought that a 'range.find' would accomplish this, but have since been advised otherwise.

Anyone any ideas????

Adjacent columns in the row are for additional pertinent data. The search will be used either to retreive the additional data, or, to add to or change it.

thnx in advance,

Edited by - Remalay on 8/22/2006 6:50:32 AM

Find Macro Or Open Find &"find' In Excel Form
How can I make a macro or what is the code to open and use the "find" in excel using a form. I tried to record the macro opening the Edit then Find. Please fully explain if possible.

Thanks in advance.

Excel Named Range --&gt; Vb Range

I have an excel spreadsheet which has a named ranged which defines a table.

in i want to open the workbook goto the sheet (this i can do) then copy the table using the named excel range stored in the worksheet and then paste this table into powerpoint.

I do not know how to refrence the named range in excel. is it part of the worksheet property? i have not been able to figure that out.

i also might have problem with the pasting into powerpoint. when i paste the data will the grid from excel show up or can i paste it in as a picture?


Is There A Find Function To Find The Position Of A Character In A String?
I am using Microsoft Excel 2000.
I wanted to know if there is a way of easily finding the position of a character in a string.
My main problem is that I need to go thru a string and remove all characters that are not letters or numbers.

This is what i am doing so far: (i would be most grateful for any suggestions or help)

Dim yo As String
Sub kristy()

    Dim numberofcharts As Integer
    Dim name, name2, tempa As String
    numberofcharts = 200
    'loop thru each chart and select the title and use that as the file name
    For i = 1 To numberofcharts
        name = ActiveChart.ChartTitle.Text
        tempa = name
        name2 = StripOut(tempa)
        fname = ThisWorkbook.Path & "" & name2 & ".png"
        ActiveChart.Export Filename:=fname, FilterName:="PNG"
        name2 = ""
    Next i
End Sub

Public Function StripOut(from As String) As String
    Dim h, a, b, c, result As Integer
    Dim tempChar, temp, str1, str2, str3 As String
    StripOut = from
    str2 = "abcdefghijklmnopqrstuvwxyz"
    str3 = "1234567890"
    For h = 1 To Len(StripOut)
        temp = Mid$(StripOut, h, 1)
        a = InStr(str1, temp)
        b = InStr(str2, temp)
        c = InStr(str3, temp)
        If ((a = 0) And (b = 0) And (c = 0)) Then
            'if the character is bad then delete it
            StripOut = Replace(StripOut, temp, "")
            'put something here so that it looks for the previous thing in the string and then increments it by one
        End If
    Next h
End Function

Thanks for any suggestions!!

Edited by - kristy2710 on 6/3/2004 1:41:02 PM

How To Find The Paragraph Of A Range
i need to get the paragraph of the selection(no text selected), a code that run fastly,pl.

How Do You Use Results From Range.find?
I'm programming with Visual Basic 2005 Express. I have an excel sheet with unique serial numbers, and use the following code to find the cell holding the serial number I'm searching for:

Function Cyclers(ByVal SN) As VariantType
Dim R As Object
Dim xlValues As VariantType
R = oWS.Range("B1", "B65000").Find(txtSN.Text)
If Not R Is Nothing Then
End If
End Function

The R.Address says $B$187. But now I need to retrieve the information from $C$187 and $D$187. How would I code this in VB? The cell address will be different with each serial number.

Thanks in advance.

Problem With Range.find
I have a problem with a range.find method. Basically i have a program that works a bit like vlookup to search and copy cells. I made this a couple of years ago and it worked fine with excel97. Now I have excel 2002 and I get a "Subscript out of range" error when executing the set c = range.find line from the following code:

arvo = Workbooks(nimi).Sheets(lehti).Cells(lukum + Osoite, selectioncolumn).Value

If arvo <> "" Then
Set c = Workbooks(perus).Sheets(pagenum).Columns(optio).Find(arvo, LookIn:=xlValue, LookAt:=xlWhole, SearchOrder:=xlByRows)

If Not c Is Nothing Then

The reference to the range that should be looked for the values seems to ok. Atleast the workbookname and indexes seem to make sense when i run it. Has something changed since the '97 or was i only lucky that it worked then? I tried to search the help files and these forums but didn't get any ideas. anyone?


1st Find Right Range Then Vlookup
Hi, you look like smart guys concerning VBA and Excel issues as I see from threads' history so maybe you can help to solve my problem.

I have an Excel file with the names of some securities and the according tickers and two cells with month end dates (beginDate & endDate) on Sheet3. I would like the user to be able to select some of these securities and see monthly performances of those securities on Sheet4.

For that I have this huge separate data file with tickers in the first row and in the same column all historical price dates and in the next column the daily prices.

Are you still with me? Ok, so I am trying to make a solution where the macro searches Sheet3 column to find word "Yes", take the ticker from the same row and locate the same ticker in the data file using Find method. Then I would define a variant for the range to use VLookup to find month end prices. I want to use VLookup instead of Find because different securities might have different dates for a last price date in a given month.

So here is what I have written:

Sub FindPerformance()
Application.ScreenUpdating = False
Dim FoundTicker, SearchRange, controllerRange As Range
Dim ticker, fundName
Dim FoundCell As Variant
Dim performance, columnIndex, rowIndex, priceNow, priceLast As Integer
Dim tickerDate, beginDate, endDate As Date
Dim ws, resultWs As Worksheet
Dim resultWb, dataWb As Workbook

Set ws = ActiveWorkbook.ActiveSheet
Set resultWs = ActiveWorkbook.Worksheets(4)
Set resultWb = ActiveWorkbook
beginDate = Worksheets(3).Range("H2").Value
endDate = Worksheets(3).Range("H3").Value

Workbooks.Open Filename:="prices.xls", UpdateLinks:=0
Set dataWb = ActiveWorkbook
Set controllerRange = ws.Range("E:E")
columnIndex = 1
rowIndex = 2
priceNow = "Nothing"
For Each Cell In controllerRange
If Cell.Value = "Yes" Then
Set ticker = Cell.Offset(0, -1)
If columnIndex = 1 Then
tickerDate = DateAdd("m", 1, beginDate)
Do While tickerDate <= endDate
resultWs.Cells(rowIndex, columnIndex) = tickerDate
rowIndex = rowIndex + 1
tickerDate = DateAdd("m", 1, tickerDate)
End If
Set fundName = Cell.Offset(0, -4)
columnIndex = columnIndex + 1
rowIndex = 1
tickerDate = beginDate
Set FoundTicker = dataWb.Worksheets(2).Range("1:1").Find(What:=ticker, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
Set SearchRange = Range(Cells(1, FoundTicker.Column), Cells(64000, FoundTicker.Column + 1))
Do While tickerDate <= endDate
priceNow = Application.WorksheetFunctions.VLookup(DateValue(tickerDate), SearchRange, 2, True)
If Not priceNow = "Nothing" Then
priceLast = priceNow
End If
' priceNow = FoundCell
performance = priceNow / priceLast
If rowIndex = 1 Then
resultWb.resultWs.Cells(rowIndex, columnIndex).Value = fundName
resultWb.resultWs.Cells(rowIndex, columnIndex).Value = performance
tickerDate = DateAdd("m", 1, beginDate)
rowIndex = rowIndex + 1
End If
End If

Application.ScreenUpdating = True

End Sub
The problem is that I get "Run-time error 438 Object doesn't support this property or method" on row
priceNow = Application.WorksheetFunctions.VLookup(DateValue(tickerDate), SearchRange, 2, True)

I assume that the problem is with using SearchRange although I can't really see what should I do differently, because when I write MsgBox(SearchRange.Address) on the precedent line it shows the correct range.

Any Ideas?

How Do I Find Out The Range For A Chart?

I´m totally new to VBA and would like to ask a probably very simple question.
I have created a macro to create a chart, see below:

Sub TestaTest()
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A47"), PlotBy:= _
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Antal"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub

How could I make this macro work, even though I do not know the number of rows containing data?
I will use it on an output that could differ from time to time.
This means that the value "D7" (see macro above), could differ from time to time, how would I find out this value?



Problem With “Find” In Range Object
I have coded a function called “FindIndexRow”using the Find method in Range Object. The function is used to search through an Index Workbook [oIndexWB]to get certain key data. The key data [in A column] are arranged in 1 row per item. When there is a match, it returns the row numeric number.

It has been proved working in one part of the program where its index content can be copied to a worksheet of another workbook. But, when it comes to work with oWB / worksheet 1, the Find method failed. “Find” can’t find the match. In this case, identical data is searched through the index list. n is the loop inhibitor such that the function will not be allowed to find the data forever. Normally it will find the data upon the first hit, that is n = 1.

Is there any bug in the coding that causes this problem.. ?

Option Explicit
Dim oWB As excel.Workbook
Dim oWS As excel.Worksheet
Dim oIndexWB As excel.Workbook
Dim oIndexWS As excel.Worksheet

'oWB workbook already opened above this line

sPath = "H:VB6DataTemplateRhkjcIndex.xls"
oExcel.Workbooks.Open sPath
Set oIndexWB = oExcel.ActiveWorkbook
Set oIndexWS = oWB.Worksheets("Sheet1")

If oWS.Range("F1").Value = "" Then Debug.Print
sBurntMark = oWS.Range("F1").Value
sBurntMark = TrimALL(sBurntMark)
iStartRow = FindIndexRow(oWB.Worksheets("Sheet1"), "A", sBurntMark, 3, , True)
If iStartRow = 0 Then
Debug.Print "Can't find index"
GoTo CleanIndexWB
End If
oWS.Range("E1").Value = oIndexWS.Range("D" & iStartRow).Value
oIndexWB.Close (False)
Set oIndexWS = Nothing
Set oIndexWB = Nothing
Function FindIndexRow(oWorkSheet As excel.Worksheet, ByVal sCol As String, _
ByVal sFind As String, Optional ByVal iRow1 As Integer = 2, _
Optional ByVal lRow2 As Long = 65536, Optional bUp As Boolean = True) As Integer
Dim rngSource As Range, oCell As Range, rng As Range
Dim sAddress As String, n As Long
Dim aArray As Variant

FindIndexRow = 0
If bUp Then
Set rngSource = oWorkSheet. _
Range(sCol & iRow1, oWorkSheet.Range(sCol & lRow2).End(xlUp)) 'Search column
Set rngSource = oWorkSheet. _
Range(sCol & iRow1, oWorkSheet.Range(sCol & lRow2).End(xlDown))
End If
For Each oCell In rngSource
Set rng = rngSource. Find(sFind)
n = n + 1
Debug.Print n
If n >= 10 Then Exit For
If Not rng Is Nothing Then Exit For
Next oCell
If Not rng Is Nothing Then
sAddress = rng.Address(RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlA1)
aArray = Split(sAddress, ":")
If UBound(aArray) <> 0 Then
FindIndexRow = GetNumerics(aArray(0))
FindIndexRow = GetNumerics(sAddress)
End If
End If
Set rngSource = Nothing
Set oCell = Nothing
Set rng = Nothing
End Function

Find Method - How To Include After:=Range
This is my first post so please go easy on me...

Concerning the Find Method, I understand that the search will begin only after the specified cell and will not include it until it wraps around the specified range.

Does anyone know if there is a way to include it ? Right now what I do is I specify the beggining of the search as the last cell in the Range so that the search actually includes the first cell in of the range in its first pass.

Please let me know if there is a way to include the first Cell and if there isn't then does anyone know the reason why not?

Thank you,


Special Caracters - Range.Find
When I use this code

sItemDesc = PERMAFIL LTE~L16 LIN 16"x12'0"

Set mRange = Columns(1).Find(What:=sItemDesc,after:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext)

To Find a cell that contain:

PERMAFIL LTE~L16 LIN 16"x12'0"

I it unable to find it

Does (") or (') or (~) causing problem?

Find &amp; Replace Plus Format The Range
Hello All,

A particular range say ("A") in my excel sheet will contain the values like '5676787878785'. The requirement is to format the range into "Text" Format. Then, i need to change the values in the range to 5676787878785. ie., i need to remove the single quotes in either side of the value for the cells in particular range.

Please do help me out with your suggestions as to how to do in MS-Access VBA.

How Do I Find An End Cell In A Changing Range
In Excel i have a data range from A1 to C234. The number of rows in this range can vary. In a macro I want to go to Cell D234. ie go to the last row and move right to the first blank cell.
eg if the data range is A1 to C500. I want to get to Cell D500

Please! Help Me To Find Something To Select Date Range In VB!
I need to add possibility to select date range on the form. 2 DTPickers are not too good.

Find A Left Over Number Range

I have number range from 1 to 10000

i have programe to take out some number say 101 to 200

then 401 to 450 and 801 to 900

How i will find left over number range

1 to 100
201 to 400
451 to 800
901 to 10000

please help me to find this number range from a to b


Find Lowest Date In A Range

I have 4 textboxes in VB. All these contain some date or the other. Now i have to write an efficient program to find out which of the textboxes contain the lowest/minimum date.

Can someone help me with this?

Ex: (dd/mm/yyyy)

TextBox1: 04/01/2005
TextBox2: 03/01/2005
TextBox3: 01/01/2005
TextBox4: 02/01/2005

Result for lowest date = : TextBox3.Text

I hope this makes sense.


How Do You Find Font Size Range?
I need to be able to testing for a font size value for a given font name. Any ideas? I already have the code to verify that the font name selected is valid but not for the size.

How Find The Number Of Non Blank Rows In An Range
  Guys, i need help in finding number of non blank rows in a range.


Find Cell Values In Discontinous Range
I am trying to find out the name of the column (the value in row 1) from each column in a discontinuous range. I want to add each of these values to a collection for processing later. I started with the following function, but I think I am going about this all wrong.
Function FindColsInRange(rng As Range)
  Dim ColNames As New Collection
  Dim ws As Worksheet
  Set ws = rng.Parent
  Dim i As Integer, NumCols As Integer, n As Integer
  Dim NumArea As Integer
  n = 1
  NumArea = rng.Areas.Count
  For i = 1 To NumArea
    NumCols = rng.Areas(i).Columns.Count
    Do Until n = NumCols
      If ws.Cells(1, n) 'In the original range Then
        'Add this cell to the collection
          ColNames .Add ws.cells(1,n).Value, CStr(ws.cells(1,n).Value)
          n = n + 1
      End If
  Next i
  FindColsInRange = ColNames
Exit Function

Range Function
Hello All!

I'm new here, was hoping someone can help me out.

I am using VB 6.3 in excel 2003, and I have the following line of code which breaks with an error:


The only thing I see wrong with this line of code is the fact that the word "Range" is not capitalized (it usually is!) However if I try to capitalize Range it automatically changes to lowercase range.

Why does it change to lowercase range automatically?

Thanks a lot!

Assignment To Word TableX.Range.Find.* Fails
I'm working with VBA for Word and I'm running into an assignment failure when I try to set Find members in a Table.Range.

By way of introduction in my first post here, I'm a VB and VBA relative newbie; I got "voluntarily drafted" to create some utilities for my team at work after I built a little tool, which people found useful, as a way of self teaching myself VBA. So I'm still finding lots of places where I have to stop and learn some detail that's kind of "doh, obvious" once I figure out what silly little nuance I didn't understand.

Here's the complete code with comments indicating the failing assignments. Any aid will be greatly appreciated. Thanks!

Sub Main()

Dim appWord As Word.Application
Set appWord = New Word.Application

Dim tableX As Word.Table
Dim sStyle As String

appWord.Documents.Open FileName:="Sample.doc"

' This succeeds, I can see tableX and it is populated as expected.
Set tableX = appWord.ActiveDocument.Tables(2)

' tableX.Range is populated as expected.
' tableX.Range.Find is populated with default values at this point.

' None of these assignments populate the lvalue.
tableX.Range.Find.Text = "Hello World!"
tableX.Range.Find.Style = appWord.ActiveDocument.Styles("My Style")
tableX.Range.Find.Style = "My Style"

' These assignments all populate the lvalue as expected.
sString = appWord.ActiveDocument.Styles("My Style")
appWord.Selection.Find.Text = "Hello World"
appWord.Selection.Find.Style = appWord.ActiveDocument.Styles("My Style")

Set appWord = Nothing

End Sub

How To Find Out If Current Selected Cell Is In A Certain Named Range
Is there any way I can tell if the current selected cell is in a certain named range. I am going to be using it with the selection change function of the worksheet.

Many thanks

How To Escape Wildcard Match For Range.find And FindNext.
How do I write the equivalent of .MatchWildcards = false (for Word) in Excel? My data contains "*" and I have to match these characters NOT as wildcard.

Thanks in advance.

Find Matching Entry, Then Copy And Paste A Range
I have what I hope is a simple question, but have not been able to find an answer in my searches of the forum.

I have two workbooks (well call them WB1 and WB2). The workbooks will have a varying number of rows. I would like the macro to loop down column A in WB1 and attempt to find a match of that cell value in column A of WB2. If it finds a match, it will copy columns C:E out of WB2 and paste it in cells C:E of of WB1. If it does not find a match in WB2, it would move down to the next cell in column A of WB1 and repeat until it reaches the bottom of column A in WB1.
Any help would be greatly appreciated!!

Vb/Excel: How To Read An Excel File In A Column WITHOUT Specifying The Range ??
Hello, all.

I found a bit of code that lets me read an Excel file from and to a specifi
range. See the code below. But what if I only want to specify the STARTING
position and simply want it to continue reading down the column until it
hits an empty cell?

Does anyone know how to do this? Thanks!

Here is the range-only code:

' ** turn screen updating off, prevent the user from seeing the source
' ** then find the file and identify both the worksheet and range
Application.ScreenUpdating = False
Set sourcewb = Workbooks.Open(file, False, True)
listitems = sourcewb.Worksheets(sheet).range(temp_range).Value

' ** close the source workbook without saving changes, then reactive
screen update
sourcewb.Close False
Set sourcewb = Nothing
Application.ScreenUpdating = True

' ** read the data, convert values to a vertical array
listitems = Application.WorksheetFunction.Transpose(listitems)

For count = 1 To UBound(listitems)
' ** read the info into a temporary field
temp_var(count) = listitems(count) ' ** populate the array
Next count

' ** no items selected, set to 0 to select the first item
'Combo_machine.ListIndex = -1

Close Workbook

Range Getting Function That I Need To Work

Here is the deal. I have a function that is supposed to add numbers up, not so bad right. Well there is a range named tmnrng that gets passed to this function. It is simply B2:B49, it contains times of the day. there is another range namve dayrng it is also passed. It is some cells in column 50 that another sub defines(that sub works), it contains day names. Then I pass a string that is a day name, and date that is a time of day. so now what I need to happen is have the function sum all cell values that are on the right day of the week, as specified by the Day string, and at the right time as specified by the date that I pass. Are you with me?

the issue is that I can't predict how many instances of a day will be in the dayrng. So, here is the funciton:

Function Get_Total(tmnrng As Range, dayrng As Range, tmn As Date, day As String) As Long
Dim wks As Worksheet, righttmn As Range, rightday As Range, firstAddress As String
Dim i As Long, a As Long, cell As Range
Set wks = Worksheets("Sheet1")
With wks
Set righttmn = tmnrng.Find(tmn)
Set rightday = dayrng.Find(day)
If Not rightday Is Nothing Then
firstAddress = rightday.Address
Set rightday = Union(rightday, dayrng.FindNext(day))
Loop While Not rightday Is Nothing And rightday.Address <> firstAddress
End If
For i = 1 To rightday.Count
Set cell = Cells(rightday.Row, righttmn)
a = a + cell.Value + cell.Offset(0, 1).Value
Next i
MsgBox a
End With
End Function

Im getting a runtime error '1004' "Unable to get the FindNext property of the Range Class" error message.

Help please

Date In Range Function
Can anyone give me a function that will tell me if a certain date is in the range of another two dates?
It sounds a simple task, but I can't manage to do it.

How To Find The Date Range In MS Access And Display In MS Flex Grid?
How do i search the date range which can be specified by the user and all the records from these date range will be display in MSFlexGrid. Can someone help me with this??


Subscript Out Of Range Error With This Function.. Any Help?
Im getting a subscript out of range error with my function. I probably don't have the redim function correct but i'm not sure. Any help?

Public Function GetFilesInDir(strPath As String, Optional strExt As String = "*.*") As String()
Dim i As Integer
Dim strFile As String
Dim fileList() As String

strFile = Dir$(strPath & strExt)
Do While strFile <> ""
fileList(i) = strFile
strFile = Dir$
i = i + 1
ReDim Preserve fileList(i)
ReDim Preserve fileList(i - 1)
GetFilesInDir = fileList
End Function

Copyright © 2005-08, All rights reserved