Need to get vba code help.

[dhylbert]

I am looking to use a active x command button, when clicked, it will ask user how many rows they want to insert. I then want it to insert rows, and add row highlights. I also want column g to be change to formula. Here is the code I have so far. I am getting an error. Can someone help?

Private Sub CommandButton1_Click()

Dim numrow
numrow = Application.InputBox("How many rows would you like to add?", "Insert Row", , , , , , 1)
If IsNumeric(numrow) Then
For i = 1 To numrow
Call INR
Next i
End If


End Sub

and


Sub INR()

Sheets("Assessment").Range("A" & Rows.Count).End(x1Up).Select
ActiveCell.EntireRow.Insert Shift:=xlDown

Sheets("Assessment").Range("$A":"$L").Select
Selection.Borders.Weight = xlThin

Sheets("Assessment").Range("G4").Select
ActiveCell.Formula = "=IF(ISBLANK($F4),"", CONCATENATE(Priority & $L4 & , & $F4))"

End Sub

 

[richh]

I'm thinking the error is the formula. You have to escape the double quotes, or reference them by ASCII value.

ActiveCell.Formula = "=IF(ISBLANK($F4)," & CHR(34) & CHR(34) & ", CONCATENATE(Priority & $L4 & , & $F4))"

 

[Fluff]

Is Priority a variable? if not I suspect that you want

ActiveCell.Formula = "=IF(ISBLANK($F4),"""", CONCATENATE(""Priority "",$L4,"","",$F4))"

 

[dhylbert]

Here is the error I am getting now

 

[Marcelo Branco]

See if this does what you want

In the Sheet module

Private Sub CommandButton1_Click()
    Dim numrow As Long, i As Long
    
    numrow = Application.InputBox("How many rows would you like to add?", "Insert Row", , , , , , 1)
    
    If IsNumeric(numrow) Then
        For i = 1 To numrow
            Call INR
        Next i
    End If
End Sub

In a Standard Module

Sub INR()
    Dim LR As Long
    
    Sheets("Assessment").Activate
    'Get the last row with data in column A
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    'Insert row before the last row with data in column A
    Range("A" & LR).EntireRow.Insert Shift:=xlDown
    'Put borders
    Range("$A" & LR).Resize(, 12).Borders.Weight = xlThin
    
    'Dont understand why the formula should be in G4. Please, explain.
    Range("G4").Formula = "=IF(ISBLANK($F4),"""", ""Priority "" & $L4 & "","" & $F4)"
End Sub

Hope this helps

M.

 

[Fluff]

On this line

Sheets("Assessment").Range("A" & Rows.Count).End(x1Up).Select

you have x1Up, when it should be xlUP (a lower case L not the number 1)