Translate Yes Or No To Digit And Summarize In YTD Sheet
Apr 23, 2014
I have excel workwook with Month sheet Jan,Feb,Mar....Dec and YTD
In each monthly sheet i will have either "yes"or "No" in cell B66:K75.
Below is what i need to do in YTD sheet cell B66:K75. Each cell in TYD B66:K75 will have 12 digit number
Need to go thru each monthly sheet for each of the above cell and find if Yes or No and translate that into number (yes = 1, no = 0)
Example:
If B66 in Jan, Feb and Mar is Yes and B66 in Apr thru Dec is No then the number in YTD in cell B66=111000000000
View 5 Replies
ADVERTISEMENT
May 25, 2008
I have the following code always throwing this error message: "subscript out of range". When i run the macro with worksheets that only have 10 rows each, it's running fine. I suspect some issues on the line: a = Range("a1"). CurrentRegion.Resize(, 3) - the debug always throws the error before entering the loop. This macro is supposed to find the lowest price among 3 spreadsheets by matching the "Code" column and sending the final result into a new spreadsheet. But since im trying to run the macro on spreadsheets holding over 6000 lines each, this error is appearing.
Sub kTest_v2()
Dim a, w(), i As Long, z, dic As Object
Dim MySheets, ws As Worksheet, MinPrice
MySheets = Array("Sheet1", "Sheet2", "Sheet3") 'change to suit
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
For Each ws In Sheets(MySheets)
With ws
.Activate
'assume Col A Country, Col B Code and Col C Price
a = Range("a1").CurrentRegion.Resize(, 3)
End With
For i = 2 To UBound(a, 1) 'assume column heading. if not replace 2 with 1
If Not IsEmpty(a(i, 1)) Then
If Not dic.exists(a(i, 2)) Then
Redim w(1 To 4): w(1) = a(i, 1) 'Country
w(2) = a(i, 2) 'Code
w(3) = a(i, 3) 'Price
w(4) = Cells(i, 1).Address(external:=True) 'cell address
dic.Add a(i, 2), w
Else.............................
View 7 Replies
View Related
Jul 1, 2012
i attached link to sample and its contains employee sheets and Report sheet ,and in Report sheet there is comment
[URL]
View 7 Replies
View Related
Apr 3, 2008
I know you can take a number from one cell and combine it with number from another cell and make it one number. What I need to do is the reverse. Take a two digit number in a cell and separate it into single digits in two cells. If you have the number 50 in a cell, then is there a formula that will take the 5 and put it in cell and take the 0 and put it in the cell beside it?
View 4 Replies
View Related
Jun 10, 2013
I have a built a catalogue of records for various of my companies assets. Each asset has its own sheet in a work book and I have used a vb macro to include an automatic Index sheet at the front of the book. This takes the names of the various tabs, in the order I have them in the workbook and creates a simple listing in column A as an Index List
What I want to do is to build a summary table to the right of the Index List, pulling in a few of the key data points about the assets into a single table.
To do that I need to be able to extract the text string from the Index List and use it to form part of a cell reference pointing to a specific cell in the relevant individual Asset sheets.
View 4 Replies
View Related
Jun 13, 2014
How to calculate a check digit in excel. The details to calculate this are as follows:
All variables in the calculation are positive integers.
We take each integer of the pro number and multiply it by a value and sum them to get a total.
An Example: 8 Digit Pro Number: 66988757
Pro Number: 6 6 9 8 8 7 5 7
Position in the Pro Number: 1 2 3 4 5 6 7 8
Multiply each digit in the pro number by (10-position) in the number, to achieve a sum.
Using our example pro again:
6*(10-1) + 6*(10-2) + 9*(10-3) + 8*(10-4) + 8*(10-5) + 7*(10-6) + 5*(10-7) + 7*(10-8)
6*9 + 6*8 + 9*7 + 8*6 + 8*5 + 7*4 + 5*3 + 7*2
54+ 48+ 63+48+40+28+15+14 = 310
Take the Sum of the previous calculation and divide it by 11
310/11 = 28
(Actually, it's 28.181818, but since we're working with integers, we truncate everything behind the decimal).
Figure a remainder by multiplying the quotient by 11 and subtracting from the sum.
Remainder = 310 - (28*11) ---> Remainder = 310 - 308 ----> Remainder = 2
Check Digit = 11- Remainder
Check Digit = 11 - 2 ---> Check Digit = 9
Note: if the check digit is 10 or 11, need to subtract 10 from the Check Digit.
View 3 Replies
View Related
Feb 12, 2014
Index of the VBProj.VBComponents("ThisWorkbook") component?
I need to refer to that component but not by its name because the file I use, goes to some users that have MS Excel installed with another language and their Excel translate the component's name in that language so
VBProj.VBComponents("ThisWorkbook") should be VBProj.VBComponents("[that-language-translation]")
View 3 Replies
View Related
Aug 11, 2008
I have been sent a spreadsheet containing the following Macro, which is fair enough, only thing is, I don't understand the Macro, its too high for my extremely basic knowledge of VBA.
Private Sub CommandButton1_Click()
Dim rng As Range
Dim i As Integer
Dim sht1 As New Worksheet
Dim sht2 As New Worksheet
Set sht1 = Sheets(ActiveSheet.Range("D2").Value)
Set sht2 = Sheets(ActiveSheet.Range("D3").Value)
Set rng = sht1.Range("First_Line")
i = 0
Do Until sht1.Cells(rng.Row + i, 1) = ""
sht2.Range("Managers_Name") = sht1.Cells(rng.Row + i, 1)
sht2.PrintOut
i = i + 1
Loop
End Sub
translate this for me, step by step, as to what this Code is doing?
View 2 Replies
View Related
Feb 29, 2012
I have a spreadsheet with thousands of rows of text that I need converted from one language to several other languages.
Is there a macro I can use that taps into some free service, running down my page and giving a foreign language conversion for each cell?
[URL]
View 14 Replies
View Related
Dec 14, 2006
How would I go about putting the following formula into VBA code? The .008 would be going in field O8. =IF(AND(M8>5,M8<10,N8>5,N8<10), 0.008)
View 5 Replies
View Related
Feb 9, 2007
It looks like the above has been inserted into my current workbook (by mistake). How do I delete it all (lots of the options have the remove option disabled)
View 3 Replies
View Related
May 8, 2007
I am trying to find a way to make a certain number equal a certain name. For example 1111 will equal Name1 in another cell and 1112 will equal Name2....
View 5 Replies
View Related
Apr 9, 2009
I have 3 columns of data, column C has a number representing a length of time in months. I need to add a number to the end of each row (Column D), depending on the number in column C. Example:
If column C has a number between 0 and 3, the number to go in D would be 1. If column C has a number between 4 and 6, the number to go in D would be 2. Here are the list of possiblities (only 5):
0-3 = 1
4-6 = 2
7-9 = 3
10-12 = 4
12+ = 5
This will form part of a macro, so a VBA solution would be ideal. I thought of having a named range and looking up each row with a list of all possiblities. But i see that as rather crude. I'm hoping someone has a more logical solution they could share.
View 2 Replies
View Related
Nov 23, 2009
I am trying to create a formula that can translate true calendar dates when a date is entered IE 2/15/09 if with the normal formaula, it alwways calulates 30 days or even on 31 day months.
I need th formula to calculate actual dates and know when there is a 31 versus a 30 day month or 28/29 for February.
View 9 Replies
View Related
Mar 20, 2008
I have several hundred points in cartesian x,y,z coordinates. I used an Excel add-in program to calculate the equation of best fit to the points. The equation is:
z=A*x+B*y+C
where (for this case):
A= 2.29091
B= -1.77338
C= -192.494
My objective is to define the several hundred x,y,z points in cartesian coordinates about a new cartesian coordinate system whose origin is the centroid of the data points and whose z plane (ie z=0) is equal to the best fit plane defined above (when referenced from the original coordinate system).
Using Pro/e (a cad program), I was able to create the new coordinate system using the x, y, and z intercepts from the best fit plane, and then was able to redefine the points about the new coordinate system. After the points were redefined in Pro/e, I was able to export the points back to Excel; however, I would prefer to only use Excel / VBA because I prefer the calculations be transparent for optimal accuracy and validation.
View 9 Replies
View Related
Dec 9, 2009
I've developed a spreadsheet in Excel 2000 where one of the cells contains the number of months a quantity will last, calculated by taking the total amount and dividing it by the amount used each month (a constant).
I would like to have another cell where that number is translated into the future date that number of months represents, so that in addition to seeing how many months the total amount will last, one can see when the amount will be depleted.
For example, suppose it is December 9, 2009 (as it is now) and the number in the number-of-months cell is 6. Then, is there a way that when 6 is calculated, in another cell appears something like June 9 2010 or 6/9/2010 or 6/9/10 (assuming the computer's clock is correct)?
It would be nice if the formula would handle decimals too, such as the date for 6.4 months, but that's not essential.
View 9 Replies
View Related
Mar 25, 2013
Lets suppose that in a drop down box I have the words : "I" "want" "to" "create" "a" " sentence".
Well what I would like to do is have each word as selected in the drop down box, placed in another cell creating a sentence.
If you picked the words in order the cell should read: I want to create a sentence or it could read any combination of these words depending in the order they were picked. So it could read: I sentence want to create a sentence if the words were selected in this order.
View 14 Replies
View Related
Mar 17, 2013
How do I translate this to a vba code?
=INDEX('Workers List'!D3:D13,MATCH('Workers List'!C4,'Workers List'!C3:C13,0),1)
what i have done is
test = Application.Index(Sheets("Workers List").Range("C3;C13"), Application.Match(1, Sheets("Workers List").Range("D3:D13"), 0), 1)
But i keep getting an error, may i know whats wrong with it?
View 5 Replies
View Related
Dec 21, 2008
I have some data for international dialed numbers, I need to translate this into a country or destination name.
for example if the dialled number begins with 001, the is the united states etc..
I have all of the required codes, but they all differ in length.
ie 0033 is spain but 003356 is a spain mobile.
I was thinking that i could use VLOOKUP to find the relevant text to display, but this does not work with the varying lengths of the codes.
View 9 Replies
View Related
Mar 2, 2009
I have a column of 6 digit numbers in excel, and I need to remove the last digit from each number, turning it into a 5 digit number. No rounding, just simply remove the last digit. Each number is different. Does anyone know how to quickly and efficiently remove the last digit from each number? I can convert to alphanumeric string if need be...
View 4 Replies
View Related
Jul 3, 2014
I need a macro where I can highlight a column and change all of the 2 digit years to a 4 digit year (actually, some of the 2 digit years are only 1 digit, e.g. "9" instead of "09").
I have an if then statement that I can use in the column after it, but I'd rather change the actual numbers in the original column, rather than adding another column (and having to keep the original, too).
The statement I had was:
=if(A1>=50,1900+A1,2000+A1)
I just copy dragged down to get the cell numbers for the rest of the column... but using A1 was just for an example, here, it's not necessarily going to be in that column. It needs to be just whichever cells I select.
It seems like it should be pretty simple, but I don't know how to word it in a macro.
Starting Column Example:
12
13
14
99
11
[Code] ......
For some reason I can't get rid of the borders...
View 6 Replies
View Related
Apr 19, 2014
In Excel 2013, how do I translate all characters of selected cells to Unicode?
There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.
View 1 Replies
View Related
Apr 19, 2014
In Excel 2013, how do I translate all characters of selected cells to Unicode? There is a formula to translate the first character into Unicode [=UNICODE(text)] and that formula would have worked fine for me .Only if it could translate all the characters to Unicode not just the first one.
View 10 Replies
View Related
Apr 25, 2014
I have an address (city, state, zip format) in a cell. Some of the zip codes only have a 4-digit zip code which is the way the file was received. I know these zip codes have leading zero. How can I convert these to a five-digit code. Examples are as follows:
Waltham MA 2453
convert to 'Waltham MA 02453'
Boston MA 2210
convert to 'Boston MA 02210'
CEDAR GROVE, NJ 7009-1174
convert to 'CEDAR GROVE, NJ 07009-1174'
TEANECK CITY, NJ 7666
convert to 'TEANECK CITY, NJ 07666'
View 3 Replies
View Related
Apr 15, 2008
I have 2 digit years (98, 99, 00, 01) that I need to convert to 4 digit years (1998, 1999, 2000, 2001). There is one year per cell. If it was simply a matter of adding 19 or 20 to the beginning of each, I could do that. But since there's a combination of both 19 and 20 that needs to be added and there all intermingled, I'm not sure how to do it.
Can a rule be written to add 19 to the beginning except if the current cell starts with a 0, then add 20? The highest year is 2008 (no 2010 to deal with).
Example:
98 --> 1998
99 --> 1999
00 --> 2000
01 --> 2001
View 9 Replies
View Related
Dec 15, 2013
build a formula that can lookup and summarize.
I attached file >>>Lookup summary.xlsx
View 4 Replies
View Related
Jul 5, 2007
Date ModelStationLot_SizeSampleReject
04/7/07APCA771
04/7/07CPCA64320
04/7/07BPCA36320
04/7/07BPCA441
04/7/07CPCA110
04/7/07AB/B880
How can I quick summariz these data by VBA ? as below result.
Date ModelStationLot_SizeSampleReject
04/7/07APCA771
04/7/07AB/B880
04/7/07CPCA65330
04/7/07BPCA40361
This is just my sample data. The real data is more complicate than this. I want someone suggest me about VBA code.
View 9 Replies
View Related
Feb 3, 2014
How to translate a specific number into a specific letter related to the number itself.
I have lots of different numbers for example all going down a specific cell (These numbers I want to separate into 3 different catagories. The positive numbers I want to be calculated as the same Letter that is next to that row directly over to the left of that cell.
So if the letter
B / cell / cell / +9 / Then I want if its a positive number like +9 to answer in the formula with the same letter (B) listed to the right in the same row in a different cell of course
B / cell / cell / -6 / Then I want if its any negative number like -6 to answer in the formula as the opposite Letter of (P) listed to the right in the same row in a different cell of course
P / cell / cell / +17 / Then I want if its a positive number like +17 to answer in the formula as the same letter (P) listed to the right in the same row in a different cell of course
P / cell / cell / -3 / Then I want if its a negative number like -3 to answer in the formula as the opposite Letter of (B) listed to the right in the same row in a different cell of course
and thirdly if its a (0) zero and only (zero's) Then I want the answer to stay (0) zero.
The number can be any numbers it doesn't matter. Only distinction is that positive number of +1 and up repeat and stay the same letter that the cell on the far left is already stated.
and all the negative number of -1 amd below all reverse the letter to the opposite letter of (B to a P) or (P to a B) listed to the right in the same row in a different cell of course
and of course (0) zero would be listed as a positive number and only return a (0) zero as the result of the formula.
View 9 Replies
View Related
Feb 4, 2009
I have a spreadsheet containing Date, Description and Value in a three column setup.
I wish to generate one summary for each unique description, based on a table of types. I can then say that for instance "Apple", "Oranges", "Lemon" are all part of a "Fruit" group.
The lookup would then find every row containing words in this group and summarize the values for them all.
View 7 Replies
View Related
Aug 25, 2009
I think I need a modified SUMIF formula but I can't figure it. The attached spreadsheet is a sample of what I need. I'm using Excel 2003.
View 3 Replies
View Related
