Posts by Love Dog

    Greetings – and thanks in advance!

    I am SUCCESSFULLY using a wildcard value within a MATCH formula to locate the appropriate column and then retrieve a value:
    =IF(LEN(E11)>0,INDEX(Consolidated!$C$1:$O$109,MATCH($E11,Consolidated!$C$1:$C$109,0),MATCH("*Incident*",Consolidated!$C$1:$O$1,0)),"")


    When I substitute a Named Range for the Column Name lookup, e.g, Number_of_Incidents_Attended, I receive a #N/A result:

    =IF(LEN(E11)>0,INDEX(Consolidated!$C$1:$O$109,MATCH($E11,Consolidated!$C$1:$C$109,0),MATCH(Number_of_Incidents_Attended,Consolidated!$C$1:$O$1,0)),"") ==> #N/A

    As a sanity check, I used this formula, which evaluates to TRUE:
    =Number_of_Incidents_Attended = "Number of incidents attended"

    And... the value in the Column Header is exactly "Number of incidents attended", no trimming required. (Also verified by formula, in addition to eye-balling.)

    Why are these formulas not working identically?!?!

    I solved it! And I have no idea what I did. I made some syntax change that made everything 'happy':


    Rng.EntireRow.Insert


    One of these days... I may just wind up learning how VBA works.

    When the VBA script attempts to insert/paste the cut row's values, I get the message, "This selection isn't valid. Make sure the copy and paste areas don't overlap unless they are the same shape and size."


    What am I not doing correctly?! - Thanks in advance


    Dim FindString As String


    Dim Rng As Range
    Dim HeaderRow As Range


    ‘Find and go to Header Row
    With Worksheets("Roster").Cells
    Set HeaderRow = .Find("Reserves", LookIn:=xlValues)
    If Not HeaderRow Is Nothing Then
    HeaderRow.Select
    End If
    End With


    ' Now Move Member Status Heading Row
    Rows(ActiveCell.Row).Cut


    'First look for and insert Header Row above cell in Column K with a value of 'Reserve’
    FindString = "Reserve"
    With Sheets("Roster").Range("K:K")
    Set Rng = .Find(FindString, LookIn:=xlValues)
    Rows.Insert Shift:=xlDown <=== PROBLEM COMMAND
    End With

    I'm struggling to find the correct commands and syntax for my simple challenge. I want to:


    1) Search for the word "Reserve" in Column E
    2) Cut the entire row that contains "Reserve" in Column E (Note: This is a header row)
    3) Search for the word "Reserve" in Column K (Note: This will be the first occurence in a range of sorted data.)
    4) Paste/Insert the cut (header) row above the Row that contains the word "Reserve" in Column K (Note: This is the start of the cells in Column K that contain "Reserve")


    This seems like a very straightforward task, yet I'm getting hung up on using the precise language to make this happen.


    Many thanks to you for helping me.