Replace text in fields

    Hello!


    First of all, excuse if my english is bad.


    I would like to create a very simple Excel file that makes the following:


    If I enter 2 in a field I want that 2 replaced with "07.15-19.00" and also a 1 entered to the field next to it. If I enter a 4 I want the 4 replaced with 00:00-08:00 and the number 8 entered in the field next to it.


    I assume this is very simple but I can't find anything in the help.


    Any tips?


    Thanks!
    Romanenko

    Re: Newbie: Replace text in fields


    Quote from Steve at work

    What are the cell references of your fields?


    Do you mean the A4 and so on?
    I want to enter the numbers that is to be replaced with text in column A starting on line 4 and all the way down to 20. The number that is going to be added is going to be added in column M on the same line. Is this what you mean?

    Re: Newbie: Replace text in fields


    yep. Right click your sheet, go to view code, and then change the option form general declarations to worksheet and selection change.


    Code
    If ActiveCell.Offset(-1, 0).Value = 2 Then
ActiveCell.Offset(-1, 0).Value = "7.15 - 19.00"
ActiveCell.Offset(-1,11).Value = 1


Elseif ActiveCell.Offset(-1, 0).Value = 4 Then
ActiveCell.Offset(-1, 0).Value = "00:00-8:00"
ActiveCell.Offset(-1,11).Value = 8
End If

    Re: Newbie: Replace text in fields


    This is great! One problem though. Now when I enter a 2 it changes it to text in every field, I only want it to change in column B (sorry that I said A earlier).


    Another thing. It would be nice to have a line that I can modify myself. Something like:
    x=line


    "If Bx=2 then replace 2 with "18:30-00.00" and put the number 5.5 in Mx"


    Really appreciate your help.

    Re: Newbie: Replace text in fields


    You might also look at Case logic, which is quite handy for translation type tasks. Assume that variable "X" contains the value to be tested and replaced, then:
    [VBA]Select Case X
    Case 2: X = "7.15 - 19.00"
    Case 4: X = "00:00-8:00"
    Case Else: ....
    End Select[/VBA]

    Re: Newbie: Replace text in fields


    I don't know what you mean by a line that you can modify yourself. If you want to be able to change it so it applies only to a certain row, use this If loop:

    Code
    If ActiveCell.Row = x then
....
end if



    And here's the modification that will make it do this only in column B

    Re: Replace text in fields


    Thank you so much! I almost got everything working now.


    The last thing is that I want to add a number into two fields and I've tested using And but cant get it to work.


    ElseIf ActiveCell.Offset(-1, 0).Value = 4 Then
    ActiveCell.Offset(-1, 0).Value = "00:00-8:00"
    ActiveCell.Offset(-1,10).Value = 8
    End If


    What I really want is the number 6 to be put in field 10 and the number 2 in field 11. I tried
    ActiveCell.Offset(-1,10).Value = 8 And ActiveCell.Offset(-1,11).Value = 2 but that returns the number 0 in field 10.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login