MOD Func

[ExcelChampion]

The below is simple:

If I type 3 in A1 the formula returns 1. If I type 1 in A1 the formula returns 2. If I type 2 in A1 the formula returns 3.

I want the opposite. If I type 2 in A1 it returns 1. If I type 1 in A1 it returns 3. If I type 3 in A1 it retunrs 2.

Can't get my head around it. Any help is appreciated.

PPT.xls
	A	B	C	D
1	3
2
3	1
Sheet1

 

[ExcelChampion]

Todd

Couldn't you use something like this?

Sub sht_Forward()
      If ActiveSheet.Index = Sheets.Count Then
            Application.Goto Sheets(1).Range("A1"), True
      Else
            Application.Goto Sheets(ActiveSheet.Index+1).Range("A1"), True
      End If
End Sub

Absolutely I could. but, this just seemed cleaner to me. Just call me picky.

Sub sht_Forward() 
    Sheets(Evaluate("MOD(" & ActiveSheet.Index& "," & Worksheets.Count & ")+1")).Select 
End Sub

 

[Norie]

Todd

If that works that's the important thing.

But I would have to ask why are you using Evaluate?

Why not actually use the VBA Mod function?

Sub sht_Forward()
    
    Application.Goto Sheets(ActiveSheet.Index Mod Sheets.Count + 1).Range("A1"), True
    
End Sub

 

[ExcelChampion]

Norie, that is even better!

Thanks!