Range Resizing compile error

[billyheanue]

Hi all,

I am trying to resize the range A5 into a different amount of rows, with only 1 column wide.

The cell located in coordinates [13,6] holds a value that contains the number of rows I want the range resized to.

I tried using Cells(13,6).value to accomplish this, but running it is giving me an error, and I think my syntax is all messed up.

Private Sub CommandButton5_Click()


Range("A5").resize(Cells(13,6).value),1).Formula = "=VLOOKUP(Sheet1!B16,Sheet2!" _
    & Sheets("Sheet2").Range("A4").CurrentRegion.Address & ",Sheet1!G$13,FALSE)"
    
End Sub

Can anyone help me out?

Thanks!

 

[MARK858]

Try removing the red )

Range("A5").resize(Cells(13,6).value[B][COLOR="#FF0000"])[/COLOR][/B],1).Formula = "=VLOOKUP(Sheet1!B16,Sheet2!" _
    & Sheets("Sheet2").Range("A4").CurrentRegion.Address & ",Sheet1!G$13,FALSE)"

 

[JackDanIce]

Maybe try breaking it down to isolate the error, try:

Private Sub CommandButton5_Click()

Dim resizeVal as Long
Dim strFormula as String
Dim rng as Range

resizeVal = Cells(13,6).Value
If Not IsNumeric(resizeVal) Then
  Msgbox "Value in F13 is not numeric, range will not resize"
  Exit Sub
End If

strFormula = "=VLOOKUP(@1,Sheet2!@2,@3,0)" '0 is equivalent to FALSE
strFormula = Replace(strFormula, "@1", "Sheet1!B16")
strFormula = Replace(strFormula, "@2", Sheets("Sheet2").Range("A4").CurrentRegion.Address)
strFormula = Replace(strFormula, "@3", "Sheet1!$G$13")

Set rng = Range("A5").Resize(resizeVal)

If Not rng Is Nothing Then
    rng.formula = strformula
End If

End Sub