Actions carried out on wrong sheet

[andysh]

Hi

Apologies for the messy, cobbled together code but that's my novice level (for now)

For some reason the below is carrying out the first part of the code on worksheet "Aged stock" instead of worksheet "Finance" and I'm at a loss as to why.

Any suggestions gladly welcome.

With Worksheets("Finance")
Columns("N:Q").EntireColumn.Delete
LR = Cells(Rows.Count, 2).End(xlUp).Row
Range("N2:N" & LR) = Evaluate("M2:M" & LR & "/K2:K" & LR)
Range("N2:N" & LR).NumberFormat = "£0.00"
End With



With Worksheets("Aged Stock")
LR = Cells(Rows.Count, 2).End(xlUp).Row
Range("A2:A" & LR).Copy Range("H2:H" & LR)
Range("H2:H" & LR).TextToColumns Destination:=Range("H2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="@", FieldInfo:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True
End With



With Worksheets("Aged Stock")
LR = .Cells(.Rows.Count, "H").End(xlUp).Row
With .Range("I2:I" & LR)
.FormulaR1C1 = "=VLOOKUP(RC8,'Finance'!C1:C14,14,FALSE)"
.Value = .Value
End With
End With



With Worksheets("Aged Stock")
LR = Cells(Rows.Count, "I").End(xlUp).Row
Range("J2:J" & LR) = Evaluate("F2:F" & LR & "*I2:I" & LR)
Range("J2:J" & LR).NumberFormat = "£0.00"
End With

 

[Joe4]

If you are going to use "With", if you want the ranges inside to apply to the sheet mentioned in the "With" statement, it needs to look like this:
.Range(...)
not
Range(...)

Without the period before the word "Range", it defaults to the active sheet.
The period is what tells Excel that the range applies to the sheet in the "With" statement.

 

[Fluff]

You need to add a full stop in front of the ranges like

With WorkSheets("master")
   .Columns("N:Q").EntireColumn.Delete
   lr = .Cells(Rows.Count, 2).End(xlUp).Row
   .Range("N2:N" & lr) = .Evaluate("M2:M" & lr & "/K2:K" & lr)
   .Range("N2:N" & lr).NumberFormat = "£0.00"
End With

Otherwise it's looking at the activesheet

 

[andysh]

Thanks Joe, good explanation

 

[Joe4]

You are welcome.
Glad we could help.

 

[andysh]

Hmmm
Now I've added the dots the 'evaluate' calculation is returning a #DIV/0!, is there something I need to change so it calculates on the same sheet?
It was working fine before the dots were added

 

[Joe4]

Now I've added the dots the 'evaluate' calculation is returning a #DIV/0!, is there something I need to change so it calculates on the same sheet?

Did you add the dot before "Evaluate", like Fluff showed in his response?

 

[andysh]

DOH
It's always the tiny things you miss


Thanks guys