Split the Address into Row/Column 1

[kjv1611]

I was attempting to use a string to store the value of ActiveCell.Address, and that worked great. What I want to do now is to take that value, and split it to where I can use the new data to form a new range. For example, this is kind of what I want to do:

     Columns.SpecialCells(xlCellTypeLastCell).Select
     CurrentRange = ActiveCell.Address
     MyArray = Split(CurrentRange, "$")
     CurrentColumn = MyArray(1)
     CurrentRow = MyArray(2)
     CurrentColumn = CurrentColumn - 1
     Range(CurrentColumnCurrentRow).Select
     [GREEN]'Not sure of the right format to use here,
     'since the format is Range(a1) where a is the column, 
     'and 1 is the row. Maybe someone could help me there as well?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[SkipVought]

How perceptive!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kjv1611]

Would it not be possible to use the code regardless of the active sheet name? It worked fine when the code only included selecting "Chart1" on the ActiveSheet, regardless of the sheet name, just has this one error when dealing with the Range() or Cells() statements. If not, then how can retrieve the name of the active workbook without knowing in advance what the name will be?
I originally recorded a macro in the personal macro workbook, and used it to change the fill color of a data series for a chart based on the value of the current ActiveCell. That worked great, I just couldn't get the whole "lastcell" deal to work. Anyway, this is all kind of testing (for me) to be able to find the last column, subtract one from the column in order to move one left, and select the last cell containing data in that row. I kind of just started "shooting" at it, since none of the predefined functions seemed to be working for me. Don't know if it's reading all formatted cells, or just those with data. Anyway, thanks for the help whether I get it to work or not. I at least have it working for the user to just click on a cell, then click a button. If I ever get this one down, the user will only need to click on a button from the toolbar, which will automatically check the correct cell on each sheet, and format the chart on each sheet accordingly. I know that this particular use will work simply (or should), b/c the chart is always named "Chart1".

Anyway, thanks - whether I get this fully "conquered" or not

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[SkipVought]

NOT TO SELECT!!!!


Select ONLY works on the ACTIVE SHEET!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

With ActiveSheet
    With .Columns.SpecialCells(xlCellTypeLastCell)
        LastCol = .Column
        LastRow = .Row
    End With
    .Cells(LastRow, LastCol).Select
End With

works WITHOUT knowing the Active sheet.

But if you need to reference another SPECIFIC sheet, you may have to know a sheet name. It all depends what you are trying to accomplish!

So, what sheet were you on when you wanted to execute the Select?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kjv1611]

The one I am trying with now is labeled "T", and there are others in the same workbook - which I was planning on using a loop to make it work for all the sheets in the book, as they all have a chart with same type info, and same chart name on each sheet. Another sheet is labeled "T-HOU".

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[SkipVought]

I guess I was wanting to know if you were on ANOTHER sheet OTHER THAN the sheet your code was trying to SELECT on.

As far as looping thru all sheets in a workbook, looking for embedded charts...

dim ws as worksheet, co as chartobject
for each ws in activeworkbook.worksheets
  for each co in ws.chartobjects
    with co

    end with
  next
next

NOTE: Nothing is ACTIVATED or SELECTED but you can do stuff to the objects on these sheets if referenced properly.

faq707-4105 How Can I Make My Code Run Faster?



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kjv1611]

OH, sorry. No, just the active sheet (for now), but once I get it working for the active sheet, then I'll try to get ti working for the entire workbook. Well, anyway, here's one last try: I'll just post the entire code here (for changing the chart, and selecting the cells, etc.), all code that I have so far for this one item:

Sub ChangeLineColor()
    Dim MyRange As String
    Dim CurrentRange As String
    Dim LastCol As String
    Dim LastRow As Integer
    Dim LastAddress
    Dim MyArray(2)

    Columns.SpecialCells(xlCellTypeLastCell).Select
    With ActiveSheet
        With .Columns.SpecialCells(xlCellTypeLastCell)
            LastCol = .Column
            LastRow = .Row
        End With
    End With
    Range(LastRow, LastCol).Select [GREEN]'got error here[/GREEN]
[GREEN]'This part (MyArray part) would only be used to put the reference back together if needed)[/GREEN]    
    MyArray(1) = LastCol
    MyArray(2) = LastRow
    LastAddress = Join(MyArray, "$")
    Cells(LastAddress).Select [GREEN]'Got same error here[/GREEN]

[GREEN]'Above code or whatever you want to call it, is supposed to select the last cell in the next to last column with data in it.  Of course, I don't have all the code there yet, b/c I've just done part of it, and been testing before do the rest.[/GREEN]
    
[GREEN]'I know the below code works, but it is just so you can see what I am doing with the code.[/GREEN]
    If ActiveCell.Value > 1 Then
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.SeriesCollection(2).Select
        With Selection.Border
            .ColorIndex = 50
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With
        With Selection
            .MarkerBackgroundColorIndex = xlNone
            .MarkerForegroundColorIndex = xlNone
            .MarkerStyle = xlNone
            .Smooth = False
            .MarkerSize = 5
            .Shadow = False
        End With
    Else
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.SeriesCollection(2).Select
        With Selection.Border
            .ColorIndex = 3
            .Weight = xlThin
            .LineStyle = xlContinuous
        End With
        With Selection
            .MarkerBackgroundColorIndex = xlNone
            .MarkerForegroundColorIndex = xlNone
            .MarkerStyle = xlNone
            .Smooth = False
            .MarkerSize = 5
            .Shadow = False
        End With
    End If
End Sub

Well, if there is anything that gives anyone a hint on this one, I'm all ears... [BIGEARS]

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[SkipVought]

Your error was in 2 places...

    Dim LastCol As [red][b]Integer[/b][/red]
    Dim LastRow As [red][b]Long[/b][/red]
    Dim LastAddress
    Dim MyArray(2)

    Columns.SpecialCells(xlCellTypeLastCell).Select
    With ActiveSheet
        With .Columns.SpecialCells(xlCellTypeLastCell)
            LastCol = .Column
            LastRow = .Row
        End With
    End With
    [red][b]Cells[/b][/red](LastRow, LastCol).Select 'got error here

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[kjv1611]

Thanks a bunch, Skip! That was the ticket, all runs smoothly, now, and I can take out part of the garbage that will not be necessary, such as the MyArray stuff. Have a star (especially for your patience here).

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[SkipVought]

BTW,

The REASON that column is INTEGER and row is LONG is that columns range from 1 to 256 while rows range from 1 to 65536, EXCEEDING the limit of INTEGER.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884