Posts by JRSmith729

  • Copy Paste Special As Values Based On Validation Cell Contents

    Re: Copy Paste Special As Values Based On Validation Cell Contents


    Hi Carl!


    Thanks for the code!


    It works great, but I actually want the macro to fire on the (manual) click of the FORM button but base the copy range on the contents of the validation cell at the time of the FORM button click, wheras your code fires immediately after a new selection has been made in the validation cell.


    I'll try to modify this timing issue on my own, but if anyone has a quick fix to share before I get there by trial and error, it'd be much appreciated.

  • Copy Paste Special As Values Based On Validation Cell Contents

    Hello OzGrid!


    Can anyone help me with a VBA macro that will copy & paste (Special > AS VALUES) from one of two (Data A & B) sheets based on the contents of a validation cell ($D$4) in a third (Report) sheet? The destination starting cell would be $F$11.


    ALSO - I'd like to have the Named Ranges "DataAExtract" & "DataBExtract" used in the code (for the COPY region) so I can see an example of how to reflect my actual named ranges in my working file.


    The reason for doing this is that the "c.Characters...." lines in my conditional formatting macros (attached) are not working on cells containing formula output (in my working file the Report page is all populated by VLOOKUP results), but the macros run fine on hard-coded values. In my attached workbook, I'd like to have the "NEW" macro for the copy & paste step fire first in the sequence of macros running after the FORM button-click (control located in cell $D$5 of the Report sheet), whether that's by writing a new macro and calling mine before the new one ends, OR by consolidating all of my macros plus the new one into one smooth progression.


    With this low-tech approach I can get updated VALUES into the report area once the user selects a data source and a customer on the report sheet. The COPY ranges in my working spreadsheet will update based on the selections made in the report page. I tried recording a macro and then modifying the recorded code to add the "If > Then" functionality I'm looking for, but I'm pretty green when it comes to VBA code and syntax.


    Any help is much appreciated. This should be pretty straight forward. Let me know if you have any questions.

  • Embedding With Under Select Case Versus If Statements

    Re: Embedding With Under Select Case Versus If Statements


    Hi Norie!


    Quote from above:


    I know the sample code below doesn't work, and I realize I only know enough about what I'm talking about to be really dangerous and put myself and others around me at risk of injury, but I'm just searching for possibilities. Maybe something that might look like:


    I'm just making stuff up to see if it spurs someone out there more knowledgable than me to take another approach (although I have no idea how to implement it or develop it on my own).

  • Embedding With Under Select Case Versus If Statements

    Re: Embedding With Under Select Case Versus If Statements


    Hi Brandtrock!


    Thanks, but I tried your formula suggestion and continue to get the result of ALL text being converted to match the "c.Interior.ColorIndex" - I think this is due to my concatenation which combines a number (1 to 7), the characters "-$", and the price point in the form ##.##, resulting in: "#-$##.##" which doesn't seem to compute as a number to be converted into text by this formula.


    I'm in way over my head here, but could the following snipets I've seen elsewhere somehow be incorporated into the final section of my code?


    Code
    Selection.NumberFormat = ";;;"


    Code
    Selection.NumberFormat = "General"


    I know the sample code below doesn't work, and I realize I only know enough about what I'm talking about to be really dangerous and put myself and others around me at risk of injury, but I'm just searching for possibilities. Maybe something that might look like:


    Code
    End Select
        c.Interior.ColorIndex = icolor
        c.Characters(Start:=1, Length:=2).Selection.NumberFormat = ";;;"
        c.Characters(Start:=3, Length:=Len(MyLength)).Selection.NumberFormat = "General"
c.Font.ColorIndex = xlAutomatic 'If ";;;" hides the 1st 2 chr's anyway, the font color wouldn't matter, right?
    Next c
