How to use object variables in .Formulas VBA

[sakkari]

Hi guys!

Long time reader, first time poster :D

I've created a quote calculator for our company and having trouble getting costs for a specific products summed up into one cell. I'm trying to write a formula in excel VBA to sum specific values from different worksheets. I have 2 different tables i want to sum from. Additionally i'm running the formula through a FOR loop depending on the amount of products.

For i = 1 To 6 'Getting total costs for additional equipment
    If x_num_addequip = 1 Then Exit For 'x_num_addequip is a value returned by a dropdown list (1 = 0)
    sheetname = "'Additional Equipment'!"
    financial_information(8).Cells(i + 2, 2).Formula = "=" & sheetname & Cells(i * 22, 5).Address(False, False) & "+" & add_equipment_labor_information(i) & Cells(24, 5).Address(False, False) & "+" & add_equipment_labor_information(i) & Cells(37, 5).Address(False, False) 'addititional equipment 1-6 total cost
Next i

For the first sheet "Additional Equipment" i can easily get the specific cell simply by multiplying i, because all tables are of equal size. For the sheet "Addtitional Equipment Labor" things are a little bit more complicated and i can't get to the correct cell by multiplying i.

Earlier in the code i have made and object variable "add_equipment_labor_information". This variable has 6 different ranges in it. The ranges refer to the tables on the sheet.

My question is: Can i use the object variable inside the Formula and how to do it, if possible. Currently it returns a type mismatch error.

Thanks for the help!

 

[sakkari]

Hi,

Got the problem solved. Didn't have to use the object variable in the formula. Still not sure, if it's possible to use them. Seems like it only accepts strings. I had to add 3 more string variables inside the FOR loop to get it working.

For i = 1 To x_num_addequip - 1 'Getting total costs for additional equipment
    If x_num_addequip = 1 Then Exit For
    sheetname = "'Additional Equipment'!"
    sheetname2 = "'Additional Equipment Labor'!"
    cellname = add_equipment_labor_information(i).Cells(12, 4).Address(False, False)
    cellname2 = add_equipment_labor_information(i).Cells(25, 4).Address(False, False)
    financial_information(8).Cells(i + 2, 2).Formula = "=" & sheetname & Cells(i * 22, 5).Address(False, False) & "+" & sheetname2 & cellname & "+" & sheetname2 & cellname2 'addititional equipment 1-6 total cost
Next i

 

[Norie]

What exactly is this 'object' variable you are referring to and how have you populated/set it?

 

[sakkari]

Hi,

The object variable is populated as shown below. It's a set of ranges in my worksheet:

With Sheets("Additional Equipment Labor")
    Set add_equipment_labor_information(1) = .Range("add_equip1_labor")
    Set add_equipment_labor_information(2) = .Range("add_equip2_labor")
    Set add_equipment_labor_information(3) = .Range("add_equip3_labor")
    Set add_equipment_labor_information(4) = .Range("add_equip4_labor")
    Set add_equipment_labor_information(5) = .Range("add_equip5_labor")
    Set add_equipment_labor_information(6) = .Range("add_equip6_labor")
End With

 

[Norie]

Couldn't you use the named range, e.g. 'add_equip1_labor', 'add_equip2_labor' etc., in the formula?

 

[sakkari]

Hi,

I probably could, but because i'm looping through with i, shouldn't I then write a formula for each and every named range? I figured that the .Formula only accepts strings as an argument. That's why i added 3 more string variables.

I thought that my original code would return the value of the object "add_equipment_labor_information(i)" as a string, but i think it returns it as an array, so Excel won't accept it. I'm pretty much guessing here, because I haven't had to use the .Formula expression inside a FOR loop before.

 

[Norie]

You couldn't use I directly in the formula but you could use it when constructing the formula.

Here's a simple example.

For I = 1 To 10

    Range("A" & I).Formula = "=COUNT(add_equip" & I & "_labor)"

Next I