Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / November 2007

Tip: Looking for answers? Try searching our database.

Can I Write A Macro That Will Execute Fomula In The First Empty Ce

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
tomaski - 06 Nov 2007 15:19 GMT
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!!
Joel - 06 Nov 2007 17:01 GMT
This is an interesting macro.  It puts the countif function into yoiur
worksheet automatically.  Just select the upper left hand cell in the range
of grades.  The macro look for the first empty column and adds the number of
A's in the row (will vary depending on number of grades in the row).  Then
moves over one row and does B's, then C's, and then D's.  It repeats this for
all rows until it finds the first empty row.

Sub count_results()

Set topleft = ActiveCell
Lastrow = topleft.End(xlDown).Row
LastColumn = topleft.End(xlToRight).Column
ColumnA = LastColumn + 1
For RowCount = topleft.Row To Lastrow
  For Columnoffset = 0 To 3
     Select Case Columnoffset
        Case 0
           Grade = """A"""
        Case 1
           Grade = """B"""
        Case 2
           Grade = """C"""
        Case 3
           Grade = """D"""
     End Select
     Formula = "=Countif(R" & RowCount & "C" & topleft.Column & _
        ":R" & RowCount & "C" & LastColumn & "," & Grade & ")"
     Cells(RowCount, ColumnA + Columnoffset).FormulaR1C1 = Formula
  Next Columnoffset
Next RowCount
End Sub

> Hi,
>
[quoted text clipped - 15 lines]
>
> Hope this is clear enough. Any answer will be appreciated!!
rogerchengnjit@gmail.com - 16 Nov 2007 21:42 GMT
> This is an interesting macro.  It puts the countif function into yoiur
> worksheet automatically.  Just select the upper left hand cell in the range
[quoted text clipped - 47 lines]
>
> > Hope this is clear enough. Any answer will be appreciated!!

Just want to say I really thank you for your help!
I am starting reading some VB and VBA books now and I hope I can be as
good as you^^
rogerchengnjit@gmail.com - 16 Nov 2007 21:47 GMT
> This is an interesting macro.  It puts the countif function into yoiur
> worksheet automatically.  Just select the upper left hand cell in the range
[quoted text clipped - 47 lines]
>
> > Hope this is clear enough. Any answer will be appreciated!!

Just want to say I really appreciate your help!

Sorry I have been busy for a while and it is kind of late.

You are the best and I am reading some VBA and VB books now.

Hope I can be as good as you^^
JLatham - 06 Nov 2007 17:09 GMT
"Is there any macro...?"  There is now!

The macro below will do what you want.  You could even expand it later to
permit more answer options - it's set for 4 options now in array
possibleAnswers().

Press [Alt]+[F11] to open the VB Editor, choose Insert | Module and copy and
paste the code into the module presented.  Choose the sheet with the student
answer entries in it, and use Tools | Macro | Macros to run the macro.  It
presumes that NO percentages have been calculated for any row when you run it.
Also presumes that QID is in column A and all that follow it on a row are
answers entered by students when it begins.

Sub CalcResults()
'must start with QID and answer inputs ONLY
'you'll need to clear any previous entries
'on ALL rows before using this
'
'you must have the data sheet selected
'before running this Macro
'
 Const firstAnswerRow = 2 ' first row # with question data
 Dim possibleAnswers(1 To 4) As String
 Dim resultsChosenCounts() As Long
 Dim lastRow As Long
 Dim lastCol As Long
 Dim maxColumns As Long
 Dim baseCell As Range
 Dim rOffset As Long
 Dim cOffset As Long
 Dim numEntries As Long
 Dim selectionCount As Long
 Dim ALC As Integer ' array loop counter
 
