Evaluate as in a cell

[KillGorack]

Hey,


Previous answer showed this is possible, but tried the code below, but when ran each cell has #VALUE! within.

any ideas?

Sub populateBindField()

    ' ===============================================================
    ' Declarations
    ' ===============================================================
        Dim x As Integer
        Dim tempheight As Integer
        Dim tempwidth As Integer
        Dim r As String
    ' ===============================================================
    ' Size it up
    ' ===============================================================
        tempheight = ThisWorkbook.Sheets("raw data").Cells(Rows.Count, 1).End(xlUp).Row
        tempwidth = ThisWorkbook.Sheets("raw data").Cells(1, Columns.Count).End(xlToLeft).Column
    ' ===============================================================
    ' Subtotals.
    ' ===============================================================
        For x = 21 To tempwidth
            r = "SUBTOTAL(9, RANGE(ThisWorkbook.Sheets(" & Chr(34) & "raw data" & Chr(34) & ").cells(2, " & x & "), ThisWorkbook.Sheets(" & Chr(34) & "raw data" & Chr(34) & ").cells(" & tempheight & ", " & x & ")))"
            ThisWorkbook.Sheets("output").Cells(36, x - 20).Value = Evaluate(r)
        Next x
    ' ===============================================================
  
End Sub

 

[Anthony47]

Which "formula" would you like to obtain and be evaluated??

 

[KillGorack]

Sorry, the subtotal isn't working when I evaluate it. Starting with r=.

Or an example of evaluating a subtotal using a range defined without column letters but numbers instead. Does that make sense?

 

[Anthony47]

You should get in R something like SUBTOTAL(9,'raw data'!C4:C9) but I cannot guess which area you wish to calculate, and reverse engineering your r = etc etc didn't help

 

[Anthony47]

Maybe

            r = "SUBTOTAL(9," & Chr(39) & "raw data'!" & Cells(2, x).Address & ":" & Cells(tempheight, x).Address & ")"

This will create in r: SUBTOTAL(9,'raw data'!$U$2:$U$9)
(tempheight is 9 in my test sheet)

Bye

 

[KillGorack]

You should get in R something like SUBTOTAL(9,'raw data'!C4:C9) but I cannot guess which area you wish to calculate, and reverse engineering your r = etc etc didn't help

Understood I have too much code there.. too the point.. in your code where you have the letter "C" is my issue, I want to use numbers instead.

So instead of using

=SUBTOTAL(9, 'raw data'!U2:U1890)

I need to set the range using numbers instead similar to;

Sheets("output").Cells(36, x - 20).Value = Evaluate(SUBTOTAL(9, RANGE(Sheets("raw data").cells(2, 21), Sheets("raw data").cells(1890, 21))))

Is it more clear? I cant get the code above to work either.

 

[Anthony47]

Did you see my message in post #5, timed 2 minutes before your latest one?

 

[KillGorack]

Did you see my message in post #5, timed 2 minutes before your latest one?

No i didn't trying now,...

<edit>
Yup that works.. new sytax for me I will get my head around it.. Freaking awesome.
</edit>

 

[KillGorack]

Maybe

            r = "SUBTOTAL(9," & Chr(39) & "raw data'!" & Cells(2, x).Address & ":" & Cells(tempheight, x).Address & ")"

This will create in r: SUBTOTAL(9,'raw data'!$U$2:$U$9)
(tempheight is 9 in my test sheet)

Bye

Yea OK my issue was trying to add VB code into a cell somehow.. that looks more like a cell function.. it was a total brain burp.. thanks!

 

[Anthony47]

..and of course you could use also WorksheetFunction:

ThisWorkbook.Sheets("output").Cells(36, x - 20).Value = Application.WorksheetFunction.Subtotal(9, Sheets("raw data").Cells(2, x).Resize(tempheight, 1))

There are many options, so keep trying, it's the only way for learning

Bye