reference issue using vba

Question

I have 3 columns, with headers A B & C as shown at the picture, and I want to check if the value in column A is equal to 10% of column B, if yes, I want to set it to be the value for column C, if not, I want to get 10% of values in column B. I am in sheet1 and I want to set a VBA button in sheet2 to run the codes.

Here is the code:

Sub Macro1()
    Dim ws As Worksheet, lastrow As Long
    Set ws = Worksheets("sheet1")
    ws.Activate
    ActiveCell.Formula = "=IF(A2=B2*0.1, A2, B2*0.1)"
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Range("C2").AutoFill Destination:=Range("C2:C" & lastrow), Type:=xlFillDefault
End Sub

My issues is if I point my mouse at C2 in sheet1 and I just run the vba codes, it will work. If I am at sheet2 and pressing the button, it won't work, it just doesn't show any data. Is there a way to set values to column C based on my criteria?

omegastripes · Accepted Answer · 2016-11-04 01:01:48Z

2

To make the code working on sheet1 independently of active sheet is, you need to apply .Range method exactly to Worksheets("sheet1") object. Try the below code:

Sub Macro1()

    Dim lastrow As Long

    With Worksheets("sheet1")
        lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        If lastrow = 1 Then
            MsgBox "No data"
            Exit Sub
        End If
        .Range("C2:C" & lastrow).Formula = "=IF(A2=B2*0.1, A2, B2*0.1)"
    End With

End Sub

Why not to be more straightforward: always set the value to column C equal to 10% of column B? The result will be the same.

Sub Macro1()

    Dim lastrow As Long

    With Worksheets("sheet1")
        lastrow = .Range("A" & Rows.Count).End(xlUp).Row
        If lastrow = 1 Then
            MsgBox "No data"
            Exit Sub
        End If
        .Range("C2:C" & lastrow).Formula = "=B2*0.1"
    End With

End Sub

edited Nov 4, 2016 at 1:01

answered Nov 3, 2016 at 22:13

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user6432984 Over a year ago

The OP wants the code to execute on Sheet1. I would alsi remove the Exit Sub in add an Else clause to the if statement.

omegastripes Over a year ago

@ThomasInzina Now I see that missed something. Fixed the answer.

Collectives™ on Stack Overflow

reference issue using vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related