Excel VBA Syntax

LaOs

Good afternoon everyone.

I'm not familiar with VBA at all, but have been given the following piece of code to accomplish a goal. It looks like it might work, except it encounters an error on a certain line. I'll paste the code:

Sub CopyTemplate()
Dim rng As Range, r As Range, temp As String, ws As Worksheet, msg As String
With Sheets("Entry")
    Set rng = .Range("c5", .Range("c5").End(xlToRight))
End With
For Each r In rng
    temp = r.Text
    If Len(temp) > 31 Then
        msg = vbLf & "Exceeds character limit"
[COLOR="Red"]    ElseIf[/COLOR]
        With CreateObject("VBScript.RegExp")
            .Pattern = "[:\\\?\[\]/\*]"
            If .test(temp) Then
                msg = msg & vbLf & "Used invalid character"
            End If
        End With
    Else
        On Error Resume Next
        Set ws = Sheets(temp)
        On Error GoTo 0
        If Not ws Is Nothing Then
            msg = msg & vbLf & "Already used"
        End If
    End If
    If Len(msg) Then
        MsgBox temp & msg
    Else
        Sheets("Template").Copy after:=Sheets(Sheets.Count)
        Sheets(Sheets.Count).Name = temp
    End If
    Set ws = Nothing: msg = ""
Next
End Sub

The code encounters an error when I try to execute it. The red line is highlighted and an dialogue box appears saying "Compile error: Syntax error" and all I can do is click OK or Help.

What is causing the error and how should it be corrected to maintain the intended functionality of the code?

Thanks for your help!

Daenris

Well... there's no If clause for that ElseIf...

LaOs

Daenris wrote: »

Well... there's no If clause for that ElseIf...

That's what I thought might be a problem... but then the problem is that I barely know enough about coding VBA to have barely been able to see that that might be the problem... and that was only because it was highlighted for me.

What I'm saying is that you are going to have to explain the why the problem is a problem at the same time as pointing out the problem. That's all before we even get to solving the problem...

whuppins

In VBA, ElseIf behaves syntactically more or less the same as If. It's a logical test and you've given it nothing to test. In other words, you need to put something after "ElseIf" that will determine if that branch gets executed or not.

Edit: On a much more basic level, consider the statement: "If you're good, I'll give you a cookie." That code has left out the "you're good" part of that sentence, so it doesn't make sense syntactically.

Edit again: The next question you may want to ask yourself is, "under what conditions is the CreateObject, etc. code allowed to run?" Only when len(temp) is 31 or below? Or is there an additional restriction that you'll need to consider before that bit is allowed to execute? If not, can't you just get rid of the ElseIf bit and move the whole "With CreateObject" block down into the "Else" section?

Daenris

In general you can have a few forms of If statements.

If some condition
  do stuff
end if

if some condition
 do stuff
Else
 do some other stuff because the condition isn't true
end if

if some condition
 do stuff
else if some other second condition
  do some other stuff because this second condition is true but the first isn't
else 
  do something else because neither the first or second conditions is true
end if

In this case the If is testing whether the length of the variable temp is greater than 31. if it is, it complains that it's too long. An ElseIf also needs to test a condition, but this code isn't giving any condition. It looks like it's trying to test whether the temp variable contains any illegal characters, but it doesn't look like it's set up right. The easiest solution would probably be to change it to something like this:

If Len(temp) > 31 Then
        msg = vbLf & "Exceeds character limit"
    Else
        With CreateObject("VBScript.RegExp")
            .Pattern = "[:\\\?\[\]/\*]"
            If .test(temp) Then
                msg = msg & vbLf & "Used invalid character"
            Else
               On Error Resume Next
               Set ws = Sheets(temp)
               On Error GoTo 0
               If Not ws Is Nothing Then
                  msg = msg & vbLf & "Already used"
               End If
          End If
        End With
End If

LaOs

I had the general understanding as you laid it out, but thanks for confirmin it for me, whuppins.

Daenris, thanks for the code.

So, it looks like the way it was originally written was trying to check for a second condition that it didn't really need to check for? From what I can tell, you've just told the code to check for one thing (length>31) and if it isn't, to just move on, with a sub-check (for improper characters) nested within the result for the first check being false?

I am about to test this code, but does combining all that stuff into the same With affect anything? What's a With do, anyway?

Daenris

LaOs wrote: »

I had the general understanding as you laid it out, but thanks for confirmin it for me, whuppins.

Daenris, thanks for the code.

So, it looks like the way it was originally written was trying to check for a second condition that it didn't really need to check for? From what I can tell, you've just told the code to check for one thing (length>31) and if it isn't, to just move on, with a sub-check (for improper characters) nested within the result for the first check being false?

