I have an array with an ubound of 10,000 because I will be loading a different amount of numbers each time I run the macro. Is there a way to tell the amount of digits filled inside the array if it is less than 10,000?
View 4 RepliesIs there a function, or how would I write a vba to figure out the following.
I have 86 items all with a different price, which come to a total of $348,359.33, is there a way to figure out which combination of the 86 entries will give me a total of $57,673.36
Is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?
DATA output should be
asd67,h876 --------> 67876
2,3,ujdj5&34 -------> 23534
909k86m34 --------> 9098634
Right now I am writing a macro with a defult action if an arry is empty. Unfortunatly the way I wanted to do it will not work becuse it can't recognize the difference between one entry in the array and no entries:
If LBound(array) = UBound(array) Then
I am creating my first class and it will be possible for the Class Initialize event to fire twice.
Is there any way to check if the Class is already "loaded" .
1. In column A i have a list from 1-12 (months in a year)
2. In column B i have the formula that references the corresponding cell in column A in the same row i.e. B1 "=(A1={1;3;5;7;8;10;12})"
Problem: This formula works only when the value in column A equals "1"...i need it to work when it equals any of the identified numbers in the set.
I'm having problems loading data into a listbox. When the form first opens, it loads the data twice. I can then kill the form and restart it and the data is in there three times. I can't see any loading problems with the userForm_initialize routine. How can I ensure that the listBox is empty before I start adding data? This would also seem to be necessary prior to loading a different set of data into the form - correct?
View 7 Replies View RelatedI call this code from within Workbook_Open.........
If these Refrences libraries are loaded then an error appear tells
Name conflict with object library........it's clear why......
Is there a way 2 check if the Refrences are loaded then exit the sub i.e. no need to excute this code , and if not loaded then excute the code ?
Sub GetRefLibr()
Dim ID As Object
On Error Resume Next
'*Reference ADO Object Library using Major / Minor GUID
Set ID = ThisWorkbook.VBProject.References
ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5
ID.AddFromGuid "{000204EF-0000-0000-C000-000000000046}", 2, 5
ID.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 1, 5
ID.AddFromGuid "{420B2830-E718-11CF-893D-00A0C9054228}", 1, 0
On Error Goto 0
Dim MyVar
MyVar = "Come see me in the Immediate pane."
Debug.Print MyVar
End Sub
I have an array of variants..
lvarArrSource = Array(12, "=F", 2, 3, 4, 5, 11, 6, 7, 8, 9, 10, "123", "F", "F", "F", "F", 11, "F", "NR", "F")
which i am using to map columns between spreadsheets. The basic numeric entries refer to columns to copy. But I want to make the routine smarter with the strings.
If array(x) = 12 (For example) Then
Do something
End If
If left(array(x),1) = "=" Then
Do something Else
End If
If array(x) Is String literal (e.g "xyz" Or "123") Then
Do a third thing
End If
i cant find a typeof or isstring kind of function. Isnumeric works ok for some values but quoted numbers (eg "123") return true (which isnt what i want). I have tried the left(string,1) = """ but excel seems to hide the quotes.
I have a row A through N. In cell A1, have a dollar amount. In cells C1 through N1, I have different dollar amounts. In cell O1, I want to have a formula that will take the dollar figure in A1 and subtract from that amount the total of all the amounts in cells C1 thorugh N1. But I don't know how to write that formula.
Let's say I've got $100 in A1, and then in C1 through N1, I have $2, $3, $4, etc. for a total amount of $94 in cells C1 through N1. I'd like to have a formula in O1 that would show $6.
How to write up that formula in cell O1.
Is there a way to automatically determine the final row number showing after a manual filter operation? Or maybe getting a list of the row numbers showing on the screen.
The project requires a macro that loops through only the rows of a filtered list, not all of the rows in the worksheet.
I need a cell to restrict the input:
-Only numbers are allowed.
-No date posible.
-Any amount of decimals (they must all be shown in the cell).
I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".
I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.
Is it possible to just show numbers greater than an amount in a pivot table?
View 3 Replies View RelatedI have list of dollar amounts given to me and a general ledger sum which some of those in the list must equal. How can I determine which of these amounts will equal a given ledger total?
View 12 Replies View Relatedthis formula and have tried various nested ifs and I can come up with a formula that works. What I am trying to do it to is detirmine what my multiplier should be based on a range of numbers. Let me try and explain lets say A1 is a number and B1 is a number and I need the Formula for C1.
If B1 is between 0 and 5 multiply A1 by 2 if B1 is between 5 and 10 multiply A1 by 4. and so on and so on. Is there an easy formula for this? I know it might be trivial but im stumped for some reason.
I'm trying to determine the average of a set of numbers, based on a scale of 1 through 10. There are two columns and it would look something like this:
1 3,452
2 2,990
3 564
4
etc.
I want to determine the STDEV here as well but assume I can just exchange the AVG function with that function, once I know how to tie column B (the numbers) to column A (the rank).
Is there anyway to use Excel to determine which of a set of numbers equals a total? I encounter this issue when dealing with accounts receivable. We receive payments with no details on what is being paid. I have a list of open items and a total being paid. I would like to have Excel determine which combination of invoices can be added together to match the payment.
View 4 Replies View RelatedI need to know if there’s a function (or method) in excel that’ll allow me to select an array of numbers and once those numbers are selected, to use only the numbers (for an averaging function) that yield the best sigma?
View 9 Replies View RelatedI need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
For the below macro.
ABCDG
CodeTotalBaseNetA1 = 20%
A110083.3316.67A2 = 10%
A2350318.1831.82A3 = 7%
A3220205.6114.39A0 = 20%
A015015030
A1 is the code, take the total(B) and calculate the base(C) on the percentage given in the G, net = B-C. for A2 and A3 it is the same process.
For A0 the total (B) is equal to Base(C) and calculate the net on the given percentage in G.
I have 1-d array that gets populated from a number of sheets.
This array may contain duplicate entries... for example,
let's say this array contains animals.. cows, chickens, horses, pigs... etc.
What I would like to do is take this info from this array and create another where it would contain all the different types of animals but without all the repeats.
I have account numbers in column A. I have 2 columns which contain account range.For example column/cell f1 contains 45555555 and column cell g1 contains 45666666. I have to check if the account in column A is in this range, and if yes ,I have to have "klo" in column B.
The problem is , that I have to check a lot of rows (and I do not mentioned but I have to check other attributes with the same structure as well) and the do- loop-if structure is rather slow. Could you tell me how can I solve this with say arrays-or with other method-which can boost my macro?
I have an array of data like below:
1
3
5
7
9
I need that array interpolated to say 20 data points but the first and 20th data point must be the actual first and last data point of the array (1 & 9 in the example). I have downloaded the xlfun software but the interpolat function only does 1 point at a time...than as you drag the function down it changes the initial array. Of course I realy have anywhere from 40-60 data points to be interpolated to 100.
How can I subtract a series of numbers from an array. example.
Code:
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
13
14
25
26
37
39
[Code] ......
=ABS(1-B2) C2,D2,E2,F2 AND G and after start again with =abs(2-B2:G2)
I want to know how to do this for example until 60, I mean 60 minus the row B:G
I have a 3x3 array with positive numbers and zeros.I'm trying to write a macro that copies only the positive numbers in a row.
View 9 Replies View RelatedLets say I need an array of 100 numbers from zero to 100. I want a mean of 75, and SD of 12. Or the same data based on a skewness and kurtosis value. Can I do that in Excel?
I'm just generating fake datasets for my stats class to analyze.
Edit: I found that =norminv(rand(),,) will work.
I have in column B, cells that have any number, one number or several like B2.
#
A
B
C
D
E
[Code]...
Thinking that I have a loop for rows 1 to 3:
When in column "A" is "No", I want to consider values in C and D to get:
Code:
a[1]=Cells(1,"C") & "-" & Cells(1,"D") & "-" & "ABC"
But when in column "A" is "Yes", I want to store in array each value within cell in B (in this example B2) to apply later a For/For Each
over each number (in this example are 3 values only within cell B2), something like:
Code:
a[1]=Value_In_B2[1] & "-" & "ABC"
a[2]=Value_In_B2[2] & "-" & "ABC"
a[3]=Value_In_B2[3] & "-" & "ABC"
I want to compare the content of the cell numbers of the cells with the numbers "C1 - D1 - E 1"
If there is no comparison in the cell is written the number in the cell
If there is no cell writes in "zero"
Input
result
5
7
9
0
0
532
532
Comparative numbers
146
0
111
0
217
217
854
854
848
0
648
0
I'm using the vlookup function to pull numbers off an array. Is there a way that I can get the vlookup function to pick the smallest number greater than or equal to my Look_up value. If not is there a function that can do that?
View 9 Replies View Related