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

Xephire

New Member
Joined
Oct 17, 2008
Messages
13
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?
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Maybe it's because you haven't qualified the Cells property:

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

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

In what way isn't it working?
 

Xephire

New Member
Joined
Oct 17, 2008
Messages
13
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Can you post the entire procedure please?
 

Xephire

New Member
Joined
Oct 17, 2008
Messages
13
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
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I can't see why that line shouldn't be working, sorry. It works perfectly for me (Excel 2010).
 

Xephire

New Member
Joined
Oct 17, 2008
Messages
13
That's the annoying thing. It works for me and some of my colleagues as well... Must be some local setting or profile issue
 

Watch MrExcel Video

Forum statistics

Threads
1,122,214
Messages
5,594,881
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top