I currently rotate through 3 values of XXX (i.e., 0, 1, and 2) like this:
For XXX = 0 To 3
What should I write if I want to rotate only through XXX values of 0, 2, and 3?
Should I write:
For XXX = (0, 2, 3)?
Not sure it'll work...
How can I write a For-Next Loop and have the counter skip values in between. For example:
For i = 8 to 17
When that is completed, the counter jumps to 21:
For i = 21 to 30
When that is completed, the counter jumps to 34:
For i = 34 to 43
I'm looking for vba code to use that will incrementally rotate an image based on values entered within a cell of an Excel spreadsheet.
I have two graphics, one is the face of a dial and the other is the pointer very much like a speedometer. I can achieve the desired outcome using two excel charts, one overlaid on top of the other, but, this doesn't give me the visual effect I'm looking for.
I'm using the face of the semicircle dial as the background image and I have a separate image for the pointer. When a value between 1 and 100 is entered in cell O3 i would like the pointer to rotate to that position on the dial. The pointer is to have a fixed position and arc left to right where the value of 1 is left-most and horizontally aligned, and for the value 100 the pointer would point in the opposite direction.
I want to rotate an arrow image to exactly a value entered in a cell (dropdown of values between 0 and 180)
e.g. if the value is 0 it should point south, if East or West 90+ or - and if pointing North 180. Also all value between these e.g. 45 for South East or South West etc.
I need some VBA I guess to do this but I don't know how.
When the arrow has stopped, I need the macro to end cleanly so that the user can input other values.
Is this even possible?
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 10 columns (A-J). Column B is the Date & Column C is the Time, I have the data sorted 1st by date then by time. The 5th Column, E, is Size. The 10th column, J, is "Position."
This 10th column, J, is in binary format. A 1 equals a Position & a 0 equals no Position.
1) I need to find the Max Sum of the Size in which the Max consecutive set of 1s has occurred.
2) I need to also do this on a day by day basis, using the Date in Column B, without having to manually go through & modify all the formulas to reference the date.
I have a Sumif formula for the 2nd but it won't copy down to update the formula for each unique date. I have to manually change the date values & I have ~957 unique dates to do; so it would be a rather painstaking process if only done manually. Moreover, it doesn't count the Max Sum of all consecutive 1s in Column J...
The length of the non unique columns (all columns w/o unselecting the duplicate entries) is 19,068.
If you can be of help, I'd be very thankful...my brain is overloaded w/googling & using trial & error.
I'm looking for a VBA code or formula that will count consecutive values in a column.
Please look at my example sheet.
I'm trying to use this formula to count consecutive values but somehow it doesn't work properly.
Does anyone see what's going wrong here or is there a better formula to this?
Basically I want to calculate each team's home winning/drawing/losing streak. Here is a sample of the table (I've simplified it a bit and added fictional results):
(See Attached)
So, I want a formula that can tell me a given team's (e.g. Arsenal's) latest result, and how many previous consecutive results of that kind there have been. For example, for Arsenal, the result would be something like "W2" (i.e. Won the last two games). At the moment, the best I can do is to get the TOTAL NUMBER of a given team's latest result using this formula:
=LOOKUP("ARS",B:B,C:C)&SUMPRODUCT(--(B1:B13="ARS")*(C1:C13=LOOKUP("ARS",B:B,C:C)))
(Which also returns "W2").
I have a range of binary numbers and would like to count the number of times the value of 1 appears BUT there must be at least 4 consecutive 1's.
Here's an example:
1
1
1
1
1
1
0
1
0
1
1
1
The final count should be 6.
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
I was trying to see the values only if the same value arrived more than three times in the given range.
Ex:
ABCDEFGHIJ
1AAAABAA
2
in the given range A1:J1, I would like to count 'A' only if it repeats more than or equal to 3 times. For row 1 I should get count as 4.
I have been trying to adapt a couple of the posts I've seen on this topic but they seem to refer to counting in rows and I'm having trouble translating that to columns.
I have a simple situation. I have 1s or 0s in columns from left to right. I only want to count the first occurrence of consecutive "1"s and only if the series starts with "1" in the left most column. (I am trying to calculate consecutive years of giving and the columns are FY14, FY13, FY12, etc. Only people who gave in FY14 would count.)
Example:
Row 2: 11101010
Row 3: 11001011
Row 4: 00100110
I would like Row 2 to count "3", Row 3 to count "2" and Row 4 to count "0".
I have a user form with 125 checkboxes and if checked would like them to display one message box containing a specific message for that the checkbox slection, but all messages in one message box.
I have one command button that will list each message box individually, using code like this -
If CheckBox12 = True And CheckBox5 = True Then MsgBox "You would need to see your manager"
Each message box individually is time consuming and it would be better if they all displayed in one box. Of the 125 check box options, 7 or 8 will only be selected at any given time.
The formula in column B counts the number of values that are
View 9 Replies View RelatedObjective: I am trying to find consecutive (2 or more) lap time that is above a certain value per lap. Once that is accomplished. I would like to know the summation of these values and the average. These values (summation and average) are not over the entire data, but for the consecutive periods only. Ideally I would like the for n number of laps and lap-time.
(i.e) if threshold is 85 seconds. Please view the attached image and sample spreadsheet.
Lap #
Lap Time
Threshold
2 or more laps
0
118.2
85
118.2
[Code] ........
The formula I used to get the consecutive data above
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above,
I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2. Also I would require the summations used.
I have a spreadsheet with employees performance results for the last 12 months. Column A contains name, columns B to M contain text "Gold", "silver" or "bronze". I need to count how many consecutive months thay have achieved "Gold" status BUT must include latest month.
So, if column M contains "silver" or "bronze", the consecutive answer (column N) would be 0. If col M = "Gold" but L doesn't then N = 1. If M and L = "Gold" then N = 2 etc etc.
Is there a formula I can use in column N for this without doing 12 "if" functions?
I think this needs to be an array formula but the ones I've tried I can't get to work.
I wanted to see if there was a way to hide rows that do not have a pre determined amount of values in a row (one after another). Attached is a simple spread sheet to hopefully illustrate what I need.
I'd like to be able to only show rows where 2 consecutive 0's exsist. Then 3 consecutive 0's. Ultimately I'd like to show anywhere from 2-12 consecutive 0's and have that selection available via the combo box.
I was wondering if it would be possible to filter by two consecutive cell values or colors. For example if I have in "A"
X1 (red fill)
X2 (blue fill)
X1 (red fill)
X3 (xx fill)
X3 (xx fill)
X1 (red fill)
X2 (blue fill)
X4 (aa fill)
I want to be able to filter so that the consecutive cells for x1 and x2 show up or red followed by blue.
So when I filter it will only show rows 1 & 2 and 6 & 7.
X1
X2
and there would be many of these values in the spreadsheet.
Objective: I am trying to find consecutive (2 or more) lap time that is above a certain value per lap. Once that is accomplished. I would like to know the summation of these values and the average. These values (summation and average) are not over the entire data, but for the consecutive periods only. Ideally I would like the for n number of laps and lap-time.
(i.e) if threshold is 85 seconds. Please view the attached image and sample spreadsheet.
Lap #Lap TimeThreshold2 or more laps
0118.285118.2
192.7492.74
284.82
387.587.5
486.586.5
582
681
784.2
887
981
The formula I used to get the consecutive data above
formula I used is =IF(AND($B2>$D$2,OR($B3>$D$2,$B1>$D$2)),$B2,""). This yielded the values on the last column.
My question is, how can I sum and find the average and summation of consecutive value till blank cell. Using the example above, I would require the average, ((118.2+92.74)/2) in one cell. I would require the next cell to give me the average, ((87.5+86.5)/2. Also I would require the summations used.
Say I have Column A populated with Employee Names, and Column B populated with "Pass" or "Fail" (which indicates a passed or failed customer service call monitoring).
For each employee, I want to be able to look at all of their overall scores (usually between 3 and 10 evaluations per month) and easily highlight people who have failed 2 out of 3 consecutive evaluations.
I want to integrate this feature in to a weekly quality report.
I am trying to select a range of identical values in a column. Basically I need to ask Excel to highlight any and every part of the worksheet where the value '0' (zero) occurs 30 or more consecutive times in any given column.
View 2 Replies View RelatedI have a range of say B1:Z2. In row B1:Z1 I have dates in the format dd-mmm-yy. In row B2:Z2 I indicate presence of a person by "P". This is at infrequent appearance i.e. p,p,p,blank,blank,blank,p,p,p,p,p,blank,blank,p,p,blank,p,p,p, etc. I am trying to find or workout a formula that would find the most repetitive Ps in row B2:Z2 and give me the sum thereof. I.e, from the above example it should be 5. It does not have to count specifically P. Something that counts the most non-blank sequence of cells in the range should also do.
View 8 Replies View RelatedI am looking for a better way to determine if a column of values contains a sequence of consecutive matches. For example, column A contains single words. To determine if three consecutive cells contain the word "neuron", I enter in cell B3 the formula:
=IF(AND(A3="neuron",A3=A2,A3=A1),1,"")
And copy down. I then test column B to see if there are any non-blank cells.
If we want to change the word or the number of consecutive occurrences, I have to re-edit all the formulas in column B. I want to eliminate the helper column. I would like to put the word in B1, the number of consecutive occurrences in B2 and have a formula in B3 that will return either True or False.
I can do this with a UDF, but the user has rejected this option.
1.jpg
I wan to calculate the average for three cells not in Sequence using AVERAGEIF with condition (VALUE >0)
Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.
There's 2 main events in this loop (I hope that's the correct terminology):
Input 1) User defines the beginning cell to start the loop. In this case, A2.
Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......
i am trying to draw a shape (square or rectangle) using vba textbox's i have managed to draw 2 of the 4 lines.
i am stuck on the ShapeRange.Flip msoFlipHorizontal codeing.
i have attached a example of what iam trying to do.
the coorinates from the last line enterd are saved to sheet1 for use with the next line entered ...
Need excel formula (not a marco) to rotate the names in the roster.
View 2 Replies View Relatedis it possible to rotate a graph. i need to turn my stacked cone graph upside-down so that the point is facing down and not up
View 2 Replies View RelatedI am rotating an image on a userform by uploading different gif images to simulate the rotation. There is an annoying screen flicker around the image when changing images that I would like to get rid of if possible. how to get rid of the flicker or a different technique I could use to rotate an image? 1st attempt - uploading different pictures
For h = 1 To Sheets("Bar Contents"). Range("B" & ItemRow) Step 1
PictureLocation = ThisWorkbook.Path & "PicturesTransparent" & Sheets("Bar Contents").Range("C" & ItemRow) _
& " " & h & ".gif"
GameUserForm.DrinkAnimation.Picture = LoadPicture(PictureLocation)
Sleep 50
DoEvents
Next h.........................