Mulitple Action When Condition Met

Oct 21, 2008

How do you set the code so that, if a condition is met, then several cells are filled with values?

So far, my code is

HTML
If Cells(i, 2) < Cells(i, 4) Then
Cells(i, 5) = "A"
Cells(i, 6) = Cells(i, 2)
End If

View 11 Replies


ADVERTISEMENT

Mulitple IF Statements Regarding One Cell

Dec 4, 2008

I am trying to using multiple if staements regarding one cell. What I am trying to achieve is:

if b2=0, then null, however if b2=1, then arable, however if b2=2, then manged grassland, however if b2=3, then forestry/woodland, however if b2=4, then semi-natural vegetation, however if b2=5, then urban, however if b2=6, then water

I have these 2, but they don't work - what am i doing wrong?

=IF(B2=0,null, IF(B2=1, Arable, IF(B2=2, Managed_Grassland, IF(B2=3, Forestry/Woodland, IF(B2=4, Semi-natural_Vegetation, IF(B2=5, Urban, IF(B2=6, Water, NO_LAND_USE)))))))

=IF(B2=0,IF(B2=1,IF(B2=2,IF(B2=3, IF(B2=4,IF(B2=5, IF(B2=6, Water, null), Urban),Semi-natural_Vegetation), Forestry/Woodland), Maneged_grassland), Arable), null)

View 5 Replies View Related

How To Return Mulitple Corresponding Values

Sep 8, 2005

i want to look up a name that occurs several times in one column of a
spreadsheet and return corresponding values from each row the name occurs on.
Vlookup returns only one value. How can I get multiple values?

View 14 Replies View Related

If Not Exist Add, If Mulitple Delete..

Jan 14, 2009

I have a worksheet that has three columns, TYPE, VALUE, PERIOD.

Within TYPE column it will always be either A or B
Within VALUE column a number common to both A & B for that "period".
Within PERIOD column a month-year.

A shown here:

TypeValuePeriod
A3000Jan-09
A3000Jan-09
A3000Jan-09
A3000Jan-09
A3000Jan-09
B3000Jan-09
B3000Jan-09
B3000Jan-09
A3200Feb-09
A3200Feb-09
A3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09
B3200Feb-09....................

View 9 Replies View Related

Mulitple Lookup Criteria

Jul 30, 2008

I have a report that I paste into excel. The report contains 4 columns. Column A contains a numeric value. The numeric value relates to a geographical area and the same numeric value may show up more than once. The reason the numeric value may show up more than once is because in column B there is a list of dates showing when a percentage in column D become effective. I have attached an example of the report.

What I am looking for is a was where the user can enter a geographic number in cell J4 and a control date in cell J5 and have cell J7 display the percentage value. I need the lookup to look up the correct area and then use the control date to find the corresponding percentage. So if Area 1 (numeric value) shows up in Column A in rows 2 through 5, the control date will determine which row to pull the percentage from using the effective dates in column B.

View 3 Replies View Related

Combo Box To Remove Mulitple Rows

Jan 12, 2010

I am attempting to design 2 combo boxs by which you can select from a number of equipment types and a criticality:

Equipment Types Criticality
Vac Pump 1A
Filter Dryer 2A
Valve 3A

I want to attempt to write a macro so that when the equipment type and criticality has been selected, a corresponding number of rows will disappear.

For example:

If Vac Pump and 1A are selected then all the rows from 70 downwards will be hidden, and if Filter Dryer and 2A are selected, then the rows from 6 to 38 and from 47 onwards will be hidden.

View 10 Replies View Related

Inserting Mulitple Rows Using Macro

Mar 18, 2009

I'm trying to write a macro that inserts multiple rows by giving user a MsgBox to enter the # of desired rows. Meanwhile, I need to make sure that the formulas in certain cells are filled down (not just copied from cell directly above). I put something together but it's only adding a single row. Attached is my sample workbook.

View 2 Replies View Related

Highlighting Mulitple Cells With A Macro

Nov 6, 2009

I need to highlight cells for all of the products and rates I need to get pricing for. I have a macro that goes out to my data and pulls in each different rate and program (columns BM:BN). That macro also sets the first note rate in the range to the lowest of the group, so this will always be different.

I now manually highlight each cell that I need to get pricing, but would like to create a macro that looks up those codes and rates in column BM:BN and highlights the cells for me. I need it to highlight in all 7 boxes. I've attached a sample worksheet of what it looks like after I highlight the cells.

View 2 Replies View Related

How Do I Link Mulitple Drop Down Boxes Together

Feb 20, 2006

I have 2 drop down lists made, but I want to have one linked to the other
one.

For example the first list has types of wood in it.

Bamboo
Maple
Oak

The 2nd one has pricing in it.
1.25
2.50
3.50

If I click on Bamboo - I want the 2nd list to auto generate 3.50. Can that
be done?

View 14 Replies View Related

Retrieve Latest Value From Mulitple Columns

May 4, 2008

I have a list of teams in 2 columns (columns A and B). These teams play matches against each other. The teams repeat themselves but vary in their order in which they play and whether they are home or away; i.e. appear in column A or B.

Each team has a points brought forward value in column C or D which correspond to the teams in column A and B respectively. They also then have a carried forward points value in column E or F produced after the points from the current game (not shown in the attached file).

I need to be able to find a formula which will retrieve the latest (last match) carried forward value for a team and enter it into the brought forward cell. This is made difficult because the team may last appear in columns A or B and there is no logic as to when they last played. It is easy enough to use lookup to find the value from the first row of an array but i cannot work out how to find the value in the last row.

View 10 Replies View Related

VBA Copy Workbook To Mulitple Workbooks

Apr 25, 2007

I am attempting to create a macro that does the following. I need to copy the active workbook to 25 new or existing workbooks without showing "save as" etc. These workbooks will reside in the same directory as the active wb. Problem is, I have multiple directories that I will need to do the same action.

C:ClientClientNameExample1.xls (only has sheet1)
---This will be the master which needs to be copied to 25 more times like below.

C:ClientClientNameExample2.xls
C:ClientClientNameExample3.xls
C:ClientClientNameExample4.xls
C:ClientClientNameExample5.xls
etc...

View 9 Replies View Related

Macro To Search Particular Column On Mulitple Worksheets

Sep 15, 2009

I am using the code below to search my entire workbook for a value entered by the user at a popup.

View 2 Replies View Related

3 Way Switch Or Change Values From Mulitple Places

Dec 29, 2006

On sheet 1 there is a dropdown box that indicates a condition. For example the choices are 1-4. On Sheet 2 calculations are made based on the condition in the drop down box.

I also show the condition # on sheet 2 with a simple =sheet1!a1. What I would like is the ability to have the drop down box on both sheet 1 and sheet 2 and that if you change the value in either place then is changes in the other. Same way that you can operate a light with 2 different switches.

View 9 Replies View Related

Data Search & Copy From Mulitple Worksheets

Dec 31, 2009

I have a workbook with 26 multiple worksheets that each represent 1 of 26 payperiods per year (bi-weekly payperiods). These worksheets consists of rows of employees names and hours worked each day. The columns consist of dates representing each day in that payperiod.

I need to be able to search all the worksheets (ie. search all 26 payperiods) to find specific 30 day intervals and then copy each employees hours worked to a master spreadsheet. I have tried variations of Index & Match formulae as well as vlookup/hlookup/ISNA without luck. I am not able to use VBA.

View 5 Replies View Related

Select Mulitple Sheets Without Naming Each Sheet

May 14, 2006

Im sure there is a post somewhere on this forum as I have seen it before but I cant find it anywhere. All I want to do is be able to select Sheets Sun,Mon,Tue,Wed,Thu,Fri,Sat without naming each of the sheets. The post I seen would select all the sheets between the two sheets that were stated in the vba code. something like: Sheets( Array("Sun" To "Sat").select

View 5 Replies View Related

Bringing Data From Mulitple Worksheets To A Master Sheet

Jun 10, 2008

Each salesman has an enquiry log / hot prospect sheet that they are responsible for.

At present at the end of the month our receptionist has to consolidate this information manually onto a master sheet.

I would like to create an automatic master sheet that auto updates each time it is opened.

It has to read data from about 13 work books each with varting amounts of date. All works books are formatted identically (or they will be)

View 14 Replies View Related

Search In Mulitple Columns And Return Corresponding Column/Row Result

Apr 30, 2008

Basically, I need to look for an item, lets use "apple" as an example, in a range of cells, A1:D100. Then the corresponding result would be in another column in the same row.

View 10 Replies View Related

Import Mulitple Single Column Text Files

Apr 20, 2008

I am trying to get a VBA code to import multiple text files into an excel worksheet. The text files contain about 5,000 words each listed in a single column. I found this thread Import Multiple Text Files and the code listed there:

Sub test()
Dim myDir As String, fn As String, ff As Integer, txt As String
Dim delim As String, n As Long, b(), flg As Boolean, x, t As Integer
myDir = "c: est" '<- change to actual folder path
delim = vbTab '<- delimiter (assuming Tab delimited)
fn = Dir(myDir & "*.txt")
Do While fn <> ""
Redim b(1 To Rows.Count, 1 To 1)
ff = FreeFile
Open myDir & "" & fn For Input As #ff
Do While Not EOF(ff)..........................

View 8 Replies View Related

How To Set Password For An Action

Apr 27, 2009

How to set a password before executing any action.

For example, I have "cancel" button in my userform. If i click "cancel" it unloads the form. Instead of that i need a password prompt. so when i click the "cancel" button ,it should ask for a password. only if i provide the correct password then only the form should be unloaded.

View 6 Replies View Related

Put A VBA Class Into Action?

May 28, 2014

JSON import to Excel - Stack Overflow

I understand most of what this post is saying, but I don't understand how to put it together in an excel module or class module.

View 5 Replies View Related

Undo Action In VBA

Jul 18, 2006

I have a database in excel which I want only selected users to have edit rights & others should have readonly rights. I have written the following code where a pop up would appear in selected cells warning user for editing the said cell. What I want is when a user clicks yes he should be able to edit it & when he clicks no the cell should get protected.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rTriggerCell As Range
If Not Intersect(Target, Range("D1:D100")) Is Nothing Then
Set rTriggerCell = Target
Application.EnableEvents = True
If MsgBox("Edit Cell?", vbYesNo)
End If
On Error Goto 0
Exit Sub
End If

View 6 Replies View Related

Cell Equals 0 Changes When Condition Met - Leave Unchanged When Condition False

Mar 24, 2014

I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.

The Cell formula that I am trying to use is as follows:

=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))

A3 contains TODAY()

H114 is the cell being calculated

AA118 is the cell containing the calculated value for the current month.

View 2 Replies View Related

Multi Condition Format? Lookup And Date Condition Valid For

Aug 13, 2009

I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.

View 6 Replies View Related

Loop Through An Action Within A Macro

Nov 19, 2008

I am trying to loop through an action within a macro and require some assistance.

I have the following

View 2 Replies View Related

Action When Macro Is Aborted

May 30, 2009

I have a spreadsheet that when I am running an specific macro and userforms, this one sheet is visible, when the macro is not running, then, the sheet is hidden.

Now, that happens automatically the way that I set up my macro; however, if the macro is aborted, the sheet doesn't hide.

View 7 Replies View Related

Cancel A Message Box Pop Up Action

Aug 13, 2009

If I press either the "OK" or "Cancel" button without entering any states in (because I changed my mind and want ALL states in there, or I pressed either button by accident), the macro eliminates ALL the rows because it needs criteria.

Can this code be modified so that the message box does the following...

1. If I press the "OK" button but I haven't entered in any criteria in the message box, bring up another message box saying "you have not entered in any states in" which brings up an "OK" box and brings you back to the original message box.

2. If I hit the "Cancel" button, bring up a message box that says "No states will be removed" with an "OK" button, essentially cancelling the "State" macro portion of the sub, but continuing with the rest of the code in the sub if there is any.

I would like to have this message box work this way even if states were entered in the input box but the user pressed the "Cancel" button anyway.

3. Create a NEW button named "Keep ALL States" which essentially does the same thing as the "Cancel" button. (I'm trying to "idiot proof" this).

View 4 Replies View Related

Comment Out Using VBA - How To Create This Action

Feb 1, 2012

I have created vba which I want to comment out. I totally stumped how to create this action on vba. My current vba code which I want to comment out using vba

Code:
Option Explicit
Sub YearlyInput()
End Sub
'Speeding Up VBA Code
With Application

[Code] .......

View 8 Replies View Related

Cursor Not Stopping While TAB Action?

Aug 24, 2012

I have a for which consists of

2textbox
1 listbox
1 textbox.

While entering the data, the cursor does not stop in the list box and goes directly goes to the next textbox. Autotab is set to true.

View 4 Replies View Related

Repeating Action In A Macro

Nov 23, 2007

I have a macro that is working fine. It calculates some values based on a SQL database and then copy the values and paste them into another spreadsheet.

However, I need to repeat the same procedure 1296 times.

Please see below the macro.

Sheets("QUERIES").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "2"
Range("C5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("D5").Select
Selection.QueryTable.REFRESH BackgroundQuery:=False
Range("C5:G5").Select
Selection.Copy
Sheets("AWB RANGE").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I need to increment the line #3 (ActiveCell.FormulaR1C1 = "2") that should start with 2 and end with 1296.

Also, I need to increment the line #11 (Range("K2").Select) that should start with K2 until K1296.

The other parts of the macro will not change.

Would you be able please to give me a hand on this explaining how to create a counter to start on 2 and stop with 1296 ?

View 9 Replies View Related

Personal.xls Is Missing In Action

Feb 25, 2008

Last friday my personal.xls file went missing. I cannot find it anywhere which means I am missing about 10 key macros. While it is not the upset of the century, my problem is that now when I go into Record Macro and try to "Store New Macro in:" -> "Personal Macro Workbook" and then click OK, Excel brings up a msg box that says:

Personal Macro Workbook in the startup folder must stay open for recording.

Click OK and Excel says:

Unable to Record

View 9 Replies View Related







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