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

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"JR Hester" <(E-Mail Removed)> wrote in message
news:A3C2FA8A-EB41-4630-9B43-(E-Mail Removed)...
> 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

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" wrote:

>
> 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
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "JR Hester" <(E-Mail Removed)> wrote in message
> news:A3C2FA8A-EB41-4630-9B43-(E-Mail Removed)...
> > 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
>
>

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.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"JR Hester" <(E-Mail Removed)> wrote in message
news:2F877E08-928E-4049-877F-(E-Mail Removed)...
> 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" wrote:
>
>>
>> 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
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "JR Hester" <(E-Mail Removed)> wrote in message
>> news:A3C2FA8A-EB41-4630-9B43-(E-Mail Removed)...
>> > 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
>>
>>

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.



"JR Hester" wrote:

> 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" wrote:
>
> >
> > 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
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > (E-Mail Removed)
> > "JR Hester" <(E-Mail Removed)> wrote in message
> > news:A3C2FA8A-EB41-4630-9B43-(E-Mail Removed)...
> > > 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
> >
> >