• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Variable search/"echo off"

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
Carol052098
Asked:
Carol052098
1 Solution
 
cymbolicCommented:
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
 
davekoCommented:
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
 
Carol052098Author Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now