I am about to test this code, but does combining all that stuff into the same With affect anything? What's a With do, anyway?

I believe the original code was first checking the length. If that was okay, it then should check for illegal characters, but I think they just had the ElseIf section written wrong. If the length is okay AND there are no illegal characters it goes on to the later code.

The rewrite I gave checks the length. If the length is okay, it goes into the Else, which then checks a second If to see if there are illegal characters. If there are not illegal characters, it continues to the Else with the code to be run.

LaOs

Okay, cool... I think I was following along correctly.

Your code change works, except I've run into a problem I had with a completely different attempt to do the same thing.

What this is doing is checking one sheet (Entry) and making copies of another sheet (Template) for as many names as are entered from C5 and beyond (C5, D5, E5...), renaming the new copy of Template the same as the entry in row 5.

It works great, except when there is only an entry in C5. If D5 is blank, even if there is an entry in E5, it hits Runtime error 1004: Application-defined or object-defined error. When I step into the code, a line is highlighted yellow:

Sub CopyTemplate()
Dim rng As Range, r As Range, temp As String, ws As Worksheet, msg As String
With Sheets("Entry")
    Set rng = .Range("c5", .Range("c5").End(xlToRight))
End With
For Each r In rng
    temp = r.Text
    If Len(temp) > 31 Then
        msg = vbLf & "Exceeds character limit"
    Else
        With CreateObject("VBScript.RegExp")
            .Pattern = "[:\\\?\[\]/\*]"
          If .test(temp) Then
                msg = msg & vbLf & "Used invalid character"
            Else
              On Error Resume Next
              Set ws = Sheets(temp)
              On Error GoTo 0
              If Not ws Is Nothing Then
                  msg = msg & vbLf & "Already used"
              End If
          End If
        End With
    End If
    If Len(msg) Then
        MsgBox temp & msg
    Else
        Sheets("Template").Copy after:=Sheets(Sheets.Count)
[COLOR="Red"]        Sheets(Sheets.Count).Name = temp[/COLOR]
    End If
    Set ws = Nothing: msg = ""
Next
End Sub

I have highlighted the line in red.

I know it would be incredibly strange, due to the fuction of this sheet, for there to be only one entry (C5 and nothing else), but why is this error happening? How would we suppress it?

What it appears to be doing is creating the copy for the entry in C5 just fine. But then it tries to create another copy, but doesn't know what to name it because D5 is blank, and then errors out? I really don't know...

whuppins

The error goes back to how .End works. To illustrate, put a single entry in C5 and put your cursor there. Now hit Ctrl + Right arrow (which is the equivalent of using .End). Your cursor goes to the end of the sheet instead of staying on C5. This is just how Excel works. When there's only one column in the range, the code nevertheless establishes the range as extending through the end of the sheet. It then gets to D5, finds a null value, and falls over.

One quick & dirty fix would be to add in a special if/then case that hard-codes the range as C5 only if D5 is blank. A slightly better solution would be to use a more flexible way to set rng. Try swapping in the following for Line 4:

Set rng = .Range(Cells(5, 3), Cells(5, 256 - Application.WorksheetFunction.CountBlank(Sheets("Entry").Range("C5:IV5"))))

That actually counts how many entries there are in C5 and beyond, and sets the range to extend that number of columns.

LaOs

whuppins wrote: »

The error goes back to how .End works. To illustrate, put a single entry in C5 and put your cursor there. Now hit Ctrl + Right arrow (which is the equivalent of using .End). Your cursor goes to the end of the sheet instead of staying on C5. This is just how Excel works. When there's only one column in the range, the code nevertheless establishes the range as extending through the end of the sheet. It then gets to D5, finds a null value, and falls over.

One quick & dirty fix would be to add in a special if/then case that hard-codes the range as C5 only if D5 is blank. A slightly better solution would be to use a more flexible way to set rng. Try swapping in the following for Line 4:

Set rng = .Range(Cells(5, 3), Cells(5, 256 - Application.WorksheetFunction.CountBlank(Sheets("Entry").Range("C5:IV5"))))

That actually counts how many entries there are in C5 and beyond, and sets the range to extend that number of columns.

Thanks so much for the explanation, whuppins! That makes sense, and I should have been able to figure that out myself.

Your code for line for fixes the problem when there is only one entry (C5). Thanks for that! Unfortunately, when there is an entry in C5, D5 is blank, but there is an entry in E5, the code errors again (1004: Application-defined or object-defined error (highlights line 26)).

Why does this cause an error? Is it because there are 2 entries in the range from C5 to the end, so it's going to try to make copies for 2 columns worth of entries (C5 and D5)? It doesn't seem to know that even though there are 2 entries in the whole range, there's actually a blank cell between the first and second entry... does that sound right? I have no idea, nor any idea how to fix it, but that seems to be the problem to me.

[Edit]
Now, this is more than likely never going to come up, but I know you and I both know that our users can do some strange things sometimes :P Thanks for your help!

whuppins

LaOs wrote: »

Why does this cause an error? Is it because there are 2 entries in the range from C5 to the end, so it's going to try to make copies for 2 columns worth of entries (C5 and D5)?

Yep, that's it. I didn't realize that would be a possibility. I'll get back to you in a bit with a better fix; you're certainly right about the creativity of users when it comes to breaking stuff we write for them

whuppins

Okay, here we go:

Sub CopyTemplate()
Dim c As Integer, temp As String, ws As Worksheet, msg As String
For c = 3 To 256
    If Sheets("Entry").Cells(5, c).Value <> "" Then
        temp = Sheets("Entry").Cells(5, c).Value
        If Len(temp) > 31 Then
            msg = vbLf & "Exceeds character limit"
        Else
            With CreateObject("VBScript.RegExp")
                .Pattern = "[:\\\?\[\]/\*]"
            If .test(temp) Then
                    msg = msg & vbLf & "Used invalid character"
                Else
                On Error Resume Next
                Set ws = Sheets(temp)
                On Error GoTo 0
                If Not ws Is Nothing Then
                    msg = msg & vbLf & "Already used"
                End If
            End If
            End With
        End If
        If Len(msg) Then
            MsgBox temp & msg
        Else
            Sheets("Template").Copy after:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = temp
        End If
        Set ws = Nothing: msg = ""
    End If
Next c
End Sub

That behaves more like your original code except instead of trying to isolate the range in which entries appear, it just checks every column. The code is then run on a case-by-case basis depending if there's an entry in the current column or not.

LaOs

whuppins wrote: »

LaOs wrote: »

Why does this cause an error? Is it because there are 2 entries in the range from C5 to the end, so it's going to try to make copies for 2 columns worth of entries (C5 and D5)?

Yep, that's it. I didn't realize that would be a possibility. I'll get back to you in a bit with a better fix; you're certainly right about the creativity of users when it comes to breaking stuff we write for them

Yeah, it more than likely won't ever be an actuality, but it's possible...

While you're doing that, or to anyone else paying attention, I have another quirk I'm curious about.

What this whole this is doing is tracking part-time faculty appointments. Names and information are entered in columns on sheet Entry. Sheet Template has formulas that pull the information (dates working, discipline, etc.) from Entry based on the Name of the appointee. The formulas are set to lookup in Entry based on B5 in Template, and B5 in Template is set to copy the actual name of its sheet. So, on Template, B5 is Template. If C5 in Entry is Paul, N. the copy of Template will be created and automatically named Paul, N. In sheet Paul, N. B5 now is Paul, N. and that cell is used to lookup in Entry for the information pertaining to the appointee Paul, N.

What I'm seeing is that Template, which should not have any results in its formulas because there is no entry of Template anywhere on Row 5 in sheet Entry, actually has results. It appears, most of the time (although not all of the time) that Template shows the results the same as the last successful copy of Template. What I mean is, after running the code, Template shows results in days worked, salary earned, etc. just the same as Paul, N. (in the earlier example).

The strange thing is this doesn't always happen. Just now, when I have one entry in C5, everything works properly. A copy of Template is correctly created, showing the right information, and Template has N/A values for its formulas, which it should. But, if I have three names entered (C5, D5, E5), I find that all three copies of Template are exactly the same as the information for the last entry on sheet Entry (E5) and that Template itself now shows that information rather than N/A values.

If the code is just copying Template and then renaming that copy, why is this happening? There shouldn't be any changes to Template itself, should there? This is kind of a confusing situation, so if I've not explained it clearly, please ask for clarification... Sometimes this affects things, and other times I've noticed it hasn't. This may be due to changes in the code--I don't remember exactly--but I still can't figure out why any of this is happening at all.

Anyway, thanks for your help and your patience.

LaOs

whuppins wrote: »

Okay, here we go:

Sub CopyTemplate()
Dim c As Integer, temp As String, ws As Worksheet, msg As String
For c = 3 To 256
    If Sheets("Entry").Cells(5, c).Value <> "" Then
        temp = Sheets("Entry").Cells(5, c).Value
        If Len(temp) > 31 Then
            msg = vbLf & "Exceeds character limit"
        Else
            With CreateObject("VBScript.RegExp")
                .Pattern = "[:\\\?\[\]/\*]"
            If .test(temp) Then
                    msg = msg & vbLf & "Used invalid character"
                Else
                On Error Resume Next
                Set ws = Sheets(temp)
                On Error GoTo 0
                If Not ws Is Nothing Then
                    msg = msg & vbLf & "Already used"
                End If
            End If
            End With
        End If
        If Len(msg) Then
            MsgBox temp & msg
        Else
            Sheets("Template").Copy after:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = temp
        End If
        Set ws = Nothing: msg = ""
    End If
Next c
End Sub

That behaves more like your original code except instead of trying to isolate the range in which entries appear, it just checks every column. The code is then run on a case-by-case basis depending if there's an entry in the current column or not.

whuppins, thanks for that code. That solves the problem encountered if there is a blank column. Please see my above post, though, as this code encounters the same situation. Thanks.

whuppins

I think I'm starting to understand what you're talking about, but is it possible for you to provide more of the code that's responsible for doing the things you're describing, like populating B5, etc.? Since there are multiple ways to accomplish what you're talking about, it may be hard to reproduce your problem if I'm using my own code.

Edit: Also, being able to check out the formulas being used in Template might help, too.

LaOs

The only actual code in the whole workbook is this code we've been hashing out to copy Template for as many entries as there are in Row 5 and name the new sheets after those entries. Everything in Template is a formula.

Here is (I hope) a screenshot of Template:

Let me know if this doesn't work.

Anything to the right of the image is just simple formulas referencing only other cells within Template.

The formulas:
B5 =MID(CELL("filename",B4),(FIND("]",CELL("filename",B4))+1),(LEN(CELL("filename",B4))-FIND("]",CELL("filename",B4))))

B8 =IF(AND(HLOOKUP($B$5,Entry!$C$5:$BE$97,8)="C",HLOOKUP($B5,Entry!$C$5:$BE$97,6)="yes"),"1st half day/week x "&HLOOKUP($B5,Entry!$C$5:$BE$97,24)&" Clinic weeks ("&HLOOKUP($B5,Entry!$C$5:$BE$97,7)&" - "&HLOOKUP($B5,Entry!$C$5:$BE$97,9)&", "&HLOOKUP($B5,Entry!$C$5:$BE$97,10)&")","")

I8 =IF(B8="","",HLOOKUP($B5,Entry!$C$5:$BE$97,24)*Values!$A$5)

B20 =IF(HLOOKUP($B5,Entry!$C$5:$BE$97,4)="","0 Years of Service x $"&Values!$A$11&"/year (Max $"&Values!$A$12&")",HLOOKUP($B5,Entry!$C$5:$BE$97,4)&" Years of Service x $"&Values!$A$11&"/year (Max $"&Values!$A$12&")")

I20 =IF(HLOOKUP($B5,Entry!$C$5:$BE$97,4)="",0,IF(HLOOKUP($B5,Entry!$C$5:$BE$97,4)*Values!$A$11>Values!$A$12,Values!$A$12,HLOOKUP($B5,Entry!$C$5:$BE$97,4)*Values!$A$11))

B29 (merged cell to E29) =IF(HLOOKUP($B$5,Entry!$C$5:$BE$97,6)="yes","August: "&HLOOKUP($B$5,Entry!$C$5:$BE$97,11),"")

Now, obviously that's not all the formulas, but that's the different basic types, at least. If you've got questions about specific cells or whatnot, feel free to ask.

[Edit]
Fixed the link to the image, I think.

whuppins

Hmm, at first glance, I would guess that it has something to do with the fact that B5 uses a formula to pull the value of its parent sheet's name. That's a very clever way to do it, but try emptying the cell and sticking the red line in toward the end of your code:

...
            Sheets(Sheets.Count).Name = temp
            [color=red]Sheets(Sheets.Count).Range("B5").Value = temp[/color]
        End If
            ...

This will hard-code the value, which may be less confusing for Excel. The downside is, of course, that B5 will not automatically update itself if/when the sheet is re-named. Let me know if that's a problem.

Edit: Pic works now. Lunch is here. Back in a bit.

LaOs

I may have changed the link for the image while you were replying... does it still not work? Thanks, though.

So, I put your red line of code where it appeared as though it should go. I deleted the formula in B5 on Template, and tried to activate the new code. It generated an error:

Run-time error: 1004:

Copy method of Worksheet class failed