Call HideRepetitiveText
Application.ScreenUpdating = True
End Sub


    Again thanks for the instruction today. I'm taking notes!

  • Embedding With Under Select Case Versus If Statements

    Re: Embedding With Under Select Case Versus If Statements


    Hi Brandtrock!


    Thanks for the code sample and for your patience with me.


    I've implemented it (somewhat) successfully into the code sample below...


    BUT, interestingly enough, the macro ONLY has the desired effect when the cell contents are hard-coded values, not formula output (I discovered this when I performed a "Copy" & then "Paste Special > Values" action over my "Calendar" range in a further stripped-down sample file (i tried to attach it but it's still 85KB with a max of 45KB! - SORRY!).



    How can this code be modified to recognize the output of the following formula (AS TEXT?), without destroying the formula(s) and associated functionality I have built into my spreadsheet?


    =IF(ISERROR(VLOOKUP($A14,INDIRECT($D$4),H$1,FALSE)=TRUE),"0",VLOOKUP($A14,INDIRECT($D$4),H$1,FALSE))


    Again, your help is much appreciated. I hope this isn't getting to just be a long hard kick in the head for you!

  • Embedding With Under Select Case Versus If Statements

    Re: Embedding With Under Select Case Versus If Statements


    Hi Norie!


    I totally respect the fact that there are much more important demands on your time...I just don't have the VBA syntax IQ/vocabulary to comprehend or implement most of your suggestions on my own the first time around! They fly right over my head, even though I know enough to recognize that you are mercifully offering good tips and suggestions!


    To be clear, that's a slight on me - not you!


    Here's what I came up with when I tried to act on your suggestion (and "drop set") with my novice VBA skill-set (I got an error message stating "Object variable or With block variable not set"):



    I tried to strip down my 15MB file to the nuts & bolts and only succeeded in getting it down to 12.5MB! That's still too big to post, right? I removed customer names and product names to protect the innocent, and shortened the array for my VLOOKUPS from 3000 to 30 lines for a simple example, and it's still that big! I feel bloated!


    My desired result is to "hide" the first 2 characters of the text strings returned by VLOOKUPS in the primary worksheet. The cells are all populated with an expression like (#-$9.99). My code system for each tactic (the numbers in the first digit - driving my Select Case arguments) means nothing to my audience, so I don't want them to see it.


    Can anyone help polish this thing off? So close...

  • Embedding With Under Select Case Versus If Statements

    Re: Embedding With Under Select Case Versus If Statements


    I keep getting a "compile Error : Object Required" message with my attempts to resolve this code syntax. I've tried the following variations:


    Code
    Dim icolor As Integer, c As Range, Text As String
    Set Text = c


    Code
    Dim icolor As Integer, c As Range, Text As String
    Set Text = c.Value


    Code
    Dim icolor As Integer, c As Range, Text As String
    Set Text = ActiveCell


    Code
    Dim icolor As Integer, c As Range, Text As String
    Set Text = ActiveCell.Value


    Code
    Dim icolor As Integer, c As Range, Text As String
    Set Text = c.Text


    Code
    Dim icolor As Integer, c As Range, Text As String
    Set Text = ActiveCell.Text


    So close, and yet still so far!

  • Embedding With Under Select Case Versus If Statements

    Re: Embedding With Under Select Case Versus If Statements


    I feel really lame, right now norie, but I am eating my humble pie for breakfast right now.


    In all humility, HOW do I define values for "Text" in VBA code?


    I'm really just a macro monkey. "Monkey see, monkey do!" I've learned a lot the last week or so, and I've stepped out on my own to make a few attempts at modifying and correcting stuff, but I'm still very green, ESPECIALLY with syntax.


    Any help in the form of a code sample would be much appreciated!

  • Embedding With Under Select Case Versus If Statements

    Re: Embedding With Under Select Case Versus If Statements


    Here's another (unsuccessful) attempt on my own:



    These unsuccessful samples are abbreviated to avoid repeating all of my Case arguments (#1-8), but they are still present in my working code.

  • Embedding With Under Select Case Versus If Statements

    Re: Embedding With Under Select Case Versus If Statements


    Brandtrock!


    Yabadaba, You-Da-Man!


    That sounds like the missing link, but I still don't get the result I'm looking for. Thank you for pointing me in what sounds like the right direction, I'm just not very fluent in VBA code, so when you say add a variable to capture the total number of characters:


    1) How would I got about doing that in the code? My attempt still colors ALL of the text!
    2) I do have varying lengths of text strings in my range so I've attempted the variable definition below, but I also need the code to calculate the "TOTAL length MINUS the first 2 characters", right? Or am I over-complicating this?



    Thanks everyone for your help!

  • Improve Color Index Conditional Formatting Macro

    Re: Improve Color Index Conditional Formatting Macro


    Thanks!


    I appreciate you taking the time to educate me on the syntax. I've definitely learned a lot from you on this project.


    I started a new thread to try to resolve just the issue I've been having with the "With / End With" lines of code you provided to handle the task of matching the font-color of the frist few text characters to the color-fill of the cell. I've tried lots of different things but none of them have the desired effect.


    I've only had two results with my efforts: the macro either 1) ignores the code and does nothing to the text, or 2) it changes ALL text in the cell!


    You can take a look at the new thread if you like. Just search with the keywords "With Under Case Select" in your search.


    Again, you've been a huge help. Thank you.

  • Combining Rows

    Re: Combining Rows - Example Attached


    Hi Cringe!


    I guess in my original question I just showed the END desired result (not fully grasping how difficult my final desired result would be to achieve), but when working on the project, I had to add intermediary steps to get there (where the last attachment just shows one of those steps on the way).


    ORIGINAL data layout had one row for one "event" with lots of potential matches of customer name (individual column) and product type (individual column). The data system only provides customer name, product type, start date of the event, duration in weeks for each event, tactic type and price. Lots of potential "matches" of customer and product combinations. 8,000 lines of raw data.


    STEP 1 was getting the "events" out into a 52 week lay-out for all rows using a week # in the 52 columns and then converting the start date to its corresponding week # and then using IF / AND logic to place a concatenated combination of the tactic type and the price into the column for any week number that fell on or between the start and end week #. This still resulted in 8,000 rows, but began to get the data to "look" like a calendar, but with a weird phasing effect for common customer / product matches. Like this:


    1234567890
    _xx_______
    _____xx___
    ________xx


    STEP 2 got the data to "cascade" visually where row 1 had just the first event & row 2 had the first event (maybe week 2-3) AND the second event (maybe week 6-7) - with repitition of data, as you pointed out. Still 8,000 rows. This step is shown in my last attachment. It looks like this in the "calendar" field:


    1234567890
    _xx_______
    _xx__xx___
    _xx__xx_xx


    STEP 3 tagged unique rows with any "X" where the concatenated combination of customer name and product type was DIFFERENT from the concatenated value for the row below it (in an alphabetically sorted list arrangement provided by the data source system). This identified the total number of necessary lines (which contain the largest number of event "plots in the calendar field) to capture all customer / product matches that were unique or necessary visually. A total of about 3,000 useful rows of data. This step was described in the text of my last post on this project.


    STEP 4 goes beyond what I described earlier and just extracts the unique rows of data using an auto-filter to highlight rows with the X for unique and visually necessary entries and then using "Edit > Go To > Visible cells only" to copy the unique rows and then use "Paste Special > Values" to insert them into a new workbook free from all the formulas to get to that extract. This other workbook contains macros that apply complex formatting to make the "calendar" a more compelling visual aid for understanding our planned promotion activity with our customers. This file size is up to 11MB.


    NOTE - My "processing" file size is up to 55MB, which is why I am absolutely 100% positive that I have NOT found the most efficient method to get to my desired result, and also why I haven't posted my actual working document with formulas, etc., but I did get there in the end.


    Now I'm working on macros to fine-tune the formatting conditions in VBA code.

  • Embedding With Under Select Case Versus If Statements

    Re: Embedding With Under Select Case Versus If Statements


    Hi RBRHODES!


    I tried your suggestion in the following ways:


    Code
    End Select
    With c
        .Interior.ColorIndex = icolor
        .Characters(Start:=1, Length:=2).Font.ColorIndex = c.Interior.ColorIndex
    End With
Next c
Call HideRepetitiveText
Application.ScreenUpdating = True
End Sub


    AND


    Code
    End Select
    c.Interior.ColorIndex = icolor
    c.Characters(Start:=1, Length:=2).Font.ColorIndex = c.Interior.ColorIndex
    Next c
Call HideRepetitiveText
Application.ScreenUpdating = True
End Sub


    BOTH versions had the same result = ALL text in ALL cells was changed to match the FILL-COLOR within the cell, rendering the data "invisible" rather than just hiding the first two characters.


    Can you detect any errors in my syntax which may be preventing this code from doing what I WANT it to do rather than just what I am TELLING it to do?


    ACW wanted to see all of my code. I'm not sure if that's necessary, but here it is. I have a calendar which is populated with 52 columns (the first cell in each column is a named range "Week01" to "Week52" referenced in my code sample) x 125 rows of VLOOKUP results based on a user's selection of one customer name from a validation cell (containing about 50 options). The VLOOKUPs reference a concatenated tag which changes based on the user selection as mentioned above.


    The output of the VLOOKUPs is in the form "#-$9.99" where the # is from 0 to 8. "0" has been used to avoid "" blanks, and for purposes of my visual effect are just place fillers to be colored white since no price information will be included when a zero is present. "8" is used to maintain formatting of gray shading between major product groups. #'s 1 to 7 represent different promotion tactics or conditions but these numbers are ONLY necessary to drive the formatting conditions and will mean nothing to my audience, so I'd like to:


    1) hide this code system I have devised and
    2) ensure that the pricing data IS visible, as that data WILL matter to my audience



    I hope this helps YOU help ME!


    Thanks for trying!

  • Embedding With Under Select Case Versus If Statements

    Hi there!


    I have produced the following code to apply COLOR-FILL based on multiple conditions:



    I have also been provided a few apparently simple WITH statements that should make the FONT-COLOR of the first 2 characters of the cell value match the COLOR-FILL behind the text:


    Code
    With ActiveCell.Characters(Start:=1, Length:=2).Font.ColorIndex = ActiveCell.Interior.ColorIndex


    OR


    Code
    With c.Characters(Start:=1, Length:=2).Font.ColorIndex = c.Interior.ColorIndex


    I just can't get this With statement to work in my code. The macro will apply the COLOR-FILL formatting but the text remains the same. It's like my With statement doesn't even register.


    1) Can anyone help bring these two code fragments together for me? I've tried using the With argument after each Case definition, and I've tried slippling the With statement in after End Select line, but nothing has worked for me!


    2) In general - CAN a "With" argument be nested under a Select Case argument, OR do I need to approach this with a series of "If" or "Else If" arguments to get my desired result?


    I'd really appreciate it if someonecould post a solution within my original code since I keep botching the syntax with my own attempts.


    Your help is much appreciated.

  • Improve Color Index Conditional Formatting Macro

    Re: Improve Color Index Conditional Formatting Macro


    Hi there!


    For future reference I managed to correct this macro compile error on my own as follows to get the desired result:



    Consider my questions posted here as closed. I'll start a new (shorter) thread with any further issues.

  • Improve Color Index Conditional Formatting Macro

    Re: Improve Color Index Conditional Formatting Macro


    Hi norie!


    Thanks for the suggestion, but it's still not working the way I've imlemented the code. Here's how I modified the (end) of my original code:


    Code
    End Select
        c.Interior.ColorIndex = icolor
        c.Characters(Start:=1, Length:=2).Font.ColorIndex = icolor
    Next c
