comin' home

[nicitalia]

Hi everybody folks,

I've a urgent problem with a macro, must send the work in two days please help me!

Here's the problem:

I've got a sub with 3 big different with...end with structure, and each one of them contains some if statements.
I've built the sub in a way that to run correctly you have to select D10 cell and then launch the sub, so D10 is my Activecell.
My problem is, how I can select or activate this cell everytime that a with...end with ends?
I mean after the first with...end with return in D10 and do the second with...end with and so on...

Obviously I can't write simply range("D10") beacause I've to use this sub on different worksheets and range...

eg:

Sub CatA_2007()

On Error Resume Next

Dim Xcella As Long
Dim Ycella As Integer

Xcella = ActiveCell.Row
Ycella = ActiveCell.Column


With Range(ActiveCell, ActiveCell.Offset(300, 0)).Select

For Each c In Selection

If c.Offset(0, -2).Value = 1015 Then c.Value = c.Offset(0, -1).Value / Worksheets("rif").Range("Aimp07").Cells(1).Value
If c.Offset(0, -2).Value = 1017 Then c.Value = c.Offset(0, -1).Value / Worksheets("rif").Range("Aimp07").Cells(2).Value
If c.Offset(0, -2).Value = 1018 Then c.Value = c.Offset(0, -1).Value / Worksheets("rif").Range("Aimp07").Cells(3).Value

Range(Xcella, Ycella).Activate 'don't work!!!!

End With
End Sub

 

[SkipVought]

Hi,

My advice: avoid select and activate methods as much as possible and rather, code using references.

If your requirement is that the user selects a cell before the procedure runs, that is fine, but do not change the selection in the procedure, rather reference relative to the user's selection.

Sub CatA_2007()

    On Error Resume Next
    
    For Each c In Range(ActiveCell, ActiveCell.Offset(300, 0))
        Select Case c.Offset(0, -2).Value
            Case 1015
                c.Value = c.Offset(0, -1).Value / Worksheets("rif").Range("Aimp07").Cells(1).Value
            Case 1017
                c.Value = c.Offset(0, -1).Value / Worksheets("rif").Range("Aimp07").Cells(2).Value
            Case 1018
                c.Value = c.Offset(0, -1).Value / Worksheets("rif").Range("Aimp07").Cells(3).Value
        End Select
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}