CODEWITHSUNDEEP
vbaexcel
UnbrokenChain
UnbrokenChain

Reputation: 183

How to find a specific cell value in separate worksheet?

I have a piece of code that searches a range in the current worksheet for a specific cell value and then does an action on that column. The macro is launched by a form control button on the same page. I need to move the form control button to a different worksheet but I am not sure how to udpate my code to only search in sheet1 and not in the sheet where the button in.

Below is my code that works when using the button on the same worksheet as the data. I just need it to look at sheet1 instead of the current sheet.

Dim R1 As Range
    Set R1 = Range(Range("A2:AX2").Find("BMA Authorization ID"), Range("A2:AX2").Find("BMA Authorization ID").End(xlDown))
    R1.Select
    R1.Copy
    R1.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Upvotes: 1

Views: 317

Answers (3)

u8it
u8it

Reputation: 4296

You can just use Sheets(1).Range... or Sheets("Sheet1").Range...

Take a look here for more details on referencing sheets in VBA... Trying to reference another worksheet in active workbook

EDIT: Please don't take this as an argument against the other answers here. They are excellent answers for the OP (part of why I waited until one was accepted). I just think this is worth mentioning as a consideration.

I have personally adapted, come to appreciate, and would recommend the convention of avoiding with statements whenever possible per the following sentiment...

Why doesn't C# have VB.NET's 'with' operator?

Many people, including the C# language designers, believe that 'with' often harms readability, and is more of a curse than a blessing. It is clearer to declare a local variable with a meaningful name, and use that variable to perform multiple operations on a single object, than it is to have a block with a sort of implicit context.

by @Jon Skeet
https://stackoverflow.com/a/4174826/3546415

The use of variable declaration would look something like this...

Dim S1 As Worksheet
Set S1 = Sheets("Sheet1")
S1.Range... blah blah

Of course, there are still situations where using with does still make sense. Such as when a variable can't be defined or when setting a large number of properties, but I do agree that readability is generally enhanced when you don't have to worry about nesting and checking the top of your with structure to see what .Range is actually referring to. For a simple program this is hardly an issue, but it's worth keeping in mind for something more complicated.

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

As findwindow suggested; you need to qualify the sheet. Normaly I would leave this in the comments but the line is too long not to use a with block.

Dim R1 As Range
Dim ws As Worksheet

Set ws = Sheets("Sheet1")'Change this name to the sheet desired.
'Use a with block.
'When using a with block .Range = ws.Range
With ws
    Set R1 = .Range(.Range("A2:AX2").Find("BMA Authorization ID"), .Range("A2:AX2").Find("BMA Authorization ID").End(xlDown))
    R1.Value = R1.Value
End With

Upvotes: 5

user4039065
user4039065

Reputation:

Use a With ... End With statement to set the range/cell parent worksheet.

with worksheets("Sheet1")   '<~~change to suit
    with .Range(.Range("A2:AX2").Find("BMA Authorization ID"), _
                .Range("A2:AX2").Find("BMA Authorization ID").End(xlDown))
        .value = .value  'same as .Copy .PasteSpecial xlPasteValues
    end with
end with

Note that all of the Range(...) are now .Range(...). The prefix period (aka . or full stop) makes all of the ranges' parent the worksheet referenced within the With .. End With.

Upvotes: 5

Related Questions