Getting Run-time error '1004' when trying to change Cell Orientation

[Pablo Espressobar]

Hello Everyone,

I'm trying to write an excel macro that sets the Orientation of the 2nd Row of a column to "90" where i have a selected cell and back to "0" when i'm selecting a cell in another column.

So for example:
The orientation in Row2 is "90" in all Rows. Then i'm clicking into "C8". This triggers my macro and it changes the Orientation of "C2" to "0". Then when i'm clicking into "E6", the Orientation of "C2" changes back to "90" and the Orientation of "E2" changes to "0".

My code gives the following Error Message: Run-time error '1004': Method 'Range' of object'_Worksheet' failed

Sorry for the long introduction, but i want to make sure you know everything.

Here is my current code: (Error line in red)

Private lastColumn As String


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim column As String
Dim finalColumn As String
Dim isNum As Boolean
Dim length As Integer
Dim i As Integer

column = Target.address
column = Replace(column, "$", "")

length = Len(column)

For i = 1 To length

If Not (IsNumeric(Mid(column, i, 1))) Then

finalColumn = finalColumn & Mid(column, i, 1)

End If

Next i


If finalColumn <> lastColumn Then
Range(finalColumn & "2").Orientation = 0
Range(lastColumn & "2").Orientation = 90
End If

lastColumn = finalColumn

End Sub

Thanksfully,
Pablo

 

[RedBeard]

Re: Getting Run-time erroe '1004' when trying to change Cell Orientation

You have defined the lastColumn variable outside the sub.

 

[Pablo Espressobar]

Re: Getting Run-time erroe '1004' when trying to change Cell Orientation

That shouldn't be a problem. I've tried to declare lastColumn inside my subroutine, but it gives the exact same error.
I want lastColumn to keep its value after the subroutine exits so it can remember the "last column" for resetting it's Orientation.

 

[RedBeard]

Re: Getting Run-time erroe '1004' when trying to change Cell Orientation

Then you need to read this: https://support.microsoft.com/en-us/help/141693/scope-of-variables-in-visual-basic-for-applications

 

[JoeMo]

Re: Getting Run-time erroe '1004' when trying to change Cell Orientation

From the code you have shown us, lastColumn = "" so Range(lastColumn & "2").Orientation = 90 becomes Range("2").Orientation = 90 which is not a valid range object. If you want lastColumn to retain its value, dimension it as static inside the routine:
Static lastColumn as string
Dim column As String
Dim finalColumn As String
Dim isNum As Boolean
Dim length As Integer
Dim i As Integer

 

[Fluff]

Re: Getting Run-time erroe '1004' when trying to change Cell Orientation

Try this.
In the sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim finalColumn As Long
Dim isNum As Boolean
Dim length As Integer
Dim i As Integer

finalColumn = Target.column

If lastColumn = 0 Then lastColumn = finalColumn


If finalColumn <> lastColumn Then
    Cells(2, finalColumn).Orientation = 0
    Cells(2, lastColumn).Orientation = 90
End If

lastColumn = finalColumn

End Sub

and then at the very top of a standard module

Public lastColumn As Long

Ensure you remove the dim lastcolumn statement from your sheet module

 

[Pablo Espressobar]

Re: Getting Run-time erroe '1004' when trying to change Cell Orientation

Thank you for your answers, I've modified my code according to your suggestions and it works now.

 

[Fluff]

Re: Getting Run-time erroe '1004' when trying to change Cell Orientation

Glad we could help & thanks for the feedback