Solved

Variable search/"echo off"

Posted on 1998-05-21
3
341 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now