'initialize possibleAnswers() array
 possibleAnswers(1) = "a" ' case is not important
 possibleAnswers(2) = "b"
 possibleAnswers(3) = "c"
 possibleAnswers(4) = "d"
 ReDim resultsChosenCounts(LBound(possibleAnswers) To _
  UBound(possibleAnswers)) ' dimensions must match
 If Val(Left(Application.Version, 2)) < 12 Then
   'in Excel 2003 or earlier
   lastRow = Range("A" & Rows.Count).End(xlUp).Row
   maxColumns = Columns.Count
 Else
   'in Excel 2007 (or later)
   lastRow = Range("A" & Rows.CountLarge).End(xlUp).Row
   maxColumns = Columns.Count
 End If
 If lastRow < firstAnswerRow Then
   Exit Sub ' no question data entered
 End If
 Set baseCell = ActiveSheet.Range("A" & firstAnswerRow)
 'start looping through used rows
 Do Until rOffset + baseCell.Row > lastRow
   lastCol = baseCell.Offset(rOffset, 0).End(xlToRight).Column
   numEntries = lastCol - 1 ' assumes all are question results
   If lastCol > (maxColumns - UBound(resultsChosenCounts)) Then
     MsgBox "Not Enough Columns to Present all Results.", _
      vbOKOnly, "Aborting"
     Set baseCell = Nothing
     Exit Sub
   End If
   'reset/initialize
   selectionCount = 0
   cOffset = 1 ' start looking in Col B
   For ALC = LBound(resultsChosenCounts) To _
    UBound(resultsChosenCounts)
     resultsChosenCounts(ALC) = 0 ' reset
   Next
   For ALC = LBound(possibleAnswers) To UBound(possibleAnswers)
     For cOffset = 1 To lastCol - 1
       If UCase(Trim(baseCell.Offset(rOffset, cOffset))) = _
        UCase(Trim(possibleAnswers(ALC))) Then
         resultsChosenCounts(ALC) = resultsChosenCounts(ALC) + 1
       End If
     Next
   Next ' end ALC loop
   For ALC = LBound(resultsChosenCounts) To _
    UBound(resultsChosenCounts)
     baseCell.Offset(rOffset, (lastCol - 1) + ALC) = _
      resultsChosenCounts(ALC) / numEntries
     baseCell.Offset(rOffset, (lastCol - 1) + ALC).Style = _
      "Percent"
   Next ' end ALC output loop
   rOffset = rOffset + 1 ' move to next row
 Loop ' row loop
End Sub

> Hi,
>
[quoted text clipped - 15 lines]
>
> Hope this is clear enough. Any answer will be appreciated!!
rogerchengnjit@gmail.com - 07 Nov 2007 19:46 GMT
On Nov 6, 12:09 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
wrote:
> "Is there any macro...?"  There is now!
>
[quoted text clipped - 105 lines]
>
> > Hope this is clear enough. Any answer will be appreciated!!

Thanks so much for your reply!!!! That is really a lifesaver!!!!

The code will calculate only if answers are filled. Is there anyway to
perform more accurate calculation if some answers are left blank?

Once again, thanks for your prompt reply!!!!!
JLatham - 07 Nov 2007 22:18 GMT
Should not be too difficult to change the code to do that - I presume that
you mean a situation like this (for first row of 'b's) that if it were b b  
b then total answers would be 3 vs 4?  To expand further, then if inputs were
a b   c the results would be:
%a = 33.33%  %b = 33.33%  %c = 33.33% %d = 0%

> On Nov 6, 12:09 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
> wrote:
[quoted text clipped - 114 lines]
>
> Once again, thanks for your prompt reply!!!!!
rogerchengnjit@gmail.com - 07 Nov 2007 23:13 GMT
On Nov 7, 5:18 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
wrote:
> Should not be too difficult to change the code to do that - I presume that
> you mean a situation like this (for first row of 'b's) that if it were b b
[quoted text clipped - 121 lines]
>
> > Once again, thanks for your prompt reply!!!!!

Yes. That's exactly what I meant.
Thanks for your neat code!!!
JLatham - 08 Nov 2007 02:12 GMT
In that case, these modifications in the way we find the last used column
(looking from right edge of sheet back to the left) and the way we count
entries found will do the calculations the way you want:

Sub CalcResults()
'must start with QID and answer inputs ONLY
'you'll need to clear any previous entries
'on ALL rows before using this
'
'you must have the data sheet selected
'before running this Macro
'
 Const firstAnswerRow = 2 ' first row # with question data
 Dim possibleAnswers(1 To 4) As String
 Dim resultsChosenCounts() As Long
 Dim lastRow As Long
 Dim lastCol As Long
 Dim maxColumns As Long
 Dim baseCell As Range
 Dim rOffset As Long
 Dim cOffset As Long
 Dim numEntries As Long
 Dim selectionCount As Long
 Dim ALC As Integer ' array loop counter
 
