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




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.


Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If frmCF.Visible = False Then
Else
frmCF.lblsearch.Caption = ActiveCell.Address
DoEvents
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.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
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
Paul

Excel, Return Range Of Selected Cells
Hi there,

Does anybody know how I can reference a selection of cells in a worksheet in Excel during runtime? i.e. if a user makes a selection, then runs a macro, I would like that macro to be able to call upon those cells selected.

Thanx

Find Selected Area In Excel
Dear VB users,
I have a problem to find out what the selected in Excel is.

- Opening Excel sheet in VB5,
- select an area,
- with Alt Tab back to the VB5 program,
- ?

On the question mark I want to find the coordinates of the area.
Can someone gives me the source?

Nice regards,

Ingrid.

Excel Range Find Function In VB
Hi,

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:


Code:
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
Else
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
Else
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?

VB To Find A Range In Excel Document
Hi,

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"
.Workbooks(1).Worksheets(1).Select

count = 1

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

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

count = count + 1

Wend

objXLApp.Workbooks.Close

Form2.List1.ListIndex = 0

Form2.Show

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.

Example:

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.

Rgds,
Daniel.

Excel - Find Position For Selected Text
Sub find_eor()
'
' find_eor Macro
I am trying to return the row position of the column which contains text "end of report", so that it can be included in the 'Range' selection, instead of the 'Range("A1:P506").Select.


' Macro recorded 26/07/2005
'

'
Dim lastRow As Integer

