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




Please Tell Me Where Can I Find Help On MS EXCEL...


I need some serious answers in MS Excel...do you know where can I get it from???




View Complete Forum Thread with Replies

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

Find Location Of A Cell In Excel-import From Excel Into Access
Hi
I want to import some columns from an excel spreadsheet into an access table using vb6.

I am going abt this by finding the location of the cell but am having problems this way


Code:
Private Sub ImportExcelAttend()
Dim imA As New ADODB.Recordset 'Recordest for temp table
Dim ex As New Excel.Application
Dim I, J, a As Integer
Dim TheFile As String

imA.Open "select * from tbl_tempAttend", MyConn, , adLockOptimistic

ex.Workbooks.Open txtLocation.Text
ex.Cells(I, J) = "%att"


however i cannot get it to work. any help this way would be appreciated.

OR if you know another way to import these few colums with unknown location of cells into access i would be very grateful. Location will always change every time.

Regards
JohnnyH

Find A Particular Comment In An Excel Worksheet Using Excel VBA
If I can select all the comments in a worksheet using VBA: -

    Set rg = Selection.SpecialCells(xlCellTypeComments)

why can't I cycle through all the selected cells to find out their individual values?

I want to home in on a particular cell (containing a comment) to do some processing. The above line manages to select all the comment-containing cells. But I cant seem to "find" the cell I want.

Remember we have to do it using VB Editor in Excel.

Thanks for going through the problem

Ali

VBA Excel Find / Find Next
Good morning good people.

I find that what I need MS find is not sufficient for my needs. I want to find a number of text in a Excel cell and make the cell bold.

I can do this with the first one, but I am having trouble do this for each one in the spreadsheet. With the following code I can use it to find and replace, but I want the user to have control and search for the first occurrance (and bold it) and then either click the find button again or another command button (find next) to go to the second occurrance (3rd, 4th, 5th, etc.).

Any suggestions? DAVE

Private Sub cmdFind_Click()
Dim Counter As Integer
Private Sub cmdFind_Click()
With Worksheets(1).Range("A12000")
   'Find
   Set c = .Find(txtFind.Text, LookIn:=xlValues)
    
    If Not c Is Nothing Then
       firstAddress = c.Address
        
       'Replace
       Do
            c.Rows.Select
            Selection.Font.Bold = True
            Set c = .FindNext(c)
       Loop While Not c Is Nothing And c.Address <> firstAddress
    Else
      MsgBox "Sorry " & txtFind.Text & " was not found."
    End If
End With
End Sub



Edited by - wheels on 4/9/2008 4:43:10 AM

Find Last Excel Row
Hi. I have an Excel macro that copies some rows from one worksheet and then pastes them onto another worksheet. This is working fine.

Now, I want to find the last row number that contains values and then set that as a variable. I have no idea how to do this.

Any help would be great. Thanks

Excel Find
How do I have the find function just check the first 4 characters of a cell?? I.E.

RUBY1.25MMADC

that cell would still be found my the find function if I'm just searching for Ruby...I'm confused Here's the code I'm trying


Code:
set r=obworkbook.sheets(1).range("A1:A10000").find("RUBY")
not an exact copy and paste, but it's the find function that's important....I don't have a clue how to go about this

Also, my eventual goal with this is to then find the last occurence of it....how do you use the findlast function in excel?? I couldn't get it to work...thanks!!

Excel - Find All...
Within Excel's Find, you have a button called 'Find All' which returns all of the cells, their formulas, and values which match your criteria.

Is there a way to do this programmatically?


I know I can use VBA's FindNext and loop through all of the cells which match my criteria. Is this my only option?

-Jeff

Find X Value In Excel
Hello,
This is my first program in VB.
I want from VB find a value that is in excel.

EXCEL
Column 1 Column2
10/10/2003 30
11/10/2003 20
..
30/1/2004 40
.. ..

If I have x = 11/10/2003. I want to find this value and take the column2 value.
I don’t know how many rows are.

Please help me. I am working on this and I am not making any progress.

THANKS A LOT

Find Last Row In Excel
Hi there,

