Adding code using VBA

  • I have created a button that will insert a new row into a table and add a new worksheet. It will also rename the worksheet with whatever was inserted into Cell L7.


    I also need the button to insert formulas into certain cells on the new row of the table.


    The problem I am having is that the formula must contain a reference to the new worksheet that was created.


    My code is below:



    Where the "**L7**" is, is where I need whatever value was typed into cell L7 to be inserted.


    I was asked to create this workbook by my employer, and have just started learning VBA to do it.


    Thank you for the help.

  • Re: Adding code using VBA


    Hi 0zero0, welcome to OzGrid.


    You can use string concatenation in VBA by using an ampersand (&) symbol, for instance:


    Code
    myString = "cat"
    
    
    myNewString = myString & " dog"
    
    
    '// myNewString now contains "cat dog"


    We can use this logic to build a formula with an external reference, in your case like so:


    Code
    table_object_row.Range(1, 5).Formula = "=LOOKUP(Data!B2," & Range("L7").Value & "B9:B373," & Range("L7").Value & "N10:N374)"


    Hope that helps.

  • Re: Adding code using VBA


    Almost! I just need the formula to reference the worksheet that has been named whatever is in cell L7.


    So, if "test" is entered into cell L7 and the button is pressed, the following is entered into the respective cell:


    =LOOKUP(Data!B2,testB9:B373,testN10:N374)


    I need it to be:


    =LOOKUP(Data!B2,test!B9:B373,test!N10:N374)


    so that it will reference the newly created worksheet.

  • Re: Adding code using VBA


    Just add the exclamation marks in, don't forget if there are space is the sheet name you will also need surrounding apostrophes:


    Code
    If InStr(Range("L7").Value, " ") > 0 Then
         table_object_row.Range(1, 5).Formula = "=LOOKUP(Data!B2,'" & Range("L7").Value & "'!B9:B373,'" & Range("L7").Value & "'!N10:N374)"
    Else
         table_object_row.Range(1, 5).Formula = "=LOOKUP(Data!B2," & Range("L7").Value & "!B9:B373," & Range("L7").Value & "!N10:N374)"
    End If

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!