Script for Automatically Generating Cell Value in Excel 1

[weigoldk]

I've simplified my issue a bit, but I don't think I've left off any important information.

Thanks in advance for your thoughts.

I have an Excel workbook (we have a mixed bag of 2000 and 2003 installs)

Column B has numbers
1
2
3
4
5

Column A has text

When I click onto cell Ax, I want Bx to automatically generate. For this instance, if I clicked into A6, B6 is populated with 6.

To go further, I might be forced to remove row 3 after I've completed row 4, 5, 6, etc., but I don't want the numbers in column B to adjust. If I remove row 3, I want column B to look like the list below. I'm unable to use the function Bx=Bx-1+1 or because my number 4 below would compute to 3 after the removal of the third row.
1
2
4
5

 

[taupirho]

How about this for a right click on the cell, should give you some ideas anyway


Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
If (Cells(Target.Row, 1)) = "" Then
Cells(Target.Row, 2) = ""
Else
Cells(Target.Row, 2) = Target.Row
End If
End Sub

 

[weigoldk]

Thanks taupriho, but what do I use as the "formula" for incrementing the number?

 

[SkipVought]

Hi,

I'd use the Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Count > 1 Then Exit Sub
        If .Column = 1 Then
            With .Offset(0, 1)
                If .Value = "" Then
                    .Value = .Offset(-1).Value + 1
                End If
            End With
        End If
    End With
End Sub

CAVEAT: entries in column A must be contiguous.


Skip,

_[red][/red]

 

[SkipVought]

... and start in ROW 2

Skip,

_[red][/red]

 

[weigoldk]

It works wonderfully.

Now if you would please point me to a reference or help me learn how it works.

I got adventurous and thought I'd try something like, if the contents of cell A3 was F (and only F), assign B3 B2+1

I modified just one line of code to:
If .Value = "F" Then

But that didn't work--as in nothing is populated into B3.

I know I'm changing the rules in the middle of the game, so if the answer is that I'd need to take a completely different route, I'll be happy with that as an answer.

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Count > 1 Then Exit Sub
        If .Column = 1 Then[b]
            If .Value = "F" Then[/b]
                With .Offset(0, 1)
                    If .Value = "" Then
                        .Value = .Offset(-1).Value + 1
                    End If
                End With[b]
            End If[/b]
        End If
    End With
End Sub

Skip,

_[red][/red]

 

[weigoldk]

Ohhhhhh.

Yea, that makes sense.

Thank you!

 

[weigoldk]

I am hugely grateful for your help.

But, I'm finding that I have to type the F in cell A3 then move out of cell A3 and back into it before cell B3 will populate.

 

[SkipVought]

I should have included that this code must be put in the SHEET Code Window, for the SHEET of interest, as this is where all the Worksheet Event code is.

For the Change Event, the Target argument is the Range of the change. It could be one cell that you changed or it could be more than one if, for instance, you pasted from a multi cell copy.

I'd direct you to being by examining the Excel Object Model to understand the Objects that are available and what the Properties and Methods are for manipulating them. Use the Object Browser in the VB Editor. Use HELP. Use

http://www.microsoft.com

Check out postings here at Tek-Tips. I have learned ALOT by just observing and playing around with new ideas. I STILL LEARN, because the subject is so vast, I may be an expert in one area and a novice in another.

Post back with any other specific question that you might have.

Skip,

_[red][/red]