-1

I am having an issue with switching worksheets within one workbook. Please have a look at the following:

Set chs = Range("1:1")
Set Employ = chs.Find("Employee")
Set Sal = chs.Find("Salary")

Worksheets("Sheet1").Activate
a = Range(Columns(Employ.Column), Columns(Sal.Column)).Select
Worksheets("Sheet2").Activate
b = Range(Columns(Employ.Column), Columns(Sal.Column)).Select

What I want the program to do is activate sheet1 and then select the columns with the headings "Employee" and "Salary" (I define these below), and then activate sheet2 and then select the columns with the headings "Employee" and "Salary" again that is specifically in the activated sheet.

However, this is what it is doing. Say in sheet1, the columns for employee and salary are A and F. These will be selected just fine with everything in between. But when activating sheet2, if I try to select the columns employee and salary that are say columns B and K respectively, it will still only select columns A to F. The reverse also happens: if I activate sheet 2 first instead, it will select the columns B to K in sheet1, which doesn't match up to the headings.

Any help is appreciated.

Bobby Jatahwa
  • 3
  • 2
  • 2
    You should be explicit about your ranges by prefacing them with a sheet qualifier: – MacroMarc Jun 17 '17 at 23:45
  • Hi @MacroMarc thanks for your message. Honestly though I have no idea what that means, could you give me more guidance? – Bobby Jatahwa Jun 17 '17 at 23:48
  • 2
    [This thread](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) will help you understand. – MacroMarc Jun 17 '17 at 23:56
  • @MacroMarc I had a look at the thread and tried to implement changes such as defining my ranges first but I'm still struggling to make it work. I am very new to this and have been stuck on this problem for a while. Could you be more explicit about what I need to do please – Bobby Jatahwa Jun 18 '17 at 00:21
  • What @MacroMarc suggests is that you define `Employ` based on a specific sheet. Something like `Set chs = ThisWorkbook.Worksheets("Sheet1").Range("1:1")` should guarantee chs is always referring to the top row of Sheet1. And in general, you should always refer to a range with its sheet name, to avoid any surprises. See my answer below. – lebelinoz Jun 18 '17 at 00:22

1 Answers1

0

The way you've set up the Employ variable, it won't change every time you switch sheets. In this case, your program starts by finding the "Employ" string in Range("1:1"), but it's not clear where that is: it will be in whichever sheet is active.

From context, it sounds like you need a function which finds Employ and Sal dynamically. Maybe you need a function like this:

Function FindEmploy(sht As Worksheet) As Range
    Set FindEmploy = sht.Range("1:1").Find("Employ")
End Function

and similar for FindSal. Then your code would be something like:

Dim sht1 As Worksheet
Set sht1 = Worksheets("Sheet1")
sht1.Activate
a = sht1.Range(Columns(FindEmploy(sht1).Column), Columns(FindSal(sht1).Column)).Select

and similar for Sheet2 and b.

lebelinoz
  • 4,890
  • 10
  • 33
  • 56
  • 1
    Nice. I'd suggest using a more generic function to find any header string since that is the only difference between FindEmploy and FindSal functions: `Function FindHeader(sht as Worksheet, header as String) as Range // Set FindHeader = sht.Range("1:1").Find(header) // End Function` Apologies, but // means new line – MacroMarc Jun 18 '17 at 01:14
  • Also use a With to qualify the statements with each worksheet, or preface the Columns property with sht - for robustness, or as you say to avoid surprises... – MacroMarc Jun 18 '17 at 01:26
  • @lebelinoz This was very helpful and has gotten me a step further, thank you. But I've now run into another problem: instead of a and b being select functions, I want to use a vlookup - for a, i want to find the salary of an employee in the first sheet, and for b i want to find the salary of an employee in the second sheet using the employee name from the first sheet, so that i may compare salaries. Retrieving a works find, but for b, where the first argument is specified as sht1.Range(FindEmployer(sht1).address).Offset(1, 0) and is where i think it might be playing up. Any advice? Thank you. – Bobby Jatahwa Jun 18 '17 at 01:44
  • This sounds like a whole separate new question. Please accept an answer to this question, and I'll be happy to help with any new question you might have. – lebelinoz Jun 18 '17 at 02:06
  • @lebelinoz Unfortunately I cannot ask another question right now. – Bobby Jatahwa Jun 18 '17 at 02:09