End Sub


    The result was that the fill updated properly, but ALL text was changed to match the fill coloring (so none of the text was "visible" anymore, it all just blends into the fill coloring), not just the first 2 characters in each cell.


    When I tried using the With/End With expression like this, nothing happened to the text, but the macro ran successfully and colored the fill as expected:


    Code
    End Select
        c.Interior.ColorIndex = icolor
        With c.Characters(Start:=1, Length:=2).Font.ColorIndex = icolor
        End With
    Next c
End Sub


    What am I missing to get both the FILL color, AND the FONT color of the first 2 characters of the text to MATCH? Do I need to place the With/End With expression WITHIN each individual case argument in the original code sample at the beginning of this post?


    Also, I am getting a compile error (End If without block If) when I run my version of the recommended code from MrkFrrl for changing the font color based on left column similar values. Here's what I tried:


    Code
    Sub HideRepetitiveText()
    Dim i As Integer
        For i = 1 To 200
        If ActiveCell.Value = "" Then Exit Sub
        If ActiveCell.Value = ActiveCell.Offset(0, -1).Range("A1").Value Then ActiveCell.Font.ColorIndex = ActiveCell.Interior.ColorIndex
        End If
    End If
    ActiveCell.Offset(1, 0).Range("A1").Select
Next i
End Sub


    I don't follow why the "ActiveCell.Offset(#,#).Range(name).XXXXX" are referencing "A1." Is that just a place holder within the code to name the cell to the left "A1" is the code really looking at cell A1? Does that reference need to be changed to suit my situation (my target area begins at column F, so intuitively the first "offset(0,-1)" should be column E)?


    I hope I'm making sense.

  • Improve Color Index Conditional Formatting Macro

    Re: Improve Color Index Conditional Formatting Macro


    I'm feeling really lame right now. You've given me all this great material to get me to my end goal, but in terms of implementing the code I'm still back at square one. I've tried numerous variations and placements of your line of code:


    Code
    With ActiveCell.Characters(Start:=1, Length:=4).Font 
    .ColorIndex = iColor ' same as other listed in Select Case
