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

axsprog · #1 (**permalink**) 12-30-09, 16:31

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.

Colin Legg · #2 (**permalink**) 12-30-09, 18:25

Hi Dale,

Quote:

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?

Quote:

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

axsprog · #3 (**permalink**) 12-30-09, 20:06

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