Macro to build formula in the first empty cell

[SANJINJIT]

Hi,

I have a spreadsheet looks like this:

QID     C1   C2   C3   C4   %a    %b     %c   %d
13123   b    b    b    b    0%    100%   0%   0%
13124   d    d    d    d    0%    0%     0%   100% 
13125   d    d    d    d    0%    0%     0%   100%

QID is the question ID and C1 to C4 are the answers by students. Column "%a" is the percentage of "a" answered by students. I use countif function to calculate that.

Everytime I copy and paste a group of answers the number of questions ( number of rows) and number of answers (number of columns) will be different.
Is there any macro that will calculate (%a, %b, %c and %d) and insert the result in the first empty column?

Hope this is clear enough. Any answer will be appreciated!!

Thanks a lot!!!

[kadghar]

...
Everytime I copy and paste a group of answers the number of questions ( number of rows) and number of answers (number of columns) will be different.
Is there any macro that will calculate (%a, %b, %c and %d) and insert the result in the first empty column?
...
Thanks a lot!!!

well there's for sure a macro that does that, the problem is to make it. Why dont you give it a try by yourself and answer a little bit more specific questions.

Use a couple of DO or FOR, use CELLS, may be writing the range into a variant should be good...

Check this little example, I hope it'll help you to figure out how to do what you want.

dim i as long
dim dou1 as double
dim a
a = range(cells(2,2), cells(2,2).end(-4121))
 
for i = lbound(a) to ubound(a)
    dou1=dou1+a(i,1)
next
i=1
do
    i=i+1
loop until cells(2,i)=""
 
cells(1,1)=dou1 / (ubound(a)-lbound(a)+1)
 

[SANJINJIT]

well there's for sure a macro that does that, the problem is to make it. Why dont you give it a try by yourself and answer a little bit more specific questions.

Use a couple of DO or FOR, use CELLS, may be writing the range into a variant should be good...

Check this little example, I hope it'll help you to figure out how to do what you want.

dim i as long
dim dou1 as double
dim a
a = range(cells(2,2), cells(2,2).end(-4121))
 
for i = lbound(a) to ubound(a)
    dou1=dou1+a(i,1)
next
i=1
do
    i=i+1
loop until cells(2,i)=""
 
cells(1,1)=dou1 / (ubound(a)-lbound(a)+1)
 

Thanks for reply.

I use the following macro trying to insert the formula but it wont allow me.

Is there anything wrong?

Sub InsertFomula()
Set r = ActiveSheet.UsedRange
'Dim COUNTA As Integer
nLastRow = r.Rows.Count + r.Row - 1
nLastColumn = r.Columns.Count + r.Column - 1
For i = 1 To nLastColumn
  For j = 1 To nLastRow
    If Cells(1, i).Value <> "*" Then
      Cells(1, nLastColumn + 1).Formula = "=countif(Cells(1, nLastColumn + 1),"a"/nLastColumn"
      Selection.AutoFill Destination:=Cells(1, nLastColumn + 1), Type:=xlFillDefault'
      Cells(1, nLastColumn + 1).Select
    End If
  Next
Next
End Sub

[kadghar]

Thanks for reply.

I use the following macro trying to insert the formula but it wont allow me.

Is there anything wrong?

first of all, the cells you're changing have nothing to do with the range you defined. you're just changing cells on the active sheet. (i dont know if that's what you wanted)

And the formula you're writing has a few mistakes. try using FormulaRC instead.