I am attempting to multiply the value of strings/integers for an output in a function macro. I know it's a simple question, but a simple answer would be suffice. I'm in the middle of a massive brain fart...
View 6 RepliesI need to declare a two dimensional array that will return the Row and the cell's string value. My only solution is to return the row as a string like this.
Public GlobalArray(2, 100) As String
How to get more control than this? Maybe I should just declare it as a variant but this might open it up for problems later.
I have a row where C5 is the total of C1:C4. What I am trying to do is if C1 or C2 or C3 or C4 has a number value that I can multiply the cell in question by 2 and then add up all of the cells to give a total in C5. Also, I have a name in A1, but when I select E1 and enter =A1 into the cell then return, I get A1 in the cell instead of the name in A1.
View 5 Replies View RelatedI cant seem to multiply two groups of cells at the same time. Its a hard one to explain so this is what I have tried to use and it doesn't work
=sum(a1:a5*5),(b1:b5*10)
so I tried...
=sum((a1:a5*5)(b1:b5*10)) and that didn't work either
what I want to do is add a1-a5 then * 5 and add it to the total of b1-b5*10.
I am working with the attached sheet and it is made up of groups of rows that alternate with a 6 digit NAICS code (industry code) then an aggregation of those codes into a more general 3 digit NAICS. I need to have the row with the 3 digit NAICS code calculate a number that multiplies a column called "% of industry" by several columns of of numbers and then sums the result into a cell in that 3 digit NAICS row. Different groupings have different numbers of rows. I would like to know if there is a 1 cell formula that can achieve this and be easily copied down the sheet to the other 3 digit NAICS rows.
You can see in row 37 this formula being executed for a simple 2 row aggregation. But that would take forever to replicate down the sheet, especially for the larger aggregations.
An example for row 32 that achieves the desired result and could be easily copied down the sheet.
3 digit Table.xlsx
Excel (2000), what formula would I use to accomplish the following?
If A1 equal or less than 1000 value, multiply by B1, otherwise multiply by C1.
I am using this formula to output data into a cell:
=MID(A1,4,2)
The cell value is 50. I am wanting it to appear as 5.0 instead. Can I do this by adding to the formula above or will I need to utilize another cell to accomplish this?
I am having trouble using the MATCH function in my code. I am looking for a match in one worksheet from a cell in another. I have defined these cells and ranges as variables. However, it doesn't seem to be working with my application. I would like to find the row of the matching cell and use the output of the MATCH function to determine the next step in my code with an IF function. Here is my code. (NOTE: this is my edited code, I removed irrelevant pieces. The major problem is the MATCH function.)
View 4 Replies View RelatedI 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?
Excel vlookup function returns #VALUE when the lookup value exceeds 256
characters. I need a hash function to transform large strings into a value
that does not exceed the 256 character limit.
i've tried searching for "Mode Function in Text" , "Mode Function by Text" but to no avail. Can someone please guide me in how to do a Mode by Text? =MODE(number1,number2,...) But if i have a whole chunk of data in Strings in cells Range A1:C50
1) How can i get the mode of that Range ?
2) is it possible to get the statistic of those for e.g.
Apple 20
Pear 30
Watermelon 50
etc......
What I would like to do is output the first 4 characters of a cell into another cell. eg Cell A1 has "1234 Sydney". If I just use the manual formula "=left(A1,4)" it would give me the correct output "1234" in say cell B2, but as a formula. As I need to automate this in a macro, how could you code this in VBA to output the String "1234" into cell B2(ie the actual output rather than a formula)?
View 2 Replies View RelatedI am having some trouble with the "mid()" function. I have it saved as a variable which pulls 6 digits from another variable. I was trying to paste from the mid variable into my excel worksheet but it keeps on telling me that there is an object required error.
The goal is to input the "mid()" output in my code into a cell in the spreadsheet.
Here is some sample
is there a formula that would have as it's output the cell address of the
same cell where the formula has been entered.
e.g. In cell A10 I input a formula whose output is the cell reference A10.
I've in cell A1 an entry like this: 123*456*7890
=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890
=ISTEXT(A2) in cell 'A3' gives me: TRUE
BUT, =A3+1 gives me: 1234567891
Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?
I would like to know if it is possible for the output from a match function to be correlated to a location? The output I receive is just a number. I would like to use this number as the row, but I would like to hyperlink it into a formula so that I can create a macro for this process.
In essence I would like for a number in one cell to become a location in another cell.
I want to pass an array to a function, and am unclear how to do it. For example, the following declaration does not work:
Public Function xyz(a() as Variant) as Double
There is an additional requirement that I would like the output also to be a range of the same dimensions, typically one column and 100 rows. Is this possible?
A further twist is that the function I am writing calculates two values for every element in the input array and I need the output of them both.
An inelegant solution would be to write two separate functions, but I was wondering if it is possible to get two array outputs from a single function.
I have numbers in cell A1 (say 3545852) as a sum of a data range. I want to display it like this "Rs. 35,45,852.00 Cr."
How can i do this. I used text function like this
="Rs. " & text(A1,"0,00.00") & " Cr."
But it displays "Rs. 3545852.00 Cr."
I want thousand separator also.
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
I have been using the CONCATENATE function to merge 2 strings together into a single cell. However the problem is, the third concatenated cell depends on the other 2 existing. The idea is I want to take 2 columns of data, and use a formula to merge them, then delete the original 2 data columns so I have a single column with the full data merged.
COL 1 (A3) = First Name
COL 2 (B3) = Last Name
COL 3 (C3) = Full Name (wanted)
So I do =CONCATENATE(A3,B3) and that puts the fullname in C3, but I want to sort of "flatten" C3 so that I have the fullname by itself and I can delete the original cells (A, and B)
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).
I have been trying to use the concatenate function to join some text strings to a cell on a different sheet - From an input sheet -Sheet 1 named Input, to a Notification Form (Sheet 2). Although the Function Argument display tells me that it will display the result I want it actually displays just the formula. It's a very simple thing
Address 1 10 Downing Street
Address 2 Westminster
Address 3 London
Postcode SW1A 1AA
I want displayed as: 10 Downing Street, Westminster, London, SW1A 1AA in a single merged cell. All I am getting on the Form is =concatenate(Input!c25," ",Input!c26," "Input!c27," "Input!c28). I feel sure that it is a very easy solution but I can't arrive at it!!
I have a list of data that I'm using the index - match function to output data based on user input.
For example, I have:
1 10
2 15
3 20
So when '1' is selected another cell outputs '10'.
However what I want is for example if 1.25 is input, I want it to interpolate to spit out the correct number between 10 and 15.
I've recently been making a macro in visual basic that loops through all my excel files and replaces an old company name to a new company name and It's working great, well except for one thing... It always misses one string in the file and it leaves me with 90% of the file corrected. I think that the string might be a part of a merged range so Range.Find is not able to find it. I was suggested to use this code:
[Code] ......
Because that's the code that is generated when using the replace function in MS Excel 2010, but I keep getting syntax errors?
I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
I have data from (row 1, column 1) to (row 53, column 5) on 283 consecutive worksheets in a singular excel file that I would like to be presented on a singular worksheet starting from the data on worksheet 1 and descending to the data on worksheet 283.
I am looking for a copy and paste loop solution that will copy the data from each page and sequentially paste the results on a singular output page in descending order (worksheet 1 data, worksheet 2 data... etc) so that I can sort the data.
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
I'm writing quite a large electronic logging program using excel and vba and have hit a snag in one of my subs which reads from a text init file. Let's take a look at the code.
Private Sub ReadInit()
'Opens txt init file, looks for target list, splits line by commas and enters into array
Dim FileNumber As Integer
Dim TargetsRead As Boolean, LastFileRead As Boolean
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = ObjFSO.OpenTextFile(SaveDir & Init)
TargetsRead = False
LastFileRead = False
Do Until objFile.AtEndOfStream 'do until end of file
Attached is my sample data Sample Data_Combine Sheets.xlsm.
What the code in the attached sheet does is that it combines the data in Sheets Set1 and Set2 into the worksheet named "Combine".
Both input sheets share same columns. Code I use is as below:
VB:
Sub BuildPivotData()
Dim wrsht As Variant
Dim i As Integer
Sheets("Combine").Range("A2:AI500").Clear
wrsht = [{"Set1", "Set2"}]
For i = 1 To UBound(wrsht)
Sheets(wrsht(i)).[a2].CurrentRegion.Offset(1).Copy Sheets("Combine").Range("A65536").End(xlUp)(2)
Next i
End Sub
I want to amend the output in such a way that when the macro is run , in the "Combine" worksheet an additional column is added at the start(col A) with header as "Category".
The category should be nothing but the input Sheet name i.e Set1 or Set2.
Here is an example ... Output Sheet.xlsx.
I have created (via recording) a very simple macro in Microsoft Word to convert a fraction of the form a/b to the form where a is directly over b with a horizontal line between them (like how you would actually write it in math class). Before I send it to my co-workers, I want to beef it up a little.
Right now, it will only work if a and b are integers or if a and b are words or phrases with no spaces (i.e. change/time will convert correctly but the phrase change in x/change in y will not). Also, it would be great if it could work with parenthesis (i.e. (2x-1)/(2x+1)).
Just so you know, I am very comfortable working with code in order to come up with a beefier macro. However, Word uses Visual Basic and I'm afraid I don't know much about the syntax of that code. So, if any of you know of a site I could view to learn a little Visual Basic or if you know a site that already has good code already written for this purpose, I would be much obliged.