I have an origin and destination matrix with one set of zones and need to convert it to another set of zones. This involves combining some zones together. I tried a sum if, but it didnt work. I have done the calcs individually but will have to repeat the process so was looking for a wuicker, more efficient and less error risk!
Just to clarify, I have to convert a 23 by 23 matrix into a 14 by 14.
I have a workbook that is used by several employees in all different time zones.
The workbook contains several sheets, but has a "Main Menu" sheet that i would like to list the current times in each zone. Is it possible to do this
I am posting the worksheet so that it may be a little clearer. I have 2 worksheets, the input area and the model area.
In the input area, users will input their data with a maximum of 5 beverage items and 10 food items along with a certain number of other data inputs.
In the model area, i would like to seperate the food items from the beverage items along with their corresponding other information as you can see in the spreadsheet i posted.
However, food items can vary in number up to 10 as well as beverage up to 5. Also, users may enter the beverage and food items in any order they wish to.
Could you help me figure out a way to transfer my data from the input area to the model area and sorting the beverage and food items so that beverage and foods go into their specific zones? i will be performing calculations which are different from food and beverages.
I have to go through the tedious process of pulling out my areacode/time zone sheet and going through each of the phone numbers and assigning it the proper time zone in the zone column. Usually Est, Cen, Mtn, or Pac
I would like to create a function that will automatically assign the time zone to the area code for me and everytime I buy new leads I can run this same function which will save me so many hours of tedious work. I think I would need to save it as a macro after I'm done creating it if I'm not mstaken
I keep the 3 digit area codes in their own column, column "B" and I keep the Zones in column "D"
I am trying to create a list where the user will select which "Lanes" to assign to "Zones" -- for example... Zone 1 will be Lanes 1 through 10. Zone 2 will be Lanes 11 through 25. Etc, etc... I want to make it very easy for a user to change which lanes are assigned to which zone. I thought about using data valuation but I need Zone 2 to automatically bump up the *start* lane if you change the end of Zone 1.
Example: Zone 1 is set at Lanes 1-10, but I want to extend it to Lane 12. How can I make it so that Zone 2 automatically starts at Lane 13 after I set Zone 1? I would like to use drop-down menus for this because it seems like that would be the most user-friendly.
I call prospects to generate business. I want to respect their time by calling before 8:00 PM so I need to know their time zone. I have a column in Excel that lists their area code, but I don't know their state.
I have tried everything imaginable to duplicate the results of the previous post to no avail. I'm using Excel 2011 on a Mac.
I'm using =VLOOKUP(B2,$I$2:$J$364,10) for my formula, where
B2 is the area code $I$2:$J$364 is the range where the area code/time zone data lives (I = area codes, J = Time Zone) 10 is column J, i.e. the Time Zone column that "feeds" the answer.
I've attached a screen shot to show what I'm doing.Screenshot at Feb 05 15-33-01.jpg
I am looking for a function to convert time given by my computer (Local time) in EST (Eastern Standard Time). We are several users of a same file (with timestamp macros) and all time need to be aligned to one time zone (EST), even if all users are working in different time zone (EST, CST and IST).
View 1 Replies View RelatedI have a table that I add rows to via a macro. I am trying to add correlation matrices that find the last row of the table, and include a certain number of rows above that to create a rolling correlation. For example, if I want the 2 week correlation, I would like the last 10 rows of data, but if I add another row, this formula should move down to incorporate the new row and deselect the now 11th day preceding. I do this manually but it is very time consuming.
View 9 Replies View Related1) I have declared:
Mtrx(20,20) as Integer, where i have a Matrix
1 2 3
1 2 3
How can I do an inverse of this matrix using only VBA commands?
2) I have :
Mtrx(20,20) as Integer, for example :
3 1
2 0
and Mtrx1(20,20) as Integer :
1 2 0
0 1 1
How to multiply these matrices using only VBA ?
I have two ranges: A1:F1 and A5:A10, 6 elements in each range. When I multiply one by the other I have to enter a formular in some cell (let's say, A14 - we will put the result there): "=MMULT(A1:F1, A5:A10)" and then press Ctrl-Shift-Enter. Very simple.
Question is this: suppose I do not know in advance that I will have 6 elements in each vector, it might be more than 6 or less than six. I will introduce an integer "n", which is the number of elements. Now I have to write a Sub that will perform multiplication. I wrote smth that does not work and I was wondering if someone has ever done this already and can give me a guidance what I am doing wrong. I HAVE ONE CONSTRAINT: I HAVE TO TYPE IN THE FORMULA INTO THE SPREADSHEET VIA VBA (as this will be used further in Solver, which requires formulas to be present in cells).
Y
Sub test()
Dim n
n = 2
Range("A14").Formula = "=MMULT(A1: & Chr(64+n-1)& 1, A5:A & 5+n-1)"
End Sub
I thought it would be equivalent to MMULT(A1:B1, A5:A6).
Problem solving multiplication of two matrices with entries decimal in vba code:
Code:
Option Explicit
Public MatrixA(30, 30) As Long
Public MatrixB(30, 30) As Long
Public MatrixC(30, 30) As Long
Public RowA As Integer
Public ColA As Integer
Public RowB As Integer
Public ColB As Integer
Function ProperMaticesSizes() As Boolean
[Code] .........
I have a matrix with default probabilities. I have this matrix in my worksheet. The user will give the number of periods to be calculated with and it will result in a new matrix with default periods over time of choice.
Private Sub UserForm_Click()
Dim numberof_text_Change As Single
Dim initial_text_Change As Single
Range("U36:AC44").Select
Selection.FormulaArray = "=matrixpower(R[-10]C:R[-2]C[8],numberof_text.value)"
End Sub
matrixpower is a function that works perfectly well. It takes one matrix as an argument and the number of periods to use. It then simply computes the chosen number of powers of my matrix. I have a userform that has an inputbox called numberof_text which specifies the number of periods that the user wishes to use in calculating the chance of default. Hence I would like to use this variable as an argument in the formulaarray. This doesn't seem to work! I only get "#VALUE!" in every cell. If I replace "numberof_text.value" in the matrixpower function with a number it works fine. What to do?
What the function needs to do is take an argument which is the matrix I have on my spreadsheet. Take the number of times to take powers of the matrix and print it on a pre-specified place on the worksheet. If I cannot use variables with FormulaArrays, then is there another good way of choosing the matrix to use with "powermatrix"?
I am working with excel sheets for dimension population of metrics.
I want to make some process less manual. In one column I am given some values like ((1110 + 1230 + 3120 + SERVSELCT) - (1110.21 + 2110.30))
Based on these values I have to write the expanded form like:
The values that do not include the decimals are known as parents, for which I have to write PCYDYNNOTRANS_1110 and for the values that include a decimal I have to write CCYDYNNOTRANS_1110.21
I have to write this in the adjacent column for all the values I had mentioned above.
I was wondering if there is a way that I can make this less tedious, and I can have a list that would pop up in the adjacent column where I could select which one I should use i.e. either PCYDYNNOTRANS_ or CCYDYNNOTRANS_ and I would have to just copy paste the values.
I'm looking for a formula or code that would take a client's name that appears in one cell, with the last name first then a comma and then the first name, and paste it into another cell with the first name first and then the last name with no comma.
Example: cell A2 contains "Smith, Joseph", cell B2 should have "Joseph Smith". If I need to provide additional information,
I have a spreadsheet list in the following format (Note Tax ID would be cell
A1, etc):
A B C
Tax ID Numbers Street
11-03-3040-0178-01-794HIGMAN PARK
11-03-2310-0001-00-3100HELMAR
11-03-3040-0169-00-0102HIGMAN PARK
11-03-3040-0165-00-4104HIGMAN PARK
11-03-0020-0039-01-6104S CRYSTAL
11-03-0021-0006-01-9105S EUCLID
11-03-0024-0004-08-8105PAUL
11-03-0018-0029-00-1105HIGMAN PARK
11-03-1880-0069-00-4107S CRYSTAL
This continues downward to cells 6577. I'm looking for a way to carry these three columns over at some point so I can put more data on a single page, thus minimizing the # of pages I will need to print, but yet remain in the proper sequence as established in column B.
=IF(AND(B3:ZZ3="Jan",B10:ZZ10>=500000,B13:ZZ13="POST"),"1","0")
And I'm pulling from a spreadsheet that looks like this:
Jan[B3], Jan[C3], Jan[D3]
500,001[B4], 632,000[C4], 992,000[D4]
PRE[B5], POST[C5], POST[D5]
(and the function is calculating how many januarys, greater than 500,000 that are also post).. how do i get it to ADD the amount of fields that meet that crieteria instead of giving me a 1? I want it to say "2" (adding together all that meet those needs)
I have a column filled with names per row. I wanted to standardize it by using the following format.
The comma after the last name is always an indicator to me that it is the last name.
<First Name> <Middle Initial(if any)> <Last Name>
My actual records contains the following:
BIMBO EPPING - OK
AHMAD SADICK - OK
Salana, Laura R - need to be arranged to > Laura R Salana
Cantos, Rhoda Grace J - need to be arranged to > Rhoda Grace J Cantos
I have two inputboxes. I want to set the default value of the second inputbox as (effectively) one cell down from the first inputbox entry. In other words, if I type G17 into the first inputbox, I want the second inputbox to then have a default value of G18.
View 5 Replies View RelatedI am unsuccessfully trying to manipulate and copy a formula.
Column A would typically be used as a benchmark for one retailer and have the base price with the other columns simply showing differences in margin etc if the price was changed. This would typically mean the BWP and fixed terms etc would be constant.
However in this circumstance I need to ascertain for different retailers a,b,c,d, etc the shelf price and compare their NTS and % Margin. Whilst I can enter the different prices and change the terms as required, the whole sheet seems to link back to column A rather than simply drawing from the info in each respective column.
a
b
c
Price On Shelf
20.12
20.05
18.87
Shelf Price Excl. GST
17.50
17.43
16.41
[code]....
I have a userform that accepts some information and checks to see if the values have already been used (address and work order number) If the value is used the user must change the value ,an input box is triggered to get the new value. My question is how do I transfer the value back to the userform??
View 9 Replies View RelatedI have 3 pivot tables I'm using a macro to get a value out of, by changing the 2 "Page" fields. However, after the macro's done running, the page fields are messed up. (The order changes, one of the options becomes "").
View 9 Replies View RelatedI have a filename in the format XXX-YYY-ZZZ.xls, where all the XXX and YYY and ZZZ are of variable length. Sometimes it is of the format UUU-XXX-YYY-ZZZZ.xls
I wish to delete all the characters to the left of the rightmost "-" character, including that "-" character.
Is it possible to modify a link so that you can change the link easily by the value of a certain cell.
='[Labour Plan Week 24.xls]Hours'!$E$135 and I want something like ='[Labour Plan Week " &H5& " .xls]Hours'!$E$135. Where H5 contains the week number
Theres a progam created using Firebird as described here: {url}
I want to be able to enter data into the firebird application from an Office application (e.g. Excel) using VBA.
I want to also be able to extract data from the application using VBA.
I am working on a macro that opens up a workbook, formats the data by moving columns etc and then spits out the data to different workbooks according to a string in col R (M,C,S etc). I am able to split the file into the workbooks, but it copies the whole sheet and not just the visible cells (its working via a filter). I have tried adding code into the loop to basically copy the visible cells to Sheet2 and then delete (or clear) sheet1 so that I only have the relevant data but have very interesting. This is only the first of a few procedures I would like to add within the loop. Ideally I want to have a prompt for the filename and path for each file, and also another procedure to add a number into each seperate file. If I know why I cant get this simple function to run I will, hopefully, be able to add in the other bits!
Function SplitSheets()
On Error Resume Next
Dim ws As Worksheet, source As Range, dest As Range
Dim daily As Worksheet
Dim sType(4) As String
Dim i As Integer
Dim strSaveFileas As String
sType(1) = "M"
sType(2) = "S"
sType(3) = "C"
sType(4) = "N"
Set daily = Worksheets(1)
For i = 1 To 4...................
I'm putting together a workbook to keep track of income generated from Contractors (I work in recruitment). I have an existing system that displays margin, hours worked and income (margin * hours) but what I am looking for is a neater solution that would still have the margin column, but when hours are entered into a cell it is factored by the margin and displays income in the same cell.
So to be sure I am describing correctly, I am after a formula that will enable me to
1. Enter a value into a cell (for the hours worked - lets call this B1)
2. Multiplies this value by another cell (a fixed amount, the margin - lets call this A1)
3. Displays this result (B1 * A1) in cell B1, the same cell as the hours were entered in.
I am trying to wrap up a project. The project consists of taking the input values from a userform and entering them into various Word documents and Excel workbooks. The procedure is run from Word. I have no issue completing this part.
However, there are a few Word documents that have an Excel worksheet embedded into them. How can I manipulate these worksheets using VBA? I have attached an example for your reference.
I have found out how to get into edit mode, but I still do not know how to actually edit the worksheet.
i need to pull data from one spread sheet and place it in a new spread sheet. and i am not that familiar with macros i am learning but i need to get this done.
View 13 Replies View RelatedA B C D
--------------------
Mike Fax 5 60
John Tel 3 30
Mike Tel 2 30
Mike Fax 1 60
John Fax 1 60
--------------------
hi all, i am faced with the following problem. before i describe the problem, i do not need help with recording a macro or using functions to solve the above problem, i know how to do that.
writing a code from scratch to do the following:
I need a code that will run through the above columns, and IF A=Mike and B=Fax, THEN C*D...so I want to calculate a total for everytime Mike sells a Fax (or a Tel). Column C is quantity, Column D is price.
I am sure this is a simple loop code, but I have spent days trying to figure it out and I can not do it. Recording a macro is useless, because it is too confusing to look at the code and play with it and try to personalize it.
I have a excel sheet where I have a list of strings seperated by a "-". I am looking to take the particular cell value and swap the characters before the dash with the characters after the dash. So for example:
FG03-FJ04 would become FJ04-FG03
AB02-CD12 would become CD12-AB02 and so forth....
I have tried findling around with the split function but cant seem to get anywhere, does anyone know a tried and tested method of doing this?