'initialize possibleAnswers() array
 possibleAnswers(1) = "a" ' case is not important
 possibleAnswers(2) = "b"
 possibleAnswers(3) = "c"
 possibleAnswers(4) = "d"
 ReDim resultsChosenCounts(LBound(possibleAnswers) To _
  UBound(possibleAnswers)) ' dimensions must match
 If Val(Left(Application.Version, 2)) < 12 Then
   'in Excel 2003 or earlier
   lastRow = Range("A" & Rows.Count).End(xlUp).Row
   maxColumns = Columns.Count
 Else
   'in Excel 2007 (or later)
   lastRow = Range("A" & Rows.CountLarge).End(xlUp).Row
   maxColumns = Columns.Count
 End If
 If lastRow < firstAnswerRow Then
   Exit Sub ' no question data entered
 End If
 Set baseCell = ActiveSheet.Range("A" & firstAnswerRow)
 'start looping through used rows
 Do Until rOffset + baseCell.Row > lastRow
   numEntries = 0 ' initialize/reset
   lastCol = baseCell.Offset(rOffset, Columns.Count -
baseCell.Column).End(xlToLeft).Column
   If lastCol > (maxColumns - UBound(resultsChosenCounts)) Then
     MsgBox "Not Enough Columns to Present all Results.", _
      vbOKOnly, "Aborting"
     Set baseCell = Nothing
     Exit Sub
   End If
   'reset/initialize
   selectionCount = 0
   cOffset = 1 ' start looking in Col B
   For ALC = LBound(resultsChosenCounts) To _
    UBound(resultsChosenCounts)
     resultsChosenCounts(ALC) = 0 ' reset
   Next
   For ALC = LBound(possibleAnswers) To UBound(possibleAnswers)
     For cOffset = 1 To lastCol - 1
       If UCase(Trim(baseCell.Offset(rOffset, cOffset))) = _
        UCase(Trim(possibleAnswers(ALC))) Then
         resultsChosenCounts(ALC) = resultsChosenCounts(ALC) + 1
         numEntries = numEntries + 1
       End If
     Next
   Next ' end ALC loop
   For ALC = LBound(resultsChosenCounts) To _
    UBound(resultsChosenCounts)
     baseCell.Offset(rOffset, (lastCol - 1) + ALC) = _
      resultsChosenCounts(ALC) / numEntries
     baseCell.Offset(rOffset, (lastCol - 1) + ALC).Style = _
      "Percent"
   Next ' end ALC output loop
   rOffset = rOffset + 1 ' move to next row
 Loop ' row loop
End Sub

> On Nov 7, 5:18 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
> wrote:
[quoted text clipped - 126 lines]
> Yes. That's exactly what I meant.
> Thanks for your neat code!!!
rogerchengnjit@gmail.com - 08 Nov 2007 19:00 GMT
On Nov 7, 9:12 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
wrote:
> In that case, these modifications in the way we find the last used column
> (looking from right edge of sheet back to the left) and the way we count
[quoted text clipped - 210 lines]
> > Yes. That's exactly what I meant.
> > Thanks for your neat code!!!

Thanks A LOT!!!!
rogerchengnjit@gmail.com - 08 Nov 2007 20:12 GMT
On Nov 7, 9:12 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
wrote:
> In that case, these modifications in the way we find the last used column
> (looking from right edge of sheet back to the left) and the way we count
[quoted text clipped - 210 lines]
> > Yes. That's exactly what I meant.
> > Thanks for your neat code!!!

How did you learn the language? You are so good!! Thanks!!!
JLatham - 09 Nov 2007 00:14 GMT
Self taught BASIC on a TRS-80 Model I about 1979-80, been programming in some
dialect of it ever since.  The real trick to programming is learning to
define the problem - that was taught to me through formal training, initially
using FORTRAN and then Honeywell 6000 Assembly Language.  I just kept at it
in various languages for many years.  And here I am.  There are many others
hanging about in here that are easily as skilled as I, and just as many more
that I keep learning from in these and other places around the web.

Glad I was able to help.  Enjoy.

> On Nov 7, 9:12 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
> wrote:
[quoted text clipped - 214 lines]
>
> How did you learn the language? You are so good!! Thanks!!!
rogerchengnjit@gmail.com - 09 Nov 2007 14:48 GMT
On Nov 8, 7:14 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
wrote:
> Self taught BASIC on a TRS-80 Model I about 1979-80, been programming in some
> dialect of it ever since.  The real trick to programming is learning to
[quoted text clipped - 222 lines]
>
> read more ?

