Worsheet Change Procedure Needs IF State Embedded

Nov 18, 2007

I wrote the following...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("Obj1")
If Not Intersect(Target, VRange) Is Nothing Then
Range("ObjDescr1").Select
ActiveCell.FormulaR1C1 = "=IF(Obj1="""","""",(IF(EmplType=""Salary"",VLOOKUP(Obj1,SalaryDevGoalsTbl,2,FALSE),VLOOKUP(Obj1,HourlyDevGoalsTbl,2,FALSE))))"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End If
End Sub

What it does, everytime range named Obj1 is selected, range named ObjDescr1 is populated to a Vlookup, depending on which type of employee.

I need the Worksheet Change procedure to handle Obj2 - 5. I want to Change Procedure to find out which named range Obj(n) is effected, and to do the rest on that one. ObjDescr(n) is also to match the value of (n).

How can I do this the most efficient way? I want to cut down on code in ever area possible, so I dont want to just copy and paste for obj1,2,3,4 and 5.

View 9 Replies


ADVERTISEMENT

Cell Color Change By State Of Other Feild

Jul 29, 2009

I'm playing with an excel file that has 3 different spreadsheets and basically the same layout just different product lines on each sheet. I need to find a snipet of VB code to change cell color depending on if another cell is used(filled in) or if it is blank. I'm very limited to my VB skills but can tinker enough to get what I need if someone points me in the right direction.

here is an example of what I'm trying to accomplish using the following Columns:

ItemNo[A], QUOTEREQUESTDATE[b], SUBMITTOSALESDATE[C] APPROVALRECEIVEDDATE[D], RELEASETOMFGDATE[E]

EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO RED CELL:
[b] USED, [C] BLANK, [D] BLANK, [E] BLANK

EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO ORANGE CELL:
[b] USED, [C] USED, [D] BLANK, [E] BLANK

EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO YELLOW CELL:
[b] USED, [C] USED, [D] USED, [E] BLANK

EXPECTED RESULTS TO CHANGE CELL[A] COLOR TO GREEN CELL:
[b] USED, [C] USED, [D] USED, [E] USED

View 13 Replies View Related

Returning State Cities By Inputting Given State Abbreviation

Jun 3, 2012

I'm trying to create an array formula that returns all the cities in a given state by imputing the state's abb in cell K2. The returned cities go into column J. California has the most cities(430) and the cities are 5000 rows deep.

View 7 Replies View Related

Macro To Prompt Then Record Data Following Button State Change

May 10, 2007

I am looking to find a way and synthesize code that will prompt the user for text data when he/she clicks a radio button, then place that text data in a "Notes" worksheet within the same workbook. Given that there are multiple radio buttons, with options of "OK" and "attention needed" (it is a checklist of maintenance items), I also need to find a way to have each note listed sequentially in the notes worksheet. My questions: Is this possible using macros? Can you steer me in the right direction? (change event, inputbox, then what?)

View 3 Replies View Related

Using Rank Worsheet Function In VBA

Aug 22, 2006

I am trying to rank number that do not have a specific adress. My problem is that I do not know how to define all parameters of this function

View 3 Replies View Related

Display Worsheet Range On UserForm

Nov 8, 2009

i want to insert a range of cells from a worksheet onto a userform. how can this be accomplished? i use excel 2007, so theres no spreadsheet control. active x controls i dont know anything about and i heart its unsafe.

View 9 Replies View Related

Work Marked Late -Either VBA Or Worsheet Function

Dec 30, 2006

I have a very large spreadsheet with the following columns: WO# (number field); Start date (date format MM/DD/YYYY); Frequency (text); and craft (number). I am trying to have code that checks the frequency and if is "Monthly" or "Weekly" it just goes on to the next row; if it is "Annual", it adds 163 to the start date (start date needs to changed to a numeric field); if it is "Semi-annual", it adds 82 to the start date; if it is "Quarterly", it adds 45 to the start date; and so on, there about 20m different frequencies. After it adds the above value to the start date, I need to check if that number is less than today's date (the day I run the code). If it is, it needs to flagged as "LATE" and the whole row of info copied to another worksheet with LATE as the title and all the column headings and info copied to the worksheet. I hope this makes sense to someone because I am a beginner in Excel and even less informed when it comes to VBA. Any help would be greatly appreciated.

The way the process must work is that I need to check the frequency and if it is "Weekly" or "Monthly" , it is ignored and goes on to the next row. All other frequencies are cut in half, i.e., "Annual" is 183 days, "Semi-annual is 92 days", "Quarterly" is 45 days, "2-Year" is 365 days, and so on. This number needs to added to the scheduled start date (now formatted as a number, not a date, and checked to see if it is smaller than today's date (also a number). If it is, it is reported on the second worksheet (titled Late).

View 11 Replies View Related

Userform Submit To Worsheet (Subscript Out Of Range)

Jan 31, 2010

creating the user form using the submit button. Ive used a tutorial to create the form and the code, but it doesnt work i get a subscript out of range error 9)

Here is what i have