Cells.Find(What:="end of report", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
' lastRow should return the Row position of the selected text
< lastRow = Selection.Position.Rows >
Range("A1" & lastRow).Select
'Range("A1" & lastRow).Select
Range(lastRow).Activate
End Sub

What is the shortcut for Intellisense in VBA for MS Excel 2000?

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

Find Whether Selected Object Is Excel Sheet In A Word Document!!!
Hi,
I want my program to detect an excel sheet. Actually I'm developing a parser for word document and I'm moving from Top to End of doc. I'm working on selection property and check for the predefined book marks for line, table, etc.


Code:

Selection.bookmarks.exists(" able")=True



But I've not found any way to detect an excel sheet during parsing. Even bookmarks do not work for excel sheet. Hope I'm clear. Can anyone help me out?

Thanks,
Subha

Find Whether Selected Object Is Excel Sheet In Word Document
Hey All,
I want my program to detect an excel sheet.
Actually i am developing a parser for word document...and i am moving from Top to End of Doc. i am working on selction property...and check for the Predefined bookmarks for Line, Table etc.

Selection.Bookmarks.Exists("Table") = True


But i have not found any way to detect an Excel Sheet during parsing. Even Bookmarks do not work for Excel Sheet. Hope I am clear
Can any body help me

.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"?

Johannes

Dates: First &amp; Last In Selected Range
I'm trying to find some kind of code that will determine the earliest and the latest date within a selected Excel range.

i.e., 01/10/73
04/01/94
06/05/02
03/11/80

In this case it would be 01/10/73 to 06/05/02.

This has been quite the difficult thing to figure out and would hope that someone that has more clue than I do would have something to offer.

Thanks in advance,
Aaron

Get User Selected Range
Summary: The user selects a block of cells, and then starts the macro. The macro has to get the selected range before running actions on the cells.

Hiya,
I'm surprised I couldn't find code to do this among the other postings - I did try! As ever, I expect one particular keyword would get lots of hits - but it's knowing what that keyword is...

The code I've written so far is

Code:
Sub GetUserSelection()

Dim startRow As Long
Dim end__Row As Long
Dim startCol As Long
Dim end__Col As Long
Dim myRow as long
Dim myCol as Long

'Get User selection
startRow = <####code to get top row of selection####>
end__Row = <####code to get bottom row of selection####>
startCol = <####code to get leftmost column of selection####>
end__Col = <####code to get rightmost column of selection####>

'Loop thro' the cells

For myRow = startRow To end__Row
For myCol = startCol To end__Col
'blah blah blah
'This code is dependent on whether row and column numbers are even or odd
Next
Next

End Sub
Hopefully this is self-explanatory; How do I set the variables startRow, end__Row, etc to the limits of the selected range?

Many Thanks

Joolz

Determine The Range Of Selected Cells?
Hello,

If the user selects a range in excel, how can I determine the first and the last row or column of the selected cells?

Best regards,

Gert

User Selected Range Across Workbooks
I am trying to write a sub that will allow the user to select a range and a collection of files. That range will be pulled from each of the files selected and then pasted to a new workbook (same sheet). I will also be doing the same thing but instead to one sheet give each file its own sheet. I have a problem with the range. When I grab the range from the user, I can immediately select that range in the open worksheet. However, when I move over to the next workbook, it seems as if its out of scope or something. The error says object required. I have stripped the code down as much as I can to the part that is causing the problem.


Code:

Public userRange As Range

Sub test1()
fn = GetUserFiles()
Workbooks.Open fn(1)
Set temp = ActiveWorkbook
Set userRange = GetUserRange()
userRange.Select
temp.Close
Workbooks.Open "C: est.xls"
Worksheets("Sheet1").Select
userRange.Select

End Sub

Function GetUserFiles()
fn = Application.GetOpenFilename("Excell Worksheets (*xls.),*.xls", , "Source Files", , True)
If TypeName(fn) = "Boolean" Then Exit Function
GetUserFiles = fn
End Function

Function GetUserRange() As Range
Application.ScreenUpdating = True
Dim oRangeSelected As Range
On Error Resume Next
Set oRangeSelected = Application.InputBox("Please select a range of cells!", _
"Select A Range", Selection.Address, , , , , 8)
If oRangeSelected Is Nothing Then
MsgBox "Action canceled!"
Exit Function
Else
Set GetUserRange = oRangeSelected
End If
Application.ScreenUpdating = False
End Function
Cross Posted:
http://www.mrexcel.com/forum/showthread.php?t=336209
http://www.xtremevbtalk.com/showthre...17#post1301417
http://www.ozgrid.com/forum/showthre...454008#Similar

Renaming Values In A Selected Range.
Hi there, i'm a bit of a beginner and am having issues with the language.

I'm trying to make it so i can refer to the values in a selected range.

eg

Dim XValues As String

Range(Selection, Selection.End(xlDown)).Select

Xvalues = <The Values in the range>

Any ideas?

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:


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?

Thanks

Verifing User Has Selected Correct Range
I have created a list in AB10:AB34 which I wish to have a user select from. The user can select a portion or all of the list
using the standard method for selecting a range of cells
on the active sheet.
Once selected, I can copy the values to an array and
search a different wkbk for these values.

My question is; How can I be sure the user has selected
from within the correct range on the worksheet ? At the moment I am getting Error 91 as the code enters the If statement.

Sub zTest1()
Dim i As Integer
c = 1
sMonthRange = ActiveWindow.RangeSelection.Address
iMonthCount = ActiveWindow.RangeSelection.Count
sRange = Left(sMonthRange, 6)
Range(sRange).Select
Dim listrange As Range
Set listrange = Range("AB10:AB34")

If Selection.Address = Intersect(listrange, Selection).Address Then
'all ok
For i = 1 To iMonthCount
sMonth(c) = ActiveCell
ActiveCell.Offset(1, 0).Range("A1").Select 'Down
c = c + 1
Next
Else
'not the same
End If
End Sub

VB 6 Data Report Within Selected Date Range
Please tell me How Build the Data Report In VB 6 withing the Date Range. that i use the SQL Between Statement to Build IT But It Does Not Work. also I want to select that data Range From DTPicker on the Another forme & I Want to know how that Two Parameters Passe to the SQL Satatement.lease Tell Me Any one Who know about it.

 

Edited by - suranga on 4/21/2003 11:47:38 PM

Pausing A Macro To Get A User Selected Range Of Cells
I need help with a certain code. I am working on a spreadsheet which isfiltered and I am trying to pause my macro so that the user can selectan area after it is filtered. Area selected is dynamic as amount oflines filtered will change each time. I found this code for selectingone cell but can't get it to select a range of cells. Below is thecode. Since I am new to VBA coding I am not sure what exactly it is Ineed to change. Any help is appreciated.



Dim UserRange As Range
Output = 565
Prompt = "Select a cell for the output."
Title = "Select a cell"
' Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8)
'Range selection
' Was the Input Box canceled?
If UserRange Is Nothing Then
MsgBox "Canceled."
Else
UserRange.Range("A1") = Output
Selection.Copy
End If
End Sub

Thanks
Delta

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.

Code:


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

ps.
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
    Else
        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
        objworksheet.PivotTables(PivotTableName).PivotCache.Refresh
        
        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,
remalay



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

Crystal Report - Export Selected Page Range To Text File. Please Help.
Hi!

I'm using CR 8.5 & VB6.
I have a multiple pages (19 pages) crystal report previewed within my vb application.
Exporting this report to text file is quiet possible as far as i know but is it possible to export this report with the defined page range? Shall i say export page 10 to 19 only, is it possible?

Please help.

Or any suggestions please...

Thanks in advance!

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
Hello,

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

in vb.net 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?

Thanks

Help With Find - Selected Text
Can any one help?
I have a find next function that needs to do two things:
1) find a word in a txt file - which it does fine
2) find a word from selected text within that file i.e. If I select two paragraphs from the whole file just search for the word within those not the whole lot

