Editing cell contents using VBA

[Mortadella]

Hi!

I have a number of situations where the cell content is quite similar and I could really benefit by being able to replace one or two characters within the cells to use elsewhere on the sheet.
Typically it will be something as simple as '=+A41 I simply want to remove the apostrophe and sometimes the cell references. Since I often have quite a few of them I want to put the macro into a loop format which I have no problem with but for the life of me with all my references I can't find any example of how to edit one cell.

Can anyone give me a hand?
All the best,
Mortadella

 

[NARAYANK991]

Hi ,

I am not able to understand ; what do you want the macro to do ?

Narayan

 

[Mortadella]

'=+A19'=+A52'=+A85
'=+A30'=+A63'=+A96
'=+C8'=+C41'=+C74
'=+C19'=+C52'=+C85
'=+C30'=+C63'=+C96
'=+C8'=+C41'=+C74
'=+C19'=+C52'=+C85
'=+C30'=+C63
'=+C96
The above would be the contents of cells Q9 to S16 (each has a '=+X22 formula) and I would like to remove the apostrophes (which would change the items to formulas that are useful to me) and I would also like to be able to alter the formulas as well to be able to use similar formulas in other configurations.

Mortadella

 

[NARAYANK991]

Hi ,

You can replace all the apostrophes using a Find & Replace , either manually or using VBA ; as far as editing a cell is concerned , see if this helps :

http://www.mrexcel.com/forum/excel-...lications-code-select-cell-put-edit-mode.html

Narayan

 

[SirJB7]

Hi, Mortadella!

Even the posted link might help you removing the apostrophes, I still don't understand what do you want to do. If that doesn't solve your issue, please elaborate and consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

 

[Mortadella]

Hi, Mortadella!

Even the posted link might help you removing the apostrophes, I still don't understand what do you want to do. If that doesn't solve your issue, please elaborate and consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!

Hi!

I haven't used this version of the software so I missed the little Upload a File - I have attached the file now. Basically I want to edit cell Q10 and do what would normally happen by keying in F2 Home Del because I want to get rid of the apostrophe that is making the formula I have in the cell a label. (Eg., makes '=+A30 into =+A30)

I would also like to be able to replace the cell references with others at a later date so I need something that will do more than eliminate the apostrophe in the long run but can be used for editing other things as well.

I was sent
Application.SendKeys "{F2}"
Application.SendKeys "{HOME}"
Application.SendKeys "{DELETE}"
and variations, but the "{HOME}" ignores the apostrophe and eliminates the = instead.

I frequently find myself looking for ways to edit cells and have yet to find a methodology that really seems to work. It mystifies me why this seems to be something that none of the manuals I have deals with.

Any help you can give me will be highly appreciated

All the best,
Mortadella

 

[NARAYANK991]

Hi ,

There is no reason why the code you have been given will not work. See your file now.

Narayan

 

[SirJB7]

Hi, Mortadella!

I wouldn't rely on SendKeys, whenever I used something happened, almost always. Instead of that try this (from 2nd uploaded file):

Option Explicit

Sub DoThoseStrangeThings()
  ' constants
  ' declarations
  Dim c As Range
  ' start
  Set c = ActiveCell
  ' process
  Do Until c.Value = "***" Or c.Value = ""
  c.Formula = c.Formula
  Set c = c.Offset(1, 0)
  Loop
  ' end
  Set c = Nothing
  Beep
End Sub

BTW NARAYANK991's code didn't work for me.

And if you're interested in Sudoku games, give a look to the uploaded file, it's a file of... 2004! (10 years yet, wow) converted later to .xslm. It's in Spanish as it was intended for F&F usage (family and friends).

Worksheets:

Juego: main tab

Work: working area (rows 1:10) and specific layouts storage (row 11 in advance)

Instrucciones: detailed instructions

Prueba: for developing test purposes

Repetidos: stats of automatically generated layouts

Worksheet Juego:
4 command buttons (from top to bottom: Build, Restart, Stop, Continue)
2 labels (from top to bottom: button pressed, playing/interrupted)
7 parameters in U2:U8 (from top to bottom)
- M (mode): Manual or Auto
- N (level): Basic, Intermediate or Advanced (only for A)
- # (clues): 28 for B, 26 for I and 24 for A (+/- 1 or 2) (only for A)
- J° (game no.): from 1 to 16777215 (only for A)
- S (fix clues): S for fixing, N for randomizing (only for A)
- P (steps): steps in game generation (avoid repeated) (only for A)
- # (clues): actual no. from Works tab

Hope it helps. If I find the time (aka the moment for having a couple -or three- Carlsberg) maybe I translate it to English. In the meantime, Google Translator should help you with the Instrucciones tab.

Regards!

PS: Clarín is the newspaper where they are published daily.

 

[Mortadella]

Dear Sir JB7,

If I haven't said thank you it's because I have been fascinated with the files you sent -my apologies for such tardiness. With respect to the files I can only say WOW!! Clarin's spreadsheet has so much stuff in it I would love to know how to do I can't believe it. I have wanted to do so many of the display techniques that he has shown for so long I can't say.

Many many thanks,
Mortadella

 

[SirJB7]

Hi, Mortadella!
Glad you liked it. Thanks for your feedback and for your kind words too. I'll try to make an international (aka language independent version) and post it here. But not sure when that would happen.
Regards!