How to use object variables in .Formulas VBA

sakkari

New Member
Joined
Dec 21, 2018
Messages
4
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.

Code:
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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.

Code:
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
 
Upvote 0
What exactly is this 'object' variable you are referring to and how have you populated/set it?
 
Upvote 0
Hi,

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

Code:
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
 
Upvote 0
Couldn't you use the named range, e.g. 'add_equip1_labor', 'add_equip2_labor' etc., in the formula?
 
Upvote 0
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.
 
Upvote 0
You couldn't use I directly in the formula but you could use it when constructing the formula.

Here's a simple example.
Code:
For I = 1 To 10

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

Next I
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top