Copy formula into a cell using vba

[BobtBuilder]

Hi folks I have this application that inserts a new sheet, based on a template, renames it and updates the table with the new name
Problem 1 After the cell.value = ... it inserts that value into the cell
Problem 2 I can not seem to figure out why the formula is giving me errors, even if I have done it successfully in the past with another formula. I have left some examples of what I have tried
Problem 3 it does not seem to exit the sub, I have to reset manually

Here is the code

Sub NewComp_Click()

Dim wsname As String
Dim cell As Range
Dim nextLocation As Integer
Dim BName As String
Dim AccType As String
Dim FormulaString As String
Dim sourceSheet As Worksheet
Dim newSheet As Worksheet
Dim prev As Range
Dim DestinationRow As ListRow
Dim DestinationTable As ListObject


' values from form
BName = Me.TextBox1.Value
AccType = Me.ComboBox1.Value

' Set the range of positions of worksheets
Set cell = Worksheets("Settings").Range("H3")
nextLocation = 0
' Find the next available location
Do While cell <> " "

nextLocation = cell.Value + 1
Set cell = cell.Offset(1, 0)
Loop
If nextLocation > cell Then

Else
cell.Value = Range("I" & nextLocation - 1)
End If

' Create new sheet
Sheets("Template").Copy After:=Sheets(cell.Value)
ActiveSheet.Name = BName

' setup to insert cells
wsname = "Settings"
Set ws = ThisWorkbook.Sheets("Settings")
Set DestinationTable = ws.ListObjects(wsname)
Set DestinationRow = DestinationTable.ListRows.Add

' formula to insert
FormulaString = "=TAKE(FILTER(INDIRECT(""'"" & I11 & ""'!K2:K"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!K:K"")) + 1)), (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <= TODAY()) * (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <> "")),-1)"

DestinationRow.Range(1, 1).Value = nextLocation
DestinationRow.Range(1, 2).Value = BName
' DestinationRow.Range(1, 3).Value = "=TAKE(FILTER(INDIRECT(""'"" & I11 & ""'!K2:K"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!K:K"")) + 1)), (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <= TODAY()) * (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <> "")),-1)"
' Range("J" & nextLocation).Formula = FormulaString

End Sub


Thank you

 

[6StringJazzer]

Problem 1 After the cell.value = ... it inserts that value into the cell

How is that a problem? What do you want to happen instead? Are you talking about this line of code:

cell.Value = Range("I" & nextLocation - 1)

It helps to be specific so we don't have to guess.

Problem 2 I can not seem to figure out why the formula is giving me errors, even if I have done it successfully in the past with another formula. I have left some examples of what I have tried

What specifically is giving you errors? What are the errors? There are no examples of anything here except your VBA code.

Problem 3 it does not seem to exit the sub, I have to reset manually

How much does it do? Is it completing all the code but just not exiting the sub? How do you know it is not exiting? What are you doing to "reset manually"?

 

[BobtBuilder]

Hi ,

Thanks for the response andI am sorry, i already thought I was being too verbose.
So problem 1 I just wanted to store the vales from that cell into a variable so I could use it later, not save it on the sheet
Problem 2 run-time error 1004 Application-defined or object-defined error
problem 3 it actually seems to reach the end sub but never ends

 

[6StringJazzer]

So problem 1 I just wanted to store the vales from that cell into a variable so I could use it later, not save it on the sheet

To do this, declare the variable then make the assignment:

Dim MyVariable ' don't call it this, give it name that reflects what it means
MyVariable =  Range("I" & nextLocation - 1)

But once you have it in a variable, what do you want to do with it?

Problem 2 run-time error 1004 Application-defined or object-defined error

This will probably not be hard to figure out but we need to know what line of code caused the error. When the error occurs, click Debug, and then tell us which line of code is highlighted in yellow.

problem 3 it actually seems to reach the end sub but never ends

How do you know it is not exiting? What are you doing to "reset manually"? I know of no scenario where a Sub runs all the way to the end but never returns. Usually when they get stuck like that, it is because of an infinite loop. You only have one loop and it looks pretty safe; it ends when it finds a blank cell in column H.

 

[BobtBuilder]

problem 1 i need the value in that cell not just the cell number
2 on line
DestinationRow.Range(1, 3).Value = "=TAKE(FILTER(INDIRECT(""'"" & I11 & ""'!K2:K"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!K:K"")) + 1)), (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <= TODAY()) * (INDIRECT(""'"" & I11 & ""'!A2:A"" & (MATCH(9.99999999999999E+307, INDIRECT(""'"" & I11 & ""'!A:A"")) + 1)) <> "")),-1)"
problem 3 as I step through the code it reaches end sub, but when i try and click on the sheet it beeps so i have to go to run reset

 

[6StringJazzer]

1. The line of code I gave you will take the value out of the cell referenced by Range("I" & nextLocation - 1) and put it into that variable. (You should declare the variable as the correct data type also, which I did not do.) Either I'm not following your question, or your understanding of VBA is much more basic than one would guess from seeing this code.

2. This could be an invalid formula, which will be hard for me to replicate without having your data to test it on.

3. You are stepping through code using F8, and you get to the end? Is there still a line of code highlighted to indicate execution when you try to click on the sheet?

 

[BobtBuilder]

The line 0f code from what I can see is exactly the same as what I am running now I changed the variable cell to newcell
Dim newCell as Range
newCell=Range("I" &nextLocation -1)
the formula is in the code and is the same formula that I had manually entered into the previous cell, had to change it due to VBA
There is no real data. it is a brand new table which has 3 cells to it 1) a unique identifier 2) a name and 3) that formula
The other 2 variables come from a simple form which asks a name, to be inserted into cell 2, and a type, which for now has no use

 

[6StringJazzer]

Sorry, I'm completely lost now. Your code assigns a value to variable nextLocation. It takes the value in the cell that is in column I, one row up from the nextLocation row, and assigns that value to variable newCell.

So what's the problem?

 

[BobtBuilder]

It, for some reason, enters the value in the table, although at this step I have not told it to do so.

 

[BobtBuilder]

so i fixed problem 1, changed a bit of code.
I fixed problem 2, seemed to be a " problem. But found a snag in the code
I found where problem 3 is. It seems the userform calls the vba script but never ends it just stays there until you hit the x on the form