How to use a macro to enter in a number in a cell....

[kjicha]

Hello, I am wanting one macro to do the following steps:

1. Wrap text when cell is selected (this will be in column C all the time)
2. Adjust the height of cell to fit the wrap text (this will be in column C all the time)
3. When column C has something in it, in column A I want to place number 1.

Note...Currently in A1 I have the following formula....=MAX(A3:A850)

I want it so that when something is in Column C, the text gets wrapped, the cell is adjusted to height correctly, and the number based on A1 is put in correctly in A column.

Hope this makes sense. Thank you.

 

[Fishboy]

Hello, I am wanting one macro to do the following steps:

1. Wrap text when cell is selected (this will be in column C all the time)
2. Adjust the height of cell to fit the wrap text (this will be in column C all the time)
3. When column C has something in it, in column A I want to place number 1.

Note...Currently in A1 I have the following formula....=MAX(A3:A850)

I want it so that when something is in Column C, the text gets wrapped, the cell is adjusted to height correctly, and the number based on A1 is put in correctly in A column.

Hope this makes sense. Thank you.

Hi kjicha, welcome to the boards.

Do you want the value in column A to always be a 1, or do you want it to be the row number? If it is always a 1 then I don't see how a MAX formula in A1 is going to give a result of anything but 1.

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I think I understood some of your requirements. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo err
    Application.EnableEvents = False
    With Target
        If .Count > 1 Then Exit Sub
        If .Column = 3 Then
            .WrapText = True
            If .Value <> "" Then .Offset(0, -2).Value = 1
        End If
    End With
err:
    Application.EnableEvents = True
End Sub

Tell me if you need changes.

I forgot to say, the code needs to be pasted into the Sheet module associated with the worksheet where you need it to work.

Regards,

 

[kjicha]

Thank you for the information. However, I can't seem to run it. I have the code in my sheet and in the module, but it doesn't do anything, I know I am doing something wrong. Sorry.

 

[RickXL]

Take a look at this and use the section headed:
[h=3]Paste code in a Sheet module[/h]

 

[kjicha]

Ok, my sheet that I am working on is called Sheet11 (Requirements) I clicked on that and pressed F7. I pasted the code that you have above in the right hand site. When I clicked on the run icon, the macro window came up and nothing was in there. Do I need to do something there?

 

[RickXL]

No, you can't run the code directly.

What will happen is when you change something in column C it will carry out the actions.

 

[kjicha]

OK, got it. Ran into one issue though. So I entered text into C3, and it put 1 in the A3 cell, which is correct. However, when I put in text in C4, it puts 1 again in C4, and it should be 2. Need it to look at what number is in cell A1 and add 1 number.

Does that make sense?

 

[RickXL]

Yes.

Try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo err
    Application.EnableEvents = False
    With Target
        If .Count > 1 Then Exit Sub
        If .Column = 3 Then
            .WrapText = True
            If .Value <> "" Then .Offset(0, -2).Value = Range("A1") + 1
        End If
    End With
err:
    Application.EnableEvents = True
End Sub

 

[kjicha]

Tried the new code and it now didn't do anything. Didn't wrap, expand cell height, or put a number in.