Private Sub addpatient_Click()

Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.txtfirstname.Value = "" Then
MsgBox "Please enter a First Name.", vbExclamation, "Patients "
Me.txtfirstname.SetFocus.........

View 11 Replies View Related

Worsheet Cell Does Not Show Correct Answer

Apr 6, 2008

I have 2 worksheets (worksheet 2 is Slicing and Worksheet 1 is Production).

Production worksheet D6 has a dropdown box for, 1, 2, 3, 4, 5, 6, 7, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6 and 7.7.

In the Production worksheet, I have in cell E6 =IF(D6=1,"ONE",IF(D6=2,"TWO",IF(D6=3,"THREE",IF(D6=4,"FOUR",IF(D6=5,"FIVE",IF(D6=6,"SIX",IF(D6=7,"SEVEN")))))))

In the Production worksheet, I have in cell F6 =IF(D6=1.1,"ONE",IF(D6=2.2,"TWO",IF(D6=3.3,"THREE",IF(D6=4.4,"FOUR",IF(D6=5.5,"FIVE",IF(D6=6.6,"SIX",IF(D6=7.7,"SEVEN")))))))

How do I show in Slicing worksheet cell B6, either 'ONE', 'TWO', 'THREE', 'FOUR', 'FIVE', 'SIX', 'SEVEN' as i have the formula =PRODUCTION!E6&PRODUCTION!F6 and the cell shows '

View 9 Replies View Related

Countif Cell Equals Active Worsheet

Apr 21, 2009

I need to enter a countif formula if a cell equals the name of the active worksheet and also cell A1. My worksheets are not always the same and I also want to be able to copy this formula to multiple worksheets so if the formula states active worksheet then is should work properly when I past it to multiple tabs (at least in my head)

View 9 Replies View Related

Embedded Browser

Feb 11, 2006

I would like to embed a chart that updates from the web into my spreadsheet. This is it here:

[url]

View 9 Replies View Related

IF Rule: Another Cell To State N/a

Jul 8, 2009

Depending on the text entered into one cell, I'd like another cell to state n/a. For example, if cell a2 shows the text of cash, then cell b2 would show n/a.

View 3 Replies View Related

Get State Of Checkbox On Worksheet

Jan 14, 2009

I need to get the state (xlon or xloff) of check boxes on a worksheet. i have tried the following but unable to get it to work.

View 3 Replies View Related

Lookup Up State Abbreviations

Aug 4, 2009

I have a spreadsheet that is used for sales territories. In column A, I have customer names, in column B, I have the State abbrev., In column C I would like to return as salesperson's name based on the State.

Aco IL John Smith
ABco OH Jane Doe
Cco WI John Smith
Czco IL John Smith
Dco WY Adam Scott
Fco UT Adam Scott
FDco CT Bill Jones
etc WI John Smith

So I need a formula to enter in column C, that will return the correct salesperson based on the state in column B. (when auto filled down)

For example: IL,WI,MN,IA,ND,SD = John Smith
OH,PA,KY,MI, = Jane Doe
etc

View 8 Replies View Related

Embedded Bigger Than Smaller Than-IF

Jul 13, 2009

The jist of the problem is that employees are paid their normal wage up untill 37.5 hours of work. From 37.5 to 40 hours of work they are paid 1.5 times their wage. Any hours over 40 are paid at 2 times the regular wage.

So i need a formula that can work out their total pay. Im not sure what i could put in a dummy document to help you since it would just be one cell for their hourly wage, one for their hours worked and then one for the calculation. so you can reference any cell as an example and assume £6 as hourly rate

View 3 Replies View Related

Import From PDF Embedded On Web Page?

Dec 15, 2009

Working on automating a data update process for my department (well, for the Italian speaking analysts anyway). I'm trying to import data from an embedded PDF to my Excel 2007 worksheet - I only need the data on the PDF, but if the whole screen can be scrapped I can clean it up in my code.

Any ideas if/how I can do this?

View 12 Replies View Related

Embedded Macros In New Solver

Feb 12, 2012

Can embedded macros be found within the new Solver and how would you make the changes you need in VBA with different constraint analysis?

View 3 Replies View Related

Attaching Already Embedded PDF To Email?

Jan 14, 2014

My "Drawing" is embedded on Sheet1, later on in Sheet12 I click a button that'll send an automatic email to a user. I want to attach the embedded PDF onto this email if it is at all possible??

Code:
Dim Drawing As Object
After I've declared my variables I've tried setting the variable "Drawing" to the object, this is where I'm encountering my errors mainly "Type Mismatch" or Object Variable not set...

Code:
Sheets("Menu").Visible = True
Drawing = Sheet1.Shapes.range(Array("Drawing"))
Selection.Verb Verb:=xlPrimary
Sheets("Menu").Visible = False
At the bottom of the code, this is where i pull in the object

Code:
.To = Sheet12.range("L8")
.CC = ""
.BCC = ""
.Subject = "Arrange P&D Request"
.htmlbody = strbody & vbNewLine & Signature
.Attachments.Add ("Drawing")

