VBA - ranges -

[heathball]

Im learning a little VBA, but I'm stuck with a range issue.

Im starting with columns A,B,C
and i want to achieve D

So my range is effectively in column A (each name change represents a new 'range' - and they are in alphabetical order down the page)
I'm currently using an 'average if' helper column in macros to help me with this, but the problem is i have big files (over 150,000 rows), and 'average if' takes a very long time (I do many of these during a macro)

within my code, im trying to use variations of 'dim' but I'm not getting anywhere.

Is there something i use with 'dim' to select column A, and use each name (each range begins with a name change moving down the column) as individual ranges, so that when i apply a function or sum in column D, and run it down the page in my macro, i can get the result as per the table below? then i can add this code ( i need this for many things, on different sheets and different workbooks, and if the range column "A" changes, i can change that reference in the code to suit the file )

I would like to use it with the various functions i use now ('if' 'and' 'or' etc) and I"m looking for something flexible that excludes specific object references to worksheet/column names etc.

Is something possible with this, or am i stuck with my slow average-if helper columns?

A NAME	B High	C Low	D high minus low
barry1000			6
barry1000	15		6
barry1000		9	6
barry2000		4	4
barry2000	8		4
barry3000			6
barry3000	12		6
barry3000			6
barry3000			6
barry3000		6	6
barry4000	26		17
barry4000		9	17

<tbody>
</tbody>

 

[Fluff]

Whilst I don't understand what you are trying to do, this will give you the difference between col B & col C

Sub GetDifference()
   Dim ary As Variant
   Dim i As Long
   
   ary = Range("A2", Range("A" & Rows.count).End(xlUp).Offset(, 3))
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(ary)
         .Item(ary(i, 1)) = .Item(ary(i, 1)) + ary(i, 2) - ary(i, 3)
      Next i
      For i = 1 To UBound(ary)
         ary(i, 4) = .Item(ary(i, 1))
      Next i
      Range("D2").Resize(UBound(ary)).Value = Application.Index(ary, , 4)
   End With
End Sub

 

[heathball]

thanks for your reply. Perhaps my explanation was not very clear.

Your code works when i transfer the above table in my example to an excel sheet.

but when i use it for one job i have which looks like the table below ( there are 40,000 rows) ...i am trying to arrive at the (b-c) sum total in column D for each range in column A, ( i have added the result in column D) ...where column A is what i call the 'range' column, so each entry eg "alb_1050" is a range, and within each range, there will always only be one value in col B, and 1 value in column C, and the result of B-C will be on every row in col D, referring to the 'range' in col A.

Perhaps i have the wrong term when i use 'range' - I'm not sure how else to say that column A contains entries in alphabetical order, and each different name - i would like to be a separate 'range.'

the line in the code that is in debug yellow is .Item(ary(i, 1)) = .Item(ary(i, 1)) + ary(i, 2) - ary(i, 3)

I hope this helps and I"m looking online for the definition of 'ary' and 'ubound' ...very interesting stuff

A	B	C	D
alb_1050		-0.562083333	1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050			1.3
alb_1050	0.737916667		1.3
alb_1100		-0.928823529	1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100			1.62
alb_1100	0.691176471		1.62

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Fluff]

What error message did you get?

 

[heathball]

run time error '13'
type mismatch

 

[Fluff]

Do cols B:C contain formulae that return "" ?

 

[heathball]

cols B:C contain no formulae, and are pasted values

in the macros I'm currently using and developing, i paste values for everything before each step.

i would like to sample and try whatever portion of code works within the vba code i use, and change the column references etc.

 

[heathball]

so that means the cells that are empty are actually 'empty cells' and the values are non-formulae pasted values

 

[Fluff]

I suspect that either the cells are not blank, or the numbers are not numbers.
in col D put =isblank(B2) where B2 is a blank cell, what is the result?

 

[Fluff]

Maybe

Sub GetDifference()
   Dim ary As Variant
   Dim i As Long
   
   ary = Range("A1", Range("A" & Rows.count).End(xlUp).Offset(, 3))
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(ary)
         .Item(ary(i, 1)) = .Item(ary(i, 1)) + val(ary(i, 2)) - val(ary(i, 3))
      Next i
      For i = 1 To UBound(ary)
         ary(i, 4) = .Item(ary(i, 1))
      Next i
      Range("D1").Resize(UBound(ary)).Value = Application.Index(ary, , 4)
   End With
End Sub