Using Excel Function with VBA

Question

I'm trying to write this function in a cell:

=CONCATENATE("Blah",ROUND(COUNTA(I26:I34)/COUNTA(I4:I24,I26:I34)*100,0),"%")

And here's what I have right now ( I put them in separate lines so it's easier to read):

DestBook.Worksheets("Sheet1").Range("I2").Value = 
DestBook.Application.WorksheetFunction.Concatenate("Blah ", 
WorksheetFunction.Round(WorksheetFunction.CountA("I4:I24") / 
WorksheetFunction.CountA("I4:I24", "I26:I24") * 100, 0), "%")

And it doesn't seem to like it since it gave this error: Object Doesn't support this property or method

I'm not sure if it's that I'm setting the value incorrectly or if my formula is wrongfully translated. Can anyone take a look at it and see what's wrong with it? Thanks!

I4:I34 in Excel, and I26:I24 in VBA? You're using different ranges... — Marc B
– Marc B, Commented Feb 12, 2013 at 22:21
Why not use the & operator, and Round() VBA function? That's why they are there for. — John Alexiou
– John Alexiou, Commented Feb 12, 2013 at 22:22
And split your statement out in multiple steps, to figure out where the problem is. — John Alexiou
– John Alexiou, Commented Feb 12, 2013 at 22:25

John Alexiou · Accepted Answer · 2013-02-12 22:32:02Z

3

Try this:

Dim r1 As Range, r2 as Range, r3 as Range
Set r1 = DestBook.Worksheets("Sheet1").Range("I4:I24")
Set r2 = DestBook.Worksheets("Sheet1").Range("I26:I34")
Set r3 = DestBook.Worksheets("Sheet1").Range("I2")

Dim S1 as Double, S2 as Double
S1 = WorksheetFunction.CountA(r1)
S2 = WorksheetFunction.CountA(r1, r2)

r3.Value = "Blah " & Format(S1 / S2 * 100, "0") & "%"

I tested it and it works.

answered Feb 12, 2013 at 22:32

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using Excel Function with VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related