CODEWITHSUNDEEP
vbaexcel
user71812
user71812

Reputation: 457

code won't work across multiple worksheets (Tried moving selection, select, etc. and still wont work)

An Excel VBA newbie here. I am working on this code for a one-click-for-all worksheets macro. So the idea is to have a macro that once clicked, can trigger the IF-ISERROR-Vlookup formula in multiple worksheets (around 12 now, but will keep increasing in the future). Both the formula and the result are displayed in the same columns in multiple worksheets, but of different number of rows (Sheet 1 col B[data] and C[formula], sheet 2 Col B and C, etc.)

Now the code I did below works only when I go to a worksheet and trigger the macro in that specific worksheet; which means I have to do it one by one per sheet. I tried removing the select, the selection and the activesheet as recommended in previous posts, but it showed an error message to me.

So my questions are:

a) How can I modify the code so that I can create a macro that triggers all worksheets in one click?

b) As you can see below, I put Range("C4:C54") even though the number of rows are different in every sheet, because I have no idea how to make the range cover different rows only until it hits the last cell with values.

Dim ws As Worksheet
 For Each ws In Worksheets
     Range("C4").Select
     ActiveCell.FormulaR1C1 = _
         "=IF(ISERROR(VLOOKUP(RC[-1],TestScore,1,FALSE)),""Pass"",""Fail"")"
     Range("C4").Select
     Selection.AutoFill Destination:=Range("C4:C54")
     Range("C4:C54").Select
 Next ws
End Sub

Anybody can help with this problem? Any help would be appreciated. Thanks so much beforehand!

Edit: Added the sample snapshot of the problem as requested Click here for image.

Upvotes: 0

Views: 281

Answers (2)

Tom
Tom

Reputation: 9898

Avoid using Select statements - it can cause issues and instead refer to the cells explicitly. This will insert your formula in cells C4 to the last cell in that column

Dim ws As Worksheet
For Each ws In Worksheets
    With ws
        .Range("C4:C" & .Cells(.Rows.Count, "B").End(xlUp).Row).FormulaR1C1 = _
            "=IF(ISERROR(VLOOKUP(RC[-1],TestScore,1,FALSE)),""Pass"",""Fail"")"
    End With
 Next ws

Upvotes: 1

Anastasiya-Romanova 秀
Anastasiya-Romanova 秀

Reputation: 3378

Put this code in the Module (Insert >> Module):

    Dim ws As Worksheet, LastRow As Long

    For Each ws In Worksheets
        With ws
            LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row      'Change the number (1) with appropriate column
            .Range("C4:C" & LastRow).FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-1],TestScore,1,FALSE)),""Pass"",""Fail"")"
            'The formula will be filled down til the last row
        End With
    Next ws

Upvotes: 1

Related Questions