VBA won't run in personal.xslb

ceytl

Board Regular
Joined
Jun 6, 2009
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I have a VBA script that works in a workbook, but it will not work if I try to run from personal.xslb

What am I doing wrong?

Also, is there a better way to write the code?

VBA Code:
Sub Test()
Dim ws As Worksheet
Dim row As Long

Set ws = Sheet1

For row = 1 To Cells(ws.Rows.Count, "A").End(xlUp).row
    If StrComp(Cells(row, 1).Value, "02455", vbTextCompare) = 0 Then
        Cells(row, 2).Value = Cells(row, 2).Value / 20
    End If
    Next row
    
For row = 1 To Cells(ws.Rows.Count, "A").End(xlUp).row
    If StrComp(Cells(row, 1).Value, "06733", vbTextCompare) = 0 Then
        Cells(row, 2).Value = Cells(row, 2).Value / 10
    End If
    Next row
    
For row = 1 To Cells(ws.Rows.Count, "A").End(xlUp).row
    If StrComp(Cells(row, 1).Value, "011255", vbTextCompare) = 0 Then
        Cells(row, 2).Value = Cells(row, 2).Value / 50
    End If
    Next row

End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
Perhaps :
VBA Code:
For row = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).row
    If StrComp(ws.Cells(row, 1).Value, "02455", vbTextCompare) = 0 Then
        ws.Cells(row, 2).Value = ws.Cells(row, 2).Value / 20
    End If
    Next row
And similarly for the other loops. Is the correct workbook active?
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
Alternative code :
VBA Code:
Sub Macro1()
[C:C].Insert
With Range("C1:C" & Cells(Rows.Count, "A").End(3).row)
    .Formula = "=IF(A1=""02455"",B1/20,IF(A1=""06733"",B1/10,IF(A1=""011255"",B1/50,B1)))"
    .Offset(0, -1) = .Value
End With
[C:C].Delete
End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
This line:

VBA Code:
Set ws = Sheet1

is using the codename of a worksheet, which only works for a sheet in the same workbook as the code. If you put the code in your personal macro workbook, you'd need to refer to the sheet by tab name, or use Activesheet for example.
 
Solution

ceytl

Board Regular
Joined
Jun 6, 2009
Messages
74
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Alternative code :
VBA Code:
Sub Macro1()
[C:C].Insert
With Range("C1:C" & Cells(Rows.Count, "A").End(3).row)
    .Formula = "=IF(A1=""02455"",B1/20,IF(A1=""06733"",B1/10,IF(A1=""011255"",B1/50,B1)))"
    .Offset(0, -1) = .Value
End With
[C:C].Delete
End Sub

If I wanted to do 39 rows what would I have to change?

Thanks!
 

ceytl

Board Regular
Joined
Jun 6, 2009
Messages
74
Office Version
  1. 2016
Platform
  1. Windows
This line:

VBA Code:
Set ws = Sheet1

is using the codename of a worksheet, which only works for a sheet in the same workbook as the code. If you put the code in your personal macro workbook, you'd need to refer to the sheet by tab name, or use Activesheet for example.

Thanks!
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,273
Office Version
  1. 2016
Platform
  1. Windows
If I wanted to do 39 rows what would I have to change?

Thanks!
If the 39 rows are based upon row 1 to the last row with data in column A, nothing needs to be changed.
 

Forum statistics

Threads
1,136,210
Messages
5,674,420
Members
419,508
Latest member
trinstrick

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