Excellent!
This works like a charm.
I’ll work on this some more Monday to include into the remainder of my procedure; i don't foresee any issues incorporating it there.
Thank you again for your quick help and solution.
Stefan
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
Excellent!
This works like a charm.
I’ll work on this some more Monday to include into the remainder of my procedure; i don't foresee any issues incorporating it there.
Thank you again for your quick help and solution.
Stefan
Hello Carim,
My apology for not replying sooner.
You are absolutely correct with your correction in your first reply!
I will take a look at your solution and will be back.
Thank you for taking time to help me out!
Stefan
Hello,
I find myself stuck looking for a solution, which preferably would be in VBA to tie into the rest of the project.
Note: The Qty may be anything >0.
One of the obstacles is that a single cell may contain one or more pattern-matching strings (######).
If more than one string per cell (thus far the most 'Seq#' per one cell was 11), then split with a line break - such as Chr(10).
Three examples before vs. after below.
Thank you,
Stefan
Before | After |
Seq#: 123456 Qty: 15,165 | 123456 |
Seq#: 456789 Qty: 285, Seq#: 345678 Qty: 14,534 | 456789 345678 |
Seq#: 123456 Qty: 15,165, Seq#: 786789 Qty: 6,285, Seq#: 345678 Qty: 14,534 | 123456 456789 345678 |
Thank You!
Stefan
Hello,
Range A1:A6 is merged into 1 range.
Cells B1, B2, B3, B4, B5, and B6 are not merged. Same for columns C, D, E, and F.
I can manually select row (header) 3 and insert an entire row. (Range A1:A6 becomes A1:A7.)
I am unable to replicate this in VBA, with recorded steps or otherwise - it always enters 6 rows instead of 1 single row.
Old age, lack of gray matter,... what gives?
Thank you kindly for your guidance,
Stefan
Thank you very much for taking the time looking at my problem.
Example 1, and Example 3 provide the correct results, also when manipulating the data the results are as expected! - Excellent. - That is, unless I add duplicate dates.
Example 2, does not produce the desired result. expected count is 5, whereas it returns 10.
I attempted a 'Sumproduct' approach and never got anywhere near where you are. - Unfortunately I am still not wise enough to figure what and how to tweak to make it also work for instances such as in Example 2.
I added my test file with the different scenarios in case it will help; sorry for not including it originally already.
Stefan
Hello,
I need a count of unique records (exclude duplicates) based on multiple criteria.
Issue at hand is that one Line can have multiple records per day per shift - we need to know how many days a Line, per shift, worked between two dates, not how many records there are.
Below some examples and basic layout references.
Thank you for your guidance,
Stefan
Following example should return count = 10
Date | Shift | Line | Value |
04/01/20 | 1 | A | 1 |
04/02/20 | 1 | A | 2 |
04/03/20 | 1 | A | 3 |
04/06/20 | 1 | A | 4 |
04/07/20 | 1 | A | 5 |
04/08/20 | 1 | A | 6 |
04/09/20 | 1 | A | 7 |
04/10/20 | 1 | A | 8 |
04/15/20 | 1 | A | 9 |
04/16/20 | 1 | A | 1 |
Following example should return count = 5
Date | Shift | Line | Value |
04/01/20 | 1 | A | 1 |
04/01/20 | 1 | A | 2 |
04/02/20 | 1 | A | 3 |
04/02/20 | 1 | A | 4 |
04/03/20 | 1 | A | 5 |
04/03/20 | 1 | A | 6 |
04/06/20 | 1 | A | 7 |
04/06/20 | 1 | A | 8 |
04/07/20 | 1 | A | 9 |
04/07/20 | 1 | A | 1 |
Following example should return count = 1
Date | Shift | Line | Value |
04/01/20 | 3 | A | 0 |
04/01/20 | 2 | B | 2 |
04/02/20 | 1 | C | 0 |
04/02/20 | 3 | A | 4 |
04/03/20 | 2 | B | 0 |
04/03/20 | 1 | C | |
04/06/20 | 3 | A | 0 |
04/06/20 | 2 | B | 8 |
04/07/20 | 1 | C | |
04/07/20 | 1 | A | 1 |
Date Start | 04/01/20 | . | . |
Date End | 04/30/20 | . | . |
Shift | Shift | Shift | |
Line | 1 | 2 | 3 |
A | . | . | . |
B | . | . | . |
C | . | . | . |
D | . | . | . |
E | . | . | . |
F | . | . | . |
G | . | . | . |
H | . | . | . |
I | . | . | . |
J | . | . | . |
K | . | . | . |
Hello Carim,
Thank you for writing; point taken.
A sample file to illustrate your approach ... is a must ... !!! :wink:
I have mocked up a sample that I hope will help. - Not shown would be the step where, after the process renumbers the task #'s, the list would be sorted to put in proper order 1...5...10... etc.
Hopefully the three samples will help you to help me.
Thank you,
Stefan
Hello,
We have a list of tasks:
At times we need to switch the order where, for example:
We are looking to increase the ranked number of tasks from the current 10 to about 60. Doing any task number renumbering manually will turn your hair gray!
I would think that vba might be able to handle this via perhaps workbook selection change on the “Task Status” column (A) to see
Though sadly, i haven't got a clue nor was i able to find any bits here or online that might get me on the right track.
Assistance with this project would be much appreciated.
Thank you,
Stefan
Thank you jolivanes.
I remember having tried that before but could not remember the outcome, so repeated the task with one of the simpler formulas.
The following takes the date entered in a cell in column B (R_Date), uses its Excel Date value and adds the row number as 4 digit code - whereas 02/02/18 in B10 becomes 431330010 in A10 (column A (R_RecordNo)) when it functions as desired.
I suppose if nothing else it requires not only the column reference, but also a row reference; the formula, when placed via macro into the cell, does not know what to do with the 'Range' bit - and I need to have the formula placed in the cell, not just the resulting value in case that matters. - Variations of placing a 'R' (Row) reference for the RC (R1C1) reference style I attempted failed.
Thank you,
Stefan
Hello,
Is there a way to move away from the 'C[-14]' and incorporate the named range 'R_ScoreDelivery' instead so as to having a more flexible macro if columns were to be added/removed in-between at some point?
Thank you for your advice,
Stefan
Re: Color Individual Lines Of Shape Group
Andy, shg, Aaron,
thank you very much for all your help. This is great. Now i have a couple different ways to go about. - My goal is not as complex as what shg has shown on the "world" sample. I am just trying to build a shape similar to Draw > AutoShapes > Basic Shapes > Bevel, that allows me to color the border parts, which cannot be done individually in the said shape set up. - Now i am off to applying what i learned here.Thank you again.
Stefan
Re: Color Individual Lines Of Shape Group
Hello,
this looks great!
I am having difficulties understanding how this works. For practice i tried to create the same shape, same location, as in my sample, needless to say without luck. Excel's help is, well, not much help.
May i ask if you are willing to help me further please? How would i use the approach you suggested, which i like alot, to create a shape using the data as shown in the sample in my original post, in the same location / coordinates of the sample?
Thank you very much.
Stefan
p.s.
since i have existing shapes, based on lines like in the sample. how do i translate that best?
Hello,
any idea how i could color fill a custom shape created by individual lines which are grouped together? The group behaves as one shape alright when, say, coloring the line, but is not "fillable". Below a simple sample. - The custom shape i am trying to color in is not a simple square or rectangle -not anything from the existing MS shapes that is.
Thank you,
Stefan
Option Explicit
Sub Macro1()
ActiveSheet.Shapes.AddLine(48#, 25.5, 144.75, 25.5).Select
Selection.Name = "aLine"
ActiveSheet.Shapes.AddLine(144.75, 25.5, 144.75, 102.75).Select
Selection.Name = "bLine"
ActiveSheet.Shapes.AddLine(48#, 102.75, 144.75, 102.75).Select
Selection.ShapeRange.Flip msoFlipHorizontal
Selection.Name = "cLine"
ActiveSheet.Shapes.AddLine(48#, 25.5, 48#, 102.75).Select
Selection.ShapeRange.Flip msoFlipVertical
Selection.Name = "dLine"
ActiveSheet.Shapes.Range(Array("aLine", "bLine", "cLine", "dLine")).Select
Selection.ShapeRange.Group.Select
Selection.Name = "AllLines"
End Sub
Display More
Re: Remove Part Name/Caption From UserForm Controls
Thank you.
you are correct, I am seeking to replace the OptionButtons Caption, not the Name. My apology for misstating.
May i please in inquire as to where to place/run the code - i'd like to have the Caption replaced/corrected permanantly, not only at the start of the UserForm.
Thank you,
Stefan
Hello,
UserForm1
Current OptionButton names are like ABC123, ABC 124, etc.
How can i change all names, with code - as i am not seeing a find and replace option within UserForms, to all but the "ABC" part, the result of the OptionButton names shall therefor be 123, 124, etc. - delete "ABC" or, find "ABC" and replace with "".
Stefan
Re: Msoshapebevel - Change Color
Hello,
I have, briefly at this point, looked at your suggestion.
At this point I find the following quite promising. - I created four shapes (trapezoids) manually, named them, and then grouped them together. Now I can color them any which way I want by calling the respective shapes name - see at the end. Looks and generally behaves as the beveled shape. I can manually change the size for the group by draging the handles, just as usual. - So far so good.
Now I need to figure out how I can resize the group automatically. I can get its current size (I found the following snippet helpful):
For Each shp In Sheets("sheet5").Shapes
myleft = shp.Left
mytop = shp.Top
mywidth = shp.Width
myheight = shp.height
Next shp
MsgBox mywidth & " " & myheight
How can I resize using custom sizes?
Selection.ShapeRange.ScaleWidth 1.75, msoFalse, msoScaleFromTopLeft '1.75 stands for resizing to 175% of its original size
If the current size shows me 50 (width) and 100 (height) and I want it to respectfully be 190 and 75... it seems its just a math problem but I cant get my head around it.
And, how can I dictate its Top and Left?
Thank you,
Stefan
' Something like this
ActiveSheet.Shapes("FrameTTL").Select
Selection.ShapeRange.Ungroup.Select
ActiveSheet.Shapes("FrameLT").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 12
Selection.ShapeRange.Fill.Solid
ActiveSheet.Shapes("FrameRT").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 12
Selection.ShapeRange.Fill.Solid
ActiveSheet.Shapes("FrameBm").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Solid
ActiveSheet.Shapes("FrameTp").Select
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Regroup.Select
Selection.Name = "FrameTTL"
Display More
Re: Msoshapebevel - Change Color
Hello,
Thank you for your reply.
I have been trying different approaches, indluding "msoShapeIsoscelesTriangle" and "msoShapeTrapezoid".
I only need the four outside trapezoids as you identified correctly. I need to be able to color them individually - as in attached image. What shapes are in the backgroup does not matter as the square/rectangle center will be covered up, potentialy hiding part of the shapes used to achieve the border effect.
I dont know how to line these individual shapes up so that the corner pieces line up. Any suggestion?
Thank you,
Stefan
Re: Calculate On Sliding Scale
Hi triste,
could you provide a before and after example maybe?
Stefan