Edit cell value

[JR Hester]

Excel XP running on WinXP3

Admittedly my macro expertise is limited to recording keystrokes then simple
code editing. I need some help in editing the contents of a cell. I am
setting up a new empty workbook from an existing workbook and need to clear a
major range in each sheet AND change teh last character of text in a cell.
Everything works except changing that last text character.

Other posts point to fact that keystroke recording stops recording once the
cell editing begins. Do I need to use the SENDKEYS command to enter the
editing mode? And if this is a feasible method, what would be the correct
syntax to
Enter edit mode, go to end of text, backspace , enter a digit (9), and
finalize the edit process.

My working code for the selection process and data clearing is below.

'
Range("B2").Select
' ActiveCell.FormulaR1C1 = "11/1/2009" -- this is where I need to replace
last character of cell B2--
Range("D2:H811").Select
Selection.Clear
ActiveSheet.Next.Select
End Sub

I think I am close, just don't know how to translate my desires into VBA
Excel can execute.

Thanks for yoru suggestions

 

[Don Guillett]

Post ALL of your code. Is your cell b2 a date or text
Sub changedate()
With Range("b2")
If IsNumeric(.Value) Then
..Value = .Value + 365
Else
..Value = Left(.Value, (Len(.Value) - 1)) & 9
End If
End With
Range("D2:H811").Clearcontents

End Sub

 

[JR Hester]

Sorry, here is the whole code after I tried inserting your suggestion. I
received an error 1004 expected End Sub after the Application.Sendkeys line.
Thanks anyway, I have already spent way too much time on this macro.

Have a great day

Sub mcrsetup()
'
' mcrsetup Macro
' Macro recorded 11/18/2008 by jhester
'
' Keyboard Shortcut: Ctrl+q
'
Range("B2").Select
' ActiveCell.FormulaR1C1 =
' Application.SendKeys ("{F2}{backspace}{backspace}o9{enter}")
' Sub changedate()
' With Range("b2")
' If IsNumeric(.Value) Then
' ..Value = .Value + 365
' Else
' ..Value = Left(.Value, (Len(.Value) - 1)) & 9
' End If
'
' End Sub

' Range("D2:H811").Select
' Selection.Clear
' ActiveSheet.Next.Select

End Sub

 

[Don Guillett]

You do NOT put a sub within a sub. You did not answer my question. You
should have tried my macro as it was INSTEAD of yours. Never use sendkeys
unless absolutely necessary (and its NOT here). Go back and try mine.
If all else fails, send your workbook to my address below along with a clear
explanation of what you want.

 

[JLGWhiz]

Working with dates and times can be difficult. Take a look at Chips page on
the subject and see if it gives you any ideas.

http://www.cpearson.com/excel/datetime.htm

Personally, I am thinking that the replace function might be what you need.