CODEWITHSUNDEEP
excelvba
user974047
user974047

Reputation: 2295

Using Excel Function with VBA

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!

Upvotes: 1

Views: 349

Answers (1)

John Alexiou
John Alexiou

Reputation: 29274

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.

Upvotes: 3

Related Questions