Sub CopyTemplate()
Dim c As Integer, temp As String, ws As Worksheet, msg As String
For c = 3 To 256
    If Sheets("Entry").Cells(5, c).Value <> "" Then
        temp = Sheets("Entry").Cells(5, c).Value
        If Len(temp) > 31 Then
            msg = vbLf & "Exceeds character limit"
        Else
            With CreateObject("VBScript.RegExp")
                .Pattern = "[:\\\?\[\]/\*]"
            If .test(temp) Then
                    msg = msg & vbLf & "Used invalid character"
                Else
                On Error Resume Next
                Set ws = Sheets(temp)
                On Error GoTo 0
                If Not ws Is Nothing Then
                    msg = msg & vbLf & "Already used"
                End If
            End If
            End With
        End If
        If Len(msg) Then
            MsgBox temp & msg
        Else
[COLOR="Red"]            Sheets("Template").Copy after:=Sheets(Sheets.Count)[/COLOR]
            Sheets(Sheets.Count).Name = temp
            Sheets(Sheets.Count).Range("B5").Value = temp
        End If
        Set ws = Nothing: msg = ""
    End If
Next c
End Sub

I have coloured the highlighted line in red.

Anyway, if the copies of Template are hard-coding the value for B5 as the same name as the name of the copied worksheet, that should be fine--B5 doesn't need to update itself anymore after the copy is initially corrected.

whuppins

I can't get it to fail using that code. Did you do anything to Template? Protect it? Rename it? Did you hide/unhide any sheets?

Take the extra line I suggested back out and replace B5's formula. Does it still give the error? None of those changes should have impacted the ability to copy sheets.

Edit: If it breaks again, Debug and hover your mouse over (Sheets.Count) in the VBA to find out its value. Compare that value to the actual number of sheets in the book at that point.

LaOs

I have not done anything to Template. All I did was select B5 and press the Delete key, removing the formula.

I tried taking out your extra line and replacing the formula for B5, but now it's still generating the same error. This is what the code looks like now:

Sub CopyTemplate()
Dim c As Integer, temp As String, ws As Worksheet, msg As String
For c = 3 To 256
    If Sheets("Entry").Cells(5, c).Value <> "" Then
        temp = Sheets("Entry").Cells(5, c).Value
        If Len(temp) > 31 Then
            msg = vbLf & "Exceeds character limit"
        Else
            With CreateObject("VBScript.RegExp")
                .Pattern = "[:\\\?\[\]/\*]"
            If .test(temp) Then
                    msg = msg & vbLf & "Used invalid character"
                Else
                On Error Resume Next
                Set ws = Sheets(temp)
                On Error GoTo 0
                If Not ws Is Nothing Then
                    msg = msg & vbLf & "Already used"
                End If
            End If
            End With
        End If
        If Len(msg) Then
            MsgBox temp & msg
        Else
[COLOR="Red"]            Sheets("Template").Copy after:=Sheets(Sheets.Count)[/COLOR]
            Sheets(Sheets.Count).Name = temp
        End If
        Set ws = Nothing: msg = ""
    End If
Next c
End Sub

When I hover over =Sheets(Sheets.Count), the value is 3. There are three worksheets in the workbook (Entry, Values, Template).

whuppins

This is weird. It really seems like something that's unrelated to the changes we've made. You don't have more than one workbook open, do you? That would confuse Excel since you haven't been using "ThisWorkbook" in front of all your Sheets() references.

Try making a new macro that just contains the red line. Does that work? If not, add ThisWorkbook like so:

ThisWorkbook.Sheets("Template").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

LaOs

I don't have any other workbooks open in Excel, and I didn't make any other changes to anything else in the workbook except the code in Module 1 and changing the formula for B5 in Template. I don't know that it matters, but I've tied this code to run when a button is clicked. That button resides on Entry and is set to activate CopyTemplate.

I tried just skipping to adding ThisWorkbook to that line like you showed, but that didn't work.

So, I just closed the workbook without saving and opened it again. I copied in the following code:

Sub CopyTemplate()
Dim c As Integer, temp As String, ws As Worksheet, msg As String
For c = 3 To 256
    If Sheets("Entry").Cells(5, c).Value <> "" Then
        temp = Sheets("Entry").Cells(5, c).Value
        If Len(temp) > 31 Then
            msg = vbLf & "Exceeds character limit"
        Else
            With CreateObject("VBScript.RegExp")
                .Pattern = "[:\\\?\[\]/\*]"
            If .test(temp) Then
                    msg = msg & vbLf & "Used invalid character"
                Else
                On Error Resume Next
                Set ws = Sheets(temp)
                On Error GoTo 0
                If Not ws Is Nothing Then
                    msg = msg & vbLf & "Already used"
                End If
            End If
            End With
        End If
        If Len(msg) Then
            MsgBox temp & msg
        Else
            Sheets("Template").Copy after:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = temp
            Sheets(Sheets.Count).Range("B5").Value = temp
        End If
        Set ws = Nothing: msg = ""
    End If
