i have spent hours on with just no idea how to do it.
Write a function named MyOddSum(R) that returns the sum of values in the odd columns in the range R. Use a double ‘For … Next’ loop structure to do the summation. (Hint: Use R.Rows.Count and R.Columns.Count to set the index counters for the double ‘For … Next’ loops. Also, use ‘Step’ keyword to specify odd columns.)
I am attempting to create some code that has 2 loops in it.
THE SITUATION:
I will have 2 ranges (eg B54:B100, and B106:B149 - the problem is I will arrive at these ranges by code such as: Range(Selection, Selection.End(xlDown).End(xlDown)).Select)).
WHAT I THEN REQUIRE
I need to have some code that will look through each cell in the second range and if the cell contents is found in the first range then the entire row from the second range will be deleted.
I am trying to create a VBA code that will search through a column"A" and if it finds a calue that C1 then color it, when it finishes searching columnA then loop again and search for items that match C2
Here's what I have so far with no success
Sub ColorItems()
Dim i As Integer
Dim j As Integer
Range("C1").Select
For i = 1 To Selection.CurrentRegion.Rows.Count
For j = 1 To ActiveCell.Offset(0, -2).CurrentRegion.Rows.Count
If ActiveCell.Offset(0, -1) = ActiveCell.Value Then
With ActiveCell.Offset(0, -2).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Say, I have two columns each having unknown number of rows. what would be the vbscript code to get the summation of a column's values
For example,
Two existing columns are A1 and B1. In order to create C1.
Column C = 10/summation(a1)/(b1)
How to do the above operation?
i have this code
Dim UltFila As Long, i As Integer, k As Integer
UltFila = Range("A65536").End(xlUp).Row
k = 2
For i = UltFila To 2 Step -1
If Cells(i, 2) = 4102 Then
Range(Cells(i, 3), Cells(i + 15, 6)).Copy Sheets("Final").Cells(k, 1)
End If
If Cells(i, 2) = 4104 Then
Range(Cells(i, 3), Cells(i + 15, 6)).Copy Sheets("Final").Cells(k, 1)
End If ..................
I´working on an excel sheet where i´m copying a range to another place. So far so good. Then I want to remove all double entries for each rows in the new range. I managed to get a code working for one row. When I want to loop it for all the rows in the copied range, I get a an error popoing up when the loop starts working on the second row. Error 457: "This is already associated with an element of this collection" The line creating this error is coll.Add cell.Value, CStr(cell.Value)
Sub Sortere_StederBeta()
Dim coll As New Collection
Dim lcount As Long
Dim cell As Range
On Error Resume Next
Range("B4:U33").Copy
Range("B36").Select
Selection.PasteSpecial Paste:=xlPasteValues
For i = 36 To 65 Step 1
Range(Cells(i, 2), Cells(i, 22)).Select
Set coll = Nothing........................
The problem seems to come from the fact that the Coll (New Collection) is not reseting for the next loop. I tried to set the Coll to Nothing but doesn´t have any effect.
I have a array as
01-12-2008 pump-1 25
01-12-2008 pump-2 28
02-12-2008 pump-1 24
03-12-2008 pump-1 26
03-12-2008 pump-2 38
03-12-2008 pump-3 35
I need sum value as
03-12-2008 pump-1 75(=26+24+25)
02-12-2008 pump-2 if no answer blank
03-12-2008 pump-2 66(=38+28)
I have a single column of data and need to convert it into 2 columns, by alternating each row:
1
2
3
4
5
want to convert to:
1
2
3
4
5
6
I've seen a previous post from 2007 where someone asked for a formula to do the reverse of this. 'Domenic' provided a formula =INDEX($A$2:$B$6,INT((ROWS(D$2:D2)-1)/2)+1,MOD(ROWS(D$2:D2)-1,2)+1) which converted
a
1
b
2
c
3
d
4
[code]....
how to reverse this formula?
Is there any possibility to protect alternate cells of column see below for example. I want to lock cells B1, B6, B11 and B16 only so that no one is able to delete the average rate.
A B
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
average rate0.0
1
1
1
1
I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1 - 31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.
View 3 Replies View RelatedHere's another question for you excel junkies to solve, while I try to understand what y'all are doing. I have two worksheets named "MASTER" and "BEDS". What I need to do, in sheet "MASTER" is go down column ("K:K"). "K:K" has unique data in it. then for each row...
"X" .value = offset minus one
"Y".value = no offset
now I need to go to sheet "BEDS" and find "Y" in column "A:A" then
from "Y" offset + 3.value = "TRUE"
"Y" offset +4.value = "X"
there should only be one instance of "Y" in column "A:A"..............
I have a price list from a friend. The price list has to have every other row shaded. This part is no problem with the conditional format command.
The real trick, at least to me, is the background shade of the row is based on the first two letters in the left most column.
For instance:
Column A
CPST4
CPST5
FLADS3EC
FLADS2EC
FIL4
CP rows would alternate with one color while FL rows would alternate with a different color and FI rows would alternate with another different color. All other rows would have no shading.
MacOffice Excel 08 is used so no VB code can be used.
When I add a table in Excel, I can choose from Table Styles, different styles that set one color for even rows and another color for odd rows.
Is there a way to say to this Table Style that assign one color for rows that have the same string in column A in consecutive rows and another color when the string in colum A changes?
I mean,
If A1= XYZ, A2=XYZ assign blue to row 1 and 2.
If A3:A9=FTG assign green to rows 3 to 9.
If A10:A13=LLKF assign blue again to rows 10 to 13.
If A14:A22=WUR882 assign green again to rows 14 to 22.
and so on.
the formula i used is " =IF(A2=A3;"YES";"NO")
the contents are as follows
A
Numbers
2525 - YES
2525 - NO
2526 - YES
2526 - NO
2528 - NO
2530 - NO
2527 - YES
2527 - YES
2527 - NO
how can i use a formula... that tells the last double entry yes too instead no?
for example above...
2525 - YES
2525 - NO (i want the formula say this "YES" too..)
So i want to find out through filtering.. all the double entries with YES.. so i can work with it without missing any double entries.
I want to be able to double click on a cell in a column. If it has a certain word in it, it will take it to that worksheet. Auto filters are used so these cells can move in a column at any time. This is why I am looking for a code to validate the contents in the cell first.
ie. Cell has the word: BSALT, when double clicked it will take it to the BSALT worksheet.
Cell has the word MLW, when double clicked it will take it to the MLW worksheet.
And so on.
These values are in one column only. If they weren't able to be autofiltered i could do it, but the mere fact that they can move around is giving me trouble.
I want to hide and unhide column AI by double clicking on a certain cell, let's say S25. Can this be done?
View 8 Replies View RelatedI have two spreadsheets, one with master file with original data and one that needs to pull in the original data. My issue is all the data to match off of is in the same column, and a number of other files link to the master file, plus it is used externally, so I cannot alter it, and I would rather not create a mock/copy file. Is there a formula that can look for 2 different items within the same column? Would prefer not to use VBA, but if that's the only option I'll take it. I am using Excel 2003.
Here is an example of the setup - I would need the formula to reference off the two different items/categories in the column, so lookup off the 'St. Louis' and following that, lookup off the produce items.
I have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then
F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)
So, New Column (O) = N - F
and the next New Column (W) = V-N ...
NOTE: Column G can be ignored.
I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?
[Code].....
I'm using Excel 2013.
How can I summate cells in a column *except* for those rows which contain a specific value in another column?
=SUM(A1:A10 where B<row num> !="SOLD"). I'm trying to add up the weights of items which are not yet sold.
I have written an SQL Stored proceedure which drags this information into excel.
All is well here, however because the information changes regularly adding and subtracting in row length it means I am having issues writing code for sums as a standard macro is absolute and not realative.
What I basically have is this ....
I need to be able to search through category(column H) and match with machine size category. Then for each category split up total time spent on each function. Granted this will be 6 formulas. I've searched through the forums and came up with what I have....
View 10 Replies View RelatedI try to add some numbers, it will display ### symbol rather than that Value.If i give Summation for 10+10+10+10+10 the result displays ### than 50. How to resolve this?
View 15 Replies View Relatedhow to make a summation formula in excel (not simply adding 2 numbers together)? I have one cell that I would enter a number into, and another cell which would specify the limit. So if the first cell A1 = 1, and cell A2 = 50, then the 3rd cell would automatically add up every number "n" plus 1 until n=50. ie: (1+1)+(2+1)+(3+1)+...+(50+1)=result. The limit would have to be flexible, so if I change the 3rd cell to 70 then it would calculate to 70.
View 9 Replies View RelatedAttached is my sample data...Indicative Data_Revenue 2.xlsx
My aim is to populate columns C to G (Q1,Q2,Q3,Q4) automatically via vba against a particular account name(in expected output tab).The account names will have a Key word (listed in the "List Of Account" tab)The Q1 Sum should be a sum of Q1 revenue against all such accounts containing the Key word. Same applies for Q2,Q3 and Q4.Each account has 3 types. viz, BAU, Top Commits and Strong Prospect.
The BAU quarter sum should be pulled from "Revenue 1 - Assured" tab where Q1=Apr+May+Jun, Q2 = Jul+Aug Sep and so on. The Top Commits Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage 4. The Strong Prospect Sum should be pulled from "Revenue 2 - Expected" against all records where "Sales Stage" Column equals Stage
Currently I am using formulas but there is too much of hard-coding and its becoming difficult to manage as Accounts increase.
I have attached a screen shot below of a sheet I am using to collate fruit orders and calculate an order total for each individual person.
Untitled.png
The price of each fruit item runs along row 3 and then each persons order has its own row. The total price for each person is then calculated in column AM. However I am looking to shorten the formula I am using to calculate the total in this column.
I am currently using:
AM4=SUM(C3*C4+D3*D4+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
AM5=SUM(C3*C5+D3*D5+E3*E5+F3*F5+........+AJ3*AJ5+AK3*AK5+AL3*AL5)
As you can see this formula is far too long so I am looking to write it in compressed formula.
I have a workbook with 11 sheets. In 10 first sheets i é use function PRODUCT(B35;$H$2) to calculate the values in each cell.
Then i tried to get the summation of 10 sheets in 11th sheet for every cell as following but i keep getting zero as a result instead of the summation:
Sheet11!B5 =Sheet1!B5+Sheet2!B5+Sheet3!B5+Sheet4!B5+Sheet5!B5+Sheet6!B5+Sheet8!B5+Sheet7!B5+Sheet9!B5+Sheet10!B 5.
I've run into an issue for which I can find no elegant solution. I consider myself a competent Excel user, so it's pretty embarassing...
What I want to do is to sum the values along the 'axes' of the table. In order to fully explain that, I'll give a small example:
Let's say, this is my source data
I'm trying to generate random numbers that will add up to a certain number and am having trouble brainstorming how to do so. For example, I'm trying to get a+b+c=6 with a, b, and c randomly generated to add up to 6.
View 4 Replies View RelatedI was trying to assist someone with 3D referencing and summing, but getting stuck on referencing text based sheetnames.
We are trying to sum range A1:A10 in the sheets between the range defined by A1:A2
Now, if the Sheet names were numerically sequenced, eg. Sheet1, Sheet2, etc...then we could use this formula with no problem
=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT(A1&":"&A2))&"'!A1:A10"),""))
where A1 and A2 housed the numbers 1 and 2, respectively,
but what if the sheets are text based? Is there a way....say the Sheets were named SheetX, SheetY, SheetZ....
I did some searching on the internet and found samples only with the numeric based sheetnames.
Currently I am trying to do a gantt like chart time summation. I'll try to explain this as clear as possible... Right now I have a program that finds the name of a part in a giant list's column and then copies a few of the other columns (priority and hours to complete) from the part names row and pastes them into a new sheet. I then sort all the found data by their priority number. The job that is gets sorted to the top is the next job to be worked on for the machine. So here is what I need to be able to do...
Based on the following example I need to be able to sum for the object bolt on Machine #1 the tenative date that the "bolt" will be completed by. It will take a minimum of 35 hours for the bolt to get off Machine #1 based on its current priority. However, the part: "bolt" needs to go to Machine #2 before being completed. The part will have to wait a hour for "hammer" before being started on machine 2. (Its data for the time it will take complete "bolt" on machine 2 is also stored in the spreadsheet.) Since the bolts priority is higher than the "nail"... the "bolt" will be the next job to be completed. My question is this: Is there a way to tenatively sum the future hours that it will take to complete the bolt and allow it to be open to changes, in case a higher priority part comes into the system?
Machine #1
PRIORITY HOURSNAME123nut212bolt334washer422kazoo
MACHINE #2
PRIORITY
HOURSNAME122screw22hammer31nail