Posts by v8nsx

  • vlookup with a dynamic range on a seperate worksheet

    Hi there with the help from this forum I was able to create a vlookup with a dynamic search range on the same page (Sheet 1 in the attached example). data is repeated throughout but I only wanna do my vlookup in block "b". I put in 5 as input and the vlookup returns b5 as I wanted. here is the equation I used and worked beautifully:


    =VLOOKUP(F1,OFFSET(INDIRECT(ADDRESS(MATCH("b",D1:INDEX(D:D,),0),2)),0,0,6,2),2,FALSE)


    However, I need to do the search and return the previous answer (b5) in Sheet 2, how should I do it?


    I tried to use the following equation to add "sheet1" into the previous equation in hope to make it search from sheet 2, but failed.


    =VLOOKUP(B1,INDIRECT("Sheet1!"&OFFSET(INDIRECT(ADDRESS(MATCH("b",Sheet1!D1:INDEX(Sheet1!D:D,),0),2)),0,0,6,2)),2,FALSE)


    Can anyone help? Attached is the worksheet.


    kenneth

  • Perform look up starting at a variable block of cells

    Re: Perform look up starting at a variable block of cells


    Hi there thx for your fast respond. It works if I am searching within the same sheet, but what do I do if my data is on a different sheet? How should I modify the code?


    Also what is the meaning of the range "A1:index(A:A)" in the match function? Why can't we simply use "A:A"?


    Thanks a lot for your help!!!


    kenneth

  • Perform look up starting at a variable block of cells

    Re: Perform look up starting at a variable block of cells


    Thx for the fast reply!!!!


    The problem is I don't know where "B" is exactly. I don't know the row number and hope to have some function to search and return the row number of B, then I can start my vlookup/index from that row on.


    kenneth

  • Perform look up starting at a variable block of cells

    Hi there I have the following condition:


    a
    1 a1
    2 a2
    3 a3
    4 a4
    5 a5

    b
    1 b1
    2 b2
    3 b3
    4 b4
    5 b5

    c
    1 c1
    2 c2
    3 c3
    4 c4
    5 c5

    I have repeated chunks of data as shown above. I want to start the vlookup/index only in a particular chunk (b section for example). Is there a excel funtion that I can start vlookup at the row where b is found, look for 3, and return b3? Thanks a lot for your help!!!

  • Dynamic Matching and Copy and Paste

    Re: Dynamic Matching and Copy and Paste


    here would be a sample of the matching:


    input


    Node 10 represents bulkhead fitting mount to FS 496 bulkhead
    3005 494.654 -30.75 69.93 AL6 30U 120
    3006 Ball Limit stop
    3007 Slip 494.404 -30.75 69.93 AL6 30U 120
    Node 3006 is a RIGID coupling (wrought ferrule) --
    3020 Bend 490.76 -30.75 69.93 AL6 30U 120
    3040 Bend 477.095 -31.541 72.87 AL6 30U 120
    3060 Bend 476.694 -31.272 79.509 AL6 30U 120
    3080 473.397 -31.272 79.509 AL6 30U 120 Anchor
    Node 3080 represents Saddle mount to FS 472 frame
    3085 469.921 -31.272 79.509 AL6 30U 120
    3086 Ball Limit stop
    3087 Slip 469.671 -31.272 79.509 AL6 300 120
    3100 Bend 463.875 -31.274 79.51 AL6 300 120
    3130 457.182 -30.994 76.171 AL6 300 120 Unreinf tee
    3132 456.362 -31.131 77.804 AL6 70 120 Conc mass
    Node 3132 represents Item 90 (sensor)
    3130 From
    3170 Bend 454.5 -30.881 74.832 AL6 300 120
    3185 450.274 -30.881 74.832 AL6 300 120
    3186 Ball Limit stop
    3187 Slip 450.024 -30.881 74.832 AL6 30U 120
    Node 3186 is rigid (wrought ferrules)
    3190 448.383 -30.881 74.832 AL6 30U 120 Anchor



    Pipe Section


    66 Non Std 0.66 0.035 0 0
    75 Non Std 0.75 0.035 0 0
    07U Non Std 0.75 0.049 0 0
    70 Non Std 0.705 0.035 0 0
    71 Non Std 0.705 0.04 0 0
    75T Non Std 0.75 0.105 0 0
    80 Non Std 0.802 0.04 0 0
    124 Non Std 1.2499 0.035 0 0 16 0.25
    125 Non Std 1.25 0.035 0 0 16 0.25
    126 Non Std 1.25 0.039 0 0
    150 Non Std 1.5 0.035 0 0 16 0.25
    15U Non Std 1.5 0.049 0 0 16 0.25
    AVG Non Std 1.935 0.049 0 0
    175 Non Std 1.75 0.035 0 0 16 0.25
    17U Non Std 1.75 0.049 0 0 16 0.25
    200 Non Std 2 0.035 0 0 16 0.25
    20U Non Std 2 0.049 0 0 16 0.25
    23U Non Std 2.37 0.049 0 0 16 0.25
    250 Non Std 2.5 0.035 0 0 16 0.25
    300 Non Std 3 0.035 0 0 16 0.25
    30U Non Std 3 0.049 0 0 16 0.25


    Ball Stiffness


    3005 401 80
    3085 1 1
    3185 1 1
    3245 1 1
    7015 2 1
    7075 105 21
    155 1 1
    165 1 1
    225 1 1
    245 92 18
    275 47 9
    365 143 29
    435 88 18
    465 86 17
    605 1 1
    685 1 1
    715 7 1
    835 19 4
    945 1 1
    985 1 1
    1205 60 12
    1285 1 1
    1335 56 11
    1505 9 2
    1585 4 1


    With the code:


    I am trying to match the first column of the "ball stiffness" page with the second column of the "input" page (The 1st column of the "input" page is blank). Once I found a match, I then need the 2nd last cell of the matching row on the "input" page, and use that cell to match with the 1st column of the "pipe section" page, and then copy and paste the corresponding matching stuffs. The part that doesn't work is to tell the program to give me the 2nd last cell of the matching row (I need 30U from the "input" sheet in this case, but can be anything for different files). I am hoping to use the line

    Code
    InputLastColumn = Worksheets("Input").Rows(InputStartRow).UsedRange.Columns.Count


    to give me to do a column count at the matching row dynamically, and use the InputLastColumn - 1 to shift back to get to 30U to do the "pipe section" match. Like I asked, what did I do wrong? What would be the more efficient way to do it? Thanks a bunch!

  • Dynamic Matching and Copy and Paste

    Hi there,
    I am trying to copy and paste stuffs by matching cells on two worksheets doing loops with matching criterias. The cells from worksheet "input" that I am matching doesn't stay at the same column all the time, so the way I came up to get to the cell I want all the time is to do a column count at the matching row, and then use the last column number to shift back to get to the matching criteria I need consistently. The following code, for some reason, doesn't work. What could be wrong? Any suggestions? Thanks a bunch.



  • Kickbutt VBA Find Function

    Re: Kickbutt VBA Find Function


    Code
    Sub Dele()


    Code
    Find_Range("-", Range("A1:A60000")).EntireRow.Delete


    Code
    Find_Range("Caepipe", Range("A1:A60000")).EntireRow.Delete



    Code
    End Sub



    I wrote a little sub to take away useless lines in files utilizing the Find_Range function. This function is great!!! When I run the code there's an error saying :
    Run-time error '91':
    Object variable or With Block variable not set


    What should I do? what's wrong?

  • Kickbutt VBA Find Function

    Re: Kickbutt VBA Find Function


    Is there a quick one liner that would take out all the lines starting with a string (word?) or symbols ("----")? if so what should I put at the the place "what"?


    Find_Range(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)



    Thanks for the great code. I'll use it a lot!!!!

  • vba search and delete code doesn't work

    This code does not work. I am trying to remove some page breaker lines. I am using this code to search the word caepipe and select a couple lines below that then delete, and hope that the loop would continue until it finish removing those lines. What could be wrong? The code runs fine, but just doesn't remove anything. Thanks.


    'Look for Row Numbers
    With Worksheets("Coordinates")
    startRow = Worksheets("a").Columns("B").Find(What:="coordinates", _
    LookIn:=xlValues, LookAt:=xlWhole).Row
    startRow = startRow + 4
    stopRow = Worksheets("a").Columns("B").Find(LargestNode, _
    LookIn:=xlValues, LookAt:=xlWhole).Row
    ' Copy and Paste Results to New Location
    Worksheets("a").Range("B" & startRow & ":F" & stopRow).Copy _
    Destination:=Worksheets("Coordinates").Range("A1")
    ' Delete Useless Info Between data
    CoordinateLastRow = ActiveSheet.UsedRange.Rows.Count
    For CoordinateStartRow = 1 To CoordinateLastRow Step 1
    If Cells(CoordinateStartRow, 1) = "Caepipe" Then
    CoordinateEndRow = CoordinateStartRow + 7
    Worksheets("Coordinates").Range("A" & CoordinateStartRow & ":E" & CoordinateEndRow).Select
    Selection.Delete
    Else: End If
    Next CoordinateStartRow
    End With