I'm writing a program that goes through an Excel file, grabs some data and match it with some SQL data. The number of rows in the Excel changes quite often, so I'd have to change the 1250 value. This is my code so far:
Code:
For i = 2 To 1250
Cells(i, 6).Select
strExcelPartNo = Trim$(CStr(ActiveCell.Text))
If strExcelPartNo = "" Then
strExcelPartNo = Cells(i, 4)
Else
' Get data from SQL and match it with Excel fields
End If
Next i
Cells(i, 4) are my reference point, since Cells(i, 6) might be empty; in other words first empty cell in the 4th colomn signals EOF.
I searched the forum and could not come up with anything. I tried at one point to do this:
Code:
c = 1
Do
c = c + 1
Loop While Not Range("D1").End(xlDown)
hoping that I can do a
Code:
For i = 2 to c
but it did not work...
Can anyone help me please?
Thanks,
poli

Find Excel
Hello,
I need to get a a number from a certian cell from an open excel document. I know you have to use FindWindow or something and how do you actually get the text too. Note: the excel document may not be saved so I cant actually open the file. Also if I could keep the focus on the excel document the entire time so this program runs in the backround. Any ideas or links?

Thanks

Find Next Row In Excel
I am copying a row from sheet1 to sheet2 in Excel. How can I find the next available(empty) row on sheet2 to paste to.

Column A is the date, so it will never be empty.

Thanks for any help.

JO

Find Excel.exe
Im using a shell command to execute Microsoft Excel and open an Excel spreedsheet.

I've had to hard code the location of Excel, therefore if the user has installed Excel to somewhere other than 'C:Program FilesMicrosoft OfficeOffice' it fails.

Is there a way to find the location that Excel is installed on the users machine?

Excel: Need To Find The Last Row / Next Available Row
I need a good way to find the next available row in a long excel spreadsheet . The next available row is blank. All rows above will have something in at least one cell. There are 20 columns, and potentially thousands of rows.

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

I.E.

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


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


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

still doesnt work though :P

Thanks!!

VB Can't Find Excel Macro
The following code fails with an error 1004 macro Translate_CSI_and_Create_Chart cannot be found. Everything else works, with the exception of the xlApp.Run command. The object definitions are in a separate module and are defined as follows:
'======================================================+
' Object Definitions |
'======================================================+
Public xlApp As Excel.Application
Public xlBook As Excel.Workbook
Public xlMacros As Excel.Workbook ' Personal.xls macro workbook
Public xlBooks As Excel.Workbooks
Public xlsheet As Excel.Worksheet
The following locations are correct in the program since they were copied via CTRL-C from Windows explorer and Excel respectively.
Path:C:Program FilesMicrosoft OfficeOfficeXLStartPersonal.xls
Macro Name:Translate_CSI_and_Create_Chart
Can you shed any light on this? I had it working at one point, but had to rebuild my C drive after a crash and it has failed consistently since then. I have tried both the entire path name and just the macro name in the variable str_Macro_Executed without success. I am running the Office97 suite under Windows 98. All patches have been applied. Thanks for your time.


Dim int_Counter As Integer
Dim str_Select_Statement As String ' save area for original select statement
Dim str_New_Select_Statement As String ' area for new select statement
Dim str_Select_Clause As String
Dim str_Order_By_Clause As String
Dim str_Where_Clause As String
Dim str_From As String
Dim str_To As String

Dim lng_Rcd_Ct As Long
Dim lng_Progress As Long
Dim lng_Len As Long
Dim int_File_Year As Integer
Dim str_File_Month As String
Dim int_Str_Len As Integer
Dim str_Macro_Workbook As String
Dim str_Macro_Executed As Variant
Dim str_Limit As String ' format yyyym All contract dates
' must be > than this
Dim str_Contract_Year_and_Month As String ' format yyyym as above

