Solved

Variable search/"echo off"

Posted on 1998-05-21
3
345 Views
Last Modified: 2012-05-04
Following is the code I wrote (and got some help from cymbolic on) for doing a search on a particular item.  I need help in two areas:  (1) how do I program the "echo off" equivalent in VB so the screen doesn't jump so much when the program is going back and forth from Worksheet to Worksheet (or is there another way to write the code so there's less "back and forth"?; and (2) how can I change the search from absolute to variable?  I'd like the user to be able to search any item on the spreadsheet and have that item, its vendor and the cost display on the second Worksheet.  Thanks in advance, you VB wizards!  --Carol

Sub ItemVendorCost4()
'
    Application.Goto Reference:="Item1"
    ActiveCell.Select
    Selection.Copy
    Sheets("Sheet2").Select
    Columns("A:A").ColumnWidth = 19.43
    Range("A1").Select
    ActiveSheet.Paste
'
    Application.Goto Reference:="Vendor1"
    ActiveCell.Select
    Selection.Copy
    Sheets("Sheet2").Select
    Columns("B:B").ColumnWidth = 9.57
    Range("B1").Select
    ActiveSheet.Paste
'
    Application.Goto Reference:="Cost1"
    ActiveCell.Select
    Selection.Copy
    Sheets("Sheet2").Select
    Columns("C:C").ColumnWidth = 11.86
    Range("C1").Select
    ActiveSheet.Paste
'
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Names.Add Name:="ITEM2", RefersToR1C1:="=Sheet2!R1C1"
    Range("B1").Select
    ActiveWorkbook.Names.Add Name:="VENDOR2", RefersToR1C1:="=Sheet2!R1C2"
    Range("C1").Select
    ActiveWorkbook.Names.Add Name:="COST2", RefersToR1C1:="=Sheet2!R1C3"
    Range("A2").Select
'
    Dim Message, Title, Default, MyValue
      Message = "ENTER ITEM NAME"
      Title = "ITEM NAME SEARCH"
      Default = "SWEATSHIRT/XL"
      MyValue = InputBox(Message, Title, Default)
'
    Sheets("Sheet1").Select
    Range("A6").Select
'
    Do Until ActiveCell = "SWEATSHIRT/XL"
        If ActiveCell = "SWEATSHIRT/XL" Then
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
'
    ActiveCell.Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste
'
    Sheets("Sheet1").Select
    ActiveCell.Offset(0, 1).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B2").Select
    ActiveSheet.Paste
'
    Sheets("Sheet1").Select
    ActiveCell.Offset(0, 4).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C2").Select
    ActiveSheet.Paste
'
End Sub

0
Comment
Question by:Carol052098
3 Comments
 
LVL 9

Expert Comment

by:cymbolic
ID: 1456470
I know I don't understand claerly what you want, but I think I can give you a few clues.

In VB, when we don't want that flashy windows scrren crap to show, we use the hide/unhide methods on the forms.  We hide it, we do all the fancy screen formats, then we unhide it.  Another trick is to place a form behind other forms using z order changes.  Another is to turn off autoredraw, then manually redraw the form after all the screen work is done.  I'm not sure which of these methods you can apply in a VBA module in Excel.

Regarding making it variable, am I being obtuse, or do you just need to substitute the variable MyValue for the literal "SWEATSHIRT/XL"?
0
 
LVL 1

Accepted Solution

by:
daveko earned 100 total points
ID: 1456471
ok.  if I understand you correctly, here's what you need to do.

A1) to turn off screen painting so that you don't get a lot of screen activity, just add the

    Application.ScreenUpdating=False

statement towards the beginning of your code block.  When you are done with all your processing and jumping around, set things back to normal by issuing

    Application.ScreenUpdating=True

This will refresh the UI for you and allow the UI to react to all code and user input as before.

A2) as for a variable searching for values to be retrieved, try looking into the VLookUp() function that VB offers.  If you organize your data into a table, then you can use VLookUp() to search the first column and retrieve the corresponding row value from any named column.

Hope that helps you do what you need.  

0
 

Author Comment

by:Carol052098
ID: 1456472
Thanks, both of you... the screen anti-jiggle Application.ScreenUpdating worked great... I turned it on and off at various parts of the program and it works seamlessly now... thanks... HOWEVER, I am still messing around with the variable situation.  As I've said, I'm very new to VB... so I need a simple solution to what seems to be a simple desire--take the user's input from a popup msg box and then search on that input (in an Excel spreadsheet).  I've tried a couple of different ways of doing it including cymbolic's suggestion... haven't checked out VLookUp() yet... how do I define the user's input (which will be different every time and therefore variable)?  I guess what I'm saying is that I need to dig a little deeper into this... unless you want to actually give me the code to plug into what I've shown you I've done on my own... thanks again, folks!  --Carol
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question