Excel 2010 macro not working in Excel 365

[renigar]

At work we have a mix of desktop MS Office (various versions) and Office 365. I wrote an Excel macro that works perfectly on my desktop (Office 2010) but fails win I try to run it on Excel Office 365 (v.2108 build 14326.20238). I get a Run Time Error '13' Type Mismatch. I've looked at this awhile. I tried changing variable types, but still no go. I'm think it's probably easy but I just can't see it. If anyone has experience with the differences in these version and can tell me what I'm missing I would be greatly appreciative. Code follows. Highlighted line is where error occurs.

Sub Update980Off()
' This macro will put employee initials into the proper crew column and the proper 980 Friday off column

Dim Emp As String       ' Holds the employees initials
Dim Last980 As Single   ' Holds the number of the last used row in the Nine Eighty Employees list
Dim Last1st As Single   ' Holds the number of the last used row of first friday employees in column B on 9-80 sheet
Dim Last2nd As Single   ' Holds the number of the last used row of second friday employees in column E on 9-80 sheet
Dim n As Single         ' This is a loop counter for employee names
Dim c As Single         ' This is a loop counter for collections crew
Dim m As Single         ' This is a loop counter for maintenance crew
Dim r As Single         ' This is a loop counter for repair crew
Dim u As Single         ' This is a loop counter for usa/services crew
Dim fL As Single        ' This is a loop counter for the friday left column (T,U,V,W)on Duty Schedule sheets
Dim fR As Single        ' This is a loop counter for the friday right column (AQ,AR,AS,AT)on Duty Schedule sheets
Dim fc1 As Single       ' This variable hold the column number for friday (column T or V)on Duty Schedule sheets
Dim fc2 As Single       ' This variable hold the column number for friday (column AQ or AS)on Duty Schedule sheets
Dim DSW1 As String      ' Holds the Duty Schedule Week-1 sheet name
Dim DSW2 As String      ' Holds the Duty Schedule Week-2 sheet name
Dim cg As Single        ' This is a loop counter for collections crew Initials color (green) column AY on Duty Schedule sheets
Dim mr As Single        ' This is a loop counter for maintenance crew Initials color (red) column AY on Duty Schedule sheets
Dim rb As Single        ' This is a loop counter for repair crew Initials color column (blue) AY on Duty Schedule sheets
Dim uo As Single        ' This is a loop counter for usa/services crew Initials color (orange) column AY on Duty Schedule sheets
Dim clm1 As String      ' Holds the column name for left Friday entries
Dim clm2 As String      ' Holds the column name for right Friday entries

DSW1 = "Duty Schedule Week-1"
DSW2 = "Duty Schedule Week-2"

' Call another macro to clear data before writing new data
' If Clear Data message box answer is no, exit both macros
Call ClearDutyScheduleWeek1and2
    If iAnswer = vbNo Then Exit Sub

' Make sure 9-80 is the active worksheet
Worksheets("9-80").Activate

Sheets("9-80").Unprotect
Sheets("Duty Schedule Week-1").Unprotect
Sheets("Duty Schedule Week-2").Unprotect

' Find the last row used of Nine Eighty Employees
With Range("J2").CurrentRegion
     Last980 = .Rows(.Rows.Count).Row
End With
 
' Find the last row used of 1st Friday
With Range("B3").CurrentRegion
     Last1st = .Rows(.Rows.Count).Row
End With
 
' Find the last row used of 2nd Friday
With Range("E3").CurrentRegion
     Last2nd = .Rows(.Rows.Count).Row
End With

' #########  This section is for the Duty Schedule Week-1 worksheet  ############

' Get employee initials for the 1st Friday and place them on the Duty Schedule Week-1 worksheet
' in the proper crew column and Friday columns for 980 off
c = 3       ' Collections loop row number
m = 3       ' Maintenance Loop row number
r = 3       ' Repair Crew loop row number
u = 3       ' USAs/Services loop row number
fL = 3      ' Friday on the left loop row number
fR = 3      ' Friday on the right loop row number
fc1 = 20    ' Represents column T (20) or column V (22)
fc2 = 43    ' Represents column AQ (43) or column AS (45)
cg = 13     ' Collections green start row in column AY (51)
mr = 23     ' Maintenance red start row in column AY (51)
rb = 3      ' Reapair blue start row in column AY (51)
uo = 33     ' USAs orange start row in column AY (51)
clm2 = "AQ" ' Holds the column name of the first entry column for Friday on the right
clm1 = "T"  ' Holds the column name of the first entry column for Friday on the left

