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
How do I generate repeat numbers when compared to previous row?
E.g.
Column No A2, B1, C1, D1, E1
Row no... A1 5 12 23 50 3
Row no... A2 10 21 5 25 6
Repeat number for above is 5
Given:
1) 5 cells only. All numbers are integers, no fractions
2) In a row same number is not repeated.
3) It is possible to repeat all numbers in a new run.
want to match column A to column C and if they match take whats in column D and put it in column B.
Column C has multiple instances of Column A and I would like each instance's, which is the following column, value put into column b
I want a macro to hid rows based on a specific value of a cell compared to another.
Example, In a cell I have the current date and time which updates when the the sheet is opened... On a row I have various info including another date in the past. When the date in the past goes over 6 months older than the current date, I want to hide that whole row.
Is this possible? Do I use an IF function in the macro I've seen to remove rows based on cells with specific values?
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
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'm trying to make this piece of code work to no avail yet... Every time I run it, the compiler pops up with Error #9: "Subscript out of range". The range consists of hundreds of cells in 1 column with geographic coordinates formatted as "General". Like this: myArr(38.91472, 37.20318, 38.63824, etc.). Tried to re-format the cells to "Number" - doesn't work either.
Code:
Sub searchArray()
Dim myArr() As Variant
Dim i As Integer
myArr = ThisWorkbook.Worksheets("Sheet1").Range("G1:G8594").Value
For i = LBound(myArr) To UBound(myArr)
Debug.Print myArr(i)
Next i
End Sub
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
I have a worksheet with a row of mixed cells near the top. By "mixed", I mean some cells have only numeric data (numeric-cells) and some have only words (word-cells).
GOAL: I want to sort the columns to the following order:
First all the columns with only numeric-cells in the mixed row, with those columns sorted in DESCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order.
So far all I have been able to accomplish is:
First all the columns with only numeric-cells in the mixed row, with those columns sorted in ASCENDING numeric order of the numeric-cells in the mixed row; followed by the columns with only word-cells in the mixed row sorted by ASCENDING alphabetic order. This was accomplished by a simple sort using ASCENDING order on the mixed row.
OR
First all the columns with only word-cells in the mixed row, with those columns sorted in DESCENDING alphabetic order of the word-cells in the mixed row; followed by the columns with only numeric-cells in the mixed row sorted by DESCENDING numeric order. This was accomplished by a simple sort using DESCENDING order on the mixed row.
This may sound as confusing to you as it does to me, so I have attached an example file showing hypothetical Input Data and the Desired Result.
I did the example file in multiple steps, but that's too painful in the general case where calculations determine what is in which cell in the mixed row. All data are calculated, even the word-cell data AND so dependent on other data, and the columns in the mixed row that are word-cells and those that are numeric cells don't remain the same.
Eventually I'd like to do this with a macro, so if VBA has some power to do this that EXCEL commands don't, then that's fine.
I could probably live with the final result as numeric-cell columns sorted in DESCENDING order followed by word-cell columns sorted in DESCENDING order, but I haven't even been able to accomplish that AND it's not quite the goal.
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe
I 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 RelatedFormula to highlight a cell in a column when compared to each value in another value. If I have the columns
A B
1 1
1 2
2 3
3 5
4 7
7 8
I want the values in column A to be highlighted if the excist in column B, both of the number 1:s.
The workbook which is attached with this thread works fine in my project but the problem is for double ids for example in my case OCD6415 is double in sheet 1. This macro just compared the two worksheet and when the comparison is found then copy the respected cells from worksheet 1 to worksheet 3 but i want that if the ids are double or more even then it must copy all the compared (even identical) ids to the worksheet 3. The code is following
VB:
Sub test2()
Dim a, i As Long, w(), e, s$
Application.ScreenUpdating = 0
[Code]....
I'm working with binned data. That is, something like this:
Value | Count
0.01 | 128
0.02 | 278
0.03 | 350
...
I found a way to get percentiles from binned data using an array formula here:
Calculating percentiles from bin/frequency data
I am wondering if there is a quick way to get the percentrank of a specified value when compared against my binned dataset.
i'm trying to simulate badminton match with MS Excel..
i just want to make this project simple..
|..A..|..vs..|..B..| Player A vs Player B
Two players compete in a match.The possibility is 50% for both players to win.However,to make it real,each player have their own strength and ability.For this project,i just indicate 3 which is smash power,net power and stamina.[/size]
Ability:Smash
Net
Stamina
Players with more ability have more advantage.How can i relate it with the final score?
Scoring system:2 or 3 set in a match (3rd set is when both tied at 1-1)
A match is the best of three games.
Played with 21 points
If the score reaches 20-all, then the game continues until one side gains a two point lead (such as 24-22), up to a maximum of 30 points (30-29 is a winning score)
In MS Excel:
...|Player A|...vs...|Player B|... <--Player A vs Player B
............|SIMULATE!|............ <--When I click Simulate!,scores appear
| 1st Set | 2nd Set | 3rd Set | <--Here where the scores appear
|............| Smash |............| <--Comparison of ability(can be edited)
|............| Net |............|
|............| Stamina |............|
I am using VBA to create a word document (.docx). This word document contains plain text content controls as well as picture content controls. I then use VBA to automatically select a picture based on the code below
Code:
Set oCC = Word.ActiveDocument.SelectContentControlsByTitle("TabPic").Item(1)
On Error GoTo TabErrorHandler
oCC.Range.InlineShapes.AddPicture Filename:="X:XFERANDREW-TDCD " & LblVL &
[Code].....
After the document has been closed down I try to open it again and I am told "The file cannot be opened because there are problems with the contents."
When I click details it says "Unspecified error" and "Location: Part: /word/document.xml, Line: 2, Column: 0"
If I click ok it says "Word found unreadable content in "". Do you want to recover the contents of this document? If you turst the source of this document, click Yes.
Clicking Yes opens the document with all the contents and it is now renamed to Document 1. If I click no it does not open.
I'm trying to capture if any changes are made to a UserForms selections.
I initially load all the data from the database when the first user form is initialized, see code below
[Code]....
Once I have selected the corresponing record from the combobox , in this case Planning Number, then select the command button which runs the code below
[Code] .....
Now the user form frmProcessEngineeringTemp is opened, it now populates all the comboboxes, textboxes and option buttons in this form based on the planning number choosen from initial user form
[Code] ....
Once the user has finished with the user form the information from the form is passed to a worksheet via a command button. If changes to the user form selections were made i need to capture what has changed (if anything), from what was initially loaded into the second user form (frmProcessEngineeringTemp).
I have seen numerous posts from various sites that discuss Change and Exit events from a Class module and many instances where each combobox, textbox and option button is hard coded to capture changes.
Being that I have captured my initial inputs from the database thru the myVar 1 thru 40, can this be put into a string array and the current inputs/selections in the frmProcessEngineeringTemp put into a second array then compare 1 to another looking for difference, then output the differences to a worksheet for tracking purposes. At the same time if changes were indeed made i would increment the Revison record by 1.
I have a spreadsheet and every row has an incident date. What I would like to do is calculate which relative month it is compared with today's date. I know how to show if it is in the current month, but what I want is something like the following:
Today's date is 9 July 13
Row 1 date = 9 June 13, hence calculation = -1
Row 2 date = 17 May 13, hence calculation = -2
Row 3 date = 29 Jan 13, hence calculation = -6
etc
Some sort of month to month comparison resulting in an integer.
how to combine the content of 2 cells into one cell and have the information separated by a comma.
For example:
CELL 1:
Software 1
CELL 2:
Spreadsheet Software
The desired results is:
CELL 3:
Software 1, Spreadsheet Software
I have a set of data I need to count.
The data consists of 2 columns essentially, Scheduled Delivery Date and Actual Delivery Date.
If the Delivery was made on time or early, I want to count it. I will do a similar count for Late as well.
Additionally, the user can enter the Scheduled date range they want to count early/on time deliveries from. There all also blanks in the Actual Delivery date column where deliveries have not been made (no actual).
Cells O3:O38 contain the Actual dates
Cells N3:38 contain the Scheduled dates
Cell B3 contains the begin date to start the range.
Cell B4 contains the end date of the range.
I have tried using COUNTIFS and am having trouble getting the formula to work.
I tried:
=COUNTIFS(O3:O38,O3:38<=N3:38,N3:N38 ,>=$B$3,N3;N38<=$B$4)
I know this will not work but I hope it shows the logic I want.Count range O3:O38 where values in O3:O38 are less than or equal to the corresponding cell value in N3:N38, for a range of N3:N38 that is greater than or equal to the date in B3 and less than or equal to the date in B4.
I am trying to compare two colums. They both contain numbers mixed with letters. I am wanting to match only the numbers in both not the letters. Example:
column a = m454 column b = fsh454-1
m543 fst998-2
m998 fsm434-1
my match is m454 and fsh454-1, m998 and fst998-2. The items can be in any order in the column. The end result I want to indicate the match by putting an X by column a item that matches column b.
Assume I have a sheet consisting of 50 rows with 3 columns of figures. A fourth column contains a formula to calculate values, eg A1+B1+C1, on each line/row. I could enter A1+B1+C1 in the first cell and Copy, Paste to the other 49 cells. Alternatively, I replace A1+B1+C1 with a UDF.
Is there any difference in performance with using a UDF compared to using the long-hand/standard approach, or is it just a question of convenience? After all, both are doing the same operation with the same calculation.
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 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 Replies View RelatedI'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