Const str_Quote As String = """"
Const cstr_Select_Clause As String = "SELECT * FROM 15_Mass_Update_File "
Const cstr_Where_Clause As String = " WHERE fld_Update_Name = "
Const cstr_Order_By_Clause As String = " ORDER BY fld_Update_Name"

On Error GoTo Error_Handler

' The following variable is the lower limit for analyzing contract prices
' All contract dates must be greater than this
str_Limit = Trim(gstr_Current_Year) & LCase(gstr_Current_Month)

str_Macro_Workbook = Trim(upr.str_Macro_Path) & "" & _
Trim(upr.str_Macro_Workbook)

str_Macro_Executed = Trim(upr.STR_MACRO_NAME)

Set xlMacros = GetObject(str_Macro_Workbook)

Do While lst_Update_Names.ListCount <> 0
lst_Update_Names.RemoveItem (0)
' Build Select statement with Update name
str_Select_Clause = cstr_Select_Clause
str_Order_By_Clause = cstr_Order_By_Clause
str_Where_Clause = cstr_Where_Clause & str_Quote & _
lst_Update_Names.List(0) & str_Quote

str_New_Select_Statement = str_Select_Clause & _
str_Where_Clause & _
str_Order_By_Clause

Adodc1_Mu.RecordSource = str_New_Select_Statement

Adodc1_Mu.Refresh
lng_Rcd_Ct = Adodc1_Mu.Recordset.RecordCount
prg_Mu.Max = lng_Rcd_Ct ' Progress bar record set size

Do While Adodc1_Mu.Recordset.EOF = False
str_From = txt_From_Path.Text
str_To = txt_To_Path.Text
str_Workbook_Name = txt_File_Name.Text
int_Str_Len = InStrRev(str_Workbook_Name, ".", -1) - 1
str_Worksheet_Name = Left(str_Workbook_Name, int_Str_Len)

' String contract yr & mo together for comparison to str_Limit
str_Contract_Year_and_Month = _
Adodc1_Mu.Recordset.Fields("fld_Contract_Year") & _
LCase(Adodc1_Mu.Recordset.Fields("fld_Contract_Month"))

If str_Contract_Year_and_Month > str_Limit _
Then
Set xlBook = xlApp.Workbooks.Open(str_To)
Adodc1_Mu.Recordset.MoveNext
Else
Adodc1_Mu.Recordset.MoveNext
End If
Loop

' Now that all the proper workbooks are open, run the analysis macro
xlApp.Visible = True

' Set xlsheet = xlBook.Worksheets(str_Worksheet_Name)
xlApp.DisplayAlerts = False

Set xlMacros = xlApp.Workbooks.Open(str_Macro_Workbook)
xlApp.Run (str_Macro_Executed)

xlApp.DisplayAlerts = True

DoEvents

Loop

Exit Sub

Error_Handler:

How To Find # Of Rows In Excel Using VB?
I'm using Visual Basic Express 2005 Beta and have the automation (thanks to this site) of Excel working to the point I can open an existing workbook, and can read data from a specific cell:

---
Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
oExcel = New Excel.Application
oExcel.Visible = False
oExcel.Workbooks.Open("C:cyclers.xls")
oWS = oExcel.Worksheets("Sheet1")

txtSN.Text = oWS.Cells(2, 2).value
---

The problem I have is the number of rows may vary, so I need to be able to use a loop to go through all the rows to find a match. I thought "for each row in .... " would work but I can't seem to get the syntax correct.

Does anyone have a suggestion I might follow that leads me to a solution?

Thanks in advance,
Scott

Need Help Find/Replace In Excel
Hi -

I'm new to this and was wondering how I do I would set this up.

I have values in column F of my worksheet named 'Submission'.

What code do I use to say:

Search within worksheet Submission, column F for " " (spaces) and replace with "+"

Thanks in advance... just tyring to finish a project for work!

Need Help Find/Replace In Excel
Hi -

I'm new to this and was wondering how I do I would set this up.

I have values in column F of my worksheet named 'Submission'.

What code do I use to say:

Search within worksheet Submission, column F for " " (spaces) and replace with "+"

Thanks in advance... just tyring to finish a project for work!

Find The Last Row With Data In Excel
Hi, i must delete some rows on my sheet (always by VB code) and i want to know the last rows with data because if i made this


Code:
For i = 1 To sheet.cells.count
If sheet.Cells(i, "A") = "" Then
sheet.Rows(i).Delete
End If
Next


it causes overflow
Note: I must delete all the rows which have nothing in column "A"
Thanks in advance

Find Macro In Excel
Hi Guys,

I am trying to Find a record and perform an action.
I am having a problem.
If the value is found the requeired action is performed
If the value is not found it gives an error msg.


==========================================================
The code I am writing is :
==========================================================

If Cells.Find(What:="abhatnagar", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate Then

Range("G5").Select
ActiveCell.FormulaR1C1 = "Arpit"
Range("A1").Select

ElseIf Cells.Find("anukumar", ActiveCell, xlFormulas, xlPart, xlByRows, xlNext, False, False).Activate Then

Range("G6").Select
ActiveCell.FormulaR1C1 = "Anubhav"
Range("A1").Select

End If

==========================================================


Now if abhatnagar is not found , the program does not proceed to the ElseIf Statement and gives an error msg.

The record abhatnagar does not exist in the sheet.
The record anukumar does exist in the sheet.



Help would be really appreciated.

Excel 97 Find Woes
why does this code give me an error in excel 97 when I run it from the "control" worksheet but not from the "reference data" worksheet?


Code:
dim myfind as range
Set myfind = Sheets("reference data").Range("rd_periods").Find(what:=Sheets("control").ComboBox1.Value, _
After:=Cells(1), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

the error is "unable to get the find property of the range class"

also, I don't get any errror at all in excel 2002.

Can't Find Excel Sheet
I'm fairly new to VB and I need a little help figuring out an error message.

I'm transferring data from an excel sheet base on a user specified sheet name. If the sheet name that the user types in the input box exist, the data will be transferred to an access database.

I have 3 sheets that I used for testing, and they import fine. Sheet Names( TrafficMM1, TrafficMMt2, TrafficMM3)

Here's the problem:
Once I finished testing, I decided to test a few more sheets(TrafficMM4, TrafficMM5 & TrafficMM6), but I keep get ting this message.

Run-time error '-2147217865(80040e37':

The Microsoft Jet database engine could not find the object 'TrafficMM4'. Make sure the object exists and that you spell its name and the path name correctly.


I have no problem importing data from the first three sheets.

Here's the SQL statement that I'm using:

sSQL = "SELECT * INTO [;DATABASE=c: rafficsystem rafficsystem.mdb].[TrafficMM_temp] From [Excel 5.0;DATABASE=" & stxtImportFile & "].[" & stxtSheetName & "]"

Thanks

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.


Code:
' Colour the SBC Total Lines
.Range("A1").Select
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:= _
False).Activate
Do Until IntX >= IntY
IntX = objExcel.ActiveCell.Row
objExcel.Selection.EntireRow.Font.Bold = True
objExcel.Selection.EntireRow.Font.ColorIndex = 10
objExcel.Cells.FindNext(After:=objExcel.ActiveCell).Activate
IntY = objExcel.ActiveCell.Row
Loop

End With

' Colour the CAT Total Lines
.Range("A1").Select
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:= _
False).Activate
Do Until IntX >= IntY
IntX = objExcel.ActiveCell.Row
objExcel.Selection.EntireRow.Font.Bold = True
objExcel.Selection.EntireRow.Font.ColorIndex = 5
objExcel.Cells.FindNext(After:=objExcel.ActiveCell).Activate
IntY = objExcel.ActiveCell.Row
Loop

End With

' Colour the BAS Total Lines
.Range("A1").Select
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:= _
False).Activate
Do Until IntX >= IntY
IntX = objExcel.ActiveCell.Row
objExcel.Selection.EntireRow.Font.Bold = True
objExcel.Selection.EntireRow.Font.ColorIndex = 46
objExcel.Cells.FindNext(After:=objExcel.ActiveCell).Activate
IntY = objExcel.ActiveCell.Row
Loop

End With


Thanks a lot.

Is There A Fast Find When Using Excel?
I have a problem in trying to perform a fast find in excel. If there is such a thing.

Background, I have an excel spreadsheet with 30,000 records, on each of the 9 worksheets. I have a set of 79 values that I want to find.

I recorded a macro, which consisted of "Finding" a specific value, in Column A which this is the code for:

Columns("A:A").Select
Selection.Find(What:="1234", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

What syntax do I have to use for the find to work in VB?
The following is the code for opening the Workbook

xl_name = "c:oeDVAR_all.xls"
Set xl = GetObject(xl_name)
xl.Application.WindowState = xlMaximized


Any help would be greatly appreciated!

Find Path Of Excel.exe
[size=2]Hi..
I want to find the path of excel.exe of any version

Find Formulas In Excel
Greetings All,
Is there any way to search an Excel file and return all the formulas (not values) in the file?

TIA
Bill

VBA Excel: Find Only Cells With A Value
hi guys,

I'm working on a little vba-app. to convert all my boss'es XLS documents to the Euro-currency.

The problem i run into is how to find a cell wit a value in it. I can make a if..then thingy, but then i need to work true the whole document (65000 x 256 cells) which takes ages per file.

is there a way to do this faster?

thanks in advance

Remvs
Holland

Find The Maximum Value In Excel
Hi all,

Could anyone provide me the code to find the first three maximum values from a spreadsheet? The values are listed in a sheet, and when the user clicks on a button, it will direct the user to another sheet whereby the top three maximum values from that column will be displayed on that sheet.

Thanks.

Excel Macros 'find'
Rather than use control and F to start a 'find' search on Excel, is there any way of creating a macro button that automatically opens this up? Most grateful if you could help. If you can, please mail me at chrisn@cpt-uk.org

Help With A 'Find' Macro In MS Excel
I am trying to search for a particular value in one worksheet (A)of a spreadsheet. The value that I am searching is the value in a cell in a separate worksheet (B) in the same spreadsheet. After identifying that particular cell in worksheet (A), I want to select the cell next to it and paste some data (that I previously copied from (B))in it.
How do I set up the find function/macro to do that?
Please help/advise!
Thanks!
Archit

Find A Comment Thru Excel VBA
I have a couple of comments in an Excel worksheet. I want to find/select a particular comment skipping all others. Can someone help me on this. Remember i have to do it through the VB Editor in Excel.

Thanks,
Amaresh Tripathi

Excel Find [Re-Resolved]
How can I write vba code that selects every instance of a phrase without using a while loop?

I know find works like so:

Code:Cells.find("phrase").select Will select the first cell Find comes across, but how do I select all instances of the phrase?

Is there like a FindAll?

Tia

-- tetrakarbon



Edited by - tetrakarbon on 8/17/2004 12:03:52 PM

Error With Find In Excel
Hi all...
This is a simple enough problem.... but I'm a bit new to VBA so sorry for being stupid.
I designed an app using macros and have adapted the macros to suit what I need done. I used the macro recorder to find a value in a range which works fine if there is a value I then copy the value from the cell and paste it into another worksheet all good.
The problem occurs when there is no value to be found in the range and Excel gives me the following error...."Object variable or With block variable not set (Error 91)"

Hopefully you can decipher it and give me a hint in the right direction...

    Selection.Find(What:=RiskVal, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False).Activate
        ActiveCell.Offset(0, 1).Range("A1").Select
        Application.CutCopyMode = False
        Selection.Copy

Thanks in advance for any help or advice that you can give...
Cormac

Excel Find (value From Textbox)
Hi !

I'm new to VB and I really need to know (for work...) how can I find data on an Excel spreadsheet with VB.

The data that I need to find is define by the text written in a textbox...


Thanks !

How To Find Last Cell In Excel?
Hello Gurus...
Can anybody help me in finding the last cell or Row that contains some data..(not empty) in an Excel Sheet using VB code...

i am Waiting for Ur Reply..
Please help me

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: www.maxcode.com
Feel free to contact me via ICQ# 82806695

Excel And VBA - Find E-mail Message
Hi all!

I plan to distribute an Excel Workbook via e-mail, BUT I want to make sure that the receiver of the file, upon opening of the file, is the actual intended recipient.

I am thinking to look for my e-mail address (i.e. the one which will be used in sending the file) and the attachment (either zipped or as ABC.xls) sent in his default mail client, AND only upon the search for the above resulting in a match, allow entry into the workbook.

How, please, does one code that into VBA?

I am not as new to VBA as I was a year ago when I started asking help on forums, but new enough to not have the foggiest as to how to do this!

Your kind assistance will, as always, be greatly appreciated!

Best regards

Harry

Excel Macro - FIND && PASTE
In Excel, i need to write some code that will search for something specific (let's say a cell containing the word EGG) Then copy some info from elsewhere (let's say the contents of cell A1) & paste it in to the cell below the "EGG" cell.

Sorry if the answer to this may be a long winded one, having a nightmare getting to grips with the whole FIND thing!

Thank you very much.


God, hopefully one day i'll have a quarter of the knowledge you lot hold!!!!!

VB6 Unable To Find Excel Macro
My object definitions are kept in a separate module and are:



'======================================================+

' Excel Object Definitions |

'======================================================+

Code:
Public xlApp As New Excel.Application

Public xlBook As New Excel.Workbook

Public xlMacros As New Excel.Workbook ' Personal.xls macro workbook

Public xlBooks As Excel.Workbooks

Public xlsheet As New Excel.Worksheet

Public xlSheets As Excel.Worksheets
In the Module that calls Excel I have:

'======================================================+

' String Variables |

'======================================================+

Code:
Dim str_Select_Statement As String ' save area for original select statement

Dim str_New_Select_Statement As String ' area for new select statement

Dim str_Select_Clause As String

Dim str_Order_By_Clause As String

Dim str_Where_Clause As String

Dim str_From As String

Dim str_To As String

Dim str_File_Month As String

Dim str_Limit As String ' format yyyym All contract dates

' must be > than this

Dim str_Contract_Year_and_Month As String ' format yyyym as above
'======================================================+

' Variant Variables |

'======================================================+

Code:
Dim vstr_Macro_Workbook As Variant

Dim vstr_Macro_Path As Variant

Dim vstr_Macro_Workbook_and_Path As Variant

Dim vstr_Macro_Executed As Variant




The following code builds a path to the Personal.xls work book:

Code:
' Get workbook name (e.g. personal.xls)

gstr_Macro_Workbook = Trim(gstr_Macro_Workbook)

' Get workbook path

gstr_Macro_Path = Trim(gstr_Macro_Path)

' Then concatenate the two

vstr_Macro_Workbook_and_Path = gstr_Macro_Path & "" & gstr_Macro_Workbook

vstr_Macro_Executed = Trim(gSTR_MACRO_NAME)

vstr_Macro_Executed = vstr_Macro_Workbook_and_Path & "!" & vstr_Macro_Executed
The following code builds a path to the Personal.xls work book:

Code:
' Get workbook name (e.g. personal.xls)

gstr_Macro_Workbook = Trim(gstr_Macro_Workbook)



' Get workbook path

gstr_Macro_Path = Trim(gstr_Macro_Path)



' Then concatenate the two

vstr_Macro_Workbook_and_Path = gstr_Macro_Path & "" & gstr_Macro_Workbook



vstr_Macro_Executed = Trim(gSTR_MACRO_NAME)

vstr_Macro_Executed = vstr_Macro_Workbook_and_Path & "!" & vstr_Macro_Executed



A bunch of code that opens the correct workbooks then:



xlApp.Run vstr_Macro_Executed

Which is the line that fails. I generate the error message dialog box with the message:
The macro 'C:Program FilesMicrosoft OfficeOfficeXLStartPersonal.xls!Translate_CSI_and_Create_Chart' cannot be found..

Microsoft Error = 1004 Microsoft Excel
I generate this message in the usual way (ERR.property), but this seems to be saying that Excel can't find it's own macro, even though it's in exactly the place specified. The module and macro names are the same, so I shouldn't have to bother with the Module name.Subroutine name syntax.

I converted to Windows 2000 Professional to avoid this, which drove me nuts in Win98, but here we are again.
Could this have something to do with which directory is the current one? All my VB6 stuff is in a directory separate from Excel. W2K has a bunch of XLSTART directories and that might upset things too. Currently, I do all my programming as Administrator.


Anyone have any ideas. Much more of this and I'll run screaming into the street.


By the way, Visual Studio 6.0 was installed fresh and W2K was installed on a bare drive. So I don't think anything on the drive did any damage.

Find/Replace In Word From Excel
I'm trying to create a macro that can find and replace data in a word document. But this macro is in an excel file. I know how to open a word file within an excel file, but looks like I can't activate it to do the find/replace job. Is there any way to do this? thank you

Lina

Excel Macro For Find/Replace
Hi there. I wrote a macro for my excel spreadsheet to find and replace country codes. For example, I get a speadsheet from our manufacturing team. DE is the country code for Germany, AU is Australia, etc. I got the first one (Germany) to work, but it doesn't do any more than that. Below is my code. What am I doing wrong?

Sub ReplaceDE()
Dim rFound As Range
Dim szFirst As String
Dim iCount As Integer

ThisWorkbook.Worksheets(1).Activate
Set rFound = Columns(19).Find("DE")

iCount = 0
Do While Not rFound Is Nothing
''' Store address of first occurrence
If szFirst = "" Then
szFirst = rFound.Address
ElseIf rFound.Address = szFirst Then
Exit Do ''' If we have looped around, quit
End If
rFound.Value = Application.Substitute(rFound.Value, _
"DE", "Germany")

iCount = iCount + 1
Set rFound = Columns(19).Cells.FindNext(rFound)
Loop
MsgBox "Replaced occurrences in " & iCount & " cells."
End Sub

Then after the find/replace, I also need to split the countries into 4 different sheets within the same workbook. I need an Americas, Asia, Europe and Misc. sheets. How can I do that?

Muchas Gracias!

Find All Occurences Of Text In EXCEL??
Trying to figure out how to search a excel worksheet for all occurences of text. I have found this code but cant get it to work any help and code would be nice.

Sub SelectItems(SearchItem)
For Each ws In ActiveWorkbook.Worksheets
'if the worksheetcontains SearchItem, then get the addresses.
If TypeName(ws.Cells.Find(SearchItem)) = "Range" Then
'Since SearchItem was found, it is safe to get its address
Set rngFound = ws.Cells.FindNext
sAddress = rngFound.Address
'Repeat until you get back to the first occurrence.
Do
'Get next occurrence
Set rngFound = ws.Cells.FindNext(after:=rngFound)
'Exit loop if you get back to the first address.
If InStr(sAddress, rngFound.Address) Then Exit Do
'Build range address string for selection.
sAddress = sAddress & "," & rngFound.Address
Loop
'Activate the worksheet.
ws.Activate
'select the range.
Range(sAddress).Select
End If
Next ws
End Sub

Thanks.

Find Date Problem In Excel
OK, I basically have a calendar made up in Excel, for example January, I have each day of the month in a cell, but just like any other calendar, it has a couple days from the previous month, like the 30th and 31st from December on Monday and Tuesday, then Wednesday being the 1st.

The problem is, I'm a code to find the date of today (or of the given day) and change the color of the cell. Here's the code:

Sub today()
Dim myRange As Range
Dim DayValue As Integer
DayValue = Day(Date)
Set myRange = Sheet2.Range("A4:N18").Find(what:=DayValue, Lookat:=xlValue)
myRange.Interior.Color = &HFF&
myRange.BorderAround xlContinuous, xlThick, xlColorIndexAutomatic
End Sub

Anyway, what happens is that it finds, for example, the "30" and "31" before finding the "3" of January. I would like think the code would just match the dayvalue, but apparently its not matching it exactly. Is there any way to specify to make it an exact match?

Thanks in advance.

Excel Find On 'Grouped' Rows
Here is another tricky question.

I would like to take advantage of excel's group functionality as well as the speed of excel's find functionality.

But, when I have rows grouped together and the details are hidden, excel's find won't look in the hidden rows.

Is there some way to search the hidden rows as well? Or do I just need to 'show' all of the detail rows when I am doing a find?

-Jeff

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.

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?

Find Last Cell In A Column In Excel
Edit: Re: http://www.xtremevbtalk.com/showthread.php?t=289692

What type is x1Up?



Edit by Moderator:
Please post your own threads, to ask your own questions, posting links to other threads where required.

Thank you.

VB6.0: Find Excel Sheet With Macro
I am trying to build a small Visual Basic application (VB6.0) that will loop through a folder and pick out all the Excel files.

My question is--How can I programmatically tell if a particular Excel file contain Macro?

Please help me out.

Thanks in advance.

Find And Select Values In Excel Using Vb
I've been trying all day to write something in visual basic that will find all rows in an excel spreadsheet which have a "Value" error in the B column. I need all those rows selected and then hidden. Any help would be GREATLY appreciated.

Thanks!

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