Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Most effective way to insert vlookup formula?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Most effective way to insert vlookup formula?

    I have a spreadsheet that I will be manually adding data to columns C:K. Periodically, I would like to run a macro that searches for rows where column C has a value, but column B is blank for that row. If column B is blank:

    insert a separate formula in column A and column B for that specific row

    Is this possible with VBA?

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Most effective way to insert vlookup formula?

    What would be the formula for the first blank cell in column B and the formula for the cell in column A in the same row if you done it manually? and what is the cell reference for the first blank cell in column B?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Most effective way to insert vlookup formula?

    Could you give an example of the formula that goes in A and the formula that goes in B, or what do you expect in A and B ?
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Most effective way to insert vlookup formula?

    Sure, sorry.. simple Vlookup

    Column A:
    Code:
    =VLOOKUP(C4,'A B'!B:D,2,FALSE)
    Column B:
    Code:
    =VLOOKUP(C4,'A B'!B:D,3,FALSE)

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Most effective way to insert vlookup formula?

    And the cell reference....

    and what is the cell reference for the first blank cell in column B?
    Edit: assuming the cell is B4 and the cells are truly Blank then maybe

    Code:
    Sub InsertFormula()
        With Columns("B").SpecialCells(4)
            .Offset(, -1).FormulaR1C1 = "=VLOOKUP(RC[2],'A B'!C[1]:C[3],2,FALSE)"
            .FormulaR1C1 = "=VLOOKUP(RC[1],'A B'!C:C[2],3,FALSE)"
        End With
    End Sub
    Last edited by MARK858; May 2nd, 2019 at 06:26 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Most effective way to insert vlookup formula?

    That's what hopefully.. the script can find .. My vision, is that for each row that has a value in column C, script checks if column A&B are blank.. if blank, insert formula.. if not blank.. ignore. Once finish row 2, look at row 3, etc. Because it will vary as data is loaded, if that makes sense.

  7. #7
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Most effective way to insert vlookup formula?

    Quote Originally Posted by srosk View Post
    That's what hopefully.. the script can find .. My vision, is that for each row that has a value in column C, script checks if column A&B are blank.. if blank, insert formula.. if not blank.. ignore. Once finish row 2, look at row 3, etc. Because it will vary as data is loaded, if that makes sense.
    It will find it but we need to know what the cell is currently as we need to know what the formula is relevant to.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  8. #8
    Board Regular
    Join Date
    Sep 2018
    Posts
    132
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Most effective way to insert vlookup formula?

    I am not so sure I understand, so pardon my ignorance. If column C contains a value in any row (value could be any alphanumeric value)... then if column A is blank, insert column A formula for that row... and if column B is blank, insert column B formula for that row that was checked in column C.

    Is this any bit helpful?

    Thanks for all that you do!

  9. #9
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,261
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Most effective way to insert vlookup formula?

    Try this

    Code:
    Sub test_formula()
        Dim c As Range, lr As Long
        lr = Range("C" & Rows.Count).End(xlUp).Row
        For Each c In Range("B5:B" & lr).SpecialCells(xlCellTypeBlanks)
            If c.Offset(0, 1) <> "" Then
                c.Offset(0, -1).FormulaR1C1 = "=VLOOKUP(RC[2],'A B'!C[1]:C[3],2,0)"
                c.FormulaR1C1 = "=VLOOKUP(RC[1],'A B'!C:C[2],3,0)"
            End If
        Next
    End Sub
    Regards Dante Amor

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,109
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Most effective way to insert vlookup formula?

    First of all in post number one you only stated if column B is blank insert the formulas in column A & B. now it is treat the columns separately for blanks, it isn't an issue but which is it?

    Second can you please just answer the question asked, as you look at it now what cells would the formulas you posted go in (just be assured if the formula went in B6 rather than B4 it would make a difference)

    If for example it was A4 and B4 the code would be

    Code:
    Sub InsertFormula2()
            Columns("A").SpecialCells(4).FormulaR1C1 = "=VLOOKUP(RC[2],'A B'!C[1]:C[3],2,FALSE)"
            Columns("B").SpecialCells(4).FormulaR1C1 = "=VLOOKUP(RC[1],'A B'!C:C[2],3,FALSE)"
    End Sub
    for separate columns (no need to loop through the cells).
    Last edited by MARK858; May 2nd, 2019 at 06:47 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •