Posts by StefanG

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.

1. Variable number of rows (column E)
2. For each cell, remove all characters unless the pattern is ###### (6 numbers)

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

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

Record Count Eval Test.xlsx

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.

• Data is located on Sheet 'Data'
• Output is located on Sheet 'Output'

Below some examples and basic layout references.

Thank you for your guidance,

Stefan

• Sheet 'Data' - A = Date
• Sheet 'Data' - B = Shift
• Sheet 'Data' - C = Line
• Sheet 'Data' - D = Value - Note: ignore record in count altogether if value = 0, or value = ""

Following example should return count = 10

• 10 unique records on 1 Shift (Shift 1) one record per Date (in 04/01/20 - 04/30/20), per Line (A), with Value <> 0, or <> ""

Following example should return count = 5

• 5 unique records on 1 Shift (Shift 1) one record per Date (in 04/01/20 - 04/30/20), per Line (A), with Value <> 0, or <> ""

Following example should return count = 1

• 1 unique record on 1 Shift (Shift 1) one record per Date (in 04/01/20 - 04/30/20), per Line (A), with Value <> 0, or <> ""

• Sheet 'Output' - B1 = C_Date_Start (for example 04/01/20)
• Sheet 'Output' - B2 = C_Date_End (for example 04/30/20
• Sheet 'Output' - A4 - Line (for example A)
• Sheet 'Output' - B3-D3 - Shift
• Sheet 'Output' - B4 = C_Shift_1 - 1
• Sheet 'Output' - C4 = C_Shift_2 - 2
• Sheet 'Output' - D4 = C_Shift_3 - 3

Hello Carim,

Thank you for writing; point taken.

Quote from Carim

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:

• Many already done and flagged as “Complete”
• Many flagged as “Pending”
• About 10 tasks at a time ranked from do first = 1, to do last = 10.
  
  
  • When 1 is moved to “Complete”, 2 becomes 1, 3 becomes 2, etc.
  • Then one from the “Pending” group may be moved to 10 right away.... or not.
  • Then we sort the list.
  • This is done manually.

  
  

At times we need to switch the order where, for example:

• 9 gets higher priority and may become 2. Then 2 goes to 3, 3 to 4 etc.
• Another time we may bounce a “Pending” task to a priority level already set. So, if task “Pending” moves to 2, then old 2 goes to 3, 3 to 4,...10 goes to 11.
• Some times we move an assigned task, such as task 2 back to “Pending”, then 3 moves to 2, 4 to 3, etc.

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

• what change was made and then renumber tasks when needed (when a number was added, changed or removed),
• assuring we have consecutive numbering starting at 1
  
  
  • (the max number is not set and only determined by however many tasks are currently numbered - today we have sometimes only 6 and other times 12, and “tomorrow” we may have 50 one time and the next 65),

  
  

• without duplicating task numbers,
• and by preserving the order of tasks.

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 changed
  
  Code

  .... "=[COLOR=#0000FF]RC[1][/COLOR]&TEXT(ROW(),""0000"")"

  
  

• To
  
  Code

  ".... =[COLOR=#0000FF]Range(""R_Date"")[/COLOR]&TEXT(ROW(),""0000"")"

  
  

• The result changes from the desired 431330010 with the formula in A10 being =B10&TEXT(ROW(),"0000")
• The result is now a #Name? error with the formula in A10 being =range("R_Date")&TEXT(ROW(),"0000")

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,

• I have several named ranges for columns, for example: 'R_ScoreDelivery' for column BG
• Via macro I am adding several calculations that take place within a row, for example: .... IF(OR(RC[-14] ... where in this instance RC[-14] points to column 'R_ScoreDelivery'

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

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"

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

Hello,
msoShapeBevel results in a shape with 5 sections. How can I color top and bottom in one color, left and right in another and the center in a third color/or blank?
Thank you,
Stefan

Sub test()
Worksheets(3).Shapes.AddShape msoShapeBevel, 50, 50, 100, 200
End Sub

Re: Calculate On Sliding Scale

Hi triste,
could you provide a before and after example maybe?
Stefan