Posts by acw

    Re: Macro To Compare And Highlight Cell Contents


    Hi


    See how this goes.




    Tony

    Re: Data Validation Source


    Hi


    Try this. Right click on sheet1 tab, select view code, and paste in the code below.



    At this stage it is only designed to work on the range C12:C15.


    Select C12 and you should see a list of names. Make a change to the selection in B12, and the dropdown list should change.


    HTH


    Tony

    Re: Import Newest Text File


    Hi


    Does this give you any help?



    If you don't want to change the drive / path, then you could include those details into the fname string.



    Tony

    Re: Wildcards Index/match


    Hi


    Enter a space in I1, then array enter (ctrl, shift, enter) the formula
    =INDEX($I$1:$I$5,MAX(IF(ISNUMBER(MATCH("*"&$H$2:$H$5&"*",B2,0)),ROW($H$2:$H$5),"")))
    in C1 and copy down to C8.



    HTH


    Tony

    Re: Filter By String Criteria


    Hi


    Could you use a test along the lines of

    Code
    1. If Left(Cells(i, 1).Value, 4) = "L-0." And Len(Cells(i, 1).Value) = 7 Then
    2. Cells(i, 1).EntireRow.Delete
    3. End If


    as a basis for your deletion? You could still use your input selection method to work out the number of characters before the *, and use the length of the selection string to make dynamic variables.....



    Tony

    Re: Dual Headings Match


    Hi


    Here it is expanded for your new sample, and absoluted.


    =INDEX($B$2:$E$2,0,MAX(IF(MIN(IF($B$3:$E$3=$I21, OFFSET($B$3,MATCH($H21,$A$4:$A$16,0),0,1,4),MAX($B$4:$E$16)))=OFFSET($B$3,MATCH($H21,$A$4:$A$16,0),0,1,4),COLUMN($B$5:$E$5)-1,"")))


    Tony

    Re: Formula In 2 Cells To Populate In Only One Cell


    Hi


    Not sure I follow. Do you want to be able to enter a value in A2, and have a formula automatically generated in B2? If so, then you will need to have an event macro with a trigger of a change to a cell in column A.


    Is that what you want to do?


    Tony

    Re: Dual Headings Match


    Hi



    Try


    =INDEX(B2:E2,0,MAX(IF(MIN(IF(B3:E3=I21,OFFSET(B3,MATCH(H21,A4:A6,0),0,1,4),MAX(B4:E6)))=OFFSET(B3,MATCH(H21,A4:A6,0),0,1,4),COLUMN(B5:E5)-1,"")))


    array entered.



    Tony

    Re: Transfer Data From 2 Worksheets Into 1 - Macro


    Hi


    Your Identified data has an extra column of "keep" data (column BJ - Q1 2008) than the Unidentified data sheet. Your Desired End Result sheet doesn't show this heading. How do you want the extra column of data handled?



    Tony

    Re: Transfer Data From 2 Worksheets Into 1 - Macro


    Hi


    See how this goes.



    HTH


    Tony

    Re: Text Box Value Validation


    Hi


    You could do something like


    Code
    1. On Error Resume Next
    2. tester = CDbl(TextBox1.Value)
    3. On Error GoTo 0
    4. If TextBox1.Value = "" Then
    5. MsgBox ("The Value MUST be Entered"), vbOKOnly, "Value of Something"
    6. exit sub
    7. end if


    You can put that in the end processing like the other test, and / or on a textbox_exit event.



    HTH


    Tony

    Re: Text Box Value Validation


    Hi


    Where is the textbox? Is it in a form, or directly on the spreadsheet.


    If on a form, then you could do something like


    Code
    1. Do
    2. If TxtBox1.Value = "" Then
    3. MsgBox ("The Value MUST be Entered"), vbOKOnly, "Value of Something"
    4. TxtBox1.SetFocus
    5. Exit Sub
    6. End If
    7. Loop Until TxtBox1.Value <> ""
    8. ws.cells(r,2).value = TxtBox1.Value



    HTH


    Tony

    Re: Macro To Transpose &amp; Duplicate


    Hi


    Try the attached. I've put the macro into your source file, and changed it so that the output will go to sheet3.


    Make sure you are on sheet1 when running, and the ranges selected match your colored source ranges.


    Runs OK for me.



    Tony

    Files

    • targeter.zip

      (16.62 kB, downloaded 218 times, last: )

    Re: Macro To Transpose &amp; Duplicate


    Hi


    See if this gets you going.


    I've done no error checking to make sure that you have selected the same number of rows in both selection. When the first input box comes up, use the cursor to select the yellow range. For the second, select the green range. You must have an output sheet called sheet2. Any existing data on that sheet will be deleted.



    Tony

    Re: Prevent Duplicate Through VBA Userform


    Hi


    See if this helps.




    Tony