PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] Just want to select a cell!-VBForums
Results 1 to 6 of 6

Thread: [RESOLVED] Just want to select a cell!

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Apr 2005
    Posts
    103

    Resolved [RESOLVED] Just want to select a cell!

    I'm going insane trying to do what should be the simplest of operations in an Excel macro, so hopefully someone can tell me where I'm going wrong.

    I have a spreadsheet with several worksheets, containing various types of data in the cells. Some of the cells are formatted as a custom date, so they appear on screen as "Monday 07 Nov". Their values are stored in the standard Excel way, as long integers counted from 1st January 1900, in this case 38663.

    Now, everything in the spreadsheet has to be translated, including these dates.
    In order to be able to do that, I need to hard-code the dates as text in the cells. I've tried several methods, and the only one that nearly works is in the code below. If I simply try to change the format of the cell in question with ActiveCell.FormulaR1C1 I get a cyclical error. Therefore, I use an empty cell, copy the contents into it and retrieve the value of this cell into the original cell. For some reason, I am unable to reselect the original cell after performing the operation. The only way I can do it is to explicitly call the range of a cell, but this is useless for a macro.

    Basically, how to I tell Excel to remember the current cell it's in, go do something else and then come back to the current cell?

    VB Code:
    1. Sub ReformatDates()
    2.  
    3. Dim Storage As String
    4. Dim rRow As Long
    5. Dim cColumn As Long
    6. Dim RangeString As String
    7.  
    8. 'use an empty cell to store value
    9. Storage = "Z100:Z100"
    10.  
    11.     'For each cell, if Cell.Format = custom, do this (not written yet)
    12.    
    13.     'Store the position of the starting cell
    14.     cColumn = Selection.Column
    15.     rRow = Selection.Row
    16.    
    17.     'make the temporary cell have the same value as this one
    18.     Range(Storage).Value = Selection.Value
    19.    
    20.     'go to the temporary cell
    21.     Range(Storage).Select
    22.    
    23.     'copy and paste itself as a value
    24.     Selection.Copy
    25.     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    26.         :=False, Transpose:=False
    27.      
    28.     'Try to format the range so the cell can be selected - does not work
    29.     'RangeString = rRow & cColumn & ":" & rRow & cColumn
    30.  
    31.     'column letters are interpreted wrongly and entire chunk of worksheet gets    
    32.     'selected instead
    33.     Range(cColumn & ":" & rRow, cColumn & ":" & rRow).Select
    34.    
    35.     'Does not work
    36.     'Range(RangeString).Select
    37.  
    38.     'This works but it useless
    39.     'Range("D13:D13").Select
    40.     'Format the cell as the text equivalent of the contents of the temporary
    41.     'cell.
    42.     ActiveCell.FormulaR1C1 = "=TEXT(R100C26,""DDDD DD MMM"")"
    43.  
    44. End Sub

    It would be great to be able to do this for every cell in the spreadsheet which is formatted as Custom, but any advancement would be welcome.

    There's probably a one-line command somewhere that reformats the selected cell as text in one go, but I can't find it.
    Note that reformatting a cell manually as text doesn't work, as in that case I get a longint in the cell instead of a formatted date.
    Last edited by pickarooney; Oct 25th, 2005 at 05:57 AM.

  2. #2
    New Member
    Join Date
    Oct 2005
    Location
    Perth, Australia
    Posts
    12

    Re: Just want to select a cell!

    Hi Pickarooney

    I've gone about this in a completely different way. Not sure how much of this you need or whether I've overlooked an Excel function which converts date serial numbers back to text. Anyhow, it works fine as a one-off solution.

    Function MonthName(intMonth As Integer) As String

    Dim vntMonthArray As Variant

    vntMonthArray = Array("None", "Jan", "Feb", "Mar", "Apr", _
    "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

    MonthName = vntMonthArray(intMonth)
    End Function

    Function DayName(intDay As Integer) As String

    Dim vntDayArray As Variant

    vntDayArray = Array("None", "Mon", "Tue", "Wed", "Thur", _
    "Fri", "Sat")

    DayName = vntDayArray(intDay)
    End Function

    Sub BuildDateText(strAddress As String)

    Dim lngCellValue As Long
    Dim strBuild As String

    Range(strAddress).Select
    'Capture the date serial number
    Selection.NumberFormat = "General"
    lngCellValue = Range(strAddress).Value
    'Use the current cell as a work area for this stored number
    '(",2" means start weeks on a Monday)
    ActiveCell.Formula = "=WEEKDAY(" & lngCellValue & ",2)"
    strBuild = strBuild & DayName(Range(strAddress).Value)
    ActiveCell.Formula = "=DAY(" & lngCellValue & ")"
    'May want to put something here to prefix <10 with zero!
    strBuild = strBuild & " " & Range(strAddress).Value
    ActiveCell.Formula = "=MONTH(" & lngCellValue & ")"
    strBuild = strBuild & " " & MonthName(Range(strAddress).Value)
    ActiveCell.Formula = "=YEAR(" & lngCellValue & ")"
    'May want to put something here to prefix <10 with zero!
    strBuild = strBuild & " " & Range(strAddress).Value

    Range(strAddress).Value = strBuild

    End Sub

    Sub HardCodeDate() '***Main Procedure***

    Dim rngCell As Range

    For Each rngCell In Range("myrange") 'ie Target range
    rngCell.Select
    If Selection.NumberFormat = "mmmm d, yyyy" Then
    BuildDateText (rngCell.Address)
    End If
    Next rngCell
    End Sub

  3. #3
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: Just want to select a cell!

    Does this help? It is hard coded, but you can replace it with whatever programmatic cell references you want. It replaced "=B7+C7" with "13" where B7 contains 6 and C7 contains 7.
    Code:
    Sub Macro1()
        Range("D7").Formula = Range("D7").Value
    End Sub
    Good Learning and Good Programming!
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  4. #4
    Frenzied Member DKenny's Avatar
    Join Date
    Sep 2005
    Location
    on the good ship oblivion..
    Posts
    1,171

    Re: Just want to select a cell!

    You need to copy the Text (displayed vs stored value) back into the cell. THopefully the following code will help.

    VB Code:
    1. Option Explicit
    2.  
    3. Sub ChangeDate()
    4. Dim WkBook As Workbook
    5. Dim WkSht As Worksheet
    6. Dim MyCell As Range
    7. Dim Temp As String
    8.     Set WkBook = ThisWorkbook
    9.    
    10.     For Each WkSht In WkBook.Worksheets 'loop through all sheets
    11.         For Each MyCell In WkSht.UsedRange ' loop through all cells in the used range
    12.             With MyCell
    13.                 If Len(.Text) <> 0 Then 'only apply to the following to cell that have a value
    14.                     Temp = .Text 'get the text (not the value) that is displayed in the cell
    15.                     .NumberFormat = "General" 'change the number form back to default
    16.                     .Value = Temp 'set the cell value equal to the saved text
    17.                 End If
    18.             End With
    19.         Next MyCell
    20.     Next WkSht
    21.    
    22.     Set WkSht = Nothing
    23.     Set WkBook = Nothing
    24. End Sub
    Declan

    Don't forget to mark your Thread as resolved.
    Take a moment to rate posts that you think are helpful

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Apr 2005
    Posts
    103

    Re: Just want to select a cell!

    Hi Guys,

    First off, thanks for all your efforts. I was away yesterday, so didn't get time to try your code until now.

    Dave, that's quite a different way of thinking to me, I have to say I'm going to try your code out in a little bit.

    Webtest, I laughed when I saw your post, as I was convinced you had just found a single-line answer that was completely eluding me. Unfortunately, it doesn't seem to work, as even after the replacement I end up with the same thing in the cell, i.e. a value of 38663, represented by the date in written format.

    Declan, your code sample looks as though it should work, but when I run it, the loop only executes 4 times, even though each worksheet in my spreadsheet has 6 columns x 236 rows. I put a watch on WkSht.UsedRange.Cells.Count and it returns 4 empty cells. I tried preselecting some cells on the worksheet, and leaving nothing selected before running the macro, but in each case it's the same - just 4 empty cells are looked at.

    [edit]I put a watch on wkbook and it seems the macro is running itself on a different file... I think it's because the macro is saved, along with all my other Excel macros in a file called PERSONAL.XLS in my Application Data Folder.

    By simply changing Set WkBook = ThisWorkbook to Set WkBook = ActiveWorkbook, the macro works perfectly

    Thank you very much.
    P
    Last edited by pickarooney; Oct 27th, 2005 at 07:24 AM.

  6. #6
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: [RESOLVED] Just want to select a cell!

    Thanks P ...

    I have precious little time to spend on the forum, so I often scan things quickly and "shoot from the hip". Some time I am going to have to review this thread thoroughly and find out what it is actually all about, but I've got to get back to real work now!
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width