VBA code working on most machine, but not for specific user

[Xephire]

Excel version 2010

Problem:
Specific line of code is working on my workstation/version of Excel, and also for another user, but for a third user it does not. All other VBA logic works for the third user.

I am trying to find out if it is workstation related or profile related (by having the users switch PCs and check)

Background:
For a client I have created a table that is dynamic in hight, based on the entries on another worksheet. This all works, but some of the internal horizontal borders are showing which the client does not want. I therefore added this line of code in the macro

dst.Range(Cells(15, 2), Cells(15 + cLastDev, 15)).Borders(xlInsideHorizontal).LineStyle = xlNone

dst = worksheet name = (worksheets("Output"))
cLastDev = number of rows added (12)

This should remove the inside horizontal borders from B15:O27 (if cLastDev = 12). This works for a number of users, including myself, but not for all.

Any idea what could cause this?

 

[Andrew Poulsom]

Maybe it's because you haven't qualified the Cells property:

dst.Range(dst.Cells(15, 2), dst.Cells(15 + cLastDev, 15)).Borders(xlInsideHorizontal).LineStyle = xlNone

 

[Xephire]

Maybe it's because you haven't qualified the Cells property:

dst.Range(dst.Cells(15, 2), dst.Cells(15 + cLastDev, 15)).Borders(xlInsideHorizontal).LineStyle = xlNone

Thanks, Will try it right away

 

[Xephire]

Thanks, Will try it right away

User came back and it still not working. Anybody has any other ideas?

 

[Andrew Poulsom]

In what way isn't it working?

 

[Xephire]

In what way isn't it working?

It does not remove the internal horizonal borders. I only want to keep the outside borders, not the inside ones. This works on my PC and two of my colleages, but not on the pc's of two others.

There are no error messages, it just looks like this line of code is ignored.

 

[Andrew Poulsom]

Can you post the entire procedure please?

 

[Xephire]

The line of code is right at the end... Main calls the BuildTable procedure

Some of the code can be optimized, but due to time restraints that is not an issue right now.

Sub Main()
Set src = Worksheets("Static Data")
Set dst = Worksheets("Output")
Call BuildTable(src, dst)
End Sub

Sub BuildTable(src, dst)

dst.Activate
'Unmerge A15

dst.Cells(15, 1).MergeCells = False
​

'Find the last currency entry

cLastRow = dst.Cells(Rows.Count, 1).End(xlUp).Row 'Check column A​

For cRows = 15 To cLastRow

If dst.Cells(cRows, 1).Value = "Dev. Mkts. (Net Cash) Total" Then​

cLastDev = cRows - 1
Exit For​

End If​

Next cRows

'Delete the old columns

dst.Range("A15:A" & cLastDev & "").EntireRow.Delete shift:=xlUp
​

'Check how many row to insert

cLastDev = src.Cells(Rows.Count, 3).End(xlUp).Row - 2 'Check column C, deduct for header​

'Insert the new columns
For cRows = 15 To 15 + cLastDev

dst.Rows(cRows).Insert shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow​

Next cRows

cDevMarkets = 2
'Insert the currency codes
For cRows = 15 To 15 + cLastDev

dst.Cells(cRows, 2).Value = src.Cells(cDevMarkets, 3).Value
cDevMarkets = cDevMarkets + 1​

Next cRows

'Add formula in C15
dst.Cells(15, 3).Formula = "=SUMIFS(Query1!$G:$G,Query1!$A:$A,C$14,Query1!$E:$E,$B15,Query1!$D:$D,""Cash and Equivalents"")"

'Copy formulas to the right (C-O)
dst.Range("C15").AutoFill Destination:=Range("C15:O15"), Type:=xlFillValues

'Copy formulas down (15 to (15 + cLastDev))
dst.Range("C15:O15").AutoFill Destination:=Range("C15:O" & cLastDev + 15 & ""), Type:=xlFillValues

'Update Sum formula
dst.Cells(16 + cLastDev, 3).Formula = "=sum(C15:C" & 15 + cLastDev & ")"
dst.Cells(16 + cLastDev, 3).AutoFill Destination:=dst.Range(dst.Cells(16 + cLastDev, 3), dst.Cells(16 + cLastDev, 15)), Type:=xlFillValues

'Merge A15 back and enter text
dst.Cells(15, 1).Value = "Dev. Mkts. (Net Cash)"
dst.Range(dst.Cells(15, 1), dst.Cells(15 + cLastDev, 1)).MergeCells = True

'Remove the bold
dst.Range(dst.Cells(15, 1), dst.Cells(15 + cLastDev, 15)).Font.Bold = False

'Put border on A15
dst.Range(dst.Cells(15, 1), dst.Cells(15 + cLastDev, 1)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium​

End With
With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin​

End With
With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous
.ColorIndex = 1
.TintAndShade = 0
.Weight = xlThin​

End With
With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin​

End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

'Remove the horizontal lines for the main body
dst.Range(dst.Cells(15, 2), dst.Cells(15 + cLastDev, 15)).Borders(xlInsideHorizontal).LineStyle = xlNone

End Sub

 

[Andrew Poulsom]

I can't see why that line shouldn't be working, sorry. It works perfectly for me (Excel 2010).

 

[Xephire]

That's the annoying thing. It works for me and some of my colleagues as well... Must be some local setting or profile issue