Hi,

I really enjoy this!! Actually I modified your code to something like
this:

Sub CalcResults()
'must start with QID and answer inputs ONLY
'you'll need to clear any previous entries
'on ALL rows before using this
'
'you must have the data sheet selected
'before running this Macro
'
 Const firstAnswerRow = 2 ' first row # with question data
 Dim possibleAnswers(1 To 4) As String
 Dim resultsChosenCounts() As Long
 Dim lastRow As Long
 Dim lastCol As Long
 Dim maxColumns As Long
 Dim baseCell As Range
 Dim rOffset As Long
 Dim cOffset As Long
 Dim numEntries As Long
 Dim selectionCount As Long
 Dim ALC As Integer ' array loop counter

'initialize possibleAnswers() array
 possibleAnswers(1) = "a" ' case is not important
 possibleAnswers(2) = "b"
 possibleAnswers(3) = "c"
 possibleAnswers(4) = "d"
 ReDim resultsChosenCounts(LBound(possibleAnswers) To _
  UBound(possibleAnswers)) ' dimensions must match
 If Val(Left(Application.Version, 2)) < 12 Then
   'in Excel 2003 or earlier
   lastRow = Range("A" & Rows.Count).End(xlUp).Row
   maxColumns = Columns.Count
 Else
   'in Excel 2007 (or later)
   lastRow = Range("A" & Rows.CountLarge).End(xlUp).Row
   maxColumns = Columns.Count
 End If
 If lastRow < firstAnswerRow Then
   Exit Sub ' no question data entered
 End If
 Set baseCell = ActiveSheet.Range("A" & firstAnswerRow)
 'start looping through used rows
 Do Until rOffset + baseCell.Row > lastRow
   numEntries = 0 ' initialize/reset
   lastCol = baseCell.Offset(rOffset, Columns.Count -
baseCell.Column).End(xlToLeft).Column
   If lastCol > (maxColumns - UBound(resultsChosenCounts)) Then
     MsgBox "Not Enough Columns to Present all Results.", _
      vbOKOnly, "Aborting"
     Set baseCell = Nothing
     Exit Sub
   End If

   'reset/initialize
   selectionCount = 0
   cOffset = 1 ' start looking in Col B
   For ALC = LBound(resultsChosenCounts) To _
    UBound(resultsChosenCounts)
     resultsChosenCounts(ALC) = 0 ' reset
   Next
   For ALC = LBound(possibleAnswers) To UBound(possibleAnswers)
     For cOffset = 1 To lastCol - 1

       If UCase(Trim(baseCell.Offset(rOffset, cOffset))) = _
        UCase(Trim(possibleAnswers(ALC))) Then
         resultsChosenCounts(ALC) = resultsChosenCounts(ALC) + 1
         numEntries = numEntries + 1
       End If

     Next
   Next ' end ALC loop

   For ALC = LBound(resultsChosenCounts) To _
    UBound(resultsChosenCounts)

     baseCell.Offset(rOffset, (lastCol - 1) + ALC) = _
      resultsChosenCounts(ALC) / numEntries
     baseCell.Offset(rOffset, (lastCol - 1) + ALC).Style = _
      "Percent"

   Next ' end ALC output loop

   rOffset = rOffset + 1 ' move to next row
 Loop ' row loop

 baseCell.Offset(-1, lastCol).Value = "A%"
 baseCell.Offset(-1, lastCol + 1).Value = "B%"
 baseCell.Offset(-1, lastCol + 2).Value = "C%"
 baseCell.Offset(-1, lastCol + 3).Value = "D%"
End Sub

I found something that if the last column is empty the macro will
insert the result there. I know it is something to do with the loop
you wrote.
Can you please give me a hint how to insert a condition so it will go
to the right column to present the right result?

Thanks.
JLatham - 09 Nov 2007 22:01 GMT
OK, 2 hints:
#1 - preferred posting method in these discussions is top posting vs bottom
posting.  This way other readers can see latest info without having to scroll
down pages and pages.
#2 - to get output the way you want, use this code instead of what I gave
you previously <g>

Sub CalcResults()
'Row 1 must have a label for each possible
'answer in all rows below even if there are
'empty cells in some of the cells in the
'columns in the results rows (row 2 to end)
'
'must start with QID and answer inputs ONLY
'you'll need to clear any previous entries
'on ALL rows before using this
'
'you must have the data sheet selected
'before running this Macro
'
 Const firstAnswerRow = 2 ' first row # with question data
 Dim possibleAnswers(1 To 4) As String
 Dim resultsChosenCounts() As Long
 Dim lastRow As Long
 Dim lastCol As Long
 Dim maxColumns As Long
 Dim baseCell As Range
 Dim rOffset As Long
 Dim cOffset As Long
 Dim numEntries As Long
 Dim selectionCount As Long
 Dim ALC As Integer ' array loop counter
 Dim firstAnswerResultColumn As Long
 
'initialize possibleAnswers() array
 possibleAnswers(1) = "a" ' case is not important
 possibleAnswers(2) = "b"
 possibleAnswers(3) = "c"
 possibleAnswers(4) = "d"
 ReDim resultsChosenCounts(LBound(possibleAnswers) To _
  UBound(possibleAnswers)) ' dimensions must match
 
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
 maxColumns = Columns.Count
 If lastRow < firstAnswerRow Then
   Exit Sub ' no question data entered
 End If
 Set baseCell = ActiveSheet.Range("A" & firstAnswerRow)
 'determine column to begin displaying results in
 'based on labels in row 1
 firstAnswerResultColumn = Range("A1").Offset(0, _
  Columns.Count - 1).End(xlToLeft).Column - _
  LBound(resultsChosenCounts)
 
 'start looping through used rows
 Do Until rOffset + baseCell.Row > lastRow
   numEntries = 0 ' initialize/reset
   lastCol = baseCell.Offset(rOffset, Columns.Count - _
    baseCell.Column).End(xlToLeft).Column
   If lastCol > (maxColumns - UBound(resultsChosenCounts)) Then
     MsgBox "Not Enough Columns to Present all Results.", _
      vbOKOnly, "Aborting"
     Set baseCell = Nothing
     Exit Sub
   End If
   'reset/initialize
   selectionCount = 0
   cOffset = 1 ' start looking in Col B
   For ALC = LBound(resultsChosenCounts) To _
    UBound(resultsChosenCounts)
     resultsChosenCounts(ALC) = 0 ' reset
   Next
   For ALC = LBound(possibleAnswers) To UBound(possibleAnswers)
     For cOffset = 1 To lastCol - 1
       If UCase(Trim(baseCell.Offset(rOffset, cOffset))) = _
        UCase(Trim(possibleAnswers(ALC))) Then
         resultsChosenCounts(ALC) = resultsChosenCounts(ALC) + 1
         numEntries = numEntries + 1
       End If
     Next
   Next ' end ALC loop
   'firstAnswerResultColumn
   For ALC = LBound(resultsChosenCounts) To _
    UBound(resultsChosenCounts)
     baseCell.Offset(rOffset, firstAnswerResultColumn + ALC) = _
      resultsChosenCounts(ALC) / numEntries
     baseCell.Offset(rOffset, firstAnswerResultColumn + ALC).Style = _
      "Percent"
   Next ' end ALC output loop
   rOffset = rOffset + 1 ' move to next row
 Loop ' row loop
End Sub

> On Nov 8, 7:14 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
> wrote:
[quoted text clipped - 294 lines]
>
>         If UCase(Trim(baseCell.Offset(rOffset, cOffset))) = _
rogerchengnjit@gmail.com - 16 Nov 2007 21:50 GMT
On Nov 9, 5:01 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
wrote:
> OK, 2 hints:
> #1 - preferred posting method in these discussions is top posting vs bottom
[quoted text clipped - 240 lines]
>
> read more >>

By the way...how to do top posting? I am new to this so I donot really
know how to do that. Sorry...
JLatham - 16 Nov 2007 23:39 GMT
I usually come in through this web page with my browser:
http://www.microsoft.com/office/community/en-us/default.mspx
Then when replying to messages you simply type your entry at the top of it.  
If you're coming in through other groups or via newsreader it would be
different.  No big deal, I can deal with it myself, but some do shy away from
bottom posted responses.

Big question is whether or not that last code I provided worked for you or
not <g>.

> On Nov 9, 5:01 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)>
> wrote:
[quoted text clipped - 245 lines]
> By the way...how to do top posting? I am new to this so I donot really
> know how to do that. Sorry...
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.