Searching down a column?

Visual Basic for Applications (VBA)

Index ‹ Microsoft ISV

- Previous
  - 1
    - Tried EVERYTHING! I need help! Hey All, TWO QUESTIONS...please let me know if you can help me with either.. 1) I have a problem with Internet explorer, everytime I close a window this message appears: The instruction at "0x01cb3795" referenced memory at "0x01794658". The memory could not be "read". Click on OK to terminate the program I uninstalled internet explorer, then installed it again, I tried many programs that allegedly fix problems, and nothing is working!!!! 2) Later i installed internet explorer 7.0, the new one, same problem as above, but the biggest problem was that whenever I tried to open a new "tab" not window, all the windows on internet explorer are automatically shut down in 5 seconds! also uninstalled it, then downloaded again, nothing works! I don't know if these are related. Apperciating your time, Zeenah Tag: Searching down a column?
  - 2
    - How to create a chat room on Communicator 2005 OK :-) I'm currently looking into this question and will post as soon as I get some additional info. -brenda (ISV Buddy Team) Tag: Searching down a column?
  - 3
    - Determing close method with vba Hi, I don't think you can tell the difference. The way to handle this is with an appliction event, use a class to create this. Then as each workbook is closed you run any code you need for that particular workbook. No need to worry about the others as they will trigger their own workbook close event. So if the application is close each open workbook should fire the event. Tag: Searching down a column?
  - 4
    - ISV Platform Test Hello. On speaking to Veritest about this test I was informed that the testing specification currently only covers applications written using .NET 2. When will this be extended to cover apps written in .NET 3 Tag: Searching down a column?
  - 5
    - Excell, VBA and unicode Hi, I am writing a script in VBA that takes data from an excel spread sheet and writes it to a text file (SQL) for import into an Oracle 10g database. The Excel file is in the font Arial Unicode MS, which is a special font that allows special characters to be represented. There are many special characters in the file, and when I write them to the text file they are lost and replaced with a question mark. Is there a way I can force the vba code to pick up the unicode chars, and to force the .txt file to Arial unicode MS when I open it for writing so I can keep the unicode chars Any and all help is greatly appreciated. Thanks Mark (aka honeyfitz) Tag: Searching down a column?
  - 6
    - iexplore.exe - Application Error (EVERY TIME I CLOSE IE7) :( The same exact thing is happening every time I close IE7. The location number is different for me (0x7c812a5b). Immediately after clicking okay for that error window... I get another. It reads "Runtime error 217 at "039EFD29". Browsing around the site here... this seems to be some sort of conflict between IE7 and Norton 2007. I read in a thread for the runtime error to do the following: Control Panel > Internet Options > Advanced (tab) > RESET Tag: Searching down a column?
  - 7
    - macro for automatically creating data labels in excel chart Does your homework specify you must use code It can be done just using formula and 2 data series. Assume months and fixed value are in A1:B13 Add these formula C2: =B2 C3: =IF(B3=$B$2,NA(),B3) drag C3 down to C13. Create a line chart on the range A1:C13. Format the second series to have no markers or line. Apply Category data labels to this series. Jan should have a label and so will any other month where the value in B does not match that of B2. Tag: Searching down a column?
  - 8
    - Visual Studio Installation Problem I got my new ISV software bundle just in time to load it on my new computer. Office went on without a hitch, but when I tried to load Visual Studio I had a problem. I am running Vista Ultimate. The DVD has a Disk 1, Disk 2 and Setup Directories. I tried running the setup from the setup directory. It gets started and asks for Disk 1 to be inserted and doesn't allow any navigational aid to point to the Disk 1 directory. I canceled the install and ran the install in the Disk 1 directory. I ran into the same problem. Can anyone give me a hand Tag: Searching down a column?
  - 9
    - How to rename worksheet without installed Excel? The following code worked for me assuming AB CD EFD $0+ (bb d_ddd) was the sheet name. Public Sub QueryWorksheet() Dim rsData As ADODB.Recordset Dim szConnect As String Dim szSQL As String ' Create the connection string. szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\temp\testado.xls;" & _ "Extended Properties=Excel 8.0;" ' Query based on the worksheet name. szSQL = "SELECT * FROM ['AB CD EFD $0+ (bb d_ddd)$']" Set rsData = New ADODB.Recordset rsData.Open szSQL, szConnect, adOpenForwardOnly, _ adLockReadOnly, adCmdText ' Check to make sure we received data. If Not rsData.EOF Then Sheet1.Range("A1").CopyFromRecordset rsData Else MsgBox "No records returned.", vbCritical End If ' Clean up our Recordset object. rsData.Close Set rsData = Nothing End Sub Tag: Searching down a column?
  - 10
    - MODI Port Is it necessary to install all the applications of MS Office to activate MDIVWCTL.DLL Also, how can I install an application of MS Office through command line silently Finally, How we can not register mdivwctl.dll by using the following command: regsvr32 mdivwctl.dll, Thanks Regards, Hadi Tag: Searching down a column?
  - 11
    - Enable and Disable text boxes on a sub-form Hi I am trying to enable and disable textbox controls on a subform when a field on my parent form changes. It appears that the subform textboxes are not visible to VBA when I am trying to write the code. Can anyone give me an example of enabling and disabling controls on a subform Regards ADG Tag: Searching down a column?
  - 12
    - Linked Tables I want to write a vba script to change all the links in the linked tables in an mdb file to link sql server database. Also, if you think of a better way to do this than writing a vba script, tell me. If you know how to do this using the upsize wizard (which mean using the same data that in the mdb files) , I will be interested to hear too. I think this is similar to the question here http://www-new.experts-exchange.com/Microsoft/Development/MS_Access/Q_21916141.html qid=21916141&qid=21916141 Tag: Searching down a column?
  - 13
    - VBA - Accessing files in a folder Hi Andy Thanks for your interest. I have now optimized the code and it is indeed very fast. Initially, i had directly used the FSO object, browsed to the specific directory, get all the files (2000+) and then loop through them to get the desired file names. Now i have used the Office.FileSearch method to select the list of files and once selected, i use the FSO.GetFile method to collect the file attributes. Cheers Raja Tag: Searching down a column?
  - 14
    - Auto code indent tidying Thanks. I downloaded the program and it installed without a hitch. It works really well. It is exactly what I was after. Seems strange that VBA does not have such a tool included. It is a basic requirement when coding. Tag: Searching down a column?
  - 15
    - Getting Access Data into Excel I need to get a large number of Access 2000 Recordsets into Excel 2000 for evaluation. I have no problem setting up the connection and getting the entire table into Excel for testing by modifying the examples at http://support.microsoft.com/kb/247412/EN-US/ using either the QueryTable or ADO code outlined there. The tables I need to access have over 200,000 records so getting the entire table is not an answer in this case. The SQL statement is complicated and changes 125 times in the course of the whole procedure. Sample SQL generated by Microsoft Query by building the Query in design mode and then changing to View SQL (The Access Query SQL is slightly different it the query is built in Access): SELECT tblCo.`Company Name`, tblCo.Symbol, tblCo.Edition, tblData.Date, tblData.Price, tblData.ProjHi, tblData.ProjLo, tblData.Performance, tblData.Timeliness, tblData.Safety, tblData.Technical, tblData.FS, tblData.PS, tblData.GP, tblData.EP, tblData.DeltaPrice, tblData.DeltaProjHi, tblData.DeltaProjLo, tblData.DeltaPerformance, tblData.DeltaTimeliness, tblData.DeltaSafety, tblData.DeltaTechnical, tblData.DeltaFS, tblData.DeltaPS, tblData.DeltaGP, tblData.DeltaEP, tblDeltaPrice.`1qDeltaPrice`, tblDeltaPrice.`1qDate`, tblCo.Symbol, tblData.DeltaTimeliness, tblData.DeltaTimeliness, tblData.DeltaPerformance FROM `C:\Documents and Settings\jch\My Documents\YoYo\Dev\CritDev`.tblCo tblCo, `C:\Documents and Settings\jch\My Documents\YoYo\Dev\CritDev`.tblData tblData, `C:\Documents and Settings\jch\My Documents\YoYo\Dev\CritDev`.tblDeltaPrice tblDeltaPrice WHERE tblData.Symbol = tblCo.Symbol AND tblDeltaPrice.Symbol = tblCo.Symbol AND tblDeltaPrice.Symbol = tblData.Symbol AND ((tblData.Date Between {ts '10/9/2003'} And {ts '10/9/2006'}) AND (tblDeltaPrice.`1qDate`=Date) AND (tblCo.Edition Like 'S%') AND (tblData.DeltaPerformance Is Null) AND (tblData.Performance Is Null))AND ((tblData.Timeliness)<3)AND ((tblData.Safety)<3)AND ((tblData.Technical)<3) When I try to introduce this SQL statement into either procedure, it doesn't work. Is the SQL incorrect or Am I missing something in changing the example VBA for my needs Am I even on the right track My long suit is statistics, not programming, so if this is just a dumb question let me know. The frustrating thing is I can do it by hand but can't seem to automate it. If some one will point me in the right direction or recommend some online or printed references, I would appreciate it. Cordially, TwoHoot Tag: Searching down a column?
  - 16
    - Associated Toolbar Button with Add-In I have created an Excel 2000 VBA project that I need to distribute to others. I would like it to have a toolbar button linked to it. I was thinking an Add-In would be an easy way to go for distribution. I can get it to work with a shortcut key but the button doesn't appear on the toolbar when the Add-In is selected. Does anyone know how I can do this The code is specific to Office 2000 Excel. Thanks. Patty Tag: Searching down a column?
- Next
  - 1
    - Display a TextBox or Non-modal Message Box Catherine: I have tried what you suggested, with no luck. I have tried many different things that I've found in various places, but somehow, I can not get the text box or a label to come up when the userform appears. I can't figure it out. The modal setting is set to false, the form is set to visible=true in the properties...I don't know what else to do. How can the userform come up, but not what I've placed inside it Thanks again for your help. Tag: Searching down a column?
  - 2
    - Easy msgbox Query Adamus, Thank you for the reply. I tried: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Sheet1.Range("C11").Value < 0 Or Sheet1.Range("C12").Value < 0 Then MsgBox "Enter Larger Value." End If End Sub And still get nothing. Ideas Jackie Tag: Searching down a column?
  - 3
    - 1004 Runtime error/Password Protected Sorry - I'm relatively new to this but here's the situation. I have a macro that works without the sheet being password protected, but once I protect the sheet, it does not work. Any ideas on how to fix this The highlighted area is what seems to be causing the problem. Range("A9:U10").Select Selection.Copy ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 8 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("A9").Select Selection.Insert Shift = xlDown Range("B10:N10").Select Application.CutCopyMode = False Selection.ClearContents Range("L9").Select Selection.ClearContents Range("J9").Select Selection.ClearContents Range("I9").Select Selection.ClearContents Range("F9").Select Selection.ClearContents Range("D9").Select Selection.ClearContents Range("C9").Select Selection.ClearContents Range("B9").Select Selection.ClearContents Range("A9").Select Selection.ClearContents End Sub Tag: Searching down a column?
  - 4
    - sending emails from MS Word with variable subject line You can make use of the DataObject to get text from clipboard. Sub x() ' requries reference to MS Forms library, which can be done by simply adding a userform Dim objDO As MSForms.DataObject Set objDO = New DataObject objDO.GetFromClipboard ' not sure of your code for getting email object Email.Subject = objDO.GetText End Sub Tag: Searching down a column?
  - 5
    - extract pivot table data Hello, I have a pivot table in Excel and I want to extract the data in it. I need the source tables, the tables that where used to create the pivot table. Help Tag: Searching down a column?
  - 6
    - Vlookup in VBA Did you manage to get this one to work Tag: Searching down a column?
  - 7
    - Problem passing a range as argument Thx Andy, that helped a lot =] Now I can do a proper calling of Subs ^^ Tag: Searching down a column?
  - 8
    - Order by in select statement not working Hi - thanks for the reply both but i have tried that too. i get compile error: Expected: end of statement Tag: Searching down a column?
  - 9
    - DoCmd.OpenReport for report based on a query I've used the DoCmd.OPenReport with the WHERE-clause for some time now. My reports where all based upon a table in my database. But now I have a report that's not based on a table but on a query. The problem is that my report opens, but it just doens't seem to hold my WHERE-clause in account. For example: I have two (linked) tables: Locations (two fields: LocationID & LocationName) Transactions (lots of fields, but one is the Location through the LocationID) Now I wanna show the Location NAME in my report and not the ID, so a query was necessary. All well up until now, but the problem is that I want my application to only show the transactions for this year, so I have something like: Code Snippet DoCmd.OpenReport "rptTransactions",acViewPreview,,"Year = " & intYear,acWindowNormal But it never filters on Year... Any thought ons this would be greatly appreciated. after edit: nobody Tag: Searching down a column?
  - 10
    - Macro To Delete Spreadsheet Columns if no data in column I found this code on another site, and it appears to work. So I thought I would post it. Sub DelEmptyColumn() For i = 256 To 1 Step -1 If Application.CountA(Columns(i)) = 1 Then If Cells(1, i).Value <> "" Then Columns(i).Delete End If Next End Sub Tag: Searching down a column?
  - 11
    - SaveAs Hi, I can't resolve problem how to save spreadsheet in any format: xls or xlsx. I use office2007, but some of people in my office still use excel2003. They must decide in which format save excel. I wrote: Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet ... and ... xlApp.Dialogs(Excel.XlBuiltInDialog.xlDialogSaveAs).Show("xxx") all is ok, default name is "xxx", but I'd like set default extension and I wrote: xlApp.Dialogs(Excel.XlBuiltInDialog.xlDialogSaveAs).Show("xxx", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel9795) and here is problem, I got error. How can I set default name, extension and location where I want save my file And a second question: how I can check which button was pressed in DialogBox In a SaveFileDialog I wrote: "If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then" but how check it in a xlApp.Dialogs Source is VB.net2005. Regards, Jacek Tag: Searching down a column?
  - 12
    - Is there a way to abort during runtime I thought CTRL+Break would work also, but it doesn't on my laptop. Have you got that to work before KW Tag: Searching down a column?
  - 13
    - error 1004 - unable to get insert property of the pictures class I have some code that works behind a command button to insert a selected image file into a cell on a worksheet. If the user clicks the 'Cancel' button on the 'getfilenamesaveas' window an error message displays... "Error 1004 - Unable to get Insert Propeerty of the Pictures Class" Am I missing something in the code (see below) Private Sub CommandButton1_Click() Range("A6").Select Dim PicLocation As String PicLocation = Application.GetSaveAsFilename("C:\Windows\My Documents", "Image Files (*.jpg),*.jpg", , "Specify Image Location") If PicLocation <> "" Then ActiveSheet.Pictures.Insert(PicLocation).Select Else Exit Sub End If Selection.ShapeRange.LockAspectRatio = msoTrue Selection.ShapeRange.Width = 235# Selection.ShapeRange.Height = 165# With Selection .Placement = xlMoveAndSize .PrintObject = True End With Range("H16").Select End Sub Any help would be greatly appreciated! Tag: Searching down a column?
  - 14
    - Changing the color of cells in a worksheet using VBA Hey, Try this �� ;-) Sub PleaseDo() For Each answer In Forum.Questions If answer.Satisfactory = True Then answer.Mark As Answered End If Next End Sub Cath Tag: Searching down a column?
  - 15
    - Using a Check Box in Word I was reading the post regarding the check box in excel and I was curious if it is possible to put a check box in word to copy a range of data from an excel document to a word document If so, how would the code change from the code displayed in the "Check Box in Excel" post Tag: Searching down a column?
  - 16
    - How to make a macro to copy some parts of a table to an email that it will create? Hi Joseph, thanks for the website. It helped me alot. I have identified the code that may be able to solve my problem. However, If I may ask, is the codes for Microsoft Word and Excel the same I think this part of code can help me copy the table out to the body of an email, currently it copies the cells B15 to B20 in the Excel worksheet. How can I make it to copy the first table in a Word document Is it possible to code it such that it only copies certain rows of the table For example I just need rows 1, 2 and 4 and not row 3 to be copied to the body of the email. Sub Mail_Range_Outlook_Body() ' Don't forget to copy the function RangetoHTML in the module. ' Working in Office 2000-2007 Dim rng As Range Dim OutApp As Object Dim OutMail As Object With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Nothing On Error Resume Next Set rng = Sheets("MailRangeSelection").Range("B15:B20").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " ron@debruin.nl " .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = RangetoHTML(rng) .Display 'or use .Send End With On Error GoTo 0 With Application .EnableEvents = True .ScreenUpdating = True End With Set OutMail = Nothing Set OutApp = Nothing End Sub Once again, thanks for the help! Tag: Searching down a column?

Blinocac

I have created an inventory spreadsheet, and want to be able to search down a particular column to match up part numbers. Once I have found the correct cell, I want to edit the value of the corresponding cell in the next column. I'd really like to find a good tutorial for using VBA in Excel, but barring that, a push in the right direction for this code would be handy.

Top

Re: Searching down a column?

Shasur

Here is a way to use find function

Code Snippet

Set rFnd = Sheets(1).Range("A:A").Find(what:=sText, LookIn:=xlValues, lookat:=xlPart)
If Not rFnd Is Nothing Then
Sheets(1).Cells(rFnd.Row, 2).Value = "Your new value"
End If

where sText is the search text you want to search. The rFnd range object will have the address of the matching cell.

If you wanr to learn Excel, You can find more infor @ Excel Developer Forum in Microsoft Website

Cheers

Shasur

Top