Returning an offset value from a range

[snowbounduk]

I have the following code:

Sub AAddEmailAddressToEachProjectSheet()
    myCriteria = Sheets("Recd Plans").Range("C2:C168")
    For Each Crit In myCriteria
        With Sheets(Crit)
            .Range("A1").Value = "Project"
            .Range("A2").Value = "Summary"
        End With
    Next Crit
 
End Sub

Rather than enter "Project" in A1, I need the code to return the appropriate value from column K.

I have tried the follwoing code with various offset statements against

.Range("A1").Value = Crit

with no success.

Sub AAddEmailAddressToEachProjectSheet()
    myCriteria = Sheets("Recd Plans").Range("C2:C168")
    For Each Crit In myCriteria
        With Sheets(Crit)
            .Range("A1").Value = Crit
            .Range("A2").Value = "Summary"
        End With
    Next Crit
 
End Sub

Any help appreciated.

 

[TiaXL]

range("a1").value = activecell.offset(0,10).value

 

[snowbounduk]

Thanks for that.

I changed the offset to 8 and It only works if a cell in column C on Recd Plans sheet is selected and then it uses the same value, (K2) across all sheets rather than using K3 for C3 etc.

Any ideas?

 

[TiaXL]

of course my bad, you want to do

range("A1").VALUE = RANGE("A1").OFFSET(0,8).VALUE

 

[snowbounduk]

I've used

            .Range("A1").Value = Range("C2").Offset(0, 8).Value

This still only gives one value for all sheets, the value in K2. ANd will only work if I run the macro from the Recd Plans sheet.

Any other pearls of wisdom?

 

[gregtx81]

Any other pearls of wisdom?

Rather snarky today, are we?

Are the values in Column K on the 'Recd Plans' sheet, or each sheet that you're looping.

If the former:
.Range("A1").value=crit.offset(0,8).value

 

[snowbounduk]

My apologies, it wasn't meant to be snarky! It was meant to be funny. Oops.

The values in column K are in the Recd Plans sheet.

I have tried the Crit.offset(0,8). value and get a Run-time error '424':
Object required.

Any ideas?

 

[TiaXL]

why dont you just select the appropriate cell in column A in your code and the offset value will work every time ?

 

[gregtx81]

Ok, here's the problem. The variable 'MyCriteria' was being treated as an array, an array of the values in the range C2:C168, as opposed to a range itself. I think changing the line

    myCriteria = Sheets("Recd Plans").Range("C2:C168")

to

    Set myCriteria = Sheets("Recd Plans").Range("C2:C168")

fixes the problem. But for good measure use option explicit and define all of the variables. Here's your code adapted for my setup (change ranges and sheetnames).

Option Explicit

Sub AAddEmailAddressToEachProjectSheet()
    Dim mycriteria As Range
    Dim crit As Range
    Set mycriteria = Sheets(1).Range("C2:C6")
    For Each crit In mycriteria.Cells
        With Sheets(crit.Value)
            .Range("A1").Value = crit.Offset(, 8).Value
            .Range("A2").Value = "Summary"
        End With
    Next crit
 
End Sub

 

[snowbounduk]

Many thanks for that, a couple of tweaks and it works a treat!

Option Explicit
Sub AAddEmailAddressToEachProjectSheet()
    
    Application.ScreenUpdating = False
    Dim myCriteria As Range
    Dim Crit As Range
    
    Set myCriteria = Sheets("Recd Plans").Range("C2:C168")
    Sheets("Recd Plans").Select
    Range("C2").Select
    For Each Crit In myCriteria.Cells
        With Sheets(Crit.Value)
            .Range("A1").Value = Crit.Offset(, 8).Value
            .Range("A2").Value = "Summary"
        End With
    Next Crit
    Sheets("Summary").Select
    Application.ScreenUpdating = True
    
End Sub