VBA won't run in personal.xslb

[ceytl]

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?

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

 

[footoo]

Perhaps :

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]

Alternative 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]

This line:

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.

 

[ceytl]

Alternative 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]

This line:

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]

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.