Next c
End Sub

I do not run into any error. What does happen, though, is just ridiculously strange.

The first time I ran the code, I had three entries: Willy, John, paul. Three copies of Template were created, and the code correctly entered the right name into B5 for each copy. But, Willy and paul were exact copies of one another, when the information in the formulas should have generated different results. Even more strange, even though B5 was updated to John, the formulas in that sheet maintained their N/A values. Evaluating the formulas showed that they were pulling John from B5 correctly but were not finding values in Entry correctly.

I deleted the three copies and tried some other changes. All the changes were simply changing the names entered in row 5 on Entry, changing the number of entries (2, 3, 1, 2 with a blank between, etc.). Sometimes all of the copies of Template were exactly the same, sometimes the first copy was right and the latter two were the same, etc. In my changes since, I haven't had the second copy not work like the first time.

I just ran it again with two entries in row 5 in Entry: one (C5), three (E5). Two copies of Template were generated and the information within the copies is correct. Deleting those sheets, I try again: C5=one, D5=two. Two copies of Template are created and the information within the copies is correct. Trying again, deleting only the sheet named two and leaving the sheet named one. Entry has two names entered: C5=one and E5=two (D5 is blank). Msg box appears, says one is already used, and then a copy of Template is created with the correct information. Finally, I delete all the copies of sheets (leaving only Entry, Values, Template). I have three names in Entry: one (C5), two (D5), three (E5). Three copies of Template are created, with B5 correctly updating, but it does not generate the right information: sheet one is perfect, sheet two is wrong, and sheet three is perfect. Sheet two is an exact copy of sheet three, so far as the information is concerned. This is so random!!

I know that was a really, really rambly explanation of what I've been seeing, but I hope it helps give you and idea of what's going on maybe? Wow... why on earth is it doing this?

whuppins

I haven't had any time to get further into it (some actual, you know, work showed up on my desk) but in the meantime while you try to diagnose the problem, I would recommend continuing to let the VBA fill in the B5 values instead of the formula method. A few posts back, in happier, simpler times, I was pretty sure that all your troubles had to do with those B5 formulas. CELL() is a real oddball function and I try not to use it if there's another method that will work just as well. I can think of a few reasons off the top of my head why the sheets would behave as you described if you continue to use formulas to populate B5.

Having said that, it seems that you did, in fact, abandon the formula for the VBA, and I must admit I can't think of any good reasons why it should continue to act like that with the names being hard-coded in by the code. Here are a few random thoughts...

- When you've run the macro and you've got a broken sheet, does clicking on any cell in the broken sheet and hitting F9 (calculate) help?

- In a similar vein, what are your settings for calculation (Tools/Options/Calculation tab in pre-07 versions of Excel)? They should be set to Automatic, with Update Remote References and Save External Link Values also checked.

- Can you send me a copy of the workbook (with dummy values replacing sensitive information, of course)? I will be busy tonight but tomorrow I may be able to look at it in a little more depth.

- Edit: What's the purpose of the Values sheet? I'm not very clear on that.

I'll continue to look at this while I'm at work for the next hour; if we don't get it sorted by then I should be available tomorrow morning.

whuppins

Hang on, I think I may have the answer. I should have taken a closer look at your formulas before.

Everywhere you use HLOOKUP() (or VLOOKUP(), for that matter), you should include the [range lookup] argument at the end. It's an optional argument, and you've omitted it, but IMO, Microsoft made a very poor decision in defaulting it to TRUE if omitted. In the majority of situations, it should be FALSE, so you need to explicitly set it every time you use a lookup function. So, for example, B8's formula should be:

=IF(AND(HLOOKUP($B$5,Entry!$C$5:$BE$97,8,[color=yellow]FALSE[/color])="C",HLOOKUP($B5,Entry!$C$5:$BE$97,6,[color=yellow]FALSE[/color])="yes"),"1st half day/week x "&HLOOKUP($B5,Entry!$C$5:$BE$97,24,[color=yellow]FALSE[/color])&" Clinic weeks ("&HLOOKUP($B5,Entry!$C$5:$BE$97,7,[color=yellow]FALSE[/color])&" - "&HLOOKUP($B5,Entry!$C$5:$BE$97,9,[color=yellow]FALSE[/color])&", "&HLOOKUP($B5,Entry!$C$5:$BE$97,10,[color=yellow]FALSE[/color])&")","")

