Problem with formula in VBA

[EssKayKay]

I have a formula in cell V16 I call in a VBA routine. However, if possible I’d rather just include the formula directly in the VBA code.

Here’s the formula in cell V16 –

=ROUNDUP(MAX(PmtNo)/periods_per_year,0)+33

Here’s a portion of the VBA code –

Range("S34:S" & Range("V16").Value).Select

I tried this but apparently VBA does not like the direct formula –

1st Test –

Range("S34:S" & ROUNDUP(MAX(PmtNo)/periods_per_year,0)+33).Select

2nd Test –

Dim EndTotal As String
EndTotal = RoundUp(Max(PmtNo) / periods_per_year, 0) + 33
Range("S34:S" & EndTotal).Select

Thanks for viewing,
Steve K.

 

[DanteAmor]

Try this:

  Dim EndTotal As Long
  EndTotal = Evaluate("RoundUp(Max(PmtNo) / periods_per_year, 0) + 33")
  Range("S34:S" & EndTotal).Select

 

[EssKayKay]

Try this:

  Dim EndTotal As Long
  EndTotal = Evaluate("RoundUp(Max(PmtNo) / periods_per_year, 0) + 33")
  Range("S34:S" & EndTotal).Select

That did it Dante - much appreciated. . .
Steve