Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Check for a value in a cell - find match in sheet2

    Hello.

    I am well versed in Access and VBA so I should not drain anyones patience. I have hundreds of posts in the Access Forums.

    I have an application In MS Access that has to produce a spreadsheet that mimics the the Access.

    The data is repetitive:
    Project Manager End Date Project Name Budget Type Cost Type
    Forecast Hours
    Forecast Manpower ($)
    Forecast Exchg Rate Diff($)
    Forecast Travel Expenses
    Forecast Non-Travel Expenses
    Actual Hours
    Actual Manpower ($)
    Actual Exchg Rate Diff($)
    Actual Travel Expenses
    Actual Non-Travel Expenses
    Variance Hours
    Variance Manpower ($)
    Variance Exchg Rate Diff($)
    Variance Travel Expenses
    Variance Non-Travel Expenses

    I have written the code that makes the headers and the row headers (variance, actueals, etc) and actually fill in each cell with the data from access. My problem now is two fold
    1) I want to lock all rows but the rows that contain forecast hours, forecast travel expense, and forecast other. In those rows I want to trigger an event that when they tab out of the cell I look at sheet 2 on that sheet I need to find a cell that matches column A and B , get that value , multiply by the value in the cell I tabbed out of and place the new value in the forecast Manpower cvell.

    2) Part of me creating the spreadsheet is pasting some formatting and records. But I am getting a message when saving the wbook that says I have a large amount of data in the clipboard, do I want to save it. That message is stopping my code - how can i eliminate that message.
    Dale Houston, TX

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Dale,
    1) I want to lock all rows but the rows that contain forecast hours, forecast travel expense, and forecast other. In those rows I want to trigger an event that when they tab out of the cell I look at sheet 2 on that sheet I need to find a cell that matches column A and B , get that value , multiply by the value in the cell I tabbed out of and place the new value in the forecast Manpower cvell.
    I think the best answer depends on how many rows of data there will typically be (1,000? 10,000? 10,000,000?) and which version of Excel are you automating?
    Rather than VBA event handling, it may be better to have formulas in that column which will automatically calculate the new value. Do you think that could work for you?

    2) Part of me creating the spreadsheet is pasting some formatting and records. But I am getting a message when saving the wbook that says I have a large amount of data in the clipboard, do I want to save it. That message is stopping my code - how can i eliminate that message.
    To do this, you can temporarily set the Excel application object's DisplayAlerts property to False. Say you use a variable called xlApp to hold a reference to the Excel instance you are automating, it would look like this:
    Code:
    Sub foo()
        Dim xlApp As Excel.Application
        
        'create Excel instance....
        
        
        
        xlApp.DisplayAlerts = False
           
        'do the Excel manipulations...
        
        xlApp.DisplayAlerts = True
        
        'etc....
    End Sub

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    The problem with a formula is that I am referencing data on sheet2 that changes in row and and column count. That is why I needed the cfode to look up values in cells on sheet2 , and the criteria is being supplied by criteria on sheet1

    Thanx for the displayalerts snippett = equivalent to the setwarnings in access
    Dale Houston, TX

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    The problem with a formula is that I am referencing data on sheet2 that changes in row and and column count.
    We might be able to work around that. Can you give some more info?

    Also, how many records will there typically be and which version of Excel?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •