Need a Lookup or IF statement within a MACRO?

[johnw993]

I've attached a sample (an extracted small component of a larger workbook) of a macro that is almost working.

In Sheet1 a user can enter Key Indicators, Key measures and enter formulas for the Key Measures. These formulas are then transposed to the "Employee Report" sheet.
There is no data attached to this sample workbook, so all the formulas will result in 0.

I've recorded a simple Macro that works fine. Problem is, no matter what formula I write or change (in Sheet1 row 67 through 78), only row 114 in Employee Report is updated.

I want for the Macro to find a match in the Employee Report for Department (Column A), the Measurement (Column E) and then populate the matching row with the formula that was entered for that department and measurement in Sheet1.

[Richard Buttrey]

Hi,

See the attached which uses the following procedure. Sheet1 A66 has also been given the range name 'TopRow'

Please Login or Register  to view this content.

[johnw993]

Thanks Richard! This works great.

One other question:
In my workbook the client/user will create their own department names, and thus the department names will be different for each client. How do I change in your code the: Case Is = "Marketing" to have the word "Marketing" change when the user has changed the name for that Department (which is done in a separate sheet)? Can we have it point to the Column header in Sheet1? like: Case Is = "Sheet1 $C$2"

The workbook I shared is only a small sample, there are up to 20 departments available to the user.

[Richard Buttrey]

Thanks Richard! This works great.

One other question:
.....Can we have it point to the Column header in Sheet1? like: Case Is = "Sheet1 $C$2"
The workbook I shared is only a small sample, there are up to 20 departments available to the user.

[/QUOTE=johnw993;3098522]

Yes that would seem a reasonable way forward. So
Case = Sheet1.Range("C2")
..etc

[johnw993]

Works like a charm! Thanks Richard!

[johnw993]

Perhaps you can help me out with another issue?:

I've created a Userform to enter (new) employee information, such as name, department, title, salary, etc. etc.
It contains a number of ComboBoxes and TextBoxes. All ComboBoxes contain lists of data from ranges.
My question: I want to be able to edit an existing employee's information using the same form, and have each Combo and Textbox show the information belonging to the employee when I select that name in the first ComboBox.
All data entered in this form is stored in a database (Table) in a separate sheet.
Other people have suggested I replace all other comboboxes with textboxes, but I don't want to do that. Using the Comboboxes helps guide the user and ensures consistency in data entry.
See below the code for the UserForm (called Admin).

(You can ignore the bottom part of the code. I'm experimenting with code to populate the database without using Tables. On the MAC you can not have multiple users using your model simultaneously if you are using Tables....)

Please Login or Register  to view this content.

[johnw993]

Perhaps you can help me out with another issue?:

I've created a Userform to enter (new) employee information, such as name, department, title, salary, etc. etc.
It contains a number of ComboBoxes and TextBoxes. All ComboBoxes contain lists of data from ranges.
My question: I want to be able to edit an existing employee's information using the same form, and have each Combo and Textbox show the information belonging to the employee when I select that name in the first ComboBox.
All data entered in this form is stored in a database (Table) in a separate sheet.
Other people have suggested I replace all other comboboxes with textboxes, but I don't want to do that. Using the Comboboxes helps guide the user and ensures consistency in data entry.
See below the code for the UserForm (called Admin).

(You can ignore the bottom part of the code. I'm experimenting with code to populate the database without using Tables. On the MAC you can not have multiple users using your model simultaneously if you are using Tables....)

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

Since this is a different request then you should start another thread with a different and appropriate title. This is a forum requirement since it assists others who may be searching for a similar situation.

I suggest you close this thread as SOLVED and create another.

Regards

[johnw993]

Sorry Richard, I'm still somewhat new to this venue and appreciate your advice. Will repost this as a new thread.