Inserting formula by button

[tyr443]

Hi all,

I am trying to add a formula to a newly created cell via button using the below:




Private Sub CommandButton1_Click()
Dim rowNum As Integer
On Error Resume Next
rowNum = Application.InputBox(Prompt:="Enter Row Number where you want to add a row:", _
Title:="Int Invoice", Type:=1)
Rows(rowNum & ":" & rowNum).Insert Shift:=xlDown

Sheets("Sheet2").Range("c" & rowNum).Select
ActiveCell.Formula = "=VLOOKUP(A" & rowNum & ",Sheet1!$A$2:$B$23,2,FALSE)"

Sheets("Sheet2").Range("d" & rowNum).Select
ActiveCell.Formula = "=ROUNDDOWN(IF(B" & rowNum & "<>"",LEN(SUBSTITUTE(B" & rowNum & ",", ",""))/5)*(VLOOKUP(A" & rowNum & ",Sheet1!$A$2:$C$23,3,FALSE)),0)"

End Sub
at this point:



ActiveCell.Formula ="=ROUNDDOWN(IF(B" & rowNum & "<>"",LEN(SUBSTITUTE(B" & rowNum & ",", ",""))/5)*(VLOOKUP(A" & rowNum & ",Sheet1!$A$2:$C$23,3,FALSE)),0)"

It tells me there is a compile error "expected end of statement".

Can anyone see what I am doing wrong?

When I get it to work without a compile error the formula doesn't go into the cell.

Thanks for any help

 

[Fluff]

You need to double up all the quotes in the formual like

ActiveCell.Formula = "=ROUNDDOWN(IF(B" & RowNum & "<>"""",LEN(SUBSTITUTE(B" & RowNum & ","", "",""""))/5)*(VLOOKUP(A" & RowNum & ",Sheet1!$A$2:$C$23,3,FALSE)),0)"

 

[tyr443]

Thanks so much, I have another question if you don't mind.

How would I change the fill colour using the above button for one of the particular cells?

I have the hex code for the colour: #F2F2F2

Thanks again!

 

[Fluff]

Maybe

activecell.Interior.Color= 15921906