Insert Formula Depending On Cells Depending Of Variables?
Jan 27, 2014
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
View 3 Replies
ADVERTISEMENT
May 20, 2014
I have the following issue:
I have a table of data, and would like my userform to select parts on this depending on different variables.
When I run the macro "Show_UserFormCalculatePrice" or click the button "Calculate Price", the userform is activated which has 2 comboboxes, Date and Service.
When a certain date a service are selected I would like the the dish name to show in the labels of the userform.
My main issue is that there will be multiple dishes that will correspond to the data, and I would like them to be vertically listed in the labels depending on their number (Column A).
Essentially I would like to run some sort of If function:
If the date and the service correspond to the criteria then they are shown in the labels.
VBALabeling.xlsm
View 3 Replies
View Related
Mar 1, 2009
On to what brought me here; I have a spreadsheet with a column containing an address, eg '12 SMITH ST SOMESUBURB NSW 2001'. I'm looking at splitting this cell in half after the street and moving the second half (suburb state postcode) into the next cell, which i've managed to do with a series on InStr statements.
Now the fun part is that not all these addresses are streets, some are avenues (AV or AVE), roads (RD), place (PL), drive (DR or DRV) etc. Initially i just did this:
View 7 Replies
View Related
Feb 9, 2014
I am trying to retrieve data from another file using the VLOOKUP function however this is only to happen depending on if any of the 3 items of data appear in column 8(H)
OLY
OLY - QUO
OLY - PRO
I have the following and know this is not correct
Sub BlockAllocationsVlookupAll()
Dim x As Long For x = 1 To 65536
If InStr(1, Sheet1.Range("$H$" & x), "OLY") > 0 Then Sheet1.Range("$I$" & x) = Sheet1.Range("$I$" & x) & "sometext" End If Next
End Sub
I know the above doesn't do exactly what needs to be edited to include the Vlookup below
=VLOOKUP(A21,'[001 - Allocations - Blocks.xls]CurrentDayAll'!$1:$65536,9,FALSE)
The other issue is that the cell the VLOOKUP points to first will also change due to the varying length of the report
View 2 Replies
View Related
Mar 20, 2014
I have six complex functions, which I would like to place in [A2] depending on what is placed in [A1], The functions themselves are quite extensive and I don't want to use any active functions (like INDIRECT) as this will cause slow calculations. [A1] will just be a given name to each of these functions.
View 3 Replies
View Related
Jun 7, 2014
I am looking for a formula which will insert a word into cell A3 depending on the number in cell A2. For example A2 is between:
0 - 10 then insert the word Div 1
11 -17 then insert the word Div 2
18 - 23 then insert the word Div 3
24 28 then insert the word Div 4
View 4 Replies
View Related
Dec 27, 2012
Example below of the data I have. I need to convert dates into DD.MM.YY (8 chars) for SAP. I receive spreadsheets where the date is dd/mm/yy and I use the replace to convert the "/" to ".", then format this column as TEXT. My problem is some of these sheets have hundreds of records and rather than individually scroll through and look for data that requires the "0", is there a way to insert this. I can custom format that it looks as it has the zero, but it is not in the true data.
1.10.12 data should be 01.10.12
11.10.12 this is correct
3.11.12 data should be 03.11.12
3.06.12 data should be 03.06.12 etc
View 5 Replies
View Related
Mar 2, 2010
i have a sheet which is being used as a payment advise with subsidies.
depending on the product selected, it is either a $value multiplied by the qty or a % of the total value.
what i am trying to do as an example:
if cell F46 = percent(or sheet2 b6) it needs to multiply by cell H32
BUT if cell F46 = $ (or sheet2 B7:B11) it needs to multiply by cell B46
(sheet 2 has named ranges to be used in a list box)
View 9 Replies
View Related
Jul 10, 2014
I have an issue with this for a couple of days actually..
I have to find in a certain date "column C" if User "column D" changes from the initial user's number to another one to gave me the first half sales of the day and then obtain the other sales of the other part, this must reestart again when the date changes.
I have to know what was the sales on the two parts of the day and then in column Q assign "Manana", S1 Cell or R1 Cell for second part of day
The User "column D", can be diferent each day.
I attach the excel data to get a better explanation on this.
Mov may-jul FARMAX.xlsx
View 7 Replies
View Related
Mar 28, 2007
I'm trying to automatically shade a number of cells dependant on the value in a single cell. ie. If D2="quote" then I want A2:Q2 to be yellow. If D2="Design" then I want A2:Q2 to be green etc etc. I have about 6 options in all of what D2 could be.
I then want to repeat this for about 200 rows.
View 7 Replies
View Related
Jul 18, 2013
My formula works fine, but I need the first number of the answer displayed with only two decimal places. The formula averages a set of numbers, then adds " of " and a cell reference.
I want the answer displayed as 2.43 of 3. Here is the formula.
=IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123))&" of "& Z9,"") it works perfectly but displays 2.42857142857143 of 3.
The cell is formatted to NUMBER with two decimal places. . Changing the format has no effect, but changing the formula does.
If I use =IF(AVERAGE(W12:W123)>0,(AVERAGE(W12:W123)) or =AVERAGE(W12:W123) the result is 2.43. When I add the next portion, &" of "& Z9,"") it changes to 2.42857142857143.
I've tried reformatting cells and nothing changes. This is the only cell I'm having problems with. The worksheet seems to be working fine.
View 10 Replies
View Related
Jan 27, 2014
On the attached spreadsheet, is there a way of highlighting a cell because it is a greater number than two other cells?
Can cell C3 be highlighted green because the number is greater than G3 and K3?
Can cell E3 be highlighted green because the number is greater than A3 and I3?
Can cell K4 be highlighted green because the number is greater than C4 and G4?
View 8 Replies
View Related
Jan 6, 2009
how I would do the following, as I have been having a play but not managed to get anything.
I have two cells, C10 and C11, they both contain numbers. I want to create a hyper link that goes to the sheet called: Semester C11 Year C10
e.g. if the cell values are 2 and 1 respectively, I want the link to go to the sheet called 'Semester 1 Year 2'
View 9 Replies
View Related
Jun 24, 2009
I have some workbooks that have some serials in A row and i need to seperate them depending on how many digits they have,
For example some have 13 digits,and some 8.I just want to seperate them into different rows.
Here is a sample
seperate sample.xls
View 5 Replies
View Related
Apr 15, 2009
I am trying to nest a formula with IF & AND to work out a RAG status. I have 2 questions per row (in Columns I:J) which will be answered either Yes or No. For each combination I need to give a RAG status in Column K – e.g. two Yes replies give Green, 2 No replies give no and one No out of 2 will give Amber
Below is hoped for response in Column K:
ColI---Col--- JCol K
Q1----Q2----RAG
Yes---Yes---G
Yes---No----A
No----Yes---A
No----No----R
View 3 Replies
View Related
Jul 27, 2012
I have a table with several criterias (Name of supplier, date, amount paid to this supplier). This table is my inputs. Each time I have an expense, I put down the name of the supplier and the amount of the expense and the date when it occurs.
I have another table with name of each supplier in rows and the date in column. Sometimes I have 2 amount for a same supplier for the same month. I would like this amounts sum in my table. I cant find a good formula to do it efficiently.
Obviously I can do a sum
(if(A2=B2, if(c6=c8);b10)+sum(if(A2=B2, if(c6=c8);b10)+ etc...
But I can 150 expenses per month...
View 2 Replies
View Related
Nov 10, 2008
when I execute this code on the selected cells, it completely ignores my selection, and makes the entire worksheet black.
Sub ColorCells()
Dim cell As Range
For Each cell In Selection
If cell.Value = "U" Then
cells.Interior.Color = 3
View 9 Replies
View Related
Jan 4, 2010
I need a macro that will "examine" the text of column B for every row checking to see if the text "Proper Text" is in the cell and if not, execute this code for that cell and then move on to perfrom the same until it finds an empty cell:
Selection.Insert Shift:=xlToRight
Range("B2085").Select
ActiveCell.FormulaR1C1 = "Click for detail image"
With ActiveCell.Characters(Start:=1, Length:=22).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
Note that "B2085" above is the cell number that the script stops on to execute this code.
View 9 Replies
View Related
May 11, 2006
I am trying to make a macro to take a set of data and shift the data range down depending on the value in the A:1 cell. For example if the data in column A starts with 5, 6, 7, 8 and so on.... I would like to copy/ paste and shift the column down 5 cells so I can start A:1 at 0.....so that the data can begin as 0,1,2,3,4,5.....etc.
View 3 Replies
View Related
Aug 19, 2006
Have a spreadsheet shows 4000 warehouse locations (location of products). Each cell is a location in the warehouse (ba050, ca809, etc, actual warehouse locations, not grid ref); some products have more than one location, the locations are always next to each other. I import a text file into the workbook which tells me which product has more than one location (2, 10, 50, etc), that’s all I’m interested in ( multiple locations).
What I’ve done so far is to use vlookup to look at locations in file and allocated the number (the number of locations that product has) next (next column) to the cell that correspond to that location. What I need is a macro or formula that will look at the number and than highlight that many cells up or down the column, according to the number in the cell to the right (5 in cell, highlight 5 cells, 50 highlights 50). The warehouse runs up one aisle and then down the next and so on. So I need to be able to highlight cells up one column and then down the next and then up, then down and so on.
Multiple locations change on a daily bases, so need a quick way of updating 4000 locations.
New to excel don’t know if this is possible or not. Learning as I go.
Need some assistance.
Sample below.
Only interested if a number is returned, “******” can be ignored as will hide all columns expect ones with locations. So:
BA020 returned value of 4, so would like to highlight (any colour) BA020, BA029, BA040, BA049.
BB780 returned value of 2, so would like to highlight BB780, BB789.
POSSIBLE?
BA090 2 BA100 **** BB749 **** BB739 ****
BA079 **** BA089 **** BB760 **** BB750 ****
BA070 **** BA080 **** BB769 **** BB759 ****
BA059 **** BA069 **** BB780 2 BB770 ****
BA050 **** BA060 4 BB789 **** BB779 ****
BA039 **** BA049 **** BB800 **** BB790 ****
BA030 4 BA040 **** BB809 **** BB799 ****
BA019 **** BA029 **** BB820 **** BB810 ****
BA010 **** BA020 4 BB829 **** BB819 ****
View 9 Replies
View Related
Dec 17, 2006
I'm trying to create a sickness record book for colleagues at work, so we can monitor their absense accoridingley. I am trying to create a spreadsheet that will do the following:
Look at the dates that are over 12 months and not count those in the total occurances countCheck the dates that someones works and compare this to the absense date and automatically calculate the total time off
Are these points possible or am I wishful thinking? Just I have 60 people in a team and its hard doing them all in one go, but would be good if there is a way of it automatically doing this.
View 4 Replies
View Related
Jan 13, 2014
I have a spread sheet I have set up to calculate sales results, I have a column for each week in the quarter with the date at the top and a different page for each individual. I want to be able to enter a number to show how many weeks we have had and the spreadsheet to give me a cumulitive amount for the cells up to that date.
For example if I was to enter 1 in a cell A1 I would want the sum t work out just C3 for example.
If I enter 3 in A1 I would want the sum to be C3+C4+C5 wich is the three cells.
If I enter 6 in A1 it would be C3+C4+C5+C6+C7+C8 etc.
View 2 Replies
View Related
Aug 10, 2009
The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in service orders tab. BUT.....If two salesman pair up on up on a job then the formula doesn't recognize the joint name. eg Scott/Ash in row 21 & 22 (Service orders). I need the totals to half the job and add it to the salesmans total accordingly.
View 4 Replies
View Related
May 30, 2014
I am trying to auto size cells depending on the lenght of the data in them. I have added some code in the work book on the attached file, this works on cell that i maunally add data to any where on the active page (template) but the cells that are auto updated with currencey data dont expand?
If you double click column H a list appears select a test and this auto populates the charges accociated with it
Is there a way to do this as it just fills with #### ???
The code in this workbook is below
[code]
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim actCol, curCol
[Code]....
View 2 Replies
View Related
Jan 7, 2009
I have a list of names in column B and either a 1 or 0 in column A as below:
0 A B
1 1 Bob
2 0 Chan
3 0 Lucy
4 1 Billy
On another worksheet I want to be able to list only the names with a 1 in column A. This must be done in another worksheet so I have something like below.
0 A
1 Bob
2 Billy
3
4
View 3 Replies
View Related
Jan 28, 2013
I have two cells, A1 = Yes/No and B1 = Date. I want to restrict someone inputting a date into B1 until A1 is marked as Yes...? I've tried data validation, but seem to be hitting a wall with it...
View 2 Replies
View Related
Dec 22, 2008
is there a way to auto hide a row depending on the data in two cells. Basically if two specific cells are empty or return a zero or specific number i want the row to auto hide.
View 5 Replies
View Related
Feb 25, 2009
I would like to distribute values in cells depending on the input value (excel file attached). This file is a test and basically i've been entering the data manualy. The format is flexible, so it can be reorganisaed.
View 2 Replies
View Related
Sep 22, 2009
I'd like to lock some cells depending on what is put in a particular cell. As shown by the file attached, if the first column (Amendments) has a "no" in A4, I'd like to lock out cells B4:D4. (The sheet will be protected). If it's a "yes" then the cells are unlocked. I'd like to then apply this to all subsequent rows (i.e. lock B5:D5 if a "no" in A5 etc.)
View 5 Replies
View Related
Oct 22, 2009
How to a change a cell colour to say red in B6 if cell b12 = 1 and if e6 = 1 to change to green. I thought I might be able to use conditional formatiing but no. I had set the spread sheet to do a cell just for a condition representing a sum from another worksheet and it was working fine but i have to incorporate the two together and am stock.
I have attached a spread sheet.
View 14 Replies
View Related
