Posts by rikcando

    Simple but it works.

    Re: Dynamic formula to pick text from a list with conditions

    This macro will find if they exist on Sheet2 and create the list in A1 through A4 on Sheet3.
    Again, this does not move any data from Sheet2.

    Re: Dynamic formula to pick text from a list with conditions

    The VLOOKUP function requires at least two columns, even if you are only using 1.
    These formulas will go into cells A1 through A4 in your output sheet.

    Column I is simply a placeholder to avoid an error.
    Column H is the column that holds the data to be searched.

    Re: Dynamic formula to pick text from a list with conditions


    Cell A1
    Cell A2
    Cell A3
    Cell A4

    Re: Compare Column on one work sheet to 3 columns on another

    Quote from olsonj23;737828

    What I'm trying to do is to compare the values in column B on sheet 3 to the values in Column A on Sheet 2, the values in column B on sheet 2, and the values in column c on sheet 2. So if the values appear in any of the three column change the color of the cell to yellow.

    I am stil not sure which columns that you are trying to compare so I create a generic subroutine that you can use.
    It take the names of the sheets as source and compare (as strings), and the source and compare columns.

    To call is like this. I have tested this on sheets with 500+ rows. It takes some time to go through all of the rows, but it works.

    Sub doTest()
    TestCompare "CamTableSample", "CamTableSample2", 1, 1
    TestCompare "CamTableSample", "CamTableSample2", 2, 2
    TestCompare "CamTableSample", "CamTableSample2", 1, 3
    End Sub

    Hope that helps.

    Re: Compare the contents of a cell to a range of cells

    If you need to add further rows you could change the definition of the origRng like so.

    Set origRng = Range("B3:B4")

    And move the definition of compRng to within the Rng1 loop and define it using the Rng1 row.

    For Each Rng1 In origRng
           Set compRng = Range("C" & Rng1.Row & ":AH" & Rng1.Row)

    Re: Compare the contents of a cell to a range of cells

    Your range origRng is missing the quotes.

    Try this. It compares the strings both in upper case to catch any misaligned cases.
    It also checks to see it the string in B3 is the start of the string in Rng2.

    I also added changing the colorindex back to -4142 if the match was found. I was testing this and the cell turns red if no match was found, but was not cleared if the string in cell B3 was changed.

    Re: Macro to obtain values every second

    Could you not use Worksheet_Change and copy the values over as they appear in column B?

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 2 Then
            Sheet2.Range("A1").Value = Target.Value
        End If
    End Sub

    Re: Macro to move text within cell


    How to do I run It??

    First of all, sorry it took so long to get back here to answer your question. Busy week, son just got married.

    As for your question, simply press F5 from the VBA environment while the cursor is within the code. Or press the 'play' button. You could put a button on teh sheet itself and assign it to the macro.

    Re: Macro to move text within cell


    Option Compare Text

    Cytop, thank you very much for this note. I have been using LCase and UCase since GWBasic and I did not know of this seemingly newer option. I guess even an old hack like me can learn something.

    Re: Boolean Function Issue

    I just pasted the code into a new spreadsheet (Excel 2010), inserted a shape called 'thisone' and added the following code. Your function worked fine and returned TRUE.

    Public Sub testshp()
       Dim tobetrue As Boolean
          tobetrue = LabelExists(Worksheets(1), "thisone")
    End Sub

    Re: Macro to move text within cell

    Cytop, I looked over your code and (I may be wrong, but) it looks like it places each new keyword at the begining of the text string. this would result in 'screw Gearbox' instead of 'Gearbox screw'.

    Also, in my example I did not account for character case, so 'Gearbox' would not be found if the keyword was 'gearbox'. The following change would fix this.


    iPos = InStr(sTmp, sKey)


    iPos = InStr(LCase(sTmp), LCase(sKey))