End With


    How would the syntax look within my original full code sample?


    I've tried placing the With / End With expression after each "Case" and I've tried just listing it once below the End Select expression, but I haven't found the proper syntax. Any suggestions? Again, any help is much appreciated.

  • Improve Color Index Conditional Formatting Macro

    Re: Improve Color Index Conditional Formatting Macro


    WOW!


    I love this forum! I logged in just 1 minute after your reply was posted! Thank you so much for your help!


    I have one question about the second code sample you provided. In your comments you warn against large sets of cells and blanks. I have about 7500 cells in the range "Calendar" (52 weeks x about 150 rows). These cells are populated with a VLOOKUP returning "" when no match is found, but every cell in the "Calendar" range has either a formula result of "" or a hard-coded text/numeric value. With this information:


    1) Will this approach for populating my spreadsheet create any problems for your code, or should I be alright?


    2) I'm always a bit intimidated when I see code with the Integer (i = # to ##) type of logic because I'm only really comfortable modifying existing code samples. I'm not yet fluent enough to write code "from scratch" on my own and this type of logic is above my level of comprehension right now. Will I need to input a Range("B2").Select argument for ALL 7500 cells in the target area, or just 52 for the full width of one row (allowing the code to somehow pass from there onto the next row)? Can I just enter Range("Calendar").Select and capture all cells in my (dynamic) Named Range?


    3) In general, what does the whole "Integer (i - # to ##)" expression actually DO? Why repeat the code 100 times? Why not 10 or 68 times?


    I really appreciate your help. And patience!