Compile a variable amount of Longs or Strings

showboater99 0
Viewed 24

At the beginning of my code, I define Longs to establish the addresses of cells

Dim rnum1 As Long
Dim cnum1 As Long
Dim rnum2 As Long
Dim cnum2 As Long

to be used to mimic an autosum and it works perfectly fine to sum a range of glass Square Footage.

ActiveCell.FormulaR1C1 = "=SUM(R" & rnum1 & "C" & cnum1 & ":R" & rnum2 & "C" & cnum2 & ")"

Glass Sheet

Looking at the image, the above code is used in H14 and H21 to add up the Area of their perspective Glass Types.

I'm now attempting to sum up the values in H14 (108.65) and H21 (32.73) in H22. The trouble is that there could be 2 Glass Types or 10 Glass Types. I need to somehow assess the number of Glass Types and compile their cell addresses into some sort of string. I'm just becoming aware of data types like string, long, boolean ect.

I'm not in need of any code at this point. Just someone who can bring me up to speed as to the hierarchy of these definitions.

vba excel
1 Answers

I'm pretty sure you could simplify your problem by just summing all and dividing by 2? So something like this at end of your macro...

Const zSumFormula = "=Sum(R[-1]C:R1C)/2"

Cells(rn2+2,cnum1).FormulaR1C1 = zSumFormula
Cells(rn2+2,cnum2).FormulaR1C1 = zSumFormula
pgSystemTester0
Related