1

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
pnuts
  • 58,317
  • 11
  • 87
  • 139
UnbrokenChain
  • 183
  • 1
  • 5
  • 18

3 Answers3

5

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.

  • Beat me by 20 seconds. – Scott Craner Nov 17 '15 at 19:27
  • @ScottCraner, and you two beat me by 1 minute. I need to get faster, hmph! – BruceWayne Nov 17 '15 at 19:29
  • I didn't check syntax in the VBE :) –  Nov 17 '15 at 19:29
  • I do have a quick question - what kind of range is going to be set? I was going to suggest to use two variables for his two `Find()` ranges, but ...what's he trying to use? The column, row, address? – BruceWayne Nov 17 '15 at 19:31
  • Is `find` most efficient here or [match](http://stackoverflow.com/questions/33725708/excel-vba-application-defined-or-object-defined-error#comment55260876_33725708)? – findwindow Nov 17 '15 at 19:32
  • @BruceWayne - This would be like going to the cell with *BMA Authorization ID* in row 2 and tapping Ctrl+Shift+▼. –  Nov 17 '15 at 19:32
  • I am assuming he is looking for a column header of sorts. Its a good work around to the fact that excel does not name columns. – Scott Craner Nov 17 '15 at 19:33
  • Yes, I am searching for column headers in a different tab, selecting the column and copy and pasting values to remove formulas. This is the first macro i've created by myself. – UnbrokenChain Nov 17 '15 at 19:36
  • 2
    @findwindow - If there were thousands then `.Range(.Cells(2, app.Match("BMA Authorization ID", .Rows(2), 0)), .Cells(2, app.Match("BMA Authorization ID", .Rows(2), 0)).End(xlDown))` might be marginally quicker but for a single operation I think you would be hard-pressed to even measure the difference. At least it is not cell-by-cell. –  Nov 17 '15 at 19:37
  • Makes sense that number of operations drives method. Thanks <3 – findwindow Nov 17 '15 at 19:39
  • @Jeeped - Yeah, I know the `.xlDown`, but I didn't know you could use a range like this `Range("A1","A2")` which is essentially what the two `finds` would return, correct? (two addresses). I asked that question because I was of the understanding that, unless specified, `Range` returns the `.Value` from a cell. Does using `Find()` change the default from a `Value` to an `Address`? (Does my question make sense?) – BruceWayne Nov 17 '15 at 19:49
  • 2
    @BruceWayne - `Range("A1, D4")` is the **union** of A1 and D4; i.e. two cells. `Range("A1", "D4")` is the **range** with A1 in the top=left and D4 in the bottom-right; i.e. sixteen cells. The OP's method was the latter. Uh... yes the default is the value but not always; `Set rng = Range("A1")` does not set rng to be the value of A1. –  Nov 17 '15 at 19:55
  • @Jeeped - last question (I'll Google if I have more ?s), when using `Set rng = Range( "A1")` hold what value - the address? If I hover over `rng` in VB, I'm pretty sure that it'll show the `Value` that's in the cell. Or is it going to hold "everything" (row, address, etc.) since later I can use `rng.Address`, `rng.Row`,etc. (Surprisingly, I don't think I've ever used Range like OP, which is why I have these pretty simplistic questions). I typically just do `Range(Cells(), Cells())` instead of using addresses. – BruceWayne Nov 17 '15 at 20:15
  • 2
    @BruceWayne - `Set rng = Range( "A1")` creates a [Range object](https://msdn.microsoft.com/en-us/library/office/ff838238.aspx). If A1 held 16 as a value, you couldn't `16.Font.Color = vbRed` but you can `rng.Font.Color = vbRed` just like you can `Range( "A1").Font.Color = vbRed`. For all intents and purposes, a [Range.Cells property](https://msdn.microsoft.com/en-us/library/office/ff196273.aspx) is a Range.Object. –  Nov 17 '15 at 20:32
  • Someone get @Jeeped a cookie~ Edit: there needs to be a SO meetup ^_^; – findwindow Nov 17 '15 at 20:37
  • As always, many thanks @Jeeped! (I should actually read the technical info. on objects :P ) – BruceWayne Nov 17 '15 at 20:59
5

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
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
1

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.

Community
  • 1
  • 1
u8it
  • 3,956
  • 1
  • 20
  • 33
  • Just a note that `Sheets(1)` isn't necessarily the same as `Sheets("Sheet1")`. Generally, they will, but not always! In this case, the format is `Sheets([sheet index no.])` vs. `Sheets("[sheet name]")`. – BruceWayne Nov 17 '15 at 19:30
  • @BruceWayne Yep, good to clarify, that's the reason for the link – u8it Nov 17 '15 at 19:31
  • Ah, didn't check the link. Thanks for that! – BruceWayne Nov 17 '15 at 19:32
  • I don't necessarily disagree with you, but my rule of thumb is if it takes longer to type the with block than just putting the abject variable, I use the object variable. But sometimes there is just so much retyping, and I don't find it especially hard to associate the correct object with the correct with statement, even in many lines of code. For example when do charts or formatting cells, a with block is the only way to go. I can see your point with Ranges and Cells. But this is a "Dealers Choice" and should be agreed upon by the group that is writing and interpreting the code. – Scott Craner Nov 17 '15 at 22:09