-1

Here is a sub routine i have written in VBA excel, for some peculiar reason i am not able to convert my string into a range so that i may expand...

Sub simplifiedperformance()

Dim selectedrange As Range
Dim cell As Range
Dim value1 As Double
Dim value2 As Long
Dim text As String
Dim i, x As Integer
Dim fund1(0 To 16) As Double
Dim total(0 To 160) As Double

Worksheets("Data").Range("C3:T13").Copy 
Sheets("Report").Range("B39").PasteSpecial 
Worksheets("Data").Range("B3:T13").Copy 
Sheets("Report").Range("A39").PasteSpecial xlPasteValues 

Set selectedrange = Worksheets("Report").Range("C40:E40")

For Each cell In selectedrange

value1 = cell.value 
value2 = cell.Offset(0, -1).value 

value1 = value1 / value2 - 1 

total(x) = value1 + 1 

If i = 0 Then 

fund1(i) = total(0) - 1 

ElseIf i > 0 Then

fund1(i) = (total(0) * total(1) * text) - 1 '<<<<<<<< HERE
text = "total(" & 2 & ") * " & text 'ATTEMPTING TO EXPAND RANGE VARIABLES ABOVE

End If

i = i + 1 
x = x + 1 

Next


End Sub

I have tried adding the following code but to no avail...

text = "total(" & 2 & ") * " & text

The error result is type mismatch.

My first thought was to have a string and add to it every loop. but of course mismatch error.

Is there a better way to convert a string in my situation?

I have added the below as it works! but adds too many lines to my code

For doug

ElseIf i = 1 Then

fund1(i) = total(0) * total(1) - 1

ElseIf i = 2 Then

fund1(i) = total(0) * total(1) * total(2) - 1

End If
NewScientists
  • 1,192
  • 4
  • 13
  • 28
  • I need more explanation about what you're trying to do. You talk about turning a string into a range, `text` is declared as a string. – Doug Glancy Jun 17 '16 at 02:40
  • 1
    That code doesn't make any sense, and it's not clear what you're trying to accomplish (what's the actual output? what's the expected output?). Please [edit] your question to clarify; see [ask] for more info. – Mathieu Guindon Jun 17 '16 at 02:42
  • Hi Doug! thanks for helping. My objective is to write a few lines of code that work on the same concept as the example i added in the edit above! – NewScientists Jun 17 '16 at 02:42

1 Answers1

0

You cannot put code as a string and then just use it as regular code. What is actually happening in your code is (Double * Double * String), which raises the type mismatch error, as expected.

There's an Evaluate() function in Excel VBA that tries to evaluate a string expression into a value or object. You can see some details about it here Application.Evaluate Method (Excel). That may do what you want, but this is definitely not a recommended approach.

Your code is not very clear, and has some mistakes, but it looks like instead of using a string you can either use the fund1(i - 1) you calculated in the previous iteration: 

fund1(i) = (fund1(i - 1) + 1) * total(i) - 1

or do a For loop on total from 0 to i with something like this:

Dim j As Integer
...
fund1(i) = Total(0)
For j = 1 To i
    fund1(i) = fund1(i) * total(j)
Next
fund1(i) = fund1(i) - 1
dePatinkin
  • 2,239
  • 1
  • 16
  • 15