View 9 Replies View Related

IF Function Using Embedded Checkbox

Jan 4, 2007

I need to create a formula that will calculate a total if someone checks a certain box. (Each check box is associated with a cost. When people check the correct costs, the bottom should total the costs checked.)

View 9 Replies View Related

Embedded If Statement In Cell

Jan 12, 2009

i want to make and if statement in a cell that does the following:

if "J8 > 10" then fail
if "5

View 9 Replies View Related

Set Color Of Embedded Labels

Nov 21, 2006

I have a worksheet with Labels (from the "Control" Toolbox) embedded and I want to read and/or set the BackColor value in VBA. I can't figure out how to accomplish this when they're embedded on a sheet like this.

View 2 Replies View Related

Looping Through Embedded Charts

Feb 28, 2007

I have a worksheets (named Graphs) that contains 12 pie charts, 8 column charts and 4 line charts. I would like to create some VBA code to loop through all these 24 embedded charts and modify only the pie charts. I'm OK with the code to do the modification (which is to set their plot area size and also to delete the title), however, I have no idea how to do the initial loop to select only the pie charts. I'm sure there's a much better way then hardcoding in each of their chart names.

View 2 Replies View Related

Hyperlink To Embedded Object

Mar 28, 2008

I'm using Excel 2003 and have a spreadsheet that I want to distribute to a handful of people around the globe.

I want some cells in the page to link to various PDF files which I need to distribute with the worksheet.

I can embed the PDF files no problem, but I need each link to the embedded objects to take up no more vertical space than one row of text (without an Icon) - ie I need to be able to access the object through a "hyperlink style" item.

I can't figure out how to do this, as the hyperlink function does not seem to permit linking to embedded objects.

View 4 Replies View Related

If Statements: Column B To State "low (0.00-0.33)

Jan 16, 2009

I've got a list of numbers between 0 and 1
eg.

0.25
0.59
0.73
0.15
0.99

in column A

if I want column B to state "low (0.00-0.33), medium (0.34-0.67) or high(0.68-1.00)" for a corresponding value in column A, how would I do that?

View 3 Replies View Related

State And Local Sales Taxes

Feb 21, 2009

Our state carries a 4% sales tax on all items except food and prescriptions.
Our county carries a 3% sales tax on everything.

Attached on my work sheet:
Column "C" determines if an item is either food or non-food.
"G5" is the subtotal of column G
"G4" is the S/tx on "G5" at 3%
"G3" is the S/tx on "G5" at 4%.
"G2" is the gross pay out.

My question is:
I'd like a formula for Cells "G3" and "G4" that can determine which items paid for in column "G" match a "N" or an "NF" in column "C".

If an item in column "G" represents a "F" in column "C", then there should not be anything in cell "G4" If an item in column "G" represents a "NF" in column "C", then there should be a figure in "G3" & "G4".

View 2 Replies View Related

Split Name, Address, City, State And Zip ..

Apr 11, 2008

to automate a process that split name, address, city, state, and zip.

here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN....

View 13 Replies View Related

State And Country Check Macro

Jun 17, 2009

1 - If any cells (with data in it) in the "state" column contain any of the following abbreviations in it...

AB, BC, MB, PE, NB, NL, NS, NT, NU, ON, QC, SK, YT

Then all matching row cells in the column named "country" should say "Canada",

AND....(it must also meet this criteria)...

Criteria 2 - If any text in "Country" column is "Kryponite", AND "State" column is blank


If BOTH criteria 1&2 are met.....THEN...

then delete both the country & state columns

If not, keep both columns and highlight in light red any errors to this macro.

View 14 Replies View Related

Boolean Variable Initial State

Jul 15, 2009

Are Boolean variables ALWAYS initialised as FALSE when they are created?

I am wanting to leave a 'marker' in a function so that I know whether it is the first time it has been used or not. Something similar to this:

View 2 Replies View Related

Split Name, Address, City, State And Zip

Apr 12, 2008

I believe many people face is to automate a process that split name, address, city, state, and zip.

here are the examples of name, address, city, state, zip that i would like to be split in each column. this is difficult because the datas are not consistent and not predictable in how it WILL BE GIVEN....

View 9 Replies View Related

Reset Workbook To Original State

Dec 21, 2008

I am looking to create a macro button which will reset the sheet to its original state.

I have locked the cells users should not imput data into, and unlocked where they add their data.

At the end of every school year, they will need to be able to reset ALL the workbooks back to their original state with all the reference formulas.

I am thinking I will need to tell the macro to create a hidden copy of the workbooks and then upon hitting reset it will use the backup to override the current. But they will need to be able to reset at the end of every year.

I would like to have one reset button that resets ALL the workbooks at once, but if not, I could put a reset button on each workbook.

As I have just started my VBA training, I have a code that will create a backup and hide it, but I have no idea how to do the reset portion. And again, since they need to be able to reset it each year for x amount of times, I don't know how to get it to keep having a fresh backup and get everything to its original.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved