Macro To Replace IF Statement That Compares 2 Cells
Nov 21, 2007
In cell M32 there will either be an O or the cell will be blank. In cell T51 I had placed an IF statment like this:
=IF(M32="O","","X")
Now that works ok but there is a glitch on my part. If T51 is blank because of M32 containing an "O" then the user will also enter a number into T51. Because I was using an IF formula in the cell it gets overwritten because of the user needing to enter data in the cell because of M32 containing a O. How do I write a macro so that a similar formula is used but still able to enter data manually without overwritting the formula?
View 14 Replies
ADVERTISEMENT
May 17, 2006
I'm trying to write a macro that compare 2 cells. I can't figure out what is wrong with the code i have attached. It keeps giving me a fatal error.
View 6 Replies
View Related
Aug 31, 2007
I need to write a macro that compares the numerical value of 2 cells and give me a result as described below:
All the numbers would be either whole numbers or whole numbers + 0.5
Example 1:
The two values are both whole numbers then no action is taken.
Cell A = 5
Cell B = 5
or
Cell A = 7
Cell B = 5
Result = No action taken..................
View 14 Replies
View Related
Jun 25, 2009
this is an extract from a pivot table:
A B
N-CONTROX 18-jun 02-jul 16-jul Q-BETACICL 22-jun 01-jul
The left column is the product, the right the delivery day.
I need to Check and extract each product delivery days. For example, for Q-betacicl I should:
1.- in cell f1, use a Vlookup function to extract 22-jun. that´s done.
2.- in cell f2, use an If function that compares the cell below Q-betacicl with the cell below. If it´s 0 (like this case) it should extract 01-jul
I used this and excel didn´t even let put the formula, arguing some error I ca´t find:
=IF(OFFSET(Vlookup(E6;$A$5:$B$27;1);1;0;1;1)=0;Offset(Vlookup(e6;$A$5:$B$27;1);1;1;1;1);0)
Basically this says:
Look for a value equal to E6 (q-betacicl) in matrix A5:b27, and if the cell immediately below it = 0, copy the value to the right of that cell (the one below it).
In the case I showed, for Q-BETACICL, it should copy 01-jul. For N-CONTROX it should copy 02-jul.
View 9 Replies
View Related
Mar 16, 2007
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
View 2 Replies
View Related
Jan 11, 2010
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
View 3 Replies
View Related
Apr 11, 2014
I have a excel which contains 5 columns in which 5th column data cells has to be replaced with another set of data which have relationship with other 4 columns data.
View 3 Replies
View Related
Jul 22, 2014
Sample Data from "Demand Planning Prem" worksheet
Date
SKU
Name
Begin FG's
[Code]....
This worksheet has around 40,000 records, and I need to create a Macro that will ask the user for a date, and when the user enters a date the macro goes through column A and finds all of the matching dates and then replaces the numbers (along with the formulas) in the corresponding Add Returns cells (column E) with 0's.
I have only been able to create an inputbox, but I am completely lost regarding the criteria due to being very new to VBA.
Sub ClearData()
Dim EnterDate As Date
Dim msg As String
[Code].....
View 2 Replies
View Related
Apr 22, 2007
I have a list of values of sheet titled "exclusives" column B Row 2 to Row 250.
Hence, if any of these values are found in sheets "1", "2", "3" and "4" in column C (of each sheet) replace the existing value with "Brand".
In addition after this bit I then need it to look up every new "Brand" and for the eight columns preceding (after) the new value I need to be cleared (any entries in those columns in that specific row of the "Brand" value deleted).
View 9 Replies
View Related
Jun 2, 2009
I am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.
What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.
Is this possible?
View 6 Replies
View Related
Sep 12, 2009
After much fretting over this (and many other Excel issues), I've decided to seek out some assistance. I've included an example with columns, because I'm not very fluent in Excel terminology.
View 5 Replies
View Related
Jul 18, 2014
i'm looking for macro for find and replace characters in a locked sheet, if a cell contains for example Monday (First day of week)
I want with a command or macro to change the (First day of week) in (Top of the Week) like this Monday (Top of the Week)
View 9 Replies
View Related
Jul 25, 2014
So I was given the task to translate some procedure instructions in an Excel file from English to Spanish. These are very simple instructions and in most cases repetitive throughout the document. There are a lot of instances where the instructions are the same except for a #. They are meant to be modified within the cell as the text that appears in the cell will be printed exactly as it appears.There are several instructions in the cell, it is basically a long continuous string in the cell. My idea is to create a macro that can search that range of cells for the instruction, replace it with the Spanish equivalent while leaving the original English instruction in the cell.
View 6 Replies
View Related
Jan 13, 2009
My program assigns a variable one of three values - either "Lancaster", "DeSoto" or "Cedar Hill". My string compares for Lancaster and DeSoto work fine but can't get anything happening for Cedar Hill.
I looked at the value of the variable and it appears to be correct. However, when I did a StrComp for the variable and the string "Cedar Hill", they did, of course, not come out as equal.
View 3 Replies
View Related
Nov 26, 2009
I am currently trying to take a list of people who have log in times at work however if they log out then back in the result is what you see below.
What I am looking to do is match the find the name then find the earliest time (login Column C) and then the last or latest time (log out Column D)
A B C D
John Smith64676076:02 AM6:13 AM
John Smith64676077:25 AM11:27 AM
John Smith64676212:08 PM4:01 PM
John Smith64676076:13 AM7:25 AM
Would like a formula as I have another Tab on the sheet that looks up name and compares against a schedule to see if the person was late or left early.
View 9 Replies
View Related
Dec 15, 2009
Is there a Function or compound Formula that compares the values of two ranges and returns True/False ?
Something along the lines of : AreRangesIdentical(Range1,Range2) returning True/False.
I have played with some array formulas but they seem to compare the two first cells only.
I know one can easily come up with a UDF that loops through each cell and returns once a cell is found whose counterpart is different but i am looking for a solution that doesn't use a loop.
View 9 Replies
View Related
Oct 10, 2008
I've modified this macro I found on this forum, all it does is compares 2 lists of 6 digit serial numbers and and then tells me which numbers are in list 1 that are not in list 2.
However, I want to modify it so that it also gives me the numbers in list 2 that are not in list 1 (put into column F). I then need it to cut the matching serial numbers (in columns A and B) from sheet 1 and paste them into sheet 2.
View 5 Replies
View Related
Apr 17, 2014
I would like to build a graph that compares 2 sets of data but only plots 1 dot where the two intersect. I have seen this graph made by others in Excel but I do not know what it is called or how to make it. An example data set is below.
ID Age Height (cm)
1 5 125
2 7 140
3 8 138
4 11 152
I would just like to plot the location where the age and height for the ID's cross and not 4 plots with the age and 4 plots with the height.
View 1 Replies
View Related
Jun 18, 2009
I have a workbook that compares two sheets of data by taking the difference between the two. Some of the cells contain N/A for value, so when I take the difference of two sheets, it gives me #Value! error. My question is how can I format it, so when the cell contains text it displays N/A or if its a value: it takes the difference and populates the value.
View 9 Replies
View Related
Jun 23, 2009
How do I count the number of cells that have a value greater than 0 in a range of cells?
View 2 Replies
View Related
Apr 22, 2009
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
View 5 Replies
View Related
Nov 5, 2011
Have been searching the forums for an answer but to no luck very new to excel. What i hope to try and do is change amount in numbers in cell range E4 to L23 which might include a decimal place to the text value in cell F1 leaving all blank cells blank.
I would if possible like to apply this macro to many sheets with lots of different titles in the same workbook
These are 2 examples of is the sort of thing i have been playing around with the bits in red are what i think i need to fill out hopefully i will be able to find some code to appy it to all sheets and add it on. Am I going down the right route
Sub replace1()
Dim rep As String
Dim newrep As String
rep = Dont know what to put here to make it apply to ant number
newrep = Dont know what to put here to make it contents of cell F1
[Code]....
View 6 Replies
View Related
May 20, 2014
In one of my worsheets in the excel document, I have data as shown below:
Name Description Minimum Values Maximum Values
ABC test desc {0.0, 0.0, 0.0} {1.0, 1.0, 1.0}
DEF test desc {{0.0, 0.0, 0.0},{0.0,0.0,0.0}} {{1.0, 1.0, 1.0}, {1.0, 1.0, 1.0}}
Basically for a 1D array, the minimum and maximum values are within one set of brackets, whereas for 2D array (as shown in the second row), there would be nested brackets with comma after every closing bracket being a row separator.
My requirement is to write a macro to replace the minimum and maximum values in every row of the worksheet (I do not know the exact number of rows as it could vary) with just one value instead of repeated values depending on the size of the array. In other words, I am looking at the below output:
Name Description Minimum Values Maximum Values
ABC test desc 0.0 1.0
DEF test desc 0.0 1.0
View 2 Replies
View Related
Mar 18, 2008
replace row of first cells match
replace row if 1st column match
But that's only a very small part of my question.
1. I have an original list of IP addresses which contain many duplicates.
2. I used "The Duplicate Finder" Add-in and created a unique list of IP addresses from the original list.
3. I did proprietary search on the unique IP list to get an approximate geographic location of each IP address.
Now I want to match all the IP search information to all the duplicates in the original list.
It's difficult for me to craft an exact question without knowing the proper terminology, so I'll "illustrate" a few point below.
Original List of IP addresses in a single column (IP Address)
IP address
12.34.56.78
12.34.56.78
98.76.54.32
95.62.84.37
95.62.84.37
95.62.84.37
95.62.84.37
55.11.99.225
55.11.99.225
23.56.89.14
78.56.12.43
78.56.12.43
Unique list of IP address with additional info in 3 more columns (4 columns total)
IP Address Country State City
12.34.56.78 USA CA SomeName
98.76.54.32 USA NY AnotherName
95.62.84.37 GB England Ye OldeName
55.11.99.225 MX EL SmokyCity
23.56.89.14 USA WA RainyCity
78.56.12.43 USA FL ViceCity
What I want to do is if an IP address in the original list matches the IP address in the unique list, the original IP row is replaced with the matching IP row in the unique list.
View 9 Replies
View Related
Jan 9, 2007
I have a list that looks like the list on my sample file. You will see ME28A16 at the top and the bottom of the list. I want to fill the Cells inbetween with that value. In my real file It may start ME or it may start PE
Here is the Script that I am using but I can not get it to work:
Dim CurrentRow As Long
Dim LastRow As Long
Dim CurrentText
LastRow = Range("A65000").End(xlUp).Row
For CurrentRow = 1 To LastRow
If InStr(4, Cells(CurrentRow, 4).Value, "M", vbTextCompare) > 0 Or _
InStr(4, Cells(CurrentRow, 4).Value, "P", vbTextCompare) > 0 Then
CurrentText = Cells(CurrentRow, 4).Value
Else
Cells(CurrentRow, 4).Value = CurrentText
End If
Next CurrentRow
View 9 Replies
View Related
Oct 10, 2008
I have a spreadsheet with 2 columns (let's pretend), the first column uses Data Validation for a dropdown list. The second column contains a formula that references the 1st column and uses lookup:
=IF(A35<>0;LOOKUP(A35;NewCons;NewGroups))
So as soon as a value is selected in column A, column B changes to display a value.
Rather than just displaying this value, I would like the cell in column B to be completely replaced by this value, so goodbye to the formula.
Is there a way that I can do this? Will I need to go and study how to use macros?
View 11 Replies
View Related
Jul 9, 2014
I'm using a large database - excel based, with lots of VBA and macros inside - where the default decimal format is “.” (period). When I copy and paste into a normal excel workbook format I want to replace the periods with comas. The paste function I'm using is “Match Destination Formatting (M)”.
I've recorded a macro to replace all of the periods with comas - still some of the values come up as a whole number, the period not being replaced with the coma.
I've tried almost all of the formats - text, general, number .... Still does not work.
View 7 Replies
View Related
Feb 10, 2009
I have a workbook (materials forecasting) that automatically pulls from another workbook (production schedule).
The materials forecasting workbooks are named in regards to the current Monday (ie 02-02-09.xls, 02-09-09.xls, etc). The production schedules are named in accordance with the financial periods and weeks, ie PD1WK1.xls, PD1WK2.xls, PD1WK3.xls, PD1WK4.xls, PD2WK1.xls, etc
The materials forecasting sheet looks two weeks out.
EXAMPLE (pretend that today is the first day of PD1WK1)
Cell C1 will reference PD1WK1.xls
Cell C2 will reference PD1WK2.xls
Each week I copy the old materials forecasting sheet and rename it to the current week (ie I take 02-02-09.xls and rename it to 02-09-09.xls). I have a bunch of macros set up to move my deliveries up a week and what not. One macro I would like to set up, however, is that by putting a value in a cell the find and replace macro will work.
EXAMPLE
Cell A1 will reference PD1WK2.xls
Cell A2 will reference PD1WK3.xls
Cell C1 will reference PD1WK1.xls
Cell C2 will reference PD1WK2.xls
Then I would have the macro find all instances of whatever value is in cell C2 and replace it with whatever value is in cell A2. Then likewise fore C1 and A1 respectively. (If you think about what I am doing here... all I am doing is moving the schedule up one week so that I am forecasting using the right schedule).
I can manually do this but I would prefer a macro based on a simple user input for when people fill in for me.
View 11 Replies
View Related
Sep 22, 2009
I have been having a problem with the worksheet (attached) and was wondering if anyone could give me any help.
I will explain the purpose of the sheet etc first and what it does at the moment:
This work sheet is to record peoples projects, hours related to those projects, and calculate their availability 37.5-(project hours+pto) for Belfast and dublin and 40-(project hours+pto) for chicago, and also their utilization which is =((37.5-Availability)/37.5)*100 for belfast and Dublin and ((40-Availability)/40)*100 for Chicago.
When a project is added I am currently going in and updating the formula for that person to incorporate the new row which their project is on, this is proving to be quite time consuming, and I was wondering if there were any macro’s that could be used so that it would search for a name on the first column and use each of the hours that are in the column for the given week to calculate the availability?
The utilization is not a big problem as it just uses the availability so as long as the availability is correct then the utilization is correct..
If there is not a macro do you know of any way this can be automated so that I do not manually have to add to formula for each cell when a new row is added with a project and hours?
View 12 Replies
View Related
Feb 21, 2012
I have a column of about 10,000 lines which contains a description Sheet 1 Column B. On another sheet (2) I have about 200 lines of abbrevations. In coulmn A2 is the full name, and column b is the Abbrevation. I would like to create a macro that would go down the list in A2 Sheet 2, go to Sheet 1 B find that word and replace it with Sheet 2 B2 abbrevation. Is there a way to do that?
Sheet 1
A B
680385 LEAD SET WITH GRABBER CONNECTION21379 ABSORBENT CARBON DIOXIDE SODASORB 3LB
Sheet 2
A B
WORDAbbreviationABDOMINALABDOMANGIOGRAPHICANGIO
View 4 Replies
View Related
