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.

 

[kjicha]

The original code that you gave me doesn't work either now....thinking I did something, strange

 

[RickXL]

Try putting the new code back then exiting Excel and trying again.

If that does not work then we will need to do some debugging. It works on my PC but that is probably not much help.

 

[kjicha]

Ok, your final code did work now, I had to close Excel and reopen it backup. One more thing though. If I insert a row between the numbers of 1 and 2, it still does wrap the text but doesn't put the number 3 in, should it be?

 

[kjicha]

I am also using Excel 2013 if that matters.

 

[RickXL]

Sorry, that was my fault. It probably explains you earlier problem as well.

Try this:

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

I was disabling events then quitting the code before re-enabling them if more than one cell had been selected.
When you insert a row the whole row is selected. S after that no more events are processed. Apologies.

Edit: Code re-structured.

Question: Overtyping an entry in column C increases the number in column A. Is that correct or should the value in A remain unchanged?

 

[kjicha]

The # should sat in value A. So if there is a value in A already for that row and I make a change in C for the same row, the value shouldn't change in A. Hope that makes sense

 

[RickXL]

OK, we must be getting close now?

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

 

[kjicha]

AWESOME!!!! Thank you very much, it all worked as planned.

 

[RickXL]

Hooray!

No problem - I knew we would get it going eventually.

Regards,

 

[kjicha]

I am having an issue when the numbering get to 1.9 after it is 1.9 and it goes to the next row it just puts 1.1 It needs to be 1.10 Same when it gets to 1.19 it just goes to 1.2 instead of 1.20.

Any ideas?