Make similar changes to all formulas on Template and see if that fixes it (I would continue to have the VBA fill in B5, as mentioned above).

LaOs

whuppins wrote: »

Hang on, I think I may have the answer. I should have taken a closer look at your formulas before.

Everywhere you use HLOOKUP() (or VLOOKUP(), for that matter), you should include the [range lookup] argument at the end. It's an optional argument, and you've omitted it, but IMO, Microsoft made a very poor decision in defaulting it to TRUE if omitted. In the majority of situations, it should be FALSE, so you need to explicitly set it every time you use a lookup function. So, for example, B8's formula should be:

=IF(AND(HLOOKUP($B$5,Entry!$C$5:$BE$97,8,[color=yellow]FALSE[/color])="C",HLOOKUP($B5,Entry!$C$5:$BE$97,6,[color=yellow]FALSE[/color])="yes"),"1st half day/week x "&HLOOKUP($B5,Entry!$C$5:$BE$97,24,[color=yellow]FALSE[/color])&" Clinic weeks ("&HLOOKUP($B5,Entry!$C$5:$BE$97,7,[color=yellow]FALSE[/color])&" - "&HLOOKUP($B5,Entry!$C$5:$BE$97,9,[color=yellow]FALSE[/color])&", "&HLOOKUP($B5,Entry!$C$5:$BE$97,10,[color=yellow]FALSE[/color])&")","")

Make similar changes to all formulas on Template and see if that fixes it (I would continue to have the VBA fill in B5, as mentioned above).

Hey, thanks for that catch! I don't know why they would default to an approximate match--that just strikes me as stupid. I'll go ahead and make the changes to the HLOOKUP formulas.

The Values sheet is just an easy way to store some variables that may change at some point in the future (if, for example, part-time faculty were to be paid more or less for each lecture in the future). Instead of hard-coding the values to multiply by, I've set them aside in a sheet for easy updating.

My calculation is set to automatic and both those options you mentioned are selected. I'll let you know how it goes with the formula changes.

Thanks.

whuppins

OK, I pretty much re-created the workbook on my end after that last post, so I don't think seeing a copy of the real thing will help now. I now have a pretty good understanding of how it's set up, and the more I work with it the more I think the FALSE thing was the culprit. I'm heading home now; hope it works out for you. Let me know.

LaOs

Well dang! That appears to have solved all those strange little quirks. So what, was it just making some match up when looking for it's approximate instead of exact match? That is so bizarre.

Well, whuppins, as usual, you've saved me again. I never would have imagined that could have caused this sort of problem, but thanks so much for pointing it out. I'm going to play around more and see if I can break it like before, but everything seems to behaving much more sensibly now.

Thanks!

whuppins

LaOs wrote: »

Well dang! That appears to have solved all those strange little quirks. So what, was it just making some match up when looking for it's approximate instead of exact match?

Yeah, pretty much. I have no idea why they thought that should be the default setting.

Glad to hear you got it working. Good luck in your future Excel endeavors!

LaOs

This isn't VBA-related anymore, but it's a really silly quirk that you might be able to shed some light on.

(Cross-posted from Mr.Excel forums)

I have a document (this same document) that I want the same rows to repeat on each page that is printed. These rows are 1:5. In Page Setup, Rows to Repeat At Top, I have entered: $1:$5.

I also have columns that I want to repeat at the left of each page. These are columns A:B. In Page Setup, Columns to Repeat At Left, I have entered: $A:$B

I notice that these specifications work fine for the first page (obviously) and the second page. But, when viewing the 3rd page in Print Preview, it does not appear correctly.

On page 3, Columns A and B correctly appear to the left. Rows 1 to 5 appear at the top, but rows 1 and 4 are cut off where they would overflow into Column C and beyond.

Row 1 has text that is entered only in Cell A1 but overflows into B1. There is nothing in B1, so this is fine (the text all shows). Row 4 has text in Cell A4 but A4 is merged all the way to G4. The other rows appear as normal on Page three.

What I'm not understanding is why Page 2 displays rows 1-4 properly but Pages 3 and beyond do not. I thought it might have something to do with the fact that text overflows from column A, but then it shouldn't work on Page 2, right? I then thought that perhaps it has something to do with specifying both rows and columns to repeat at the top, but would that make sense?

Any guidance would be greatly appreciated. If it appears I've missed something simple, please point it out--I'll look like a dolt, but that's not bad from time to time.

