ScrollArea : How I can do that
 

Hello

I have many condition to evaluate the scrollArea, how I can do that in
one line


If x = 3 Then
ActiveSheet.ScrollArea = "D6:D8"
ElseIf x = 4 Then
ActiveSheet.ScrollArea = "D6:D9"
ElseIf x = 5 Then
ActiveSheet.ScrollArea = "D6:D10"

ElseIf ....
....
End If

Like ...

Dim j, k
J = 6 ' row
K = 4 ' col

ActiveSheet.ScrollArea = " k,j: k,j+x"

I don't have any idea to write the good instruction for the ScrollArea

You can help me

Thanks

Gief

ScrollArea : How I can do that
 

ElseIf x = 5 Then
ActiveSheet.ScrollArea = "D6:D10"

One idea...

Sub Demo()
Dim x As Long
Dim R As Long, C As Long
x = 5 ' <- Your number
ActiveSheet.ScrollArea = Range("D6").Resize(x).Address
'or
R = 3
C = 4
ActiveSheet.ScrollArea = Cells(R, C).Resize(x).Address
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


"Gief Hell" wrote in message
...
Hello

I have many condition to evaluate the scrollArea, how I can do that in
one line


If x = 3 Then
ActiveSheet.ScrollArea = "D6:D8"
ElseIf x = 4 Then
ActiveSheet.ScrollArea = "D6:D9"
ElseIf x = 5 Then
ActiveSheet.ScrollArea = "D6:D10"

ElseIf ....
....
End If

Like ...

Dim j, k
J = 6 ' row
K = 4 ' col

ActiveSheet.ScrollArea = " k,j: k,j+x"

I don't have any idea to write the good instruction for the ScrollArea

You can help me

Thanks

Gief

ScrollArea : How I can do that
 

Maybe you could use this function to set the address for assigning the
Scroll Area....

Function AreaAddress(StartRow As Long, StartColumn As Long, _
RowOffset As Long, ColumnOffset As Long) As String
AreaAddress = Chr$(StartColumn + 64) & CStr(StartRow) & ":" & _
Chr$(StartColumn + ColumnOffset + 64) & _
CStr(StartRow + RowOffset)
End Function

Note: The function provides for both a row and column offset.

To use it, just pass the value you have and assign it directly. As per your
example...

J = 6 ' Start Row
K = 4 ' Start Column
X = 3 ' Row Offset
Y = 0 ' Column Offset

ActiveSheet.ScrollArea = AreaAddress(K, J, Y, X)

Rick


"Gief Hell" wrote in message
...
Hello

I have many condition to evaluate the scrollArea, how I can do that in
one line


If x = 3 Then
ActiveSheet.ScrollArea = "D6:D8"
ElseIf x = 4 Then
ActiveSheet.ScrollArea = "D6:D9"
ElseIf x = 5 Then
ActiveSheet.ScrollArea = "D6:D10"

ElseIf ....
....
End If

Like ...

Dim j, k
J = 6 ' row
K = 4 ' col

ActiveSheet.ScrollArea = " k,j: k,j+x"

I don't have any idea to write the good instruction for the ScrollArea

You can help me

Thanks

Gief

ScrollArea : How I can do that
 

Function AreaAddress(StartRow As Long, StartColumn As Long, _
RowOffset As Long, ColumnOffset As Long) As String
AreaAddress = Chr$(StartColumn + 64) & CStr(StartRow) & ":" & _
Chr$(StartColumn + ColumnOffset + 64) & _
CStr(StartRow + RowOffset)
End Function

Note: The function provides for both a row and column offset.

To use it, just pass the value you have and assign it directly. As per
your example...

J = 6 ' Start Row
K = 4 ' Start Column
X = 3 ' Row Offset
Y = 0 ' Column Offset

ActiveSheet.ScrollArea = AreaAddress(K, J, Y, X)

I made a last minute change in the order of the arguments and forgot to
update the above example. The last statement should be this instead of what
I posted originally...

ActiveSheet.ScrollArea = AreaAddress(J, K, X, Y)

Rick