'Stop

For n = 3 To Last1st
' On 9-80 sheet use vlookup to compare names in column B with column J and get initials from column K
    [highlight #FCE94F]Emp = Application.VLookup(Range("B" & n), Range("J2:K25"), 2, False)[/highlight]

' This section grabs the initials of the names in column B of the 9-80 worksheet and places them in the proper work group columns
' and in the Friday column to the right of the respective work group. It also grabs the initials of the names in column E of the
' 9-80 worksheet and puts them in the proper work group column.

' Check if Friday right side current cell is empty, if not increment counter
    If Worksheets(DSW1).Range(clm2 & fR) <> "" Then fR = fR + 1
    If fR > 10 Then fc2 = 45: If fR = 11 Then fR = 3    ' Switch columns if first column is full *********************************
    If fc2 = 45 Then clm2 = "AS"                        ' Switch columns if first column is full
 
' Check if Friday left side current cell is empty, if not increment counter
    If Worksheets(DSW1).Range(clm1 & fL) <> "" Then fL = fL + 1
    If fL > 10 Then fc1 = 22: If fL = 11 Then fL = 3    ' Switch columns if first column is full *********************************
    If fc1 = 22 Then clm1 = "V"                         ' Switch columns if first column is full

' Collections (column Y)
        If Range("C" & n).Value = "Collections" And Emp <> "EP" Then Worksheets(DSW1).Cells(c, 25).Value = Emp: c = c + 1
        If Range("C" & n).Value = "Collections" Then Worksheets(DSW1).Cells(cg, 51).Value = Emp: cg = cg + 1: _
        Worksheets(DSW1).Cells(fR, fc2).Value = Emp: Worksheets(DSW1).Cells(fR, fc2 + 1).Value = "980"
 
' Maintenance (column X)
        If Range("C" & n).Value = "Maintenance" And Emp <> "RP" Then Worksheets(DSW1).Cells(m, 24).Value = Emp: m = m + 1
        If Range("C" & n).Value = "Maintenance" Then Worksheets(DSW1).Cells(mr, 51).Value = Emp: mr = mr + 1: _
        Worksheets(DSW1).Cells(fR, fc2).Value = Emp: Worksheets(DSW1).Cells(fR, fc2 + 1).Value = 980

' Repair Crew (column A)
        If Range("C" & n).Value = "Repair Crew" And Emp <> "PP" Then Worksheets(DSW1).Cells(r, 1).Value = Emp: r = r + 1
        If Range("C" & n).Value = "Repair Crew" Then Worksheets(DSW1).Cells(rb, 51).Value = Emp: rb = rb + 1: _
        Worksheets(DSW1).Cells(fL, fc1).Value = Emp: Worksheets(DSW1).Cells(fL, fc1 + 1).Value = "980"
       
' USAs/Services (column B)
        If Range("C" & n).Value = "USAs/Services" And Emp <> "DP" Then Worksheets(DSW1).Cells(u, 2).Value = Emp: u = u + 1
        If Range("C" & n).Value = "USAs/Services" Then Worksheets(DSW1).Cells(uo, 51).Value = Emp: uo = uo + 1: _
        Worksheets(DSW1).Cells(fL, fc1).Value = Emp: Worksheets(DSW1).Cells(fL, fc1 + 1).Value = "980"

' Chief 1
        If Range("C" & n).Value = "Chief 1" Then _
        Worksheets(DSW1).Cells(fR, fc2).Value = Emp: Worksheets(DSW1).Cells(fR, fc2 + 1).Value = "980"

' Chief 2
        If Range("C" & n).Value = "Chief 2" Then _
        Worksheets(DSW1).Cells(fL, fc1).Value = Emp: Worksheets(DSW1).Cells(fL, fc1 + 1).Value = "980"
    
Next

'Stop

' Get employee initials for the 2nd Friday of the pay period and place them on the
' Duty Schedule Week-1 worksheet in the proper crew column

For n = 3 To Last2nd
' On 9-80 sheet use vlookup to compare names in column E with column J and get initials from column K
    Emp = Application.VLookup(Range("E" & n), Range("J2:K25"), 2, False)

' This section grabs the initials of the names in column B of the 9-80 worksheet and places them in the proper work group columns
' and in the Friday column to the right of the respective work group. It also grabs the initials of the names in column E of the
' 9-80 worksheet and puts them in the proper work group column.

' Collections (column Y)
        If Range("F" & n).Value = "Collections" And Emp <> "EP" Then Worksheets(DSW1).Cells(c, 25).Value = Emp: c = c + 1
        If Range("F" & n).Value = "Collections" Then Worksheets(DSW1).Cells(cg, 51).Value = Emp: cg = cg + 1

' Maintenance (column X)
        If Range("F" & n).Value = "Maintenance" And Emp <> "RP" Then Worksheets(DSW1).Cells(m, 24).Value = Emp: m = m + 1
        If Range("F" & n).Value = "Maintenance" Then Worksheets(DSW1).Cells(mr, 51).Value = Emp: mr = mr + 1

' Repair Crew (column A)
        If Range("F" & n).Value = "Repair Crew" And Emp <> "PP" Then Worksheets(DSW1).Cells(r, 1).Value = Emp: r = r + 1
        If Range("F" & n).Value = "Repair Crew" Then Worksheets(DSW1).Cells(rb, 51).Value = Emp: rb = rb + 1

' USAs/Services (column B)
        If Range("F" & n).Value = "USAs/Services" And Emp <> "DP" Then Worksheets(DSW1).Cells(u, 2).Value = Emp: u = u + 1
        If Range("F" & n).Value = "USAs/Services" Then Worksheets(DSW1).Cells(uo, 51).Value = Emp: uo = uo + 1

Next

'Stop

' &&&&&&&&&&&  This section is for the Duty Schedule Week-2 worksheet  &&&&&&&&&&

' Get employee initials for the 2nd Friday and place them on the Duty Schedule Week-2 worksheet
' in the proper crew column and Friday columns for 980 off
c = 3       ' Collections loop row number
m = 3       ' Maintenance Loop row number
r = 3       ' Repair Crew loop row number
u = 3       ' USAs/Services loop row number
fL = 3      ' Friday on the left loop row number
fR = 3      ' Friday on the right loop row number
fc1 = 20    ' Represents column T (20) or column V (22)
fc2 = 43    ' Represents column AQ (43) or column AS (45)
cg = 13     ' Collections green start row in column AY (51)
mr = 23     ' Maintenance red start row in column AY (51)
rb = 3      ' Reapair blue start row in column AY (51)
uo = 33     ' USAs orange start row in column AY (51)
clm2 = "AQ" ' Holds the column name of the first entry column for Friday on the right
clm1 = "T"  ' Holds the column name of the first entry column for Friday on the left

For n = 3 To Last2nd
' On 9-80 sheet use vlookup to compare names in column E with column J and get initials from column K
    Emp = Application.WorksheetFunction.VLookup(Range("E" & n), Range("J2:K25"), 2, False)

' This section grabs the initials of the names in column E of the 9-80 worksheet and places them in the proper work group columns
' and in the Friday column to the right of the respective work group. It also grabs the initials of the names in column B of the
' 9-80 worksheet and puts them in the proper work group column.

' Check if Friday right side current cell is empty, if not increment counter
    If Worksheets(DSW2).Range(clm2 & fR) <> "" Then fR = fR + 1
    If fR > 10 Then fc2 = 45: If fR = 11 Then fR = 3    ' Switch columns if first column is full *********************************
    If fc2 = 45 Then clm2 = "AS"                        ' Switch columns if first column is full
 
' Check if Friday left side current cell is empty, if not increment counter
    If Worksheets(DSW2).Range(clm1 & fL) <> "" Then fL = fL + 1
    If fL > 10 Then fc1 = 22: If fL = 11 Then fL = 3    ' Switch columns if first column is full *********************************
    If fc1 = 22 Then clm1 = "V"                         ' Switch columns if first column is full
    
' Collections (column Y)
        If Range("F" & n).Value = "Collections" And Emp <> "EP" Then Worksheets(DSW2).Cells(c, 25).Value = Emp: c = c + 1
        If Range("F" & n).Value = "Collections" Then Worksheets(DSW2).Cells(cg, 51).Value = Emp: cg = cg + 1: _
        Worksheets(DSW2).Cells(fR, fc2).Value = Emp: Worksheets(DSW2).Cells(fR, fc2 + 1).Value = "980"

' Maintenance (column X)
        If Range("F" & n).Value = "Maintenance" And Emp <> "RP" Then Worksheets(DSW2).Cells(m, 24).Value = Emp: m = m + 1
        If Range("F" & n).Value = "Maintenance" Then Worksheets(DSW2).Cells(mr, 51).Value = Emp: mr = mr + 1: _
        Worksheets(DSW2).Cells(fR, fc2).Value = Emp: Worksheets(DSW2).Cells(fR, fc2 + 1).Value = 980

' Repair Crew (column A)
        If Range("F" & n).Value = "Repair Crew" And Emp <> "PP" Then Worksheets(DSW2).Cells(r, 1).Value = Emp: r = r + 1
        If Range("F" & n).Value = "Repair Crew" Then Worksheets(DSW2).Cells(rb, 51).Value = Emp: rb = rb + 1: _
        Worksheets(DSW2).Cells(fL, fc1).Value = Emp: Worksheets(DSW2).Cells(fL, fc1 + 1).Value = "980"

' USAs/Services (column B)
        If Range("F" & n).Value = "USAs/Services" And Emp <> "DP" Then Worksheets(DSW2).Cells(u, 2).Value = Emp: u = u + 1
        If Range("F" & n).Value = "USAs/Services" Then Worksheets(DSW2).Cells(uo, 51).Value = Emp: uo = uo + 1: _
        Worksheets(DSW2).Cells(fL, fc1).Value = Emp: Worksheets(DSW2).Cells(fL, fc1 + 1).Value = "980"
        
' Chief 1
        If Range("F" & n).Value = "Chief 1" Then _
        Worksheets(DSW2).Cells(fR, fc2).Value = Emp: Worksheets(DSW2).Cells(fR, fc2 + 1).Value = "980"
        
' Chief 2
        If Range("F" & n).Value = "Chief 2" Then _
        Worksheets(DSW2).Cells(fL, fc1).Value = Emp: Worksheets(DSW2).Cells(fL, fc1 + 1).Value = "980"
    
Next

' Get employee initials for the 2nd Friday of the pay period and place them on the
' Duty Schedule Week-1 worksheet in the proper crew column

For n = 3 To Last1st
' On 9-80 sheet use vlookup to compare names in column B with column J and get initials from column K
    Emp = Application.WorksheetFunction.VLookup(Range("B" & n), Range("J2:K25"), 2, False)

' This section grabs the initials of the names in column E of the 9-80 worksheet and places them in the proper work group columns
' and in the Friday column to the right of the respective work group. It also grabs the initials of the names in column B of the
' 9-80 worksheet and puts them in the proper work group column.

' Collections (column Y)
        If Range("C" & n).Value = "Collections" And Emp <> "EP" Then Worksheets(DSW2).Cells(c, 25).Value = Emp: c = c + 1
        If Range("C" & n).Value = "Collections" Then Worksheets(DSW2).Cells(cg, 51).Value = Emp: cg = cg + 1

' Maintenance (column X)
        If Range("C" & n).Value = "Maintenance" And Emp <> "RP" Then Worksheets(DSW2).Cells(m, 24).Value = Emp: m = m + 1
        If Range("C" & n).Value = "Maintenance" Then Worksheets(DSW2).Cells(mr, 51).Value = Emp: mr = mr + 1

' Repair Crew (column A)
        If Range("C" & n).Value = "Repair Crew" And Emp <> "PP" Then Worksheets(DSW2).Cells(r, 1).Value = Emp: r = r + 1
        If Range("C" & n).Value = "Repair Crew" Then Worksheets(DSW2).Cells(rb, 51).Value = Emp: rb = rb + 1

' USAs/Services (column B)
        If Range("C" & n).Value = "USAs/Services" And Emp <> "DP" Then Worksheets(DSW2).Cells(u, 2).Value = Emp: u = u + 1
        If Range("C" & n).Value = "USAs/Services" Then Worksheets(DSW2).Cells(uo, 51).Value = Emp: uo = uo + 1
    
Next

Sheets("9-80").Protect
Sheets("Duty Schedule Week-1").Protect
Sheets("Duty Schedule Week-2").Protect

End Sub

 

[renigar]

I wanted to add one note that could play into the issue. I'm at home right now so can't check but I believe my desktop Excel 2010 is 32 bit. Also not sure about Office 365, it might be 64 bit.

 

[SkipVought]

Hi,

I don't get it. Your lookup range is from Row 2 to 25, but your For/Next index starts with 3???

And your Last1st is identified by...

' Find the last row used of 1st Friday
With Range("B3").CurrentRegion
     [b]Last1st[/b] = .Rows(.Rows.Count).Row
End With

Since I can't see your spreadsheet, I need to ask is the B3 range contiguous with cells in either adjacent column ?

If so you could be searching for data in a row that you don't intend, that is GREATER than the last cell in column B. That could give you a mismatch.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[renigar]

Skip,

I tested it at home and it failed. I have Excel 2019 64 bit. I'm assuming 64 bit is the reason. DO you think the following line is not compatible with 64 bit and if so is there a work around?

Emp = Application.VLookup(Range("B" & n), Range("J2:K25"), 2, False)

There are two other sheets "Duty Schedule Week-1" and "Duty Schedule Week-2", one for each week of the pay period. The macro basically checks the lists on the 9-80 sheet and gets the initials of employees and drops them in columns on the Duty Schedule sheets for their appropriate work groups and in the Friday columns for when they are off for either the first or second Friday of the 980 pay period.

Here is a pic of the 980 worksheet referred to in the macro.

Here is a pic of the Duty Schedule worksheet referred to in the macro.

 

[combo]

What is n when the code breaks? Do you have the same error after [tt]Dim Emp As Variant[/tt]? If not, what VLookup returns?

combo

 

[renigar]

Combo,

When the code breaks the loop is just starting so n = 3 and Emp = "". On the 9-80 sheet column J is an alphabetized validation list for columns B and E. When I changed the Emp variable to Variant I got the same error Run Time Error '13' Type Mismatch but it occurred at the following line:

' Collections (column Y)
[highlight #FCE94F]If Range("F" & n).Value = "Collections" And Emp <> "EP" Then[/highlight] Worksheets(DSW2).Cells(c, 25).Value = Emp

At this point n = 3 and Emp = ERROR 2042.

 

[SkipVought]

Try this...

Emp = Application.VLookup(Range("B" & n)[b].Value[/b], Range("J2:K25"), 2, False)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

It looks to me that your line:

Emp = Application.VLookup(Range("B" & n), Range("J2:K25"), 2, False)

and possibly the same will happen with:

Emp = Application.VLookup(Range("B" & n).Value, Range("J2:K25"), 2, False)

returns [tt]ERROR 2042[/tt], which is NOT a String (that you expect), but it is an error.

Sooner or later you use comparison to a String ([tt]... And Emp <> "EP" Then ...[/tt]) so you compare an error to a String, and that's why you get Type Mismatch :-(

"When I changed the Emp variable to Variant I got the same error"
Because a Variant will 'morph' (convert) to whatever you throw at it:

Sub test()
Dim x As Variant

Debug.Print TypeName(x)
x = 1234
Debug.Print TypeName(x)
x = 1234.75
Debug.Print TypeName(x)
x = "1234"
Debug.Print TypeName(x)
x = CDate("9/11/2021")
Debug.Print TypeName(x)

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[renigar]

Adding .Value to the line did not work. I also checked the Office 365 at work and it is 32 bit.

So to recap my original code works fine on my 32 bit Office 2010 desktop but fails on my Office 2019 64 bit desktop and Office 365 32 bit.

Any suggestions for an alternative lookup procedure.

 

[Andrzejek]

The values in columns B and J - are they the same 'type'?
I mean, are they both text? Or both numbers?

I ask because of information in VBA VLookup – A Complete Guide

...
We can also search for a number but you have to be careful here:

1. If the number is stored as text then the search value must be a string.
2. If the number is stored as a number then the search value must be a number.
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[renigar]

The values in columns B (and E) and J are text made up of employee names. Only column J is sorted A to Z.

 

[Andrzejek]

So, I assume your data looks something like this:

[pre]
. A B C ... J K
1 PERSON EMP
2 NAME Andy EP
3 Bob Bob RP
4 Susie Cindy PP
5 Ruth Ruth EP
6 Andy Susie DP
7 Zak Tom EP
8 ... Zak PP
9 ... ...
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[renigar]

You are correct. Check out the image I posted above of the 9-80 worksheet. I blocked out the employee names but you can see the layout.

 

[Andrzejek]

Weird, your VLookup should work OK...

I would Google: excel 365 vlookup not working

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

I suspect you could have an unexpected range when you use CurrentRegion, if you have a SPACE CHARACTER where you intended to Clear Contents, for instance.

I installed the CurrentRegion Icon on my Quick Access Toolbar.

SELECT B3. Then CorrentRegion, Then check what's selected.

Or put a BREAK in your code to check the Last Row values you have in those 3 places.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[renigar]

Skip,

I tried your Current Region suggestion. It didn't work but I did step thru the code several times and the last rows were always correct.

 

[SkipVought]

I tried your Current Region suggestion. It didn't work...

Please explain what you did that "didn't work."

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

Just out of curiosity - is there a reason you have declared all your counters as Single? Could you try Integer or Long instead?

 

[renigar]

Skip,
I put the currentregion icon on the quick access toolbar and it was grayed out. I might have had the sheet protected. I went to try it again today and can't find the currentregion icon in the quick access choices. Very strange.

strongm,
No reason for Single except tired mind, maybe.

I found a fix that works. I also changed all variables dimmed as Single to Integer. Still don't know why the original didn't work in all versions of Excel. Since I got it working I'm not spending any more time on the original problem.


This is the line that failed. It occurs four times in the code with two different column references.
Emp = Application.VLookup(Range("B" & n), Range("J2:K25"), 2, False)

These two lines replace the one above in all four instances with the appropriate column references.
E = WorksheetFunction.Match(Range("B" & n).Value, Range("J2:J26"), 0)
Emp = Range("J" & E + 1).Offset(0, 1).Value


Thanks to all who tried to lend a hand.
renigar

 

[RobertPhillips]

On a style note, in the code such as

' Collections (column Y)
        If Range("F" & n).Value = "Collections" And Emp <> "EP" Then Worksheets(DSW1).Cells(c, 25).Value = Emp: c = c + 1
        If Range("F" & n).Value = "Collections" Then Worksheets(DSW1).Cells(cg, 51).Value = Emp: cg = cg + 1

do you realise that, when the tested range is set to "Collections", the second statement will be true and executed whether or not Emp = "EP".

If this is not what you intended, I suggest rewriting like so

' Collections (column Y)
        If Range("F" & n).Value = "Collections" Then
        
            If Emp <> "EP" Then
            
                Worksheets(DSW1).Cells(c, 25).Value = Emp
                c = c + 1
            Else
            
                Worksheets(DSW1).Cells(cg, 51).Value = Emp
                cg = cg + 1
            End If
        End If

but if it is what you intended, then I suggest rewriting like so

' Collections (column Y)
        If Range("F" & n).Value = "Collections" Then
        
            If Emp <> "EP" Then
            
                Worksheets(DSW1).Cells(c, 25).Value = Emp
                c = c + 1
            End If
        
            Worksheets(DSW1).Cells(cg, 51).Value = Emp
            cg = cg + 1
        End If

You might think this is just a personal preference, I admit I do not like If and Then on the same line, nor multiple statements per line, but if you think that more time is usually spent on maintaining code than originally writing it, then style and readability are important (although I accept my spacing is very much a personal preference, I am not suggesting you should go with that, but I like it ).