[Fake-Edit]
Someone asked if it actually printed incorrectly, as sometimes Print Preview doesn't always match the actual printed output...

Unfortunately it prints exactly as it appears in Print Preview. Page 2 repeats the rows properly. Pages 3 and 4 repeat columns A and B correctly but do not show the complete Rows 1-4. It appears as though Row 5 shows correctly so the only problem is the text in A1 and A4 (A4 merged to G4) is cut off where column C would start.

whuppins

Are you talking about the Template sheet (and its copies) here? Where does it break? How many total pages are there?

LaOs

whuppins wrote: »

Are you talking about the Template sheet (and its copies) here? Where does it break? How many total pages are there?

No, this is printing the Entry sheet.

[Edit]
There are 22 pages.

whuppins

Where are the breaks? In other words, is it breaking down because each person has so many rows that need to be filled in? Or is it breaking across because there are so many people to keep track of? Or both?

LaOs

It breaks both ways.

Page 1 is Columns A to G, Rows 1 to 53.
Page 2 is Columns A to G, Rows 54 to 97.
Page 3 is Columns H to L, Rows 1 to 53.
Page 4 is Columns H to L, Rows 54 to 97.
etc.

whuppins

Okay, that makes sense. There's a legitimate reason why it's behaving like that, but it's pretty tough to explain in words. Here goes.

Consider cell C4 (yeah, even though it's part of a merged range). You want it to show up at the top of each odd-numbered page, so that the contents of A4 have room to spill over, right? But C4 doesn't get repeated horizontally, only vertically. This is because it's part of (1:5), but not part of (A:B). Therefore, its contents will only be repeated on page 2. Subsequent pages will not repeat anything to the right of columns A and B on either their top or bottom halves.

For example, put a value into H4 and print preview. This is the value that will appear on page 3 (and be repeated on page 4, since it's part of (1:5)). That should illustrate how the space above H5 is occupied not by C4, but by H4. I'm assuming that in practice, H4 will actually be blank, in which case I can understand why it might be confusing to you. After all, Excel is usually smart enough to allow empty cells to accommodate neighboring cells' spillover, like with B1 and C4. Unfortunately, this doesn't hold true for most printing tweaks. When you set up repeating rows and columns for printing, it's like Excel is literally taking a snapshot of that area of the screen and pasting it onto subsequent pages -- in reality, H4 is completely free of any spillover -- so that's how it appears on the printed page.

If this isn't making any sense, try coloring the cell backgrounds of the first 4 printed pages each a different color. This should clearly illustrate which cells are being repeated when you're in print preview.

TL;DR: It's working as intended. There may be some workarounds available to you but honestly, I think your best bet would be just to wrap/shrink/move/etc. your text so it all fits within the first two columns.

LaOs

whuppins wrote: »

Okay, that makes sense. There's a legitimate reason why it's behaving like that, but it's pretty tough to explain in words. Here goes.

Consider cell C4 (yeah, even though it's part of a merged range). You want it to show up at the top of each odd-numbered page, so that the contents of A4 have room to spill over, right? But C4 doesn't get repeated horizontally, only vertically. This is because it's part of (1:5), but not part of (A:B). Therefore, its contents will only be repeated on page 2. Subsequent pages will not repeat anything to the right of columns A and B on either their top or bottom halves.

For example, put a value into H4 and print preview. This is the value that will appear on page 3 (and be repeated on page 4, since it's part of (1:5)). That should illustrate how the space above H5 is occupied not by C4, but by H4. I'm assuming that in practice, H4 will actually be blank, in which case I can understand why it might be confusing to you. After all, Excel is usually smart enough to allow empty cells to accommodate neighboring cells' spillover, like with B1 and C4. Unfortunately, this doesn't hold true for most printing tweaks. When you set up repeating rows and columns for printing, it's like Excel is literally taking a snapshot of that area of the screen and pasting it onto subsequent pages -- in reality, H4 is completely free of any spillover -- so that's how it appears on the printed page.

If this isn't making any sense, try coloring the cell backgrounds of the first 4 printed pages each a different color. This should clearly illustrate which cells are being repeated when you're in print preview.

TL;DR: It's working as intended. There may be some workarounds available to you but honestly, I think your best bet would be just to wrap/shrink/move/etc. your text so it all fits within the first two columns.

Right. A bit earlier I realised that this was why it is behaving this way... it works for page 2 because C4 is actually C4, but on Page 3 and 4, "C4" is actually H4. It's just a bit unfortunate :P Also unfortunate is the fact that the information in Row 4 is far too long to reasonably fit in A and B only.

Oh well. I'll figure it out--thanks for explaining the quirk though.