My code does the first requiremnet but not the second and i'm at a total loss any ideas?

Thanks in advance

How Do I Find The Name Of The Currently Selected Application?
How would I go about finding the title of an application which gains focus (just after my app loses focus)?

I would like to use it in an AppActivate event.

Thanks in advance.

Sstab -find Out Which Tab Selected
In SStab i want to find out which tab is selected
how can i do it?

ListView1 Find Selected Index
I did a search and could not figure out how to find the index of the selected item in the listView1. Thanks
http://apldn.apl2000.com/apllibrary/...roperties_.htm


Code:
'this does not seem to be working
If (ListView1.SelectedItem.Index <> -1) Then

How To Find If Treeview Item Is Selected
I need to know if any nodes are selected in a treeview I've tried loads but nothing.

If TreeView1.SelectedItem = Nothing Then
MsgBox "Blah Blah Blag"
Exit Sub
else
'Something Else
End If




Help

Find Selected Item In ListView
Hi there. I hope someone can help me out.

I'm trying to find which row is selected in a ListView without having to loop through all the rows. Only one item can be selected at a time, so that should make it somewhat easier. I just need it to be fast since the lists can grow to well over 150,000 items!

Thanks!
Amp

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:

Code:
Function Cyclers(ByVal SN) As VariantType
Dim R As Object
Dim xlValues As VariantType
oWS.Range("A1:Z65000").Select()
R = oWS.Range("B1", "B65000").Find(txtSN.Text)
If Not R Is Nothing Then
MsgBox(R.Address)
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.
Scott

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?

thanks

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:

Code:
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)
Loop
End If
Set fundName = Cell.Offset(0, -4)
columnIndex = columnIndex + 1
rowIndex = 1
dataWb.Activate
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
Else
resultWb.resultWs.Cells(rowIndex, columnIndex).Value = performance
tickerDate = DateAdd("m", 1, beginDate)
rowIndex = rowIndex + 1
End If
Loop
End If
Next

resultWb.Activate
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?
Hi,

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()
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A47"), PlotBy:= _
        xlColumns
    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?

Thanks,
/Hyllplan

 

Find Co-Ordinates Of Selected Cell In A Grid
I have a grid where the cells in the last column are editable.

When the user selects a cell in the end column I call up a small form, where they input a date.
What I want to do is have the form appear directly over the active cell.

Obviously the active cell could be anywhere on a vertical plane within the constraints of the grid.

Has anyone ever come up with some clever algorithm whereby you can set the .Top and .Left properties of the form appearing over the grid in the right place, i.e. over the active cell ?

I'm playing arund at the moment but one minute it works and then on a different cell it works not so well, so I htought I would ask on here.

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

Code:
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
CleanIndexWB:
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
Else
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))
Else
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,

Gonzoxxx

Special Caracters - Range.Find
When I use this code

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

Code:
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
Thanks
Ollie

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
Hi

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
ie

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

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

thanx
Ritesh

Find Lowest Date In A Range
Hello,

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.

Regards,
~S

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
Hi,
  Guys, i need help in finding number of non blank rows in a range.

Thanks,
 Muddalli

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.
Code:
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
    Loop
  Next i
  FindColsInRange = ColNames
Exit Function
 

* RESOLVED *Can You Use SELECT CASE To Find Selected Radio Button ?
Can you use a SELECT CASE statement to find out which radio button has been selected ?

If so, can anyone tell me the syntax ?

Thanks a lot.

Copyright © 2005-08 www.BigResource.com, All rights reserved