0

I am setting up an excel worksheet to automatically populate office rotations for an incoming class of graduate students. The input will be their choices of professors and the number of open seats the professors have. I want to update the value of the number of open seats the professors have as I assign a rotation slot to each student, but the code seems to be breaking when I attempt to assign a value to a cell outside that of the calling cell.

I have tried switching between subs and functions to no avail. It seems as though VBA simply does not allow changing the value of a cell from a different cell. Is this true?

    Sub DecrementSeats(PIName As String, RotNum As Integer)

        Dim i As Integer, seats As Integer
        Dim c As Range
        seats = 0
        i = 2
        MsgBox ("In decrement")

        For Each c In Worksheets("Student Interest and Rotations").Range("A2:A41")  
            If c.Value = PIName Then
                 MsgBox (c.Value & " is the same as " & PIName)
                 seats = c.Offset(0, (1 + RotNum)).Value
                 MsgBox ("Original seats value is: " & seats)
                 seats = seats - 1
                 c.Offset(0, (1 + RotNum)) = seats
                 MsgBox ("New c.Value value is: " & c.Offset(0, (1 + RotNum)).Value)
                 Exit For
            End If
        MsgBox (c.Value & " is not the same as " & PIName & ". Checking next PI name...")
        Next c

    End Sub
Miles Fett
  • 711
  • 4
  • 17
tgoetjen
  • 1
  • 2
  • Yes, for most purposes, but there are exceptions eg https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet – SJR Sep 03 '19 at 16:44
  • 3
    A sub should work though, it's a function that is problematic. – SJR Sep 03 '19 at 16:48
  • What goes wrong? Can you clarify "but the code seems to be breaking when I attempt to assign a value to a cell outside that of the calling cell"? With a Sub, you can usually edit any cell you desire...? – BruceWayne Sep 03 '19 at 16:51
  • Sure! Sorry for being vague. The message box line: MsgBox ("New c.Value value is: " & c.Offset(0, (1 + RotNum)).Value) never shows up, but the one two lines prior appears just fine – tgoetjen Sep 03 '19 at 17:32
  • This is not the entirety of the code, there is a call to this sub within a function. The `c.Offset(0, ...)` appears to not get updated, the original value in the spreadsheet remains – tgoetjen Sep 03 '19 at 21:15
  • I ended up trying a modified version of that shown here: https://stackoverflow.com/questions/15659779/set-a-cell-value-from-a-function and that seems to have worked! – tgoetjen Sep 03